2

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 + ", "
2
  • 1
    if you really want a point of your raw data array to be the closest. Try cast your data in multipoints instead of linestring. only use ST_closesestPoint one and ask ST_X and ST_y of the result (here you are doing twice the heavy process of looking for the closestpoint). Not sure St_Dwithin will do much better ... Oct 26 at 8:54
  • On a side note, coordinates should be longitude first, then latitude. Yours are swapped.
    – JGH
    Oct 26 at 15:58
1

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

1
  • 1
    On a side note, coordinates should be longitude first, then latitude. Yours are swapped.
    – JGH
    Oct 26 at 15:58
0

With thanks to Maximilien jaffrès, the final solution ended up like this:

WITH raw_data_roads AS
( SELECT ST_Collect(ARRAY[ST_SetSRID(ST_POINT(51.056263076711446,3.7270707882305545), 4326),ST_SetSRID(ST_POINT(51.05618577685494,3.4272373015303986), 4326),ST_SetSRID(ST_POINT(51.055975179565884,3.8276880582826224), 4326)]) as line),

res AS (
    SELECT ST_ClosestPoint(raw_data_roads.line,ST_SetSRID(ST_Point(51.05484314, 3.73177228), 4326)) AS geom
    FROM raw_data_roads
)
SELECT ST_X(res.geom) , ST_Y(res.geom) FROM res

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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