10

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 @@

Thomas Dang
  • 201
  • 3
  • 4
  • 14

2 Answers2

23

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

John Powell
  • 12,253
  • 6
  • 59
  • 67
  • Thanks for your reply. I did try 4326 as well but not much different from 3857. For the distance of the first set, it is 3 meters away, since it is inside my office so I know it quite well. I also try to calculate it from here : http://boulter.com/gps/distance/ - same result, 3 meters. – Thomas Dang Jul 08 '14 at 06:14
  • What do you mean by not much different? I would say 7.45 meters and 62.74 are quite different. It is st_distance_sphere that is the issue, though, as this takes input in lat/lon, where st_distance takes distance in projected coordinates. 3857 and 4326 are radically different, being meters and degrees respectively, but you have to plug them into the correct functions to get the right answers :D – John Powell Jul 08 '14 at 06:16
  • Sorry, just go out and try measuring, around 7 meters is quite correct. Thanks for the help :) – Thomas Dang Jul 08 '14 at 06:24
  • @ThomasDang. Use st_distance_spheroid if you want to be really accurate, but st_distance_sphere should work OK for small distances. I didn't realize Manila was so close to the equator. – John Powell Jul 08 '14 at 06:27
  • The function name 'ST_Distance_Sphere' has changed to 'ST_DistanceSphere' (underscore removed) from postgis version 2.2.0 onwards. Refer: https://postgis.net/docs/ST_DistanceSphere.html – Abhishek Shah Nov 20 '20 at 07:44
0

Here is the pseudocode

SELECT ST_Distance_Sphere(ST_MakePoint(lng1, lat1),ST_MakePoint(lng2, lat2));

Also, for sphere distance new function "ST_DistanceSphere" use the following

SELECT ST_DistanceSphere(ST_MakePoint(lng1,lat1),ST_MakePoint(lng2,lat2))
Amir Md Amiruzzaman
  • 1,911
  • 25
  • 24