0

I have a shapefile that holds information about the condition of a road network that runs across several cities (zip codes):

  • The road network is split into segments of various lengths (meters)
  • Each segment is split into 10 meters parts
  • Each part has a road condition score ranging from 1 to 5 (1 being worst and 5 best)
  • Each city area contains several segments of the road network (city zip code)

Using one SQL query, I would like to have for each city the percentages of each score relative to the total length of the city segments. For example :

Zip code 80100 has a total of xxx meters of which :

  • aa % has a score of 5
  • bb % has a score of 4
  • cc % has a score of 3
  • dd % has a score of 2
  • ee % has a score of 1

enter image description here

Improve this question
5
  • Here is my guess, but I know at least the percentage part (main query) is wrong. Any help would be very appreciated : -- Parts total per city WITH total_length AS ( SELECT tt.city, count(distinct tt.part) AS total_segment_part FROM( SELECT DISTINCT ZIP_CODE AS city, SEGMENT_ID id AS id, ROAD_TYPE AS road_type, part AS part FROM "Road_network" ) AS tt WHERE tt.road_type = "county_road" GROUP BY tt.city ),
    – dot314
    Commented Sep 4, 2023 at 7:33
  • -- Road score (1 to 5) road_score AS ( SELECT ZIP_CODE AS city, ROAD_TYPE AS road_type, ROAD_SCORE, count(*) as RScount FROM "Road_network" where road_type = "county_road" group by ROAD_SCORE, city ) -- Main query SELECT tot.city, tot.total_segment_part, typ.ROAD_SCORE, typ.RScount AS RS_Count, typ.RScount / total_segment_part * 100 AS PRCT FROM total_length AS tot INNER JOIN road_score AS typ ON tot.city = typ.city ;
    – dot314
    Commented Sep 4, 2023 at 7:33
  • Sorry for the text formatting, I don't know how to fix it :(
    – dot314
    Commented Sep 4, 2023 at 7:34
  • Up. Nobody has an answer ?
    – dot314
    Commented Sep 25, 2023 at 6:38
  • Are you using PostgreSQL?
    – Bera
    Commented Apr 18 at 5:45

0

Reset to default

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Browse other questions tagged or ask your own question.