Calculating total line lengths within polygon?

Question

I have basically the same conundrum as Calculating line segment lengths within polygon using ArcGIS Desktop? but I'm working in PostGIS, not ArcGIS. Any pointers?

This query seems to like it should work, just to show the sums:

SELECT county, ST_LENGTH(ST_Intersection(counties.the_geom,routes.the_geom))
FROM counties, routes
WHERE ST_Intersects(counties.the_geom, routes.the_geom)

But I'm not confident that I'm getting it right and I can't figure out how to reshape that into an update query.

Answer 1

If I understand the translation correctly, proceed as follows:

1)

CREATE TABLE sum_length as
SELECT ST_INTERSECTION(a.the_geom, b.the_geom) as geom,
ST_LENGTH(geography(a.the_geom)) as sum_length
FROM counties a INNER JOIN routes b ON ST_INTERSECTS(a.the_geom, b.the_geom)
GROUP BY a.the_geom, b.the_geom;

2)

SELECT SUM(sum_length.geom)
FROM sum_length;

Important: the original data lines - type LineString; EPSG 4326.

The total length of the lines will be in meters, with respect.

If you don’t like to dance the old-fashioned way like me step by step, dance in a modern and independent way by running the following script:

WITH sum_length as (SELECT ST_LENGTH(ST_Intersection(a.geom::geography, b.geom::geography)) as geom FROM counties a, routes b WHERE ST_Intersects(a.geom, b.geom)) SELECT SUM(sum_length.geom) FROM sum_length;

Good luck :-)

Answer 2

Try this:

SELECT county, SUM(ST_Length(ST_Intersection(counties.geom,routes.geom)))
FROM counties
JOIN routes ON ST_Intersects(counties.geom, routes.geom)
GROUP BY county;