update-addr-findings.sh

dmlu@errol:~/luxaddr$ cat /home/dmlu/luxaddr/update-addr-findings.sh
psql -A -F"," -P "footer=off" -d luxaddr > '/home/dmlu/public_html/luxaddr/no-match.csv' << EOF
COPY (
WITH osm_potential_addresses AS (
            SELECT osm_id, tags->'osm_user' as osm_user, tags->'osm_uid' as osm_uid, tags->'osm_version' as osm_version, tags->'osm_timestamp' as osm_timestamp, concat('https://osm.org/way/' , osm_id) as url, "addr:housenumber", "addr:place", "addr:street", "addr:postcode", "addr:city", "addr:country", "ref:caclr", "note", "note:caclr", "fixme", way
            FROM planet_osm_polygon
            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 "addr:country" like 'LU' or NULL
            UNION SELECT osm_id, tags->'osm_user' as osm_user, tags->'osm_uid' as osm_uid, tags->'osm_version' as osm_version, tags->'osm_timestamp' as osm_timestamp, concat('https://osm.org/node/' , osm_id) as url, "addr:housenumber", "addr:place", "addr:street", "addr:postcode", "addr:city", "addr:country", "ref:caclr", "note", "note:caclr", "fixme", way
            FROM planet_osm_point
            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 "addr:country" like 'LU' or NULL
            )
SELECT osm_id,
--     osm_user,
--     osm_version,
--     osm_timestamp,
       url,
       "addr:housenumber" AS numero,
       "addr:street"      AS rue,
       "addr:postcode"    AS codepostal,
       "addr:city"        AS localite,
       "note"
--      way

FROM   osm_potential_addresses
       LEFT JOIN addresses
              ON
              addresses.numero = osm_potential_addresses."addr:housenumber"
              AND
              addresses.localite = osm_potential_addresses."addr:city"
              AND addresses.rue = osm_potential_addresses."addr:street"
WHERE  addresses.localite IS NULL
       AND osm_potential_addresses."ref:caclr" IS NULL
       AND osm_potential_addresses."note:caclr" IS NULL
       AND osm_potential_addresses."addr:city" NOT in ('Esch-sur-Alzette', 'Luxembourg')
ORDER  BY osm_user, localite, rue, codepostal, numero
) TO stdout WITH (FORMAT CSV, HEADER TRUE, FORCE_QUOTE *);
EOF