Geographic Information Systems Asked on May 8, 2021
I want to do an operation i PostGIS similar to the count points in polygon
in QGIS.
I have
plan.polygon
) consisting of several areasbasemap.points
)in a PostGIS database.
The point layer is categorized by the column code_text
.
I want to count the number of each category for each of areas resulting in a multipolygon table with rows areas and columns with the count of each category.
Below you can se the areas (red lines) and the points with categorization (red, yellow, green dots).
I’ve been trying the following resulting in a query processing forever.
SELECT b.gid, b.the_geom,
(SELECT COUNT(v.code_text)
FROM basemap.points v, plan.polygon x
WHERE entity = 120 AND ST_within(v.the_geom, x.the_geom)
GROUP BY a.code_text)
AS count_category_a,
(SELECT count(y.code_text)
FROM basemap.points y, plan.polygona z
WHERE entity = 130 AND ST_within(y.the_geom, z.the_geom)
GROUP BY a.code_text)
AS count_category_b
FROM basemap.points a, plan.polygons b;
Are you looking for a simple filtered aggregate?
Try
SELECT ply.gid,
COUNT(pts.*) FILTER (WHERE pt.code_text = <category_a>) AS count_category_a,
COUNT(pts.*) FILTER (WHERE pt.code_text = <category_b>) AS count_category_b,
COUNT(pts.*) FILTER (WHERE pt.code_text = <category_c>) AS count_category_c,
ply.the_geom
FROM plan.polygons AS ply
JOIN basemap.points AS pts
ON ST_Intersects(ply.geom, pts.geom)
GROUP BY
ply.gid, ply.geom
;
Correct answer by geozelot on May 8, 2021
Get help from others!
Recent Questions
Recent Answers
© 2024 TransWikia.com. All rights reserved. Sites we Love: PCI Database, UKBizDB, Menu Kuliner, Sharing RPP