road_comparison.sql

/*

Find out which reference ways aren't present in osm too

With many thanks to Jerry Clough (http://www.openstreetmap.org/user/SK53) and Rory McCann (http://www.openstreetmap.org/user/rorym)

reference is all reference highways, planet_osm_bighighways is all osm significant ways:

construction
cycleway
footway
living_street
motorway
motorway_link
pedestrian
primary
primary_link
raceway
residential
road
secondary
secondary_link
service
tertiary
tertiary_link
track
trunk
trunk_link
turning_circle
unclassified

We leave out:

bridleway
escape
elevator
no_longer_exist
platform
proposed
rest_area
services
steps

This casts a pretty wide net: we don't care about classification right now, care about what we miss more than about what we classify differently, and disagree more often than we miss anyway.

Ideally, we'd be able to compare by type, but I think the reference data isn't good enough :).

I wonder how we could filter out the ones that are reported as false positives on maproulette from a refined challenge with a smaller buffer/filter. Maybe get their coordinates as bbox, and filter out the ways that intersect with that bbox from the new challenge.

TODO: only consider the reference ways that were extra the previous time
TODO: try to compare by ref ~= /^CR /, by type, etc.
TODO: cut ref lines at the national border, we're getting lots of FR/BE/DE false positives! Note: didn't I do that already?
TODO: different tolerance for different road importance. For A/B/N/CR or similar osm types, we want low tolerance.

*/

--  BEFORE RUNNING, import the reference ways with ~/osm/osm-road-comparison-0.1/import-reference.sh

SET work_mem TO 1200000;
-- Time: 1.019 ms

DROP TABLE if exists planet_osm_bighighways, planet_osm_bighighways_buffer, reference_union, reference_extra, reference_extra_interesting;
-- Time: 13.708 ms


-- Join all our reference ways in one big union
-- Time: 68870.954 ms (01:08.871)
SELECT
    st_union(reference.geometry)
AS geometry
INTO reference_union
FROM reference;


--- Select our OSM ways
-- TIME: Time: 1039.148 ms (00:01.039)
SELECT planet_osm_line.way AS geometry
INTO planet_osm_bighighways
FROM planet_osm_line
WHERE highway in
('cycleway', 'construction', 'footway', 'living_street', 'motorway', 'motorway_link', 'path', 'pedestrian', 'primary', 'primary_link', 'raceway', 'residential', 'road', 'secondary', 'secondary_link', 'service', 'tertiary', 'tertiary_link', 'track', 'trunk', 'trunk_link', 'turning_circle', 'unclassified', 'steps');

-- Select our OSM polygons - the exterior ring, actually.
-- Time: 92.938 ms
INSERT INTO planet_osm_bighighways (geometry)
SELECT
    ST_ExteriorRing(
        (ST_DumpRings(
            (ST_Dump(planet_osm_polygon.way)).geom
        )).geom
    ) AS geometry
FROM planet_osm_polygon
WHERE highway in
('construction', 'footway', 'living_street', 'pedestrian', 'raceway', 'residential', 'service');

-- Build a big polygon of the buffer around our osm ways
-- Used to take 162723.489 ms in 2015 - a lot more ways now?
-- Time: 633117.046 ms (10:33.117)
-- Buffer of union takes 2.5* as long
SELECT
    ST_Union(
        ST_Buffer(
            planet_osm_bighighways.geometry, 15
        )
    )
AS geometry
INTO planet_osm_bighighways_buffer
FROM planet_osm_bighighways;

-- Difference between the two
-- Start here when you're updating
-- Time 2015: 1773 875.884 ms (29 minutes)
-- Time: 3893534.879 ms (01:04:53.535)
SELECT
    ST_Difference(reference_union.geometry, planet_osm_bighighways_buffer.geometry) as geometry
    INTO reference_extra
    FROM reference_union, planet_osm_bighighways_buffer;

SELECT st_length(reference_extra.geometry) from reference_extra;
 -- 439.553 km 2014-06-21 (buffer de 10)
 -- 294.346 km 2014-06-30 (buffer de 15)
 -- 283.951 km 2014-10-14 (buffer de 15)
 -- 174.999 km 2015-01-13 (buffer de 15)
-- 162.248 km 2018-10-04 (buffer de 15)


-- cookie cutter along the lines
-- Time: 2431.166
SELECT
 ST_Intersection(reference_extra.geometry, letzebuerg.way) AS geometry
 into reference_tmp
FROM
 (select way from planet_osm_polygon WHERE admin_level='2') as letzebuerg,
 reference_extra;
drop table reference_extra;
alter table reference_tmp rename to reference_extra;

 -- To update, don't re-do all the work
 drop table reference_union;
 alter table reference_extra rename to reference_union;

-- Maybe filter out bits that are smaller than 10 + buffer metres long

select reference_extra_ways.geometry, st_length(reference_extra_ways.geometry) as length
    into reference_extra_interesting
    from (select (ST_Dump(geometry)).geom as geometry from reference_union) as reference_extra_ways
    where st_length(reference_extra_ways.geometry) > 10
    -- order by length limit 1000
    ;