[postgis-users] Counting intersecting polygons

Brent Wood Brent.Wood at niwa.co.nz
Mon May 27 15:15:59 PDT 2019

At a guess, you are asking for the count(*) from both tables... you only want the count from table 1.

SELECT COUNT(Table_1.wkb_geometry) FROM Table_1,Table_2 WHERE ST_Intersects(Table_1.wkb_geometry,Table_2.wkb_geometry);

Brent Wood

Programme leader: Environmental Information Delivery
DDI:  +64 (4) 3860529

[cid:imagee12d55.PNG at cdb06479.44912a6c]<http://www.niwa.co.nz>

Brent Wood
Principal Technician - GIS and Spatial Data Management
Programme Leader - Environmental Information Delivery
T +64-4-386-0529

National Institute of Water & Atmospheric Research Ltd (NIWA)
301 Evans Bay Parade, Greta Point, Wellington
Connect with NIWA: niwa.co.nz<https://www.niwa.co.nz> Facebook<https://www.facebook.com/nzniwa> Twitter<https://twitter.com/niwa_nz> LinkedIn<https://www.linkedin.com/company/niwa> Instagram<https://www.instagram.com/niwa_science>

To ensure compliance with legal requirements and to maintain cyber security standards, NIWA's IT systems are subject to ongoing monitoring, activity logging and auditing. This monitoring and auditing service may be provided by third parties. Such third parties can access information transmitted to, processed by and stored on NIWA's IT systems.

From: postgis-users <postgis-users-bounces at lists.osgeo.org> on behalf of Sarthak Vijay <sarthak at attentive.ai>
Sent: Monday, May 27, 2019 22:57
To: postgis-users at lists.osgeo.org
Subject: [postgis-users] Counting intersecting polygons

Hi All,
I have 2 tables containing polygons in same general area. What I want is the count of polygons in the first table (Table_1, represented by purple) that intersect with polygons in the 2nd (Table_2, represented by green).
[Screenshot from 2019-05-23 13-33-24.png]
For the above example, I want the output as 1. I have tried -
SELECT COUNT(*) FROM Table_1,Table_2 WHERE ST_Intersects(Table_1.wkb_geometry,Table_2.wkb_geometry);

This command returns the answer 3 which doesn't work for me.
Could someone please suggest relevant command.
Thanks in advance.

Sarthak Vijay

-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20190527/3b36f852/attachment.html>
-------------- next part --------------
A non-text attachment was scrubbed...
Name: Screenshot from 2019-05-23 13-33-24.png
Type: image/png
Size: 10149 bytes
Desc: Screenshot from 2019-05-23 13-33-24.png
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20190527/3b36f852/attachment.png>
-------------- next part --------------
A non-text attachment was scrubbed...
Name: imagee12d55.PNG
Type: image/png
Size: 12288 bytes
Desc: imagee12d55.PNG
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20190527/3b36f852/attachment-0001.png>

More information about the postgis-users mailing list