0

I am using PostGIS to do some lat and lon distance calculations for me in an SQL query. It returns the distance between two lat and lon positions but the unit it returns is the distance in lat and lon as the crow flies:

"SELECT ST_Distance(ST_MakePoint(\"Users\".\"longitude\",\"Users\".\"latitude\"), ST_MakePoint($1,$2)) FROM \"Users\" WHERE \"Users\".\"username\"=$3"

Example input:

Lat & Lon 1 -> -0.186768 51.450584

Lat & Lon 2 -> -0.572662 51.245584

Gives: 0.436965878800621

So my question is how to turn that 0.436965878800621 into e.g. 1000m

I'm not sure if Calculate distance between 2 GPS coordinates would help me? Or whether this is starting at too early / late a point in the calculation.

Thanks

Community
  • 1
  • 1
lol
  • 9
  • 1
  • 5

2 Answers2

2

You can convert the geometry that is produced by ST_MakePoint() to a geography type and the ST_Distance() will produce its output in meters:

SELECT ST_Distance(geography(ST_MakePoint("Users"."longitude", "Users"."latitude")),
                   geography(ST_MakePoint($1, $2)))
FROM "Users"
WHERE "Users"."username" = $3
Mike T
  • 41,085
  • 18
  • 152
  • 203
Patrick
  • 29,357
  • 6
  • 62
  • 90
0
var R = 6371000; // metres
var φ1 = lat1.toRadians();
var φ2 = lat2.toRadians();
var Δφ = (lat2-lat1).toRadians();
var Δλ = (lon2-lon1).toRadians();

var a = Math.sin(Δφ/2) * Math.sin(Δφ/2) +
        Math.cos(φ1) * Math.cos(φ2) *
        Math.sin(Δλ/2) * Math.sin(Δλ/2);
var c = 2 * Math.atan2(Math.sqrt(a), Math.sqrt(1-a));

var d = R * c;

this is in javascript. just to give you an idea - you can alter it.

1''
  • 324
  • 1
  • 10
  • Hi, yes I'd seen that calculation, but can you tell me how to turn that calculated value into metres, the above seems to do that from latitude and longitude which I don't have at the calculation stage. – lol Jul 28 '15 at 02:18
  • @lol you showed an example input of lat and long values?? What do you mean? The `0.436965878800621` number? – 1'' Jul 28 '15 at 02:22