# 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? Commented Nov 29, 2018 at 16:02
• then you only want the geometry of `a` that intersects the inner query? Commented Nov 29, 2018 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. Commented Nov 29, 2018 at 16:17
• @ziggy, yes to your second comment. Commented Nov 29, 2018 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. Commented Nov 29, 2018 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. Commented Nov 29, 2018 at 18:30
• check updates.. Commented Nov 29, 2018 at 18:34