0

Im trying to create a table with distances calculated from points to lines. For that im using the following SQL adapted from BOSTON gis website.

I understood the function seems to search for all candidates within the specified distance, sort them and pick the closest one

Happens that i would like to limit the search when it founds the 1st match within the specified distance. (hoping for faster results)

Should this be done diferently ?

DROP table if exists filter_distance.table_distances_@country;
CREATE table filter_distance.table_distances_@country as (
SELECT DISTINCT ON(g1.gid)  g1.gid As gid, g2.gid As id_lines , round(cast(ST_DistanceSphere(g1.geom,g2.geom) as Numeric), 2) as distance
    FROM filter_distance.points_@country As g1, filter_distance.polys_lines_@country As g2   
    WHERE g1.gid <> g2.gid AND ST_DWithin(g1.geom, g2.geom, 0.00500)   
    ORDER BY g1.gid, ST_Distance(g1.geom,g2.geom) asc); 
4

To limit to one result, simply end the SQL with LIMIT 1

SELECT DISTINCT ON(g1.gid)  g1.gid As gid, g2.gid As id_lines ,     round(cast(ST_DistanceSphere(g1.geom,g2.geom) as Numeric), 2) as distance
FROM filter_distance.points_@country As g1, filter_distance.polys_lines_@country As g2   
WHERE g1.gid <> g2.gid AND ST_DWithin(g1.geom, g2.geom, 0.00500)   
ORDER BY g1.gid, ST_Distance(g1.geom,g2.geom) asc)
LIMIT 1; 

This should give you some speed gains as you are only writing one row to the new table; but not sure how much as it still needs to evaluate the query entirely (i.e. it needs to calculate distance to all features to order the table)

  • @Freigth_Train thanks for the help , however this is not speeding the results , as you said i beleive this still needs to evaluate the query entirely. – Jorge Vidinha Jan 12 '17 at 10:13
  • Only way you can really speed it up is reduce the search area in st_dwithin – Liam G Jan 12 '17 at 10:17

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.