PostGis distance calculation

Question

Now I am making a closed navigation application, and I am trying to create a map point database in a building.

All used coordinate is taken from Google Map (this means that EPSG is 3857). Now I need to find the distance in meters, and also use D_Within in meters

When I try to extract the distance between two points:

SELECT ST_DISTANCE( ST_GeomFromText('POINT(' || StartLon || ' ' || StartLat || ')',3857), ST_GeomFromText('POINT(' || EndLon || ' ' || EndLat || ')',3857)) FROM i3_building.floordata; 

For the first 2 lines with:

 Start: 103.776047 1.292149; End: 103.77607 1.292212 (3 meters away) Start: 103.776070 1.292212; End: 103.77554 1.292406 (50 meters away) 

The result obtained:

 2.59422435413724e-005 4.11096095831604e-005 

Despite the fact that they are in parliament, the second result is only two times higher than the first. Therefore, it confuses me. Then I try to output it as counters:

 SELECT ST_DISTANCE( ST_GeographyFromText('POINT(' || StartLon || ' ' || StartLat || ')'), ST_GeographyFromText('POINT(' || EndLon || ' ' || EndLat || ')')) FROM i3_building.floordata; 

Result for the same lines:

 2.872546829 4.572207435 

This is not what I expected. I am not very familiar with PostGis and SRID, so this question may seem simple, but please help me, I am stuck without @@

Answer 1

Your coordinate system (CRS) is 4326, lat / lon. This is a common source of confusion with Google Maps: 3857 is the CRS used by Google Maps for its tiles, and projected meters based on a spherical globe. Vector sources added to Google Maps (KML data, GPS dumps, etc.) are usually located in lat / lon, 4326, which is measured in degrees and converted on the fly.

If you need a distance in meters between two lat / lon points, use ST_Distance_Sphere . For example, for your first set of points

 select st_distance_sphere(st_makepoint(103.776047, 1.292149),st_makepoint(103.77607, 1.292212)); 

which gives 7.457 meters. Your second set of points is at a distance of 62.74 meters from each other, based on the same request.

Note that there is an ST_Distance_Spheroid that takes a third parameter - a spherical measuring device, i.e. approximation of the shape of the earth. This will be potentially more accurate, but probably not significant at short distances.

ST_Distance gives the distance in the predicted coordinates, which is probably due to the fact that you got strange results, including lat / lon values.