2008/12/1 Joerg Ostertag (OSM Tettnang/Germany) <[EMAIL PROTECTED]>: > 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? > >
OK, step 1 is to populate your spatial_ref_sys table with the default projections. Postgis comes with this as an sql file you'll need to run in your database. On my system: psql gis < /usr/share/postgresql-8.3-postgis/spatial_ref_sys.sql That includes the definition for srid 4326. Unfortunately it doesn't include the definition for 900913, so transform still won't work. So step 2 is to add that definition to spatial_ref_sys. You can find the insert statement for this here near the bottom: http://trac.openlayers.org/wiki/SphericalMercator Your original SQL should then work as you expect, ie: echo "select osm_id,asText(transform(way,4326)) from planet_osm_point limit 5;" | psql gis osm_id | astext -----------+--------------------------------------- 249399325 | POINT(-179.99 -28.0833405999994) 262907788 | POINT(-179.9725899 -16.8389637999991) 249399407 | POINT(-179.9 -5.6416666999995) 240485015 | POINT(-179.8808896 -16.6881733999991) 242554118 | POINT(-179.8808154 -16.6877606999991) (5 rows) Dave _______________________________________________ dev mailing list [email protected] http://lists.openstreetmap.org/listinfo/dev
