PostGIS – Finding Nearest Point of Route with ST_DWITHIN Function

distancepostgisproximityst-dwithin

I'm new to PostGIS and distance comparisons and am having some problems with my current project. I have a an array of geopoints (long lat) which correspond to a route. And I need to find the nearest point of that route compared to my current position (long lat). I made a query using ST_ClosestPoint() but this is too slow due to the performance and gives me a point on the route and not specifically one of the geo array that I can use to link to other data.

SELECT
    ST_X (ST_ClosestPoint(line,pt)) AS latitude, 
    ST_Y (ST_ClosestPoint(line,pt)) AS longitude 
FROM (
    SELECT
        'POINT({latitude} {longitude})'::geometry AS pt,
        'LINESTRING ({streetLineString})'::geometry As line
    ) AS foo;

I have read that using ST_DWITHIN() should be higher performance due to index usage, but I do not understand how to change my current query to implement this.

NOTE: streetLineString is a cast due to the original table being an array of objects which contain an array of geopoint objects. It is transformed with a for each loop:

streetLineString += geopoint.Latitude + " " + geopoint.Longitude + ", "

Best Answer

I will propose this query :

with raw_data_roads as
( select ST_Collect("inputstreetpoints") as line),
res as (
    select ST_ClosestPoint(raw_data_roads.line,ST_SetSRID(ST_Point({latitude}, {longitude}), 4326)) as geom
    from raw_data_roads
)
select ST_X(res.geom) , ST_Y(res.geom) from res

You have two things better with this :

  • I use a MultiPoint format for your point array dataset, so the result will be one of your original point and not some point along the road the dataset represent
  • Only use St_ClosestPoint once, because if you use it twice, then you pay the price twice

I don't know if St_Dwithin will help you a lot in your case

Related Question