2

I have a table where each row has a point Geometry(POINT(...)) [long lat]. These geometries don't specify an srid. Which means we're using a cartesian plane to do the calculations. All of this data is in the contiguous United States.

My goal: Given a point I want to find the nearest point to it. I'm trying to use ST_DWithin to achieve this. I set the distance parameter to .0018. I still see issues where the location that gets returned is like 60 miles or more away from the location I want. If I use ST_Equals I almost never get a hit.

Requirements:

  • Needs to be well under 1 sec
  • Accuracy is important but there's some margin for error

Assumptions:

  • There's only a couple thousand locations
  • SRID -1
  • points are stored as geometries, long lat
  • point field is a geospatial index

I feel like my accuracy issues aren't as much related to the degrees [.0018] but the fact we're not using an srid like 4326 in my query.

What are some ways I can use this location data without an srid to get as accurate as possible hit in my database? Can I convert the srid in the query itself to improve accuracy.

I'm also trying to put together some evidence to advocate for this switch over on my team. But in the mean time I need to work with the data I have.

    SELECT
        location.id,
    FROM
        location
    WHERE
        location.point IS NOT NULL
        AND ST_DWithin(
            location.point,
            ST_GeomFromWKB(input_point),
            0.0018
        )
6
  • 4
    You should always be using a valid CRS with spatial data. If you have lon/lat data, use 4326 unless you know it's a different GCS. Never ever do distance calculation in Cartesian degrees.
    – Vince
    Nov 10, 2023 at 21:29
  • Totally understand, but this is the data I have. Is there a way to adjust the query to use 4326 even thought the points are stored without a srid? Nov 10, 2023 at 22:03
  • 2
    Update the points. It's insane to keep bad data.
    – Vince
    Nov 11, 2023 at 2:47
  • I'm coming here to learn why it's "insane". From what I understand, without the srid set we need to convert to a coordinate system in all our queries to get accuracy but this adds latency. Nov 11, 2023 at 5:26
  • 4
    Beside being best practices, setting or not the SRID of all geometries to the same SRID won't change anything to the result of st_dwithin. Computing distances with unprojected data (or with unsuitable projection such as 3857) is part of your issue. But even then, 60 miles is roughly 1 degree so there is no way that 2 locations gets associated with a 0.0018 distance threshold. This tends to lat/long swap, different coordinate systems or other bad data issues. Please show the 2 locations that are 60 miles away but still found.
    – JGH
    Nov 11, 2023 at 11:06

1 Answer 1

5

tl;dr:

Given your setup, the most optimized, accurate query would be

SELECT
  *
FROM
  location
ORDER BY
  ST_SetSRID(location.point, 4326)::GEOGRAPHY <-> ST_SetSRID(input_point, 4326)::GEOGRAPHY
LIMIT
  1
;

To get from O(n) to roughly O(log N) complexity, you want to add a coverage index:

CREATE INDEX
  ON location
  USING GIST ( CAST ST_SetSRID(point, 4326) AS GEOGRAPHY )
;

Why is this "insane"?

Imagine yourself having a bank of some sort, storing balances in your DB without the actual currency information. You just assume, say, USD.

John deposits USD 1000 - balance value in DB is set to 1000 - and comes back tomorrow to withdraw 1000 moneys, for which you assume USD - balance value in DB is set to 0. All good.

But what happens if Carlos deposits ARS 300000 and withdraws 300000 moneys the next day?


Consider two points sitting in your table without SRID:

  • POINT(0.0 46.0)
  • POINT(1.3 45.0)

Are they off the coast of Ghana (e.g. in EPSG:3857), or 5000km up North in France (e.g. in EPSG:4326)?

Now you get another point sent in from the frontend

  • POINT(0 5621521.49)

Is this point some 5620000 meters away from those in your DB, or only some 80000 meters after a correct reprojection? But between what projection systems?

This point above could have been projected in EPSG:3857- pretty common. Now you get the same point sent in referenced in EPSG:4326

  • POINT(0.0 45.0)

for which you want to find the closest of the two stored.

In Cartesian math, they are 1 degree and 1.3 degree away, respectively. Easy choice.

However:
The 1.3 degree longitudal surface distance at that latitude is less than the 1 degree latitudal surface distance! You got the wrong point.


This can be avoided by simply adding the SRID to your geometry column, allowing PostGIS functionality to work properly with different systems. This would be a simple:

ALTER TABLE <table>
  SET COLUMN <geom> TYPE GEOMETRY(POINT, 4326)
    USING ST_SetSRID(<geom>, 4326)
;

You can now safely use all functions and handle different projections natively.

But there is no projection suitable for distance measurements, covering all of mainland US...
In order to use geographic coordinate systems globally for any kind of measurements - as well as having the highest accuracy - you also want to consider using the GEOGRAPHY data type. Due to the more complicated math this comes at a small price in performance, but that is likley neglectable for individual input points.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.