[GIS] PostgreSQL : finding all lines intersecting with a polygon

postgispostgresql

I am totally new in the world of GIS but as a student surveyor I am working on a project using postgreSQL and PostGIS.

I have one database with all the roads of my country (lines), and one database with a lot of area's (polygons). And for every polygon, I need to find all the roads (lines) that intersect with it (contain in partially or fully).

I do know the ST_Intersects function, but is this the best function to use ? right now I am trying to make a loop function where I loop all the roads to see if they intersect, but there must be a quicker way..

If anybody could get me started, that would be great!

Thanks

Best Answer

Leave the looping for the database. This is SQL.

SELECT polygons.id, lines.id 
FROM polygons, lines 
WHERE ST_Intersects(lines.geom,polygons.geom);

Put spatial indexes on the geometry columns.

Related Question