• Country regions are stored as Polygons in postgres/postgis (Region:id:region_poly)

  • Cross-country routes are stored as Lines in a different table (Route:id:route_line)


  • Given a Line (route_line), how can I pull the length of the line in each polygon (region_poly) with a single query


  • Given Polygon A, B, and C, and a line X that crosses all three of these polygons, I'd like to get something like: {"A": 10km, "B": "160km", "C": 16km}

I think I have to use the ST_Intersection with ST_Length, but I can't quite wrap my head around it. (I'm still new to the gis stuff)


1 Answer 1


you basically just need an intersection of the linestrings and polygons, get the length with st_length and use the sum function with a group by on the unique polygon name or id. I am not sure what your columns are exactly so I will give you a generic query that you should be able to plug and play

select sum(st_length(st_intersection(c.geom,l.geom))) as length_of_route,
 c.region_id, c.other_column,l.other_column
from country_regions c join cross_country_routes l 
  on st_intersects(c.geom,l.geom)
group by region_id,c.other_column,l.other_column
  • 1
    Depending on the coordinate system of the source data, you might have to wrap an ST_Transform(line.geom) to get something in meaningful units... Apr 16, 2018 at 19:32
  • 1
    ...or, to get maximum precision, cast the intersection geometry (from EPSG:4326) to geography type to get measurements in meter on spheroid.
    – geozelot
    Apr 16, 2018 at 20:09
  • 1
    Thank you all guys, that was extremely helpful! I ended up tweaking ziggy's function a bit, then recasting it to geography, dividing by a 1000 and I got the exact results I needed.
    – ddgd
    Apr 16, 2018 at 20:42

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.