[OSM-dev] Help needed: postgis database transform()

Joerg Ostertag (OSM Tettnang/Germany) openstreetmap at ostertag.name
Mon Dec 1 20:03:42 GMT 2008


Probably my problem boils down to :
	Which SRID can I use to get data into the coordinate system used inside the
	planet.osm?

But here in Detail:
I need a little help in reading from the mapnik posgis database. I plan to 
expand the osmtrackfilter.pl to compare against the local data already 
existing in my mapnik-postgis-database. So I tried to get all street-segments 
inside a bounding box. For this i tried to select from the database. But 
there I have the problem that the coordintesystems are different and I have 
no clue how to convert between them.
I want to operate with coordinates compatible to the planet.osm File.
Something like 
	lat="48.8046469" lon="9.0401476"

So i thought i'll start with
	echo "select osm_id,asText(way) from planet_osm_point limit 5;" | psql gis
Result:
	  osm_id   |                  astext                   
	-----------+-------------------------------------------
	 293106148 | POINT(-2876147.15643226 4560658.25114169)
	 293105894 | POINT(-2874584.97662211 4563768.28055896)
	 293107003 | POINT(-2871694.69962705 4559831.77530905)
	 292977203 | POINT(-2871581.82166339 4556767.11400322)
	 292976398 | POINT(-2870606.29556972 4556208.15857941)
	(5 rows)

Well at least I get some data from the database. 
Then the next step is to get transformed Data in the coordinate system I 
desire ... I did remember WGS-84 was SRID 4326, but either I'm wrong or 
whatever. Doing the following 
	echo "select osm_id,asText(transform(way,4326)) from planet_osm_point limit
	         5;" | psql gis
Results is an:
	ERROR:  AddToPROJ4SRSCache: Cannot find SRID (4326) in spatial_ref_sys


Well I thought I'll have a look into this in "spatial_ref_sys".... but ....
	echo "select * from spatial_ref_sys;" | psql gis
Result:
	 srid | auth_name | auth_srid | srtext | proj4text 
	------+-----------+-----------+--------+-----------
	(0 rows)

Then I found SRID 900913 inside osm2pgsql and tried:
	echo "select osm_id,asText(transform(way,900913)) from planet_osm_point limit
	 5;" | psql gis
Result:
	  osm_id   |                  astext                   
	-----------+-------------------------------------------
	 293106148 | POINT(-2876147.15643226 4560658.25114169)
	 293105894 | POINT(-2874584.97662211 4563768.28055896)
	 293107003 | POINT(-2871694.69962705 4559831.77530905)
	 292977203 | POINT(-2871581.82166339 4556767.11400322)
	 292976398 | POINT(-2870606.29556972 4556208.15857941)
	(5 rows)

Well, this seems to be the same SRID which is used inside the Database. 

So my Question is:
	Which SRID can I use to get into the coordinate system I want?


Jörg

http://www.ostertag.name/
-- 
Jörg (Germany, Tettnang)

http://www.ostertag.name/




More information about the dev mailing list