I am trying to figure out the length and density of all major roads and minor roads within a study area to return something like:
There are two polygons within the study area - an inner focus and an outer buffer. I want the length and density grouped by these two areas. Major roads are only one type (a_roads) but other roads are stored in two different relations
|a_road|name|geom |b_road|name|geom |minor_road|name|geom
So far I can get the length of each separately using ST_Length, the area of the polygons using ST_Area and the density of a_road but not all the elements for all the roads together in one result.
Perhaps need to use subquery or
but unsure how to put all these elements together.
In the example above red line length and density for inner and outer area then combine green and yellow length and density for inner and outer area to return as "other roads".