Incorrect srid in postgis

Question

I downloaded .shp files from Diva-Gis (EPSG:4326) representing the map of Germany in order to use them in Mapbox Studio. I did conversion via ogr2ogr like that:

ogr2ogr -f "PostgreSQL" PG:"host=localhost user=michalvantuch dbname=postgis_database" -t_srs EPSG:900913 -nlt PROMOTE_TO_MULTI /Users/michalvantuch/Desktop/skuska\ ogr2ogr/DEU_adm0.shp

I want to have coordinates in geometric system with SRID 900913. But what I've got instead is 900914 in geometry_columns. I want to do something like getting the shortest distance between two cities in (k)m

SELECT ST_DISTANCE(                                                      
 ST_GeometryFromText('POINT(980421.735630649 6997024.59849449)'),
 ST_GeometryFromText('POINT(1113063.06112784 7085437.74331884)')
);

but so far it gives me nonsense value.

Answer 1

The unit of measure for 900913 or EPSG:3857 is not a meter. Please do not use this projection for measuring distances. This is a sphere-based Mercator projection of ellipsoid coordinates, which throws conformality out the window.

The unit of measure is technically a "non-Earth" meter, and the map gets stretched as you move further away from the equator.

(The length of Greenland is approx. 2,400KM, but in 900913/EPSG:3857/Spherical/Web Mercator projection shown above, it's over four times that!)

The unit of measure is not consistent throughout the map, so if you try and measure distances or areas with this projection, you're going to have a bad time.

If you are looking for a projection that will cover Germany and give you accurate distances between cities, EPSG:5243 is a much better bet.

Answer 2

900913 was the Google Mercator SRID. The new one is 3857. The spatial_ref_sys table in my installation of PostGIS no longer contains 900913, only 3857 remains.

Answer 3

You'll need to pass in the SRID to ST_GeometryFromText

SELECT ST_DISTANCE(                                                      
 ST_GeometryFromText('POINT(980421.735630649 6997024.59849449)',900913),
 ST_GeometryFromText('POINT(1113063.06112784 7085437.74331884)',900913)
);

The result will be in meters, albeit distorted.

You can use this statement to check your system table:

 SELECT *
FROM spatial_ref_sys
WHERE srtext like '%Popular%';

This result shows 4055 as the latest SRID where 3785 and 900913 are deprecated in PostGIS v2.2.