Search code examples
sqlpostgresqlpostgisspatial

Postgres ST_Distance best performance query who finds closest points

Currently, I have this query

SELECT "Id" 
FROM "Point" 
WHERE ST_Distance(ST_SetSRID(ST_Point("Longitude", "Latitude"), 4326)::geography, 
                  ST_SetSRID(ST_Point(@longitudeOfA, @latitudeOfA), 4326)::geography) <= @MaxDistance 
LIMIT 1

As you see I do not keep spatial data in my point table, but rather create it on query select statement(ST_Point) with Latitude and Longitude columns.

The purpose of this query is to check if there is at least one point of the radius @MaxDistance.

I am afraid that on every record when doing this select query I create ST_Point.

At least know there are not many records, so performance would be enough, but I am afraid it will become very expensive when there will be many Point records because of the ST_Pin creation on every record.

For some limitations, I can't create Spatial Data on Point table.

Solution

  • This query cannot be efficient with a large number of points. Not because it has to create the geographies, but because st_distance does not make use of spatial indexes.

    I would personally create a geometry or a geography as it makes it easy to work (PostGIS functions) with them.

    What is important is the spatial index. In you case, without a geometry/geography column, you would create a function index

    create index spatialIDX on mytable using gist(ST_SetSRID(ST_Point("Longitude", "Latitude"), 4326)::geography);

    And then change the query to use ST_DWithin which makes use of the index.
    Don't forget to order by distance too, else you would get a random point within the desired distance. (Note that the <-> operator uses the index, but it must be created on a geometry field, not a geography. You might as well rely on st_distance in the ordering clause. Should you have little points to be ordered, either is fine. Else, create an index on the geometries)

    SELECT "Id" 
FROM "Point" 
WHERE ST_DWithin(ST_SetSRID(ST_Point("Longitude", "Latitude"), 4326)::geography, 
                 ST_SetSRID(ST_Point(@longitudeOfA, @latitudeOfA), 4326)::geography,
                 @MaxDistance) 
ORDER BY  ST_SetSRID(ST_Point("Longitude", "Latitude"), 4326) <-> ST_SetSRID(ST_Point(@longitudeOfA, @latitudeOfA), 4326)
LIMIT 1;