I want to canvas the community to see if my nearest neighbor query can be optimized at all. I am happy with my current results, but I'm always up for improving. I have 2 tables, lakes and towns. I want to get the nearest town to each lake within 0.5 degrees. I use a calculation to get a cardinal direction output as an integer (ceil function). Below is the query. Any other suggestions are also welcome.

SELECT DISTINCT ON (lakes.wkb_geometry) lakes.wkb_geometry, lakes.ogc_fid AS lakes_id, lakes.name AS lakes_name, 
towns.ogc_fid AS towns_id, upper(towns.name) AS towns_name,
ceil(degrees(ST_Azimuth(lakes.wkb_geometry, towns.wkb_geometry))/90) AS card_dir
FROM lakes, towns
WHERE ST_DWithin(lakes.wkb_geometry, towns.wkb_geometry, 0.5)
ORDER BY lakes.wkb_geometry, ST_Distance(lakes.wkb_geometry, towns.wkb_geometry);


Your Answer

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

Browse other questions tagged or ask your own question.