Selecting lines from first table based on bounding Box of second table in PostGIS

Question

In PostGIS, I am trying to get the elements from first table(A) that intersect with bounding box of second table(B) . I get the error using the query below. What would be the correct syntax.

SELECT A.* from A,B where ST_Intersects(A.geom,SELECT ST_Extent(geom) as table_extent FROM B);

ERROR: syntax error at or near "SELECT" LINE 1: ...from rivers,india where ST_Intersects(rivers.geom,SELECT ST_... ^ ********** Error **********

ERROR: syntax error at or near "SELECT" SQL state: 42601 Character: 67

Answer 1

When passing a query into a function you have to enclose it in parentheses. In your case, the query would become:

SELECT A.* from A,B where ST_Intersects(A.geom,(SELECT ST_Extent(geom) as table_extent FROM B));

Also, You don't actually need to perform a cross join here. The following would suffice:

SELECT * from A where ST_Intersects(A.geom,(SELECT ST_Extent(geom) as table_extent FROM B));

It's worth noting that the geometry returned by ST_Extent loses it's SRID. You can reset it with ST_SetSRID:

SELECT * from A where ST_Intersects(A.geom,(SELECT ST_SetSRID(ST_Extent(geom),4326) as table_extent FROM B));