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

Dave Stubbs osm.list at randomjunk.co.uk
Mon Dec 1 20:27:42 GMT 2008


2008/12/1 Joerg Ostertag (OSM Tettnang/Germany) <openstreetmap at ostertag.name>:
> 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




More information about the dev mailing list