Recalculating distance table - is there fast (fast and dirty OK) way?

Question

I have a system that is based on PostGIS that holds a set of coordinates. It serves a lot of user requests where users send their own current coordinates and receive a set of points that are located nearby, like 5-10 km. Each time the DB pulls all points and calculates the distance between points.

Is there is a way to use a simpler way of calculation, like counting all distances to the base point, and then using some matrix transformation? Or maybe I am missing some PostGIS functionality?

UPD: The point info is stored this way:

ST_GeomFromGeoJSON('{"type":"Point","coordinates":[28.119871,48.542976]}')::geometry

The resulting SQL is really big, but the distance is calculated like:

ST_Distance(ST_Transform("fuelStation"."location",9015), ST_Transform(ST_GeomFromGeoJSON([user entered coordinates]),9015))

Maybe I need to clarify the question - the current realization works +- fast, but I wonder is there a way to "cache" distances table from 1 point and then recalculate when the point changes?

Welcome to GIS SE. As a new user, please take the [Tour]. Please [Edit] the Question to include your table definition, a list of indexes, an indication of the row count, the SQL query you are using, and the EXPLAIN plan. There is a likely efficiency to be gained, but we need to know where you are starting. — Vince, Feb 07 '22 at 14:59
Wow, okay, you can't get any slower unless you move storage to a USB 2.0 thumb drive. Right now you just need to follow best practice: +Store data in the projection in which it will be used, +Build a spatial index, +Use ST_DWithin to limit search distance to probably candidates. But this is all for naught if the table is tiny (say, under 100k rows). See https://gis.stackexchange.com/questions/247113/setting-up-indexes-for-postgis-distance-queries/247131#247131 — Vince, Feb 07 '22 at 18:24

score 1 · Accepted Answer · answered Feb 08 '22 at 20:26

This has a few failure modes because it's doing the KNN in cartesian space, but without re-organizing how you manage projections in your system (what your storage SRS is, what your in/out SRS is) it is a rough'n'ready answer.

CREATE INDEX fuelStation_location_x 
  ON "fuelStation" 
  USING GIST (location);
SELECT f.*, ST_Distance(f.location::geography, ST_GeomFromGeoJSON($text)::geography) AS distance
  FROM "fuelStation" f
  ORDER BY location <-> ST_GeomFromGeoJSON($text)
  LIMIT 5;

Recalculating distance table - is there fast (fast and dirty OK) way?

1 Answers1