Skip to main content
Geographic Information Systems

Return to Question

deleted 58 characters in body
Source Link
PolyGeo ♦
PolyGeo ♦
  • 65.4k
  • 29
  • 114
  • 345

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

Thanks in advance and I hope I am not confusing you :)

enter image description here

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

Thanks in advance and I hope I am not confusing you :)

enter image description here

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

Source Link
dot314
dot314
  • 51
  • 5

Calculating road condition score percentages for each zip code with SQL query in QGIS

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

Thanks in advance and I hope I am not confusing you :)

enter image description here