postgis / proj 900913 to 4326 projection issues with Y coordinate

Question

this is an oldie, but i cannot seem to find a solution.

When i want to do an st_transform on a 900913 coordinate to a 4326 system, the y coordinate shifts.

example:

SELECT AsText( Transform( Transform( GeomFromText( 'POINT( 449760.25168159 6790560.4594059 )', 900913), 4326 ), 900913 ) )

here the original 900913 stating point is st_stransformed to 4326 and back to 900913. the result is not the original point, y differs. (i will insert the result later, i don't have it here).

i tried altering the proj4text for 4326, adding +nadgrids=@null like i read somewhere

the proj4text for srid 4326 is currently:

"select proj4text from spatial_ref_sys where srid=4326" +proj=longlat +ellps=WGS84 +datum=WGS84 +no_defs

the proj4text for srid 900913 is currently:

"select proj4text from spatial_ref_sys where srid=900913" +proj=merc +a=6378137 +b=6378137 +lat_ts=0.0 +lon_0=0.0 +x_0=0.0 +y_0=0 +k=1.0 +units=m +nadgrids=@null +no_defs

i also tried doing a projection from 900913 to another projection to 4326, but i get the exact same point as a direct transformation from 900913 to 4326.

anyone any ideas?

EJ

Darafei Praliaskouski Darafei Praliaskouski · Accepted Answer · 2017-11-24T12:29:35

EPSG:900913 is ill-defined projection. You should use EPSG:3857 instead, which should be exactly the same Spherical Mercator but a standardized one.

Any reprojection is a lossy operation. Converting a coordinate back and forth is going to make noise in last binary digits, which are usually sub-millimeter error.

postgis / proj 900913 to 4326 projection issues with Y coordinate

2 Answers