0

I am interested in creating a list of all the IDs from Table_A and whether or not they intersect with any of the geometries from Table_B.

The following would work:

SELECT Table_A.id, bool_and(st_intersects(Table_A.geom, Table_B.geom)
FROM Table_A, Table_B
GROUP BY Table_A.id

The problem is that this means checking every geometry in Table_A against every geometry in Table_B which is very intensive. How do you query until you find the first intersect, return TRUE and then move onto the next geometry in Table_A?

The extension to this, is to then check each row in Table_A for an intersect in several other tables, with the result as follows:

Table_A_ID | Table_B_Intersects | Table_C_Intersects | ...
0001       | True               | False              | ...         
...        |  ...               | ...                | ...

This answer How to find all features in one table that intersect features in another table in PostGIS? from comments helps with the single case, but I cannot see how to apply it in this instance.

Edit: moved from comment, tried the following, but crashes pgAdmin:

 SELECT Table_A.id,
 (CASE WHEN EXISTS
    (SELECT st_intersects(Table_A.geom, Table_B.geom))
 THEN TRUE ELSE FALSE END) AS Table_B_Intersects,
 (CASE WHEN EXISTS
    (SELECT st_intersects(Table_A.geom, Table_C.geom))
 THEN TRUE ELSE FALSE END) AS Table_C_Intersects,
 ...
 FROM Table_A, Table_B, Table_C, ... 
  • 3
  • As usual, I've oversimplified my example, the duplicate indeed answer the original question. Question edited to reflect additional complexity. – George of all trades Jul 12 '17 at 14:16
  • Using CASE as well as EXISTS might be a way forward. Unfortunately the following crashes when I execute it in pgadmin: SELECT Table_A.id, (CASE WHEN EXISTS(SELECT st_intersects(Table_A.geom, Table_B.geom)) THEN TRUE ELSE FALSE END) AS Table_B_Intersects, (CASE WHEN EXISTS(SELECT st_intersects(Table_A.geom, Table_C.geom)) THEN TRUE ELSE FALSE END) AS Table_C_Intersects, ... FROM Table_A, Table_B, Table_C, ... – George of all trades Jul 12 '17 at 15:13
  • Please edit the existing question instead of adding to the comments. It makes it a lot more readable. And while you are at it, correct the spelling mistakes in the original question as well ;-) – tilt Jul 13 '17 at 7:47
1

From what I understand from your question, you want to compare TableA to a number of other tables and see which row from TableA intersects with any row of the other tables.

I would proceed with the following query:

SELECT 
  Table_A.id, 
  bool_or(Table_B.geom IS NOT NULL) AS Table_B_Intersects ,
  bool_or(Table_C.geom IS NOT NULL) AS Table_C_Intersects 
FROM Table_A 
LEFT JOIN Table_B ON ST_Intersects(Table_A.geom, Table_B.geom)
LEFT JOIN Table_C ON ST_Intersects(Table_A.geom, Table_C.geom)
GROUP BY Table_A.id

I don't know if the planner is smart enough to stop when the first bool_or condition is positive but when your tables are properly indexed (are they?!) this shouldn't take ridiculously long. Personally, I would be in favour of using a count instead of bool_or so you also get a sense of how often it overlaps.

  • Frequency of overlap may not be useful in all situations, and going the count route means checking against every geometry in every table. In this case I am testing whether parcels of land are subject to various environmental designations, a yes/no answer is sufficient. – George of all trades Jul 13 '17 at 8:05
  • tables are indexed (gist) – George of all trades Jul 13 '17 at 8:10
  • Did the query in the answer help you? Does it make a difference? – tilt Jul 13 '17 at 8:17
  • Well I'm having a go. "Shouldn't take ridiculously long" - what sort of order of magnitude should I be expecting with around 60,000 rows in the first table. Other tables range from 50 to 20000 rows. Minutes? Hours? Days? – George of all trades Jul 13 '17 at 8:43
  • Should be done by now but it also depends how many tables are involved. – tilt Jul 13 '17 at 9:05

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.