Summing road lengths by type and gridcell

Viewed 16

I am pretty new to PostGIS, PostgreSQL, and SQL, so please be patient.

I have a table 'highways' that looks like this:

enter image description here

Then I would like to sum the road lengths by type and grid cell to produce a table like this: enter image description here

I have been able to sum the road lengths in one cell (or bounding box) with:

    SELECT geom
    round(SUM(
      ST_Length(
        ST_Intersection(geom, (SELECT geom FROM boundaries WHERE area_id=-1377803))::geography
    ))) AS "length (meters)", type
  FROM highways
    WHERE ST_Intersects(geom, (SELECT geom FROM boundaries WHERE area_id=-1377803))
    GROUP BY type
    ORDER BY "length (meters)" DESC
    LIMIT 10;

And to create the grid with:

    WITH grid AS (
SELECT (ST_SquareGrid(1, ST_Transform(geom,4326))).*
FROM boundaries
)
  SELECT ST_AsText(geom)
  FROM grid LIMIT 10;

I found of example for counting points here, and I could replicate it for counting the number of roads by cell like this:

SELECT COUNT(*), grid.geom
    FROM
    highways AS hwy
    INNER JOIN
    ST_SquareGrid(
        1,
        ST_SetSRID(ST_EstimatedExtent('highways', 'geom'), 4326)
    ) AS grid
    ON ST_Intersects(hwy.geom, grid.geom)
    GROUP BY grid.geom, hwy.type LIMIT 10;

But I couldn't extrapolate it for what I intend to do. Any help with this is highly appreciated.

0 Answers
Related