DavidMoraisFerreira

/**
 * Register AQL UDF in arangosh:
 *   var aqlfunctions = require("@arangodb/aql/functions");
 *   var diff = require("diff.js");
 *   aqlfunctions.register("TEXT::DIFF", diff, true);
 *
 * Test function in arangosh:
 *   db._query(`RETURN TEXT::DIFF("hello world", "world!")`)
 *
 * Example result:

DavidMoraisFerreira

--------------------------------------------------------------------------------------
--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'

DavidMoraisFerreira

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,

DavidMoraisFerreira

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

DavidMoraisFerreira

#cd /path-to-dir/LUREF_NGL/; find . -type f -iname '*.tif' >input-files.txt
#docker run -it --rm -v /path-to-dir/LUREF_NGL:/data osgeo/gdal:alpine-normal-v2.4.1 sh -l
#(in docker) gdalbuildvrt -resolution highest -r nearest -a_srs "EPSG:2169" ANA_LUREF_NGL_DTM.vrt -input_file_list input-files.txt
# Rest not in docker...

gdaladdo -ro --config COMPRESS DEFLATE --config COMPRESS_OVERVIEW DEFLATE --config ZLEVEL 9 --config BIGTIFF_OVERVIEW IF_SAFER --config GDAL_TIFF_OVR_BLOCKSIZE 512 -r nearest ANA_LUREF_NGL_DTM.vrt 4 16 64 256 1024 4096

#Hillshade
gdaldem hillshade ANA_LUREF_NGL_DTM.vrt lu_hillshade_2017.tif -co BIGTIFF=YES -co TILED=YES -co COMPRESS=DEFLATE -co GDAL_NUM_THREADS=ALL_CPUS -of GTiff -b 1 -z 1.0 -s 0.5 -az 315.0 -alt 45.0
gdalwarp -t_srs epsg:3857 -r lanczos -multi -wo NUM_THREADS=ALL_CPUS lu_hillshade_2017.tif lu_hillshade_2017-epsg-3857.tif

DavidMoraisFerreira

#Requires GDAL, resulting BigTif is about 5GB
#https://data.public.lu/fr/datasets/digital-terrain-model-high-dem-resolution/
wget https://data.public.lu/fr/datasets/r/b21141d7-afc3-42a1-9c65-4587169ef3a7 -O ANA_LUREF_NGL_DTM.zip
unzip ANA_LUREF_NGL_DTM.zip -d ANA_LUREF_NGL_DTM
cd ANA_LUREF_NGL_DTM
mkdir AllZones
cp Zone1_Zone2/* AllZones
cp Zone3_Zone4/* AllZones
gdalbuildvrt -resolution average -r nearest ANA_LUREF_NGL_DTM.vrt "AllZones/*.tif" -a_srs "EPSG:2169"
gdaldem hillshade ANA_LUREF_NGL_DTM.vrt lu_hillshade_2017.tif -of GTiff -b 1 -z 1.0 -s 0.5 az 315.0 -alt 45.0

DavidMoraisFerreira