Finding closest polygon to point in PostGIS

Question

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

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

this question is not really a duplicate of https://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, Mar 23 '18 at 09:13

score 16 · Accepted Answer · edited Oct 08 '21 at 06:15

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.)

edited Oct 08 '21 at 06:15

Taras

32,823
4
66
137

answered Aug 21 '15 at 16:05

Rob Skelly

3,101
15
14

I use the same method but it is very slow – Roberto Marzocchi Oct 18 '19 at 14:47

score 8 · Answer 2 · edited Oct 08 '21 at 06:17

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

edited Oct 08 '21 at 06:17

Taras

32,823
4
66
137

answered Aug 21 '15 at 13:47

elmo

809
5
19

1

Thanks, but the result is the minimum distance, While I need the geom representing the closest polygon. – michael Aug 21 '15 at 13:59
4

seems like your question refer to this link http://gis.stackexchange.com/questions/14456/finding-the-closest-geometry-in-postgis – elmo Aug 21 '15 at 15:06
2

could also use ST_ClosestPoint(), and find any polygons which ST_Intersects() the closest point – Steven Kay Aug 21 '15 at 15:36

score 3 · Answer 3 · edited Oct 08 '21 at 04:31

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

Finding closest polygon to point in PostGIS

3 Answers3