10

I have a shapefile of polygons and single (point) location.

How can I retrieve the closest polygon to that single location?

enter image description here

3
  • 1
    Any illustration can be a plus
    – elmo
    Commented Aug 21, 2015 at 13:26
  • @elmo Please see my Edit
    – michael
    Commented Aug 21, 2015 at 13:29
  • 1
    this question is not really a duplicate of gis.stackexchange.com/questions/14456. That question is about finding closest Geometry in the same table. This question is about finding the closest geometry in a different table.
    – tinlyx
    Commented Mar 23, 2018 at 9:13

3 Answers 3

16

Several ways to do this. One is to find the distance and order ascending.

SELECT geom
FROM polys, points 
WHERE points.gid = 1
  AND ST_DWithin(poly.geom, points.geom, 1000) 
ORDER BY ST_Distance(a.geom, b.geom)
LIMIT 1;

I added the ST_DWithin() call to show how you might limit the number of candidates (it will make use of the index.)

1
  • I use the same method but it is very slow Commented Oct 18, 2019 at 14:47
8

I use ST_ClosestPoint() to return the closest point from each polygon, then I calculate the min distance

SELECT foo.*
FROM (
      SELECT MIN(ST_Distance(a.geom, ST_ClosestPoint(b.geom, a.geom)))
      FROM polyg a, point b
      ) foo
3
  • 1
    Thanks, but the result is the minimum distance, While I need the geom representing the closest polygon.
    – michael
    Commented Aug 21, 2015 at 13:59
  • 4
    seems like your question refer to this link gis.stackexchange.com/questions/14456/…
    – elmo
    Commented Aug 21, 2015 at 15:06
  • 2
    could also use ST_ClosestPoint(), and find any polygons which ST_Intersects() the closest point
    – Steven Kay
    Commented Aug 21, 2015 at 15:36
4

This is a follow up to ELMOs answer - which solved my problem but it took a bit of searching and trial and error to realise its a nearest neighbor problem that has a neat solution I'm posting this so theres some examples that users can see In ArcGIS I would have used AnalysisToolbox>Proximity>Near Which takes about 9 minutes

I wanted to use PostGIS to identify the shortest distance from a grid of points representing possible wildfires that a helicopter would waterbomb to a water source represented by a polygon layer.

There's 35000 points (called cell_id) and 140,000 hydrological features

If I used the following query

CREATE TABLE data_input_oct2021.cellid_rw_water_dist AS
SELECT 
  b.cell_id,
  MIN(ST_distance(b.geom,ST_ClosestPoint(a.geom,b.geom))::BIGINT)as dist_m 
FROM 
  public.allhydroarea_final as a,
  data_input.cellid_xy_st as b
GROUP BY 
  b.cell_id
ORDER BY
  b.cell_id;

it took about 5.5 hours to produce the answer

A bit of research shows its a nearest neighbor problem and theses some nice articles and documentation around

The final query was

CREATE TABLE data_input_oct2021.cellid_rw_water_dist AS  
SELECT 
  b.cell_id AS cell_id,
  ST_Distance(a.geom, b.geom)::BIGINT AS dist_m
FROM 
  data_input.cellid_xy_st b
CROSS JOIN LATERAL (
  SELECT a.geom, a.gid
  FROM public.allhydroarea_final a
  ORDER BY a.geom <-> b.geom
  LIMIT 1
) a;

This takes 7 minutes 25 seconds Better than ArcGIS

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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