FHWA PM2 Pavement Condition from HPMS

[#1]

This is how KDOT delivered the PM2 Pavement Condition summary data from the data extracted from our HPMS Dashboard for use by our MPOs.  

here are the threshold values:

Then, add this twist:

For the tenth mile segment to be “good” all three measures must be “good”.  If any two are “poor” then overall it is “poor”. 

So, first I add fields for the measure "LEVEL", and here is how I interpreted these measures, from 1/good to 3/poor, using the following code for SQL Server management studio.

The pavement surface types are the HPMS Coded values, obviously.

  update [shared].[PM2_2017]

  set IRI_LEVEL = 0, RUTTING_LEVEL = 0, CRACKING_LEVEL = 0, FAULTING_LEVEL = 0, [PM_LEVEL] = 0

  where 1=1

  update [shared].[PM2_2017]

  set IRI_LEVEL = 1

  where IRI_VN < 95  

  AND SURFACE_TYPE_VN NOT IN (0, 1, 11)

update [shared].[PM2_2017]

  set IRI_LEVEL = 2

  where IRI_VN >= 95 and IRI_VN <=170

    AND SURFACE_TYPE_VN NOT IN (0, 1, 11);

update [shared].[PM2_2017]

  set IRI_LEVEL = 3

  where IRI_VN >170

    AND SURFACE_TYPE_VN NOT IN (0, 1, 11)

update [shared].[PM2_2017]

set RUTTING_LEVEL = 1

  where RUTTING_VN < 0.2

  AND SURFACE_TYPE_VN IN (2, 6, 7, 8)

update [shared].[PM2_2017]

  set RUTTING_LEVEL = 2

  where RUTTING_VN >= 0.2 and RUTTING_VN <=0.4

   AND SURFACE_TYPE_VN IN (2, 6, 7, 8)

update [shared].[PM2_2017]

  set RUTTING_LEVEL = 3

  where RUTTING_VN >0.4

  AND SURFACE_TYPE_VN IN (2, 6, 7, 8)

update [shared].[PM2_2017]

set CRACKING_LEVEL = 1

  where CRACKING_percent_VN < 5

update [shared].[PM2_2017]

  set CRACKING_LEVEL = 2

  where CRACKING_percent_VN >= 5

  and CRACKING_percent_VN <=10

  AND SURFACE_TYPE_VN IN (5);

  update [shared].[PM2_2017]

  set CRACKING_LEVEL = 2

  where CRACKING_percent_VN >= 5

  and CRACKING_percent_VN <=15

  AND SURFACE_TYPE_VN IN (10, 3, 4);

   update [shared].[PM2_2017]

  set CRACKING_LEVEL = 2

  where CRACKING_percent_VN >= 5

  and CRACKING_percent_VN <=20

  AND SURFACE_TYPE_VN IN (2, 6, 7);

update [shared].[PM2_2017]

  set CRACKING_LEVEL = 3

  where CRACKING_percent_VN >10

  AND SURFACE_TYPE_VN IN (5);

  update [shared].[PM2_2017]

  set CRACKING_LEVEL = 3

  where CRACKING_percent_VN >15

  AND SURFACE_TYPE_VN IN (10, 3, 4);

    update [shared].[PM2_2017]

  set CRACKING_LEVEL = 3

  where CRACKING_percent_VN >20

  AND SURFACE_TYPE_VN IN (2, 6, 7);

  update [shared].[PM2_2017]

  set FAULTING_LEVEL = 1

  where FAULTING_VN < 0.1

  and SURFACE_TYPE_VN IN (3, 4, 10, 11)

  update [shared].[PM2_2017]

  set FAULTING_LEVEL = 2

  where FAULTING_VN >= 0.1

  AND FAULTING_VN <=0.15

  AND SURFACE_TYPE_VN IN (3, 4, 10, 11)

  update [shared].[PM2_2017]

  set FAULTING_LEVEL = 3

  where FAULTING_VN >0.15

  AND SURFACE_TYPE_VN IN (3, 4, 10, 11)

  update [shared].[PM2_2017]

  set PM_LEVEL = 1

    where IRI_LEVEL =1  AND CRACKING_LEVEL = 1 AND (RUTTING_LEVEL = 1 OR FAULTING_LEVEL = 1)

  update [shared].[PM2_2017]

  set PM_LEVEL = 3

    where (IRI_LEVEL =3 AND CRACKING_LEVEL = 3)

       OR (IRI_LEVEL = 3 AND RUTTING_LEVEL = 3)

       OR (IRI_LEVEL = 3 AND FAULTING_LEVEL = 3)

       OR (CRACKING_LEVEL = 3 AND FAULTING_LEVEL = 3)

  update [shared].[PM2_2017]

  set PM_LEVEL = 2

    where IRI_LEVEL != 0 AND IRI_LEVEL != 0 AND (RUTTING_LEVEL != 0 OR FAULTING_LEVEL != 0)

       AND PM_LEVEL not in (1, 3)

the results for each HPMS year are published here on our ArcGIS Online site.

http://ksdot.maps.arcgis.com/home/group.html?id=2f2d331c83d64e8882877cad7e18661b#overview 

This was not the most straightforward mapping I've done, so I thought this would be good to share the formula for how we at KDOT interpret the PM2 rules.

Maybe next time I'll parameter this for the HPMS year, but for now I've only done this for two years.