I have 3 geometry tables a, b and c with geometry column geom

What I want is to first check if geom of b and c, intersects under some given condition using a subquery, and feed the subquery geometry resultant rows into another ST_Intersection query, to check whether it intersects the geom of table a as show in the query below.

select geom from a
where st_intersects(a.geom, (
  select b.geom from b, c
  where st_intersects(b.geom, c.geom) = 1 
     and b.col_1 >= 180 
     and b.col_1 <= 350 
     and c.col_1 >= 1800 
     and c.col_1 <= 2800)
) = 1

I'm currently getting only result from intersection of the first row of a.geom with the the subquery, how can I get all geometry from table a that intersects result from the subquery of intersection of table b and c under specified condition?

  • this is a bit unclear - in the inner query of b and c you only want the geometry of b? – ziggy Nov 29 '18 at 16:02
  • then you only want the geometry of a that intersects the inner query? – ziggy Nov 29 '18 at 16:03
  • @ziggy from the inner query I'm only interested with the geometry of b, which I will use to check whether it intersects the geometry of a. The inner query returns several rows, but when put inside the st_intersects query with a.geom, i only get one row. – Erick Nov 29 '18 at 16:17
  • @ziggy, yes to your second comment. – Erick Nov 29 '18 at 16:19
  • Depending on how selective the values of col_1 are and what indexes you have, it might be better to rearrange the query so that you check the between clause, either before, or after, the results of the intersection. Also, you don't need = 1, ST_Intersects implicitly returns only those records where the condition is true. – John Powell Nov 29 '18 at 16:45

I personally would write this query using an inner JOIN to relate the inner query to the outer query

       FROM b JOIN c
       ON ST_Intersects(b.geom, c.geom) Where
     b.col_1 >= 180
     AND b.col_1 <= 350
     AND c.col_1 >= 1800
     AND c.col_1 <= 2800)t
ON ST_Intersects(a.geom,t.geom)
| improve this answer | |
  • Without a where clause between ST_Intersects(b.geom, c.geom) and AND b.col_1 >= 180, the statement throws an error. After adding the where clause, the sql statement returns geometry of the result of the inner query several times and not the geometry of the outer query, the out put result when exported to a shapefile is 500 MB, while the original size of the data as shapefiles combine (table a, b and c) before being exported to spatialite is less than 20 MB. – Erick Nov 29 '18 at 18:30
  • check updates.. – ziggy Nov 29 '18 at 18:34

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.