Point-In-Polygon
- Find Points contained in Polygons, keeping attributes
- Count kinds of Points in Polygons
- Optimize Point-in-Polygon query by evaluating against smaller polygons
- Optimize Point-in-Polygon query by gridding polygons
- Find smallest Polygon containing Point
- Find Points NOT in Polygons
- Find Point in Polygon with greatest attribute
- Find Polygon containing Point
- Count Points in Polygons with two Point tables
Find Points contained in Polygons, keeping attributes
https://gis.stackexchange.com/questions/354319/how-to-extract-attributes-of-polygons-at-specific-points-into-new-point-layer-in https://gis.stackexchange.com/questions/260055/speed-up-st-intersects-with-non-overlapping-polygons-in-postgis/473319#473319
Solution A simple query to do this is:
SELECT pt.id, poly.*
FROM points pt
JOIN polygons poly ON ST_Intersects(poly.geom, pt.geom);
Caveat: this will return multiple records if a point lies in multiple polygons.
For efficiency, or because the polygons are known to be overlapping, it is useful to ensure only a single record is returned per point. Use JOIN LATERAL and LIMIT 1 to do this:
SELECT pt.id, poly.*
FROM points pt
LEFT OUTER JOIN LATERAL
(SELECT * FROM polygons poly
WHERE ST_Intersects(poly.geom, pt.geom) LIMIT 1) AS poly ON true;
To omit points not in any polygon, use INNER JOIN (or just JOIN) instead of LEFT OUTER JOIN.
Count kinds of Points in Polygons
SELECT
polyname,
COUNT(pid) FILTER (WHERE pid='w') AS w,
COUNT(pid) FILTER (WHERE pid='x') AS x,
COUNT(pid) FILTER (WHERE pid='y') AS y,
COUNT(pid) FILTER (WHERE pid='z') AS z
FROM polygons
LEFT JOIN points ON st_intersects(points.geom, polygons.geom)
GROUP BY polyname;
Optimize Point-in-Polygon query by evaluating against smaller polygons
Count lightning occurences inside countries
Optimize Point-in-Polygon query by gridding polygons
Count occurences inside river polygons
https://gis.stackexchange.com/questions/185381/optimising-a-very-large-point-in-polygon-query
Find smallest Polygon containing Point
https://gis.stackexchange.com/questions/220313/point-within-a-polygon-within-another-polygon
Solution
Choose containing polygon with smallest area
SELECT DISTINCT ON (compequip.id), compequip.*, a.*
FROM compequip
LEFT JOIN a
ON ST_within(compequip.geom, a.geom)
ORDER BY compequip.id, ST_Area(a.geom)
Find Points NOT in Polygons
https://gis.stackexchange.com/questions/139880/postgis-st-within-or-st-disjoint-performance-issues
This is not PiP, but the solution of using NOT EXISTS might be applicable?
Find Point in Polygon with greatest attribute
Given 2 tables:
obstacles- Point layer with a columnheight_m INTEGERpolyobstacles- Polygon layer
Select the highest obstacle in each polygon. If there are several points with the same highest height a random one of those is selected.
Solution - JOIN LATERAL
SELECT poly.id, obs_max.*
FROM polyobstacle poly
JOIN LATERAL (SELECT * FROM obstacles o
WHERE ST_Contains(poly.geom, o.geom)
ORDER BY height_m LIMIT 1
) AS obs_max ON true;
Solution - DISTINCT ON
Do a spatial join between polygon and points and use SELECT DISTINCT ON (poly.id) poly.id, o.height...
Solution - ARRAY_AGG
SELECT p.id, (array_agg(o.id order by height_m))[1] AS highest_id
FROM polyobstacles p JOIN obstacles o ON ST_Contains(p.geom, o.geom)
GROUP BY p.id;
Find Polygon containing Point
Basic query - with tables of address points and US census blocks, find state for each point Discusses required indexes, and external parallelization https://lists.osgeo.org/pipermail/postgis-users/2020-May/044161.html
Count Points in Polygons with two Point tables
SELECT ply.polyname, SUM(pnt1.cnt) AS pointtable1count, SUM(pnt2.cnt) AS pointtable2count
FROM polytable AS ply,
LATERAL (
SELECT COUNT(pt.*) AS cnt
FROM pointtable1 AS pt
WHERE ST_Intersects(ply.geom, pt.geom)
) AS pnt1,
LATERAL (
SELECT COUNT(pt.*) AS cnt
FROM pointtable2 AS pt
WHERE ST_Intersects(ply.geom, pt.geom)
) AS pnt2
GROUP BY 1;