4

I have a grid polygon layer and a line layer.

Once imported into PostGIS (let's call the tables poly and 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)
2

In order to create a new table with all the columns and rows with an added count of overlapping figures, you need to use a LEFT OUTER JOIN to populate rows where no data exists:

CREATE TABLE sp_join AS
    SELECT polygons.*, count(l.idcol) as line_count
    FROM  polygons p
    LEFT OUTER JOIN lines l ON
        ST_Intersects(p.wkb_geometry, l.wkb_geometry)
    GROUP BY p.idcol

This assumes a primary key column named idcol exists in each table.

The default behavior is to only return a row once where there are multiple intersections, so nothing special is required in this regard.

Note that it's confusing to name a column of "geometry" type "wkb_geometry", because it's not storing the data in WKB format -- "geom" or "point"/"line"/"poly" better describe the column. Best practice would be to explicitly list each column in the SELECT list, but you didn't provide the column names.

  • Without a GROUP BY clause it doesn't work. Add a GROUP BY with the primary key of polygon table. – Zoltan Dec 29 '16 at 17:06
  • yeah, my bad (reinstalling PG at the moment) – Vince Dec 29 '16 at 17:16
1

Assuming that both tables have a primary key called gid.

I can not test it right now but something like this could work:

SELECT poly.gid, count(*) 
FROM poly, lines
WHERE ST_Intersects(p.wkb_geometry, lines.wkb_geometry)
GROUP BY poly.gid

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.