TransWikia.com

Get all rows that have geometries which intersect with a geometry in any row of a different table in an effective way

Geographic Information Systems Asked on November 6, 2021

I have two PostGIS tables containing thousands of geographic polygons each (each row has one polygon and numerous other attributes). Both tables represent different data sets but cover the same area of interest.

I’m trying to receive the rows of table A that intersect with rows from table B (meaning that the geometries intersect).

What I tried is the following:

SELECT tableA.polygon FROM tableA, tableB
WHERE ST_Intersects(tableA.polygon, tableB.polygon)=true;

I have no idea if this works or not since the query has not finished since I started it the first time 20 minutes ago. I have to admit that I’m running the query on a machine which is not very powerful.

Is this the correct way to do it? Is there a smarter or more effective way allowing me to reduce the computing time?

One Answer

As pointed out by @BERA, indexing the tables in combination with using the same coordinate system seems to have reduced the computing time remarkably.

Answered by Baconman on November 6, 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