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,
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.

  • It looks right. Do you have any reason to think otherwise based on the outputs you are getting. – John Powell Apr 21 '15 at 18:59
  • I was doing a similar exercise and the units or something seemed off. I compared results with QGIS, Arcmap, and even a segment inside Google My Maps. I learned that the coordinate system of the data is key. They should all match and use an appropriate datum for accurately calculating lengths in the region you're looking at, AND in the right units. I wanted a query for the continental US so I used ST_Transform with the SRID 6350. – htomita Dec 18 '18 at 23:11

If I understand the translation correctly, proceed as follows:


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;


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 :-)

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.