Skip to content

Instantly share code, notes, and snippets.

View DavidMoraisFerreira's full-sized avatar
🐢

David Morais Ferreira DavidMoraisFerreira

🐢
18 followers · 159 following
View GitHub Profile
@DavidMoraisFerreira
DavidMoraisFerreira / luxembourg_geo_challenge_1_23032020.csv
Last active March 23, 2020 14:38
Solution for the first geo-challenge posted on https://www.geoportail.lu/lb/geo-challenge/. Using QGIS (distance to nearest hub), BD-L-TC 2015 and BD-Adresses
localité HubDistance
Esch-sur-Alzette 9.675851265
Schifflange 84.14962467
Noertzange 390.5861058
Huncherange 89.2458766
Bergem 368.254373
Fennange 247.5908293
Bettembourg 145.6386522
Livange 64.63591331
Berchem 353.1625891
@DavidMoraisFerreira
DavidMoraisFerreira / build_hillshade_from_DTM_lu.sh
Last active March 26, 2020 12:55
#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
DavidMoraisFerreira / build_hillshade_from_DTM_lu_improved.sh
Last active May 30, 2020 11:14 — forked from Firefishy/gist:0f55dd3c195ddf6480f2f3bffbd2da99
#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
DavidMoraisFerreira / addresses_by_distance.sql
Last active June 17, 2020 14:39
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
DavidMoraisFerreira / bad bad buildings.sql
Created June 17, 2020 20:36
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
DavidMoraisFerreira / addresses_not_in_caclr_potentially_fixable.sql
Last active June 20, 2020 23:27
--------------------------------------------------------------------------------------
--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
DavidMoraisFerreira / diff.js
Created July 9, 2020 15:48 — forked from Simran-B/diff.js
Diff Match Patch as AQL UDF
/**
* 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
DavidMoraisFerreira / no-match.csv
Last active January 27, 2022 21:06
🇱🇺 Addresses in Luxembourg without a match to the official dataset (CACLR) ⏲️ Runs nightly at 5am ⬆️ https://dmlu.dev.openstreetmap.org/luxaddr/
We can't make this file beautiful and searchable because it's too large.
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
DavidMoraisFerreira / luxembourg.shp.geojson.poly
Last active September 7, 2020 10:02
Boundary for Luxembourg in POLY format. Extracted from the official dataset.
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
DavidMoraisFerreira / experiment_trp.sql
Last active January 27, 2022 21:05
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