PostGis Distance Calculation

Question

I am doing a indoor map navigation application right now and what I am trying to do is to build a database of map point in the building.

All of the coordinate I use is taken from Google Map (which means the EPSG is 3857). What I need to do now is to find distance in meters as well as use D_Within in meters

When I try to extract out the distance between 2 point:

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

For the first 2 row 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)

Result given is:

2.59422435413724e-005
4.11096095831604e-005

Even though they are in rad, the second result is only twice as high as the first one. So it makes me confuse. Then I try to output it as meters:

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

The result given for the same rows is:

2.872546829
4.572207435

Which is not what I expected as well. I am not very familiar with PostGis and SRID so this question might seem simple but please help me out, I am stuck no @@

Answer 1

Your coordinate reference 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 is projected meters based on a spherical globe. Vector sources that are added to Google Maps (KML data, GPS dumps, etc) tend to be in lat/lon, 4326, which is measures in degrees and converted on the fly.

If you want the 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 are 62.74 meters away from each other, based on the same query.

Note there is also ST_Distance_Spheroid which takes a third parameter, the measurement spheroid, ie, an approximation of the earth's shape. This will potentially be more accurate, but probably not significant over small distances.

ST_Distance gives distance in projected coordinates, which is probably why you got strange results plugging in lat/lon values.

EDIT: As noted in the comments, from Postgis 2.2 onwards, this function is renamed ST_DistanceSphere