Limit ST_DWithin() to first candidate found

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);
``````

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