2

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.

3 Answers 3

2

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.

Greenland

(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.

1
  • The unit of measure is a meter, but measured distances won't match "ground" distances due to the distortion of the map projection.
    – mkennedy
    Commented Jul 20, 2016 at 22:00
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.

3
  • 2
    In addition, the spherical Mercator is not suitable for distance and area measurements.
    – user30184
    Commented Jul 20, 2016 at 20:36
  • and how should I find suitable SRID for it to convert into ? I'm new to gis and obviously struggling with this Commented Jul 20, 2016 at 20:44
  • If you are planning to work with worldwide data, study the PostGIS geography postgis.net/docs/manual-2.0/…
    – user30184
    Commented Jul 20, 2016 at 20:58
0

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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