bad bad buildings.sql

WITH potential_addresses AS (
	SELECT *
	FROM addresses
	WHERE ST_Contains((SELECT ST_SetSRID(ST_Buffer(ST_Extent(geom), 5), 4326)
					   FROM bad_buildings_in_osm), geom)
), potential_matches AS (
	SELECT osm.id,
		osm.geom,
		osm."addr:housenumber",
		string_agg(numero, ',' ORDER BY numero ASC) AS caclr_potential_match,
		string_agg(id_caclr_bat, ',') AS caclr_ref,
		xmlroot(xmlelement(name osm,
				   xmlattributes('0.6' AS "version", 'dmlu_fix_addresses_LU' AS "generator"),
				   xmlagg(
					xmlelement(name node,
							   xmlattributes(floor(random() * (999990) - 999999)::int as id, 'modify' as action, 'true' as visible, lat_wgs84 as lat, lon_wgs84 as lon),
							   xmlconcat(
									xmlelement(name tag, xmlattributes('addr:housenumber' AS "k", "numero" AS "v")),
									xmlelement(name tag, xmlattributes('addr:street' AS "k", "rue" AS "v")),
									xmlelement(name tag, xmlattributes('addr:city' AS "k", "localite" AS "v")),
									xmlelement(name tag, xmlattributes('addr:postcode' AS "k", "code_postal" AS "v")),
									xmlelement(name tag, xmlattributes('addr:country' AS "k", 'LU' AS "v")),
									xmlelement(name tag, xmlattributes('ref:caclr' AS "k", "id_caclr_bat" AS "v"))
								)
					)
				  )
		), version '1.0', standalone no) AS xml_caclr_nodes
	FROM bad_buildings_in_osm AS osm, potential_addresses AS caclr
	WHERE ST_Within(caclr.geom, osm.geom)
		AND id LIKE 'way/%'
	GROUP BY osm.id, osm.geom, osm."addr:housenumber"
)
SELECT *
FROM potential_matches
WHERE "addr:housenumber" = caclr_potential_match;