addresses_not_in_caclr_potentially_fixable.sql

--------------------------------------------------------------------------------------
--Find addresses in OSM that don't appear in CACLR!
--TODO: Check if only name is wrong (name + distance nearby)
--TODO: Check if only postcode is wrong (postcode + distance nearby)

DROP VIEW IF EXISTS luxembourg_boundary;
CREATE VIEW luxembourg_boundary
AS SELECT ST_Multi(ST_Collect(way)) as way
    FROM planet_osm_polygon
    WHERE "admin_level" = '2'
      AND "boundary" = 'administrative'
      AND tags -> 'ISO3166-1' = 'LU';

WITH all_osm_addresses AS (
    SELECT "addr:housenumber", "addr:street", "addr:postcode", "addr:city", osm.way
    FROM planet_osm_point osm, luxembourg_boundary
    WHERE "addr:housenumber" IS NOT NULL
      AND "addr:street" IS NOT NULL
      AND "addr:postcode" IS NOT NULL
      AND "addr:city" IS NOT NULL
      AND ST_Contains(luxembourg_boundary.way, osm.way)
    UNION
    SELECT "addr:housenumber", "addr:street", "addr:postcode", "addr:city", osm.way
    FROM planet_osm_polygon osm, luxembourg_boundary
    WHERE "addr:housenumber" IS NOT NULL
      AND "addr:street" IS NOT NULL
      AND "addr:postcode" IS NOT NULL
      AND "addr:city" IS NOT NULL
      AND ST_Contains(luxembourg_boundary.way, osm.way)
--    EXCEPT --not good, since we need the geom as well, use LEFT JOIN instead
--    SELECT caclr.numero            AS "addr:housenumber",
--           caclr.rue               AS "addr:street",
--           caclr.code_postal::text AS "addr:postcode",
--           caclr.localite          AS "addr:city"
--    FROM addresses caclr
),
missing_in_osm AS (
    SELECT osm.* --ST_TRANSFORM(way,4326) --to load in DataGrip Geo Viewer
    FROM all_osm_addresses osm
             LEFT JOIN addresses caclr
                       ON caclr.numero = "addr:housenumber"
                           AND caclr.rue = "addr:street"
                           AND caclr.code_postal::text = "addr:postcode"
                           AND caclr.localite = "addr:city"
    WHERE caclr.numero IS NULL
      AND caclr.rue IS NULL
      AND caclr.code_postal IS NULL
      AND caclr.localite IS NULL
)
SELECT osm.*, caclr.*
FROM missing_in_osm osm
JOIN addresses caclr
    ON caclr.numero = "addr:housenumber"
     AND caclr.rue != "addr:street"
     AND caclr.code_postal::text = "addr:postcode"
     AND caclr.localite = "addr:city"
     AND ST_Distance(osm.way, ST_Transform(caclr.geom, 3857))* cosd(42.3521) < 20;