Rahul Rahul - 5 months ago 9
SQL Question

How to return all polygons that intersects a linestring?

I'm trying to return all polygons that intersect a linestring as a

GEOMETRYCOLLECTION
.

This is the query I've tried so far. It returns all the polygons that intersect a linestring but in separate rows. How can I combine all the results in the query to return a
GEOMETRYCOLLECTION


SELECT poly.the_geog FROM ST_GeographyFromText('SRID=4326;LINESTRING(85.269150 23.157234,85.278541 23.101098,85.286367 23.047280,85.287737 23.006227)') as road, poly
WHERE ST_Intersects(
road,poly.the_geog
);

Answer

You can use ST_Collect to aggregate the geometries into a collection.

SELECT ST_Collect(poly.the_geog) ...