I am pretty new to PostGIS, PostgreSQL, and SQL, so please be patient.
I have a table 'highways' that looks like this:
Then I would like to sum the road lengths by type and grid cell to produce a table like this:

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.
