4

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

6

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));

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.