# Subquery inside ST_Intersects

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

``````SELECT DISTINCT t.geom
FROM a JOIN
(SELECT DISTINCT b.geom
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)
``````
• 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