1

I've been having issues converting OpenStreetMap geometries to valid points in PostGIS.

MY PostGIS table has the this definition for the geom column:

SELECT AddGeometryColumn('node','geom',900913,'POINT',2);

Using ST_GeomFromText, all points I enter into this table have points out in the middle of nowhere.

I've isolated one geom for investigation (the lat/lon below is from the OpenStreetMap .osm file):

SELECT ST_GeomFromText('POINT(53.3302090 -6.2642905)', 900913);

I've also tried:

SELECT ST_SetSrid(ST_MakePoint(53.3302090,-6.2642905),900913);

The point produced is no nowhere near land (when displayed in QGIS, also using 900913).

However, I have tested the lat/lon above in a website: http://www.latlong.net/Show-Latitude-Longitude.html, and it correctly identified a point in Dublin, Ireland.

I am assuming the lat/lon is in the WKT format?

2

"900913" (which spells GOOGLE) is a now defunct SRID that was changed to EPSG 3875. Furthermore, the map units of this Mercator grid are in meters, not degrees. If I understand correctly your problem, the srid you should use is 4326 (wgs 84, with decimal degrees) so try using that instead of 900913.

1

Understood about 900913 being superseded, but it should still convert correctly, as it's supported in PostGIS and QGIS.

But the key was the advice about using the SRID 4326 for the OSM lat/lon. I also realised I didn't understand the correct way to call the function, the upshot being the following statement works (though I can't confirm it's the best way):

ST_Transform(ST_GeomFromText('POINT(-6.2642905 53.3302090)', 4326), 900913));

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.