# Calculating lengths and percentages of potholes with SQL query in QGIS

I have a shapefile in QGIS that holds information about the amount and severity of potholes on a road network across several cities:

• The road network is split into segments of different lengths (meters)
• Each segment is split into 10m parts
• Each part has potholes measured in percentage of 10 meters (zero, mild, important and severe potholes)
• Each city has several segments of the road network (city zip code)

I would like to have for each city the percentages of pothole types compared to the total length of the city segments. Is possible to do this in one SQL query?

• Would you mind to elaborate ? :) Commented Jun 6, 2023 at 8:47
• I'm sorry the "Each part has potholes measures in lengths (zero, mild, important and severe potholes)' is wrong. The correct phrase is : "Each part has potholes measures in percentage of 10 meters)". I guess this chages the query a bit, sorry. Commented Jun 6, 2023 at 12:48
• Not much - just use SUM(<pothole_measure> * 10) if those are fractions (0.0 - 1.0), or SUM(<pothole_measure> * 0.1) if they are percentages (0 - 100). Commented Jun 6, 2023 at 13:49

• Total length of city segments :
SELECT
pt.zip,
SUM(pt.segment_length) AS total_segment_length
FROM (
SELECT DISTINCT
"CITY ZIP CODE" AS zip,
"SEGMENT ID" AS segment_id,
"SEGMENT LENGTH" AS segment_length
FROM potholes_table
) pt
GROUP BY pt.zip
;
• Potholes types :
SELECT
"CITY ZIP CODE" AS zip,
SUM("MILD POTHOLES" * 10) AS mild,
SUM("IMPORTANT POTHOLES" * 10) AS important,
SUM("SEVERE POTHOLES" * 10) AS severe
FROM potholes_table
GROUP BY "CITY ZIP CODE"
• Combine the two :
WITH total_length AS (
SELECT
pt.zip,
SUM(pt.segment_length) AS total_segment_length
FROM (
SELECT DISTINCT
"CITY ZIP CODE" AS zip,
"SEGMENT ID" AS segment_id,
"SEGMENT LENGTH" AS segment_length
FROM potholes_table
) pt
GROUP BY pt.zip
),
types_length AS (
SELECT
"CITY ZIP CODE" AS zip,
SUM("MILD POTHOLES" * 10) AS mild,
SUM("IMPORTANT POTHOLES" * 10) AS important,
SUM("SEVERE POTHOLES" * 10) AS severe
FROM potholes_table
GROUP BY "CITY ZIP CODE"
)
SELECT
tot.zip,
tot.total_segment_length,
typ.mild / tot.total_segment_length * 100 AS mild_perc,
typ.important / tot.total_segment_length * 100 AS important_perc,
typ.severe / tot.total_segment_length * 100 AS severe_perc
FROM
total_length tot
INNER JOIN types_length typ ON tot.zip = typ.zip
;
• Thank you so much for your answer. I'll try this right away. Commented Jun 6, 2023 at 9:17
• If I get your query right, you sum the segment lengths. But it mustn't. You will see the total segmeent length several times (for each "part" of a segement). But the value of each segement length is unique. Not sure if I am clear ? For example : SEG000001 40 SEG000001 40 SEG000001 40 SEG000001 40 etc... The total length of SEG000001 is only 40 meters I am also struggling to format correctly this query in qgis db manager, but i guess i will manage. Commented Jun 6, 2023 at 10:44
• You don't get the query right, I put a sub-query with DISTINCT to get the segment length only once. Commented Jun 6, 2023 at 11:22
• WITH total_length AS ( SELECT pt.zip, SUM(pt.segment_length) AS total_segment_length ( SELECT DISTINCT "CITY ZIP CODE" AS zip, Isn't there a FROM before (SELECT DISTINCT ? Commented Jun 6, 2023 at 13:47
• It's a table alias FROM table_name AS alias_name, the AS is optional. So, total_length tot is the same as total_length AS tot. Commented Jun 7, 2023 at 6:06

If by "each city" you refer to each distinct value in "CITY ZIP CODE", the query would simply be:

SELECT
"CITY ZIP CODE",
SUM("MILD POTHOLES") / (COUNT(*) * 10) AS mp_pct,
SUM("IMPORTANT POTHOLES") / (COUNT(*) * 10) AS ip_pct,
SUM("SEVERE POTHOLES") / (COUNT(*) * 10) AS sp_pct
FROM
<potholes>
GROUP BY
1
;

Note:

This assumes that each part is exactly 10m in length - we simply count the parts per ZIP code and multiply by 10. If that is not the case (I mean, how could it, really, IRL - but that's what your question says), go with @J.Monticolo's answer if the values in "SEGMENT LENGTH" are aware of any length remainder, or work with ST_Length of the actual geometries instead.

• Maybe I wasn't clear but actually each segment has a different length, but they are all split into eaqual parts of 10 meters. For example, if a segement is 150m long, it will be split into 15 10m parts. And each part has potholes measures in it. Commented Jun 6, 2023 at 9:20
• Sure, and this query works under that assumption. Just saying that, IRL, roads tend to not have a total length evenly dividable by 10...anyways, I updated the wording from segments to parts. Commented Jun 6, 2023 at 9:27
• You're absolutely right. I didn't mentioned it, but the last part of each segement is the remainder, so it can be less than 10m. Commented Jun 6, 2023 at 9:30
• Well, on second look on your table it seems it won't matter at all; as long as you do not use the ST_Length calculated from the actual geometries, both answers here use the lengths given as attributes - and both columns seem to have values dividable by 10. Commented Jun 6, 2023 at 10:06