I have a grid polygon layer and a line layer.
Once imported into PostGIS (let's call the tables
line), how can one build a query (with
ST_Intersect for example) that counts the number of line intersections with polygons (the same as the QGIS tool:
MMQGIS > Spatial Join > Intersects > First)
and returns the
poly table with an extra column containing the sum of line intersections per polygon?
In some cases, the same line will intersect the same polygon more than once, and in that case, it should only be counted once.
So far, I have this:
CREATE TABLE sp_join AS
SELECT polygons.* COUNT (l.*)
FROM polygons as p
JOIN lines l ON ST_Intersects(p.wkb_geometry, lines.wkb_geometry)