DavidMoraisFerreira

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

#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

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

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

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

/**
 * 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

osm_id,url,numero,rue,codepostal,localite,note
"379545515","https://osm.org/way/379545515","1","Route de Leudelange","3311","Abweiler",
"302382940","https://osm.org/way/302382940","2","Route de Leudelange","3311","Abweiler",
"302382941","https://osm.org/way/302382941","4","Route de Leudelange","3311","Abweiler",
"302382943","https://osm.org/way/302382943","6","Route de Leudelange","3311","Abweiler",
"217189849","https://osm.org/way/217189849","1","Rue du Village","3311","Abweiler",
"217189832","https://osm.org/way/217189832","17","Rue du Village","3311","Abweiler",
"217189836","https://osm.org/way/217189836","2","Rue du Village","3311","Abweiler",
"217189833","https://osm.org/way/217189833","28","Rue du Village","3311","Abweiler",
"217189837","https://osm.org/way/217189837","35","Rue du Village","3311","Abweiler",

DavidMoraisFerreira

luxembourg.geojson
1
   5.888332   50.093352
   5.888453   50.093541
   5.888692   50.094004
   5.888784   50.094199
   5.889087   50.095078
   5.889186   50.095279
   5.889286   50.095547
   5.889339   50.095624

DavidMoraisFerreira

select ST_AsGeoJSON(t.*)
from (
        select distinct st_transform(ST_LineMerge(ST_UNION(trp_vc.wkb_geometry)), 4326),
            addresses.rue
        from trp_vc,
            addresses
        where trp_vc.id_rue_cac = addresses.id_caclr_rue
            and not exists (
                select 1
                from road_names_osm