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.

  • 1
    It looks right. Do you have any reason to think otherwise based on the outputs you are getting. Apr 21, 2015 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, 2018 at 23:11

2 Answers 2


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


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;
  • Thanks! Unfortunately this question is six years old so I'm not sure I can realistically check the answer. But it looks about right.
    – Amanda
    Mar 10, 2021 at 17:00

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.