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?

Answer 1

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

Answer 2

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

Answer 3

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