addresses_by_distance.sql

WITH osm_potential_addresses AS (
    SELECT osm_id, "addr:housenumber", "addr:street", "addr:postcode", "addr:city", "ref:caclr", "note", "note:caclr", "fixme", way
    FROM planet_osm_polygon
    WHERE building IS NOT NULL
      AND "addr:housenumber" IS NOT NULL
      AND "addr:street" IS NOT NULL
      AND "addr:postcode" IS NOT NULL
      AND "addr:city" IS NOT NULL
    UNION SELECT osm_id, "addr:housenumber", "addr:street", "addr:postcode", "addr:city", "ref:caclr", "note", "note:caclr", "fixme", way
    FROM planet_osm_point
    WHERE building IS NOT NULL
      AND "addr:housenumber" IS NOT NULL
      AND "addr:street" IS NOT NULL
      AND "addr:postcode" IS NOT NULL
      AND "addr:city" IS NOT NULL
)
SELECT osm.*, caclr.id_caclr_bat, ST_DISTANCE(ST_CENTROID(osm.way), ST_TRANSFORM(caclr.geom, 3857)) AS dist, ST_TRANSFORM(caclr.geom, 3857) as caclr_geom, ST_AsText(ST_ShortestLine(ST_CENTROID(osm.way), ST_TRANSFORM(caclr.geom, 3857)))
FROM osm_potential_addresses osm, addresses caclr
WHERE osm."ref:caclr" IS NULL
  AND osm."addr:housenumber" = caclr.numero
  --AND osm."addr:housenumber" ~ '-'
  AND osm."addr:city" = caclr.commune
  AND osm."addr:postcode" = caclr.code_postal::text
  AND osm."addr:street" = caclr.rue
ORDER BY dist DESC;