0

Setup:

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

Problem:

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

Example:

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

2

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... – DPSSpatial Apr 16 '18 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. – ThingumaBob Apr 16 '18 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 '18 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.