1

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:

|STUDYAREA|a_road_length|a_road_density|other_road_length|other_road_density

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

create VIEW

but unsure how to put all these elements together.

enter image description here

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".

1

All you need to do is "merge" all roads in a single "table".

For each road type table, create a query that returns the exact same data structure. Having them all in an exact same structure, you can use UNION ALL to combine the queries. You can put that inside a WITH clause and then use it as if it was a single table, and perform your queries. Something like this:

WITH all_roads as
  (SELECT
       name, geom
   FROM
       a_road
   UNION ALL
   SELECT
       name, geom
   FROM
       b_road
   UNION ALL
   SELECT
       name, geom
   FROM
       minor_road)
SELECT
... --do your stuff
FROM
    all_roads, 
    study_area
|improve this answer|||||

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.