CODEWITHSUNDEEP
postgresqlpostgisdistancelatitude-longitude
Ronald
Ronald

Reputation: 89

Calculate distance between 2 points (approximately)

Using PostGis 3.0, Postgres 12.4, I'm trying to find the distance bewteen two points in the Netherlands. According to Google Maps, this should be around 5 Km (straight line, see image below). The functions below all give about 8 Km, which is pretty far off.

The calcDistance stored procedure I found somewhere does a pretty decent job (5 Km), but that's a pretty slow way when querying a table with a lot of records, sorted on the least distance from a random point somewhere.

I've tried to find the right spheroid, but I can't seem to find the right solution. Should I use some other spheroid ('SPHEROID["WGS 84", 6378137, 298.257223563]' was the only one I could find) for distance between longitude lines in the Netherlands (latitude > 52.0)?

Outcome of query

SELECT

    "calcDistance"(52.2209471, 6.8945723, 52.221687, 6.969117) as calcDistance,

    st_distancespheroid('POINT(52.2209471 6.8945723)'::geometry,
                        'POINT(52.221687 6.969117)'::geometry,
                                'SPHEROID["WGS 84", 6378137, 298.257223563]')/1000 as distancespheroid,

    st_distancesphere('POINT(52.2209471 6.8945723)'::geometry,
                        'POINT(52.221687 6.969117)'::geometry) as distancesphere,

    st_distance(
            ST_GeographyFromText('POINT(52.2209471 6.8945723)'),
            ST_GeographyFromText('POINT(52.2216870 6.9691170)')
    );

This is what Google Maps tells me:

enter image description here

Upvotes: 1

Views: 2754

Answers (2)

Jim Jones
Jim Jones

Reputation: 19623

You switched x and y. It is longitude, latitude, not the other way around.:

SELECT ST_Distance('POINT(6.8945723 52.2209471)'::geography,
                   'POINT(6.969117 52.221687)'::geography);

 st_distance  
---------------
 5094.96164985
(1 Zeile)

Demo: db<>fiddle

Upvotes: 1

Laurenz Albe
Laurenz Albe

Reputation: 246598

The coordinates you show are not in the Netherlands, but in the sea somewhere off the Horn of Africa. Don't forget that in PostGIS, longitude is the first coordinate.

If you swap the coordinates, things will start to look better:

SELECT st_distance(
          ST_GeographyFromText('POINT(6.8945723 52.2209471)'),
          ST_GeographyFromText('POINT(6.9691170 52.2216870)')
       );

  st_distance  
═══════════════
 5094.96164985
(1 row)

Upvotes: 1

Related Questions