Assigning a nearest polygon to a point

Question

I have a point layer of households and a polygon layer of tax lots. I want to assign a nearest tax lot polygon to each household point. Note that as the household points are coded on road, not within a tax lot, ST_Within does not work properly. Do you have any suggestions?

What if you have lots on both side of the road? How do you know which one is the right one for the point? — Jakub Kania, Apr 30 '13 at 10:34

score 2 · Answer 1 · answered Apr 29 '13 at 22:58

Assuming that your points are all within a certain distance of their nearest parcels, something like this will work.

WITH parcelcandidates AS (
  SELECT p.id AS p_id, h.id AS h_id 
  FROM households h JOIN parcels p 
  ON ST_DWithin(p.geom, h.geom, 100)
  ORDER BY p.id, ST_Distance(p.geom, h.geom)
)
SELECT DISTINCT ON (p_id) p_id, h_id 
FROM parcelcandidates;

The first clause sets up a limited set of parcels and households, ordered by distance and grouped within parcel id, and the second clause just pulls the first element (the closest one) off of that list for each distinct parcel.

Assigning a nearest polygon to a point

1 Answers1

Linked