Convert kmph in analysis to mph (#892)

[lydiascarf]

Overview

Make analysis convert kmph speed limits to rounded mph

Demo

pfb=# SELECT osm.maxspeed, osm.osm_id FROM neighborhood_osm_full_line osm, neighborhood_ways WHERE neighborhood_ways.osm_id = osm.osm_id                                                         
AND (osm.maxspeed LIKE '% kmph' OR osm.maxspeed ~ '^\d+(\.\d+)?$');
 maxspeed |   osm_id   
----------+------------
 100      |   13224440
(...and a bunch of other rows...)
pfb=# SELECT osm.maxspeed, osm.osm_id FROM neighborhood_osm_full_line osm, neighborhood_ways WHERE neighborhood_ways.osm_id = osm.osm_id
AND (osm.maxspeed LIKE '% kmph' OR osm.maxspeed ~ '^\d+(\.\d+)?$') AND osm.osm_id=13224440
pfb-# ;
 maxspeed |  osm_id  
----------+----------
 100      | 13224440
(1 row)
pfb=# SELECT ROUND(substring(osm.maxspeed from '\d+')::INT / 1.609 / 5)*5
FROM    neighborhood_osm_full_line osm, neighborhood_ways
WHERE   neighborhood_ways.osm_id = osm.osm_id
AND     (osm.maxspeed LIKE '% kmph' OR osm.maxspeed ~ '^\d+(\.\d+)?$') AND osm.osm_id=13224440;
 ?column? 
----------
       60
(1 row)
pfb=# SELECT speed_limit FROM neighborhood_ways WHERE osm_id=13224440;
 speed_limit 
-------------
          60
(1 row)

Notes

Optional. Anillary topics, caveats, alternative strategies that didn't work out, anything else.

Testing Instructions

• Before running an analysis, place a bash right before line 266 of import_osm.sh
• when the analysis catches on that bash a while later, run psql -h $NB_POSTGRESQL_HOST -U ${NB_POSTGRESQL_USER} -d ${NB_POSTGRESQL_DB} to open a psql shell
• look above in the demo section and run those commands, changing the osm_id at the end of the 2nd, 3rd, and 4th queries to match an osm_id you see in the 1st command's output
  • the 1st one looks at the existing maxspeeds
  • the 2nd one zooms in on a specific row (you will likely have to change the osm_id to one you see from the 1st command's output). take note of the maxspeed
  • the 3rd one should output the maxspeed from the 2nd command converted to kmph and rounded to the nearest 5
  • before running the 4th one, exit the psql shell and, run psql -h $NB_POSTGRESQL_HOST -U ${NB_POSTGRESQL_USER} -d ${NB_POSTGRESQL_DB} -f ../features/speed_limit.sql
  • now you can go back into the psql shell and verify that the 4th command's output matches the 3rd command's output

Checklist

• [x] Add entry to CHANGELOG.md

Resolves #892