TransWikia.com

Counting Points in Polygon with PostGIS

Geographic Information Systems Asked on March 9, 2021

I’ve got a simple problem: I want to count the number of points within a set of polygons.

I have a SQL already but it only gives back the gid of the polygone that actually contains points.

My tables: a polygon layer with 19.000 rows and a point layer with 450 rows.

The following SQL-query

SELECT grid.gid, count(*) AS totale
FROM grid, kioskdhd3
WHERE st_contains(grid.geom,kioskdhd3.geom)
GROUP BY grid.gid;

returns only some 320 polygons that actually contain points. But I want all polygons returned, even thought the number of points is 0.

Of course it has to do with my WHERE-clause. Where do I have to put in my st_contains()?

2 Answers

SELECT grid.gid, count(kioskdhd3.geom) AS totale 
FROM grid
LEFT JOIN kioskdhd3 ON st_contains(grid.geom,kioskdhd3.geom)
GROUP BY grid.gid;

Correct answer by Nicklas Avén on March 9, 2021

Per this left joins are not supported in GiST indices.

May I recommend:

SELECT grid.gid, 
       SUM(CASE WHEN st_contains(grid.geom,kioskdhd3.geom) THEN 1 ELSE 0 END) AS total
FROM grid, kioskdhd3 
GROUP BY grid.gid;

Answered by raphael on March 9, 2021

Add your own answers!

Ask a Question

Get help from others!

© 2024 TransWikia.com. All rights reserved. Sites we Love: PCI Database, UKBizDB, Menu Kuliner, Sharing RPP