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 routes a INNER JOIN counties b ON ST_INTERSECTS(a.the_geom, b.the_geom)
GROUP BY a.the_geom, b.the_geom;


SELECT SUM(sum_length)
FROM sum_length;

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

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

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.