1

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?

2
  • 2
    Have you created indexes?
    – BERA
    Apr 26 '20 at 18:25
  • Only one index is necessary, but modern syntax would be to use a JOIN on the smaller table (which should be indexed), with the WHERE made into the JOIN constraint.
    – Vince
    Apr 26 '20 at 19:11
1

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.