I have a shapefile of polygons and single (point) location.
How can I retrieve the closest polygon to that single location?
Geographic Information Systems Stack Exchange is a question and answer site for cartographers, geographers and GIS professionals. It only takes a minute to sign up.Sign up to join this community
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.)
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
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