AltoAdige - Südtirol/OpenGisData HouseNumber Import2/Technical Import Documentation

Import Documentation

1. Install gdal 1.10

2. Edit of /usr/share/gdal/1.10/osmconf.ini to edit the custom OSM tag to import

  [points] 
  ...
  attributes=name,barrier,highway,ref,address,is_in,place,man_made,
  addr:country,addr:city,addr:postcode,addr:place,addr:street,addr:housenumber,addr:full,
  source,name_de,name_it
  ...
  [multipolygons]
  attributes=name,type,aeroway,amenity,admin_level,barrier,boundary,
  building,craft,geological,historic,land_area,landuse,leisure,man_made,military,
  natural,office,place,shop,sport,tourism,
  addr:city,addr:postcode,addr:place,addr:street,addr:housenumber,addr:full,source,name_de,name_it

3. Get the italy OSM data

  wget http://download.geofabrik.de/europe/italy-latest.osm.bz2

4. Unzip the file

  bunzip2 italy-latest.osm.bz2

5. Create the PostgreSQL - PostGIS database

  CREATE ROLE opengisdata;
  CREATE DATABASE opengisdata WITH ENCODING='UTF8' OWNER=opengisdata TEMPLATE=postgis;
  ALTER TABLE geometry_columns OWNER TO opengisdata;
  ALTER TABLE spatial_ref_sys OWNER TO opengisdata;
  ALTER TABLE geography_columns OWNER TO opengisdata;
  CREATE SCHEMA aa_data AUTHORIZATION opengisdata;
  CREATE SCHEMA analisi AUTHORIZATION opengisdata;
  CREATE SCHEMA osm_data AUTHORIZATION opengisdata;
  CREATE SCHEMA export AUTHORIZATION opengisdata;

6. Import the italy-latest.osm in the PostGIS DB with ogr2ogr using OSM drv

  ogr2ogr -f PostgreSQL PG:"dbname=opengisdata host=192.168.0.13 user='opengisdata' password='opengisdatax'" 
  -lco SCHEMA=osm_data -lco GEOMETRY_NAME="the_geom" -lco DIM=2 -lco OVERWRITE="YES" 
  --config OSM_USE_CUSTOM_INDEXING NO Italy-latest.osm

7. Import the South Tyrol house numbers (WEGE DB)

8. Do custom query in PostGIS to normalize WEGE data on the OSM documentation and rectify the actually OSM house numbers

8.1 Extract the points and the polygons with address number from OSM

   CREATE TABLE osm_data.aa_points AS 
   SELECT
   ogc_fid,
   osm_id,
   addr_country,
   addr_city,
   addr_postcode,
   addr_place,
   addr_street,
   addr_housenumber,
   addr_full,
   source,
   name_de,
   name_it,
   the_geom
   FROM osm_data.points 
   WHERE ST_Intersects(the_geom,(select the_geom from aa_data.boundaries_4326)) AND addr_housenumber IS NOT NULL;
   ALTER TABLE osm_data.aa_points ADD CONSTRAINT aa_points_pk PRIMARY KEY (ogc_fid );
   ALTER TABLE osm_data.aa_points ADD CONSTRAINT enforce_dims_the_geom CHECK (st_ndims(the_geom) = 2);
   ALTER TABLE osm_data.aa_points ADD CONSTRAINT enforce_geotype_the_geom CHECK (geometrytype(the_geom) = 'POINT'::text OR the_geom IS NULL);
   ALTER TABLE osm_data.aa_points ADD CONSTRAINT enforce_srid_the_geom CHECK (st_srid(the_geom) = 4326);
   --
   CREATE TABLE osm_data.aa_polygons AS 
   SELECT
   ogc_fid,
   osm_id, 
   osm_way_id,
   'IT'::character varying as addr_country,
   addr_city,
   addr_postcode,
   addr_place,
   addr_street,
   addr_housenumber,
   addr_full,
   source,
   name_de,
   name_it,
   st_multi(the_geom) as polygon_geom,
   st_centroid(the_geom) as the_geom
   FROM osm_data.multipolygons 
   WHERE ST_Intersects(the_geom,(select the_geom from aa_data.boundaries_4326)) AND addr_housenumber IS NOT NULL;
   ALTER TABLE osm_data.aa_polygons ADD CONSTRAINT aa_polygons_pk PRIMARY KEY (ogc_fid );
   ALTER TABLE osm_data.aa_polygons ADD  CONSTRAINT enforce_dims_the_geom CHECK (st_ndims(the_geom) = 2);
   ALTER TABLE osm_data.aa_polygons ADD  CONSTRAINT enforce_geotype_the_geom CHECK (geometrytype(the_geom) = 'POINT'::text OR the_geom IS NULL);
   ALTER TABLE osm_data.aa_polygons ADD  CONSTRAINT enforce_geotype_polygon_geom CHECK (geometrytype(polygon_geom) = 'MULTIPOLYGON'::text OR the_geom IS NULL);
   ALTER TABLE osm_data.aa_polygons ADD  CONSTRAINT enforce_srid_the_geom CHECK (st_srid(the_geom) = 4326);

8.2 Correction of the WEGE data:

   CREATE TABLE aa_data.wegehausnum_xy_correct AS SELECT * FROM aa_data.wegehausnum_xy;
   UPDATE aa_data.wegehausnum_xy_correct SET vied=INITCAP(vied);
   UPDATE aa_data.wegehausnum_xy_correct SET vied=REPLACE(vied,' Von ',' von ');
   UPDATE aa_data.wegehausnum_xy_correct SET vied=REPLACE(vied,' Der ',' der ');
   UPDATE aa_data.wegehausnum_xy_correct SET vied=REPLACE(vied,' Die ',' die ');
   UPDATE aa_data.wegehausnum_xy_correct SET vied=REPLACE(vied,' Das ',' das ');
   UPDATE aa_data.wegehausnum_xy_correct SET vied=REPLACE(vied,' Des ',' des ');
   UPDATE aa_data.wegehausnum_xy_correct SET vied=REPLACE(vied,' Dem ',' dem ');
   UPDATE aa_data.wegehausnum_xy_correct SET vied=REPLACE(vied,' Den ',' den ');
   UPDATE aa_data.wegehausnum_xy_correct SET vied=REPLACE(vied,' Und ',' und ');
   UPDATE aa_data.wegehausnum_xy_correct SET vied=REPLACE(vied,' Am ',' am ');
   UPDATE aa_data.wegehausnum_xy_correct SET vied=REPLACE(vied,' Unterm ',' unterm ');
   UPDATE aa_data.wegehausnum_xy_correct SET vied=REPLACE(vied,' Untern ',' untern ');
   UPDATE aa_data.wegehausnum_xy_correct SET vied=REPLACE(vied,' Unter ',' unter ');
   UPDATE aa_data.wegehausnum_xy_correct SET vied=REPLACE(vied,' In ',' in ');
   UPDATE aa_data.wegehausnum_xy_correct SET vied=REPLACE(vied,' Im ',' im ');
   UPDATE aa_data.wegehausnum_xy_correct SET vied=REPLACE(vied,'Gross','Groß');
   UPDATE aa_data.wegehausnum_xy_correct SET vied=REPLACE(vied,'Weiss','Weiß');
   UPDATE aa_data.wegehausnum_xy_correct SET vied=REPLACE(vied,'Ausser','Außer');
   UPDATE aa_data.wegehausnum_xy_correct SET vied=REPLACE(vied,'Schiessstand','Schießstand');
   UPDATE aa_data.wegehausnum_xy_correct SET vied=REPLACE(vied,'Schiesstand','Schießstand');
   UPDATE aa_data.wegehausnum_xy_correct SET vied=REPLACE(vied,'Griessfeld','Grießfeld');
   UPDATE aa_data.wegehausnum_xy_correct SET vied=REPLACE(vied,'Griessweg','Grießweg');
   UPDATE aa_data.wegehausnum_xy_correct SET vied=REPLACE(vied,'Tessman','Teßman');
   UPDATE aa_data.wegehausnum_xy_correct SET vied=TRIM((REPLACE(REPLACE(vied,'Lok.',),'Ortsch.',)));
   UPDATE aa_data.wegehausnum_xy_correct SET vied=REPLACE(vied,'str.','straße ');
   UPDATE aa_data.wegehausnum_xy_correct SET vied=REPLACE(vied,'Str.','Straße ');
   UPDATE aa_data.wegehausnum_xy_correct SET vied=REPLACE(vied,'Strasse','Straße');
   UPDATE aa_data.wegehausnum_xy_correct SET vied=REPLACE(vied,'strasse','straße');
   UPDATE aa_data.wegehausnum_xy_correct SET vied=REPLACE(vied,'. - ','.');
   UPDATE aa_data.wegehausnum_xy_correct SET vied=REPLACE(vied,' - ','-');
   UPDATE aa_data.wegehausnum_xy_correct SET vied=REPLACE(vied,'.','. ');
   UPDATE aa_data.wegehausnum_xy_correct SET vied=REPLACE(vied,'.  ','. ');
   UPDATE aa_data.wegehausnum_xy_correct SET vied=REPLACE(vied,'. -','.-');
   UPDATE aa_data.wegehausnum_xy_correct SET vied=REPLACE(vied,'"',);
   UPDATE aa_data.wegehausnum_xy_correct SET vied=REPLACE(vied,'  ',' ');
   UPDATE aa_data.wegehausnum_xy_correct SET vied=upper(substring(vied FROM 1 for 1)) || substring(vied FROM 2 for length(vied));
   --
   UPDATE aa_data.wegehausnum_xy_correct SET viei=INITCAP(viei);
   UPDATE aa_data.wegehausnum_xy_correct SET viei=REPLACE(viei,' Del ',' del ');
   UPDATE aa_data.wegehausnum_xy_correct SET viei=REPLACE(viei,' Di ',' di ');
   UPDATE aa_data.wegehausnum_xy_correct SET viei=REPLACE(viei,' Della ',' della ');
   UPDATE aa_data.wegehausnum_xy_correct SET viei=REPLACE(viei,' Dello ',' dello ');
   UPDATE aa_data.wegehausnum_xy_correct SET viei=REPLACE(viei,' Delle ',' delle ');
   UPDATE aa_data.wegehausnum_xy_correct SET viei=REPLACE(viei,' Degli ',' degli ');
   UPDATE aa_data.wegehausnum_xy_correct SET viei=REPLACE(viei,' Dell ',' dell ');
   UPDATE aa_data.wegehausnum_xy_correct SET viei=REPLACE(viei,' Dell,' dell);
   UPDATE aa_data.wegehausnum_xy_correct SET viei=REPLACE(viei,' Dei ',' dei ');
   UPDATE aa_data.wegehausnum_xy_correct SET viei=REPLACE(viei,' Su ',' su ');
   UPDATE aa_data.wegehausnum_xy_correct SET viei=REPLACE(viei,' Sul ',' sul ');
   UPDATE aa_data.wegehausnum_xy_correct SET viei=REPLACE(viei,' Sulla ',' sulla ');
   UPDATE aa_data.wegehausnum_xy_correct SET viei=REPLACE(viei,' Sulle ',' sulle ');
   UPDATE aa_data.wegehausnum_xy_correct SET viei=REPLACE(viei,' Sullo ',' sullo ');
   UPDATE aa_data.wegehausnum_xy_correct SET viei=REPLACE(viei,' Sugli ',' sugli ');
   UPDATE aa_data.wegehausnum_xy_correct SET viei=REPLACE(viei,' Sull ',' sull ');
   UPDATE aa_data.wegehausnum_xy_correct SET viei=REPLACE(viei,' Il ',' il ');
   UPDATE aa_data.wegehausnum_xy_correct SET viei=REPLACE(viei,' La ',' la ');
   UPDATE aa_data.wegehausnum_xy_correct SET viei=REPLACE(viei,' Lo ',' lo ');
   UPDATE aa_data.wegehausnum_xy_correct SET viei=REPLACE(viei,' Le ',' le ');
   UPDATE aa_data.wegehausnum_xy_correct SET viei=REPLACE(viei,' Gli ',' gli ');
   UPDATE aa_data.wegehausnum_xy_correct SET viei=REPLACE(viei,' Sopra ',' sopra ');
   UPDATE aa_data.wegehausnum_xy_correct SET viei=REPLACE(viei,' Sotto ',' sotto ');
   UPDATE aa_data.wegehausnum_xy_correct SET viei=REPLACE(viei,' Sotto ',' sotto ');
   UPDATE aa_data.wegehausnum_xy_correct SET viei=REPLACE(viei,' Al ',' al ');
   UPDATE aa_data.wegehausnum_xy_correct SET viei=REPLACE(viei,' Alle ',' alle ');
   UPDATE aa_data.wegehausnum_xy_correct SET viei=REPLACE(viei,' Agli ',' agli ');
   UPDATE aa_data.wegehausnum_xy_correct SET viei=REPLACE(viei,' Allo ',' allo ');
   UPDATE aa_data.wegehausnum_xy_correct SET viei=TRIM(REPLACE(REPLACE(viei,'Loc.',),'L0c.',));
   UPDATE aa_data.wegehausnum_xy_correct SET viei=REPLACE(viei,'Str.','Strada ');
   UPDATE aa_data.wegehausnum_xy_correct SET viei=REPLACE(viei,'V.','Via ');
   UPDATE aa_data.wegehausnum_xy_correct SET viei=REPLACE(viei,'Vic.','Vicolo ');
   UPDATE aa_data.wegehausnum_xy_correct SET viei=REPLACE(viei,'Can.','Canale ');
   UPDATE aa_data.wegehausnum_xy_correct SET viei=REPLACE(viei,'Passegg.','Passeggiata ');
   UPDATE aa_data.wegehausnum_xy_correct SET viei=REPLACE(viei,'Dott.','Dr.');
   UPDATE aa_data.wegehausnum_xy_correct SET viei=REPLACE(viei,'P.Zza','Piazza ');
   UPDATE aa_data.wegehausnum_xy_correct SET viei=REPLACE(viei,'Za.','Zona ');
   UPDATE aa_data.wegehausnum_xy_correct SET viei=REPLACE(viei,'. - ','.');
   UPDATE aa_data.wegehausnum_xy_correct SET viei=REPLACE(viei,' - ','-');
   UPDATE aa_data.wegehausnum_xy_correct SET viei=REPLACE(viei,'.','. ');
   UPDATE aa_data.wegehausnum_xy_correct SET viei=REPLACE(viei,'.  ','. ');
   UPDATE aa_data.wegehausnum_xy_correct SET viei=REPLACE(viei,'. -','.-');
   UPDATE aa_data.wegehausnum_xy_correct SET viei=REPLACE(viei,'"',);
   UPDATE aa_data.wegehausnum_xy_correct SET viei=REPLACE(viei,'  ',' ');
   UPDATE aa_data.wegehausnum_xy_correct SET viei=upper(substring(viei FROM 1 for 1)) || substring(viei FROM 2 for length(viei));
   --
   UPDATE aa_data.wegehausnum_xy_correct SET frazd=INITCAP(frazd);
   UPDATE aa_data.wegehausnum_xy_correct SET frazd=REPLACE(frazd,' Von ',' von ');
   UPDATE aa_data.wegehausnum_xy_correct SET frazd=REPLACE(frazd,' Der ',' der ');
   UPDATE aa_data.wegehausnum_xy_correct SET frazd=REPLACE(frazd,' Die ',' die ');
   UPDATE aa_data.wegehausnum_xy_correct SET frazd=REPLACE(frazd,' Das ',' das ');
   UPDATE aa_data.wegehausnum_xy_correct SET frazd=REPLACE(frazd,' Des ',' des ');
   UPDATE aa_data.wegehausnum_xy_correct SET frazd=REPLACE(frazd,' Dem ',' dem ');
   UPDATE aa_data.wegehausnum_xy_correct SET frazd=REPLACE(frazd,' Den ',' den ');
   UPDATE aa_data.wegehausnum_xy_correct SET frazd=REPLACE(frazd,' Und ',' und ');
   UPDATE aa_data.wegehausnum_xy_correct SET frazd=REPLACE(frazd,' Am ',' am ');
   UPDATE aa_data.wegehausnum_xy_correct SET frazd=REPLACE(frazd,' Unterm ',' unterm ');
   UPDATE aa_data.wegehausnum_xy_correct SET frazd=REPLACE(frazd,' Untern ',' untern ');
   UPDATE aa_data.wegehausnum_xy_correct SET frazd=REPLACE(frazd,' Unter ',' unter ');
   UPDATE aa_data.wegehausnum_xy_correct SET frazd=REPLACE(frazd,' In ',' in ');
   UPDATE aa_data.wegehausnum_xy_correct SET frazd=REPLACE(frazd,' Im ',' im ');
   UPDATE aa_data.wegehausnum_xy_correct SET frazd=REPLACE(frazd,'Gross','Groß');
   UPDATE aa_data.wegehausnum_xy_correct SET frazd=REPLACE(frazd,'Weiss','Weiß');
   UPDATE aa_data.wegehausnum_xy_correct SET frazd=REPLACE(frazd,'Ausser','Außer');
   UPDATE aa_data.wegehausnum_xy_correct SET frazd=REPLACE(frazd,'Schiessstand','Schießstand');
   UPDATE aa_data.wegehausnum_xy_correct SET frazd=REPLACE(frazd,'Schiesstand','Schießstand');
   UPDATE aa_data.wegehausnum_xy_correct SET frazd=REPLACE(frazd,'Griessfeld','Grießfeld');
   UPDATE aa_data.wegehausnum_xy_correct SET frazd=REPLACE(frazd,'Griessweg','Grießweg');
   UPDATE aa_data.wegehausnum_xy_correct SET frazd=REPLACE(frazd,'Tessman','Teßman');
   UPDATE aa_data.wegehausnum_xy_correct SET frazd=TRIM((REPLACE(REPLACE(frazd,'Lok.',),'Ortsch.',)));
   UPDATE aa_data.wegehausnum_xy_correct SET frazd=REPLACE(frazd,'str.','straße ');
   UPDATE aa_data.wegehausnum_xy_correct SET frazd=REPLACE(frazd,'Str.','Straße ');
   UPDATE aa_data.wegehausnum_xy_correct SET frazd=REPLACE(frazd,'Strasse','Straße');
   UPDATE aa_data.wegehausnum_xy_correct SET frazd=REPLACE(frazd,'strasse','straße');
   UPDATE aa_data.wegehausnum_xy_correct SET frazd=REPLACE(frazd,'. - ','.');
   UPDATE aa_data.wegehausnum_xy_correct SET frazd=REPLACE(frazd,' - ','-');
   UPDATE aa_data.wegehausnum_xy_correct SET frazd=REPLACE(frazd,'.','. ');
   UPDATE aa_data.wegehausnum_xy_correct SET frazd=REPLACE(frazd,'.  ','. ');
   UPDATE aa_data.wegehausnum_xy_correct SET frazd=REPLACE(frazd,'. -','.-');
   UPDATE aa_data.wegehausnum_xy_correct SET frazd=REPLACE(frazd,'"',);
   UPDATE aa_data.wegehausnum_xy_correct SET frazd=NULL where frazd='-';
   UPDATE aa_data.wegehausnum_xy_correct SET frazd=NULL where frazd='---';
   UPDATE aa_data.wegehausnum_xy_correct SET frazd=REPLACE(frazd,'Frak.',);
   UPDATE aa_data.wegehausnum_xy_correct SET frazd=REPLACE(frazd,'Frakt.',);
   UPDATE aa_data.wegehausnum_xy_correct SET frazd=REPLACE(frazd,'  ',' ');
   UPDATE aa_data.wegehausnum_xy_correct SET frazd=upper(substring(frazd FROM 1 for 1)) || substring(frazd FROM 2 for length(frazd));
   --
   UPDATE aa_data.wegehausnum_xy_correct SET frazi=INITCAP(frazi);
   UPDATE aa_data.wegehausnum_xy_correct SET frazi=REPLACE(frazi,' Del ',' del ');
   UPDATE aa_data.wegehausnum_xy_correct SET frazi=REPLACE(frazi,' Di ',' di ');
   UPDATE aa_data.wegehausnum_xy_correct SET frazi=REPLACE(frazi,' Della ',' della ');
   UPDATE aa_data.wegehausnum_xy_correct SET frazi=REPLACE(frazi,' Dello ',' dello ');
   UPDATE aa_data.wegehausnum_xy_correct SET frazi=REPLACE(frazi,' Delle ',' delle ');
   UPDATE aa_data.wegehausnum_xy_correct SET frazi=REPLACE(frazi,' Degli ',' degli ');
   UPDATE aa_data.wegehausnum_xy_correct SET frazi=REPLACE(frazi,' Dell ',' dell ');
   UPDATE aa_data.wegehausnum_xy_correct SET frazi=REPLACE(frazi,' Dell,' dell);
   UPDATE aa_data.wegehausnum_xy_correct SET frazi=REPLACE(frazi,' Dei ',' dei ');
   UPDATE aa_data.wegehausnum_xy_correct SET frazi=REPLACE(frazi,' Su ',' su ');
   UPDATE aa_data.wegehausnum_xy_correct SET frazi=REPLACE(frazi,' Sul ',' sul ');
   UPDATE aa_data.wegehausnum_xy_correct SET frazi=REPLACE(frazi,' Sulla ',' sulla ');
   UPDATE aa_data.wegehausnum_xy_correct SET frazi=REPLACE(frazi,' Sulle ',' sulle ');
   UPDATE aa_data.wegehausnum_xy_correct SET frazi=REPLACE(frazi,' Sullo ',' sullo ');
   UPDATE aa_data.wegehausnum_xy_correct SET frazi=REPLACE(frazi,' Sugli ',' sugli ');
   UPDATE aa_data.wegehausnum_xy_correct SET frazi=REPLACE(frazi,' Sull ',' sull ');
   UPDATE aa_data.wegehausnum_xy_correct SET frazi=REPLACE(frazi,' Il ',' il ');
   UPDATE aa_data.wegehausnum_xy_correct SET frazi=REPLACE(frazi,' La ',' la ');
   UPDATE aa_data.wegehausnum_xy_correct SET frazi=REPLACE(frazi,' Lo ',' lo ');
   UPDATE aa_data.wegehausnum_xy_correct SET frazi=REPLACE(frazi,' Le ',' le ');
   UPDATE aa_data.wegehausnum_xy_correct SET frazi=REPLACE(frazi,' Gli ',' gli ');
   UPDATE aa_data.wegehausnum_xy_correct SET frazi=REPLACE(frazi,' Sopra ',' sopra ');
   UPDATE aa_data.wegehausnum_xy_correct SET frazi=REPLACE(frazi,' Sotto ',' sotto ');
   UPDATE aa_data.wegehausnum_xy_correct SET frazi=REPLACE(frazi,' Sotto ',' sotto ');
   UPDATE aa_data.wegehausnum_xy_correct SET frazi=REPLACE(frazi,' Al ',' al ');
   UPDATE aa_data.wegehausnum_xy_correct SET frazi=REPLACE(frazi,' Alle ',' alle ');
   UPDATE aa_data.wegehausnum_xy_correct SET frazi=REPLACE(frazi,' Agli ',' agli ');
   UPDATE aa_data.wegehausnum_xy_correct SET frazi=REPLACE(frazi,' Allo ',' allo ');
   UPDATE aa_data.wegehausnum_xy_correct SET frazi=TRIM(REPLACE(REPLACE(frazi,'Loc.',),'L0c.',));
   UPDATE aa_data.wegehausnum_xy_correct SET frazi=REPLACE(frazi,'Str.','Strada ');
   UPDATE aa_data.wegehausnum_xy_correct SET frazi=REPLACE(frazi,'V.','Via ');
   UPDATE aa_data.wegehausnum_xy_correct SET frazi=REPLACE(frazi,'Vic.','Vicolo ');
   UPDATE aa_data.wegehausnum_xy_correct SET frazi=REPLACE(frazi,'Can.','Canale ');
   UPDATE aa_data.wegehausnum_xy_correct SET frazi=REPLACE(frazi,'Passegg.','Passeggiata ');
   UPDATE aa_data.wegehausnum_xy_correct SET frazi=REPLACE(frazi,'Dott.','Dr.');
   UPDATE aa_data.wegehausnum_xy_correct SET frazi=REPLACE(frazi,'P.Zza','Piazza ');
   UPDATE aa_data.wegehausnum_xy_correct SET frazi=REPLACE(frazi,'Za.','Zona ');
   UPDATE aa_data.wegehausnum_xy_correct SET frazi=REPLACE(frazi,'. - ','.');
   UPDATE aa_data.wegehausnum_xy_correct SET frazi=REPLACE(frazi,' - ','-');
   UPDATE aa_data.wegehausnum_xy_correct SET frazi=REPLACE(frazi,'.','. ');
   UPDATE aa_data.wegehausnum_xy_correct SET frazi=REPLACE(frazi,'.  ','. ');
   UPDATE aa_data.wegehausnum_xy_correct SET frazi=REPLACE(frazi,'. -','.-');
   UPDATE aa_data.wegehausnum_xy_correct SET frazi=REPLACE(frazi,'"',);
   UPDATE aa_data.wegehausnum_xy_correct SET frazi=REPLACE(frazi,'  ',' ');
   UPDATE aa_data.wegehausnum_xy_correct SET frazi=NULL where frazi='-';
   UPDATE aa_data.wegehausnum_xy_correct SET frazi=NULL where frazi='---';
   UPDATE aa_data.wegehausnum_xy_correct SET frazi=REPLACE(frazi,'Fraz. ',);
   UPDATE aa_data.wegehausnum_xy_correct SET frazi=upper(substring(frazi FROM 1 for 1)) || substring(frazi FROM 2 for length(frazi));

8.3 Normalization of the WEGE housenumber

   CREATE TABLE analisi.civici_aa AS
   SELECT 
   id AS aa2osmid, 
   wegehausnum_xy_correct.gem_id,
   wegehausnum_xy_correct.wege_id,
   wegehausnum_xy_correct.fr_id,
   astat_popolation.osm_order,
   --
   'IT'::text AS addr_country,
   --
   NULL::CHARACTER VARYING  AS addr_city,
   wegehausnum_xy_correct.gemd AS addr_city_de,
   wegehausnum_xy_correct.gemi AS addr_city_it,
   gem_4326.geml as addr_city_lld,
   --
   cap AS addr_postcode,
   --
   NULL::CHARACTER VARYING AS  addr_place,
   wegehausnum_xy_correct.frazd AS addr_place_de,
   wegehausnum_xy_correct.frazi AS addr_place_it,
   NULL::CHARACTER VARYING AS  addr_place_lld,
   --
   NULL::CHARACTER VARYING  AS addr_street,
   wegehausnum_xy_correct.vied AS addr_street_de,
   wegehausnum_xy_correct.viei AS addr_street_it,
   NULL::CHARACTER VARYING  AS addr_street_lld,
   --
   NULL::CHARACTER VARYING  AS addr_hamlet,
   NULL::CHARACTER VARYING AS addr_hamlet_de,
   NULL::CHARACTER VARYING AS addr_hamlet_it,
   NULL::CHARACTER VARYING AS addr_hamlet_lld,
   --
   CASE
   WHEN barr IS NULL THEN nume
   ELSE nume||'/'||barr
   END AS addr_housenumber,
   --
   NULL::CHARACTER VARYING  AS  addr_full,
   NULL::CHARACTER VARYING AS addr_full_de,
   NULL::CHARACTER VARYING AS addr_full_it,
   --
   st_transform(wegehausnum_xy_correct.the_geom,4326) AS the_geom
   --
   FROM aa_data.wegehausnum_xy_correct  
   LEFT JOIN aa_data.gem_4326 ON wegehausnum_xy_correct.ort_id::INTEGER=(gem_4326.gem)+21000
   LEFT JOIN aa_data.astat_popolation ON gem_4326.gem=astat_popolation.gem
   ORDER BY 
   aa2osmid;
   --
   ALTER TABLE analisi.civici_aa ADD CONSTRAINT civici_aa_pk PRIMARY KEY (aa2osmid );
   ALTER TABLE analisi.civici_aa ADD CONSTRAINT enforce_dims_the_geom CHECK (st_ndims(the_geom) = 2);
   ALTER TABLE analisi.civici_aa ADD CONSTRAINT enforce_geotype_the_geom CHECK (geometrytype(the_geom) = 'POINT'::text OR the_geom IS NULL);
   ALTER TABLE analisi.civici_aa ADD CONSTRAINT enforce_srid_the_geom CHECK (st_srid(the_geom) = 4326);
   --
   ---- POPOLA CITY
   UPDATE analisi.civici_aa SET addr_city=NULL;
   UPDATE analisi.civici_aa SET addr_city=addr_city_de WHERE addr_city_de=addr_city_it; -- IT=DE
   UPDATE analisi.civici_aa SET addr_city=addr_city_lld WHERE addr_city_de=addr_city_it and addr_city_de=addr_city_lld; -- IT=DE=LLD
   UPDATE analisi.civici_aa SET addr_city=addr_city_lld||' - '||addr_city_it WHERE osm_order IN ('LLD-IT-DE') AND addr_city_lld != addr_city_it and addr_city_it=addr_city_de; --    LLD != IT = DE
   UPDATE analisi.civici_aa SET addr_city=addr_city_lld||' - '||addr_city_de WHERE osm_order IN ('LLD-IT-DE') AND addr_city_lld = addr_city_it and addr_city_it != addr_city_de; -- LLD = IT != DE
   UPDATE analisi.civici_aa SET addr_city=addr_city_lld||' - '||addr_city_it WHERE osm_order IN ('LLD-IT-DE') AND addr_city_lld = addr_city_de and addr_city_it != addr_city_de; -- LLD = DE != IT
   UPDATE analisi.civici_aa SET addr_city= addr_city_lld||' - '||addr_city_it||' - '||addr_city_de WHERE osm_order IN ('LLD-IT-DE') AND addr_city IS NULL;
   UPDATE analisi.civici_aa SET addr_city= addr_city_lld||' - '||addr_city_de||' - '||addr_city_it WHERE osm_order IN ('LLD-DE-IT') AND addr_city IS NULL;
   UPDATE analisi.civici_aa SET addr_city=addr_city_it||' - '||addr_city_de WHERE osm_order IN ('IT-DE') AND addr_city IS NULL;
   UPDATE analisi.civici_aa SET addr_city=addr_city_de||' - '||addr_city_it WHERE addr_city IS NULL;
   -- IMPOSTA CORRETTAMENTE addr_place
   UPDATE analisi.civici_aa SET addr_place=NULL;
   update analisi.civici_aa set addr_place=addr_place_it where addr_place_de is null and addr_place_it is not null and addr_place is null;
   update analisi.civici_aa set addr_place=addr_place_de where addr_place_it is null and addr_place_de is not null and addr_place is null;
   UPDATE analisi.civici_aa SET addr_place=addr_place_de WHERE addr_place_de=addr_place_it;
   UPDATE analisi.civici_aa SET addr_place=addr_place_it||' - '||addr_place_de WHERE osm_order in ('IT-DE','LLD-IT-DE') AND addr_place IS NULL;
   UPDATE analisi.civici_aa SET addr_place=addr_place_de||' - '||addr_place_it WHERE addr_place IS NULL;
   UPDATE analisi.civici_aa SET addr_street=NULL;
   update analisi.civici_aa set addr_street=addr_street_it where addr_street_de is null and addr_street_it is not null and addr_place is null;
   update analisi.civici_aa set addr_street=addr_street_de where addr_street_it is null and addr_street_de is not null and addr_place is null;
   UPDATE analisi.civici_aa SET addr_street=addr_street_de WHERE addr_street_de=addr_street_it;
   UPDATE analisi.civici_aa SET addr_street=addr_street_it||' - '||addr_street_de WHERE osm_order in ('IT-DE','LLD-IT-DE') AND addr_street IS NULL;
   UPDATE analisi.civici_aa SET addr_street=addr_street_de||' - '||addr_street_it WHERE addr_street IS NULL;
   UPDATE analisi.civici_aa SET addr_full_de=NULL;
   UPDATE analisi.civici_aa SET addr_full_de=addr_postcode||' '||addr_city_de||', '||addr_street_de||', '||addr_housenumber WHERE addr_place IS NULL;
   UPDATE analisi.civici_aa SET addr_full_de=addr_postcode||' '||addr_city_de||', '||addr_place_de||', '||addr_housenumber WHERE addr_street IS NULL AND addr_full_de IS NULL;
   UPDATE analisi.civici_aa SET addr_full_de=addr_postcode||' '||addr_city_de||', '||addr_place_de||', '||addr_street_de||', '||addr_housenumber WHERE addr_full_de IS NULL;
   UPDATE analisi.civici_aa SET addr_full_it=NULL;
   UPDATE analisi.civici_aa SET addr_full_it=addr_postcode||' '||addr_city_it||', '||addr_street_it||', '||addr_housenumber WHERE addr_place IS NULL;
   UPDATE analisi.civici_aa SET addr_full_it=addr_postcode||' '||addr_city_it||', '||addr_place_it||', '||addr_housenumber WHERE addr_street IS NULL AND addr_full_it IS NULL;
   UPDATE analisi.civici_aa SET addr_full_it=addr_postcode||' '||addr_city_it||', '||addr_place_it||', '||addr_street_it||', '||addr_housenumber WHERE addr_full_it IS NULL;
   UPDATE analisi.civici_aa SET addr_full=NULL;
   UPDATE analisi.civici_aa SET addr_full=addr_postcode||' '||addr_city||', '||addr_street||', '||addr_housenumber WHERE addr_place IS NULL;
   UPDATE analisi.civici_aa SET addr_full=addr_postcode||' '||addr_city||', '||addr_place||', '||addr_housenumber WHERE addr_street IS NULL AND addr_full IS NULL;
   UPDATE analisi.civici_aa SET addr_full=addr_postcode||' '||addr_city||', '||addr_place||', '||addr_street||', '||addr_housenumber WHERE addr_full IS NULL;

8.4 Create the table with the OSM housenumbers normalized

   CREATE TEMPORARY SEQUENCE new_osm_id START WITH 1;
   DROP TABLE analisi.civici_osm;
   CREATE TABLE analisi.civici_osm AS
   SELECT 
       nextval('new_osm_id') AS new_osm_id,
       ogc_fid,
       --
       osm_id,
       NULL::CHARACTER VARYING AS osm_way_id,
       NULL::CHARACTER VARYING AS aa2osmid,
       NULL::CHARACTER VARYING AS gem_id,
       NULL::CHARACTER VARYING AS wege_id,
       NULL::CHARACTER VARYING AS fr_id,
       --
       addr_country AS old_addr_country,
       'IT'::text AS addr_country,
       aa_points.addr_city AS old_addr_city,
       NULL::CHARACTER VARYING AS addr_city,
       NULL::CHARACTER VARYING AS addr_city_de,
       NULL::CHARACTER VARYING AS addr_city_it,
       NULL::CHARACTER VARYING AS addr_city_lld,
       addr_postcode AS old_addr_postcode,
       NULL::CHARACTER VARYING AS addr_postcode,
       aa_points.addr_street AS old_addr_street,
       NULL::CHARACTER VARYING AS addr_street,
       NULL::CHARACTER VARYING AS addr_street_de,
       NULL::CHARACTER VARYING AS addr_street_it,
       NULL::CHARACTER VARYING AS addr_street_lld,
       aa_points.addr_place AS old_addr_place,
       NULL::CHARACTER VARYING AS addr_place,
       NULL::CHARACTER VARYING AS addr_place_de,
       NULL::CHARACTER VARYING AS addr_place_it,
       NULL::CHARACTER VARYING AS addr_place_lld,
       NULL::CHARACTER VARYING AS addr_hamlet,
       NULL::CHARACTER VARYING AS addr_hamlet_de,
       NULL::CHARACTER VARYING AS addr_hamlet_it,
       NULL::CHARACTER VARYING AS addr_hamlet_lld,

       aa_points.addr_housenumber AS old_addr_housenumber,
       NULL::CHARACTER VARYING AS addr_housenumber,
       NULL::CHARACTER VARYING AS addr_full,
       NULL::CHARACTER VARYING AS addr_full_de,
       NULL::CHARACTER VARYING AS addr_full_it,
       NULL::CHARACTER VARYING AS addr_full_lld,
       aa_points.the_geom,
       NULL::geometry AS polygon_geom
   FROM osm_data.aa_points
   UNION ALL
       SELECT 
       nextval('new_osm_id') AS new_osm_id,
       ogc_fid,
       osm_id,
       osm_way_id,
       NULL::CHARACTER VARYING AS aa2osm_id,
       NULL::CHARACTER VARYING AS gem_id,
       NULL::CHARACTER VARYING AS wege_id,
       NULL::CHARACTER VARYING AS fr_id,
       --
       addr_country AS old_addr_country,
       'IT'::text AS addr_country,
       aa_polygons.addr_city AS old_addr_city,
       NULL::CHARACTER VARYING AS addr_city,
       NULL::CHARACTER VARYING AS addr_city_de,
       NULL::CHARACTER VARYING AS addr_city_it,
       NULL::CHARACTER VARYING AS addr_city_lld,
       addr_postcode AS old_addr_postcode,
       NULL::CHARACTER VARYING AS addr_postcode,
       aa_polygons.addr_street AS old_addr_street,
       NULL::CHARACTER VARYING AS addr_street,
       NULL::CHARACTER VARYING AS addr_street_de,
       NULL::CHARACTER VARYING AS addr_street_it,
       NULL::CHARACTER VARYING AS addr_street_lld,
       aa_polygons.addr_place AS old_addr_place,
       NULL::CHARACTER VARYING AS addr_place,
       NULL::CHARACTER VARYING AS addr_place_de,
       NULL::CHARACTER VARYING AS addr_place_it,
       NULL::CHARACTER VARYING AS addr_place_lld,
       NULL::CHARACTER VARYING AS addr_hamlet,
       NULL::CHARACTER VARYING AS addr_hamlet_de,
       NULL::CHARACTER VARYING AS addr_hamlet_it,
       NULL::CHARACTER VARYING AS addr_hamlet_lld,
       aa_polygons.addr_housenumber AS old_addr_housenumber,
       NULL::CHARACTER VARYING AS addr_housenumber,
       NULL::CHARACTER VARYING AS addr_full,
       NULL::CHARACTER VARYING AS addr_full_de,
       NULL::CHARACTER VARYING AS addr_full_it,
       NULL::CHARACTER VARYING AS addr_full_lld,
       st_centroid(aa_polygons.the_geom) AS the_geom,
       aa_polygons.polygon_geom AS polygon_geom
   FROM osm_data.aa_polygons;
   ALTER TABLE analisi.civici_osm ADD CONSTRAINT civici_osm_pk PRIMARY KEY (new_osm_id );
   ALTER TABLE analisi.civici_osm ADD CONSTRAINT enforce_dims_the_geom CHECK (st_ndims(the_geom) = 2);
   ALTER TABLE analisi.civici_osm ADD CONSTRAINT enforce_geotype_the_geom CHECK (geometrytype(the_geom) = 'POINT'::text OR the_geom IS NULL);
   ALTER TABLE analisi.civici_osm ADD CONSTRAINT enforce_geotype_polygon_geom CHECK (geometrytype(polygon_geom) = 'MULTIPOLYGON'::text OR polygon_geom IS NULL);
   ALTER TABLE analisi.civici_osm ADD CONSTRAINT enforce_srid_the_geom CHECK (st_srid(the_geom) = 4326);
   DROP SEQUENCE new_osm_id;
   -- Assegna CAP e nome comune tramite intersect con geometrie comuni AA
   UPDATE analisi.civici_osm SET addr_city=gem_name
   FROM aa_data.gem_4326 WHERE st_intersects(gem_4326.the_geom,civici_osm.the_geom);
   UPDATE analisi.civici_osm SET addr_city_de=gemd
   FROM aa_data.gem_4326 WHERE st_intersects(gem_4326.the_geom,civici_osm.the_geom);
   UPDATE analisi.civici_osm SET addr_city_it=gemi
   FROM aa_data.gem_4326 WHERE st_intersects(gem_4326.the_geom,civici_osm.the_geom);
   UPDATE analisi.civici_osm SET addr_city_lld=geml
   FROM aa_data.gem_4326 WHERE st_intersects(gem_4326.the_geom,civici_osm.the_geom);
   UPDATE analisi.civici_osm SET addr_postcode=plz
   FROM aa_data.gem_4326 WHERE st_intersects(gem_4326.the_geom,civici_osm.the_geom);
   UPDATE analisi.civici_osm SET gem_id=gem_4326.gem_id
   FROM aa_data.gem_4326 WHERE st_intersects(gem_4326.the_geom,civici_osm.the_geom);
   -- aggiorna le vie OSM se le vie AA assomigliano alle vie OSM
   UPDATE analisi.civici_osm SET addr_street=NULL;
   UPDATE analisi.civici_osm SET addr_street=vie_name, wege_id=wege_vie.wege_id
   FROM aa_data.wege_vie WHERE civici_osm.gem_id=wege_vie.gem_id AND (
   viei ILIKE '%'||old_addr_street||'%' or vied ILIKE '%'||old_addr_street||'%' OR vie_name ILIKE '%'||old_addr_street||'%' OR
   viei ILIKE '%'||REPLACE(old_addr_street,'.',)||'%' or vied ILIKE '%'||REPLACE(old_addr_street,'.',)||'%' OR vie_name ILIKE '%'||REPLACE(old_addr_street,'.',)||'%' OR
   viei ILIKE '%'||REPLACE(old_addr_street,'ß','ss')||'%' or vied ILIKE '%'||REPLACE(old_addr_street,'ß','ss')||'%' OR vie_name ILIKE '%'||REPLACE(old_addr_street,'ß','ss')||'%'  OR 
   '%'||viei||'%' ILIKE '%'||old_addr_street||'%' or '%'||vied||'%' ILIKE '%'||old_addr_street||'%' or '%'||vie_name||'%' ILIKE '%'||old_addr_street||'%'
   );
   -- aggiorna le frazioni OSM se le frazioni AA assomigliano alle frazioni OSM
   UPDATE analisi.civici_osm SET addr_place=NULL;
   UPDATE analisi.civici_osm SET addr_place=fraz_name, fr_id=wege_fraction.fr_id
   FROM aa_data.wege_fraction WHERE civici_osm.gem_id=wege_fraction.gem_id AND (
   frazi ILIKE '%'||old_addr_place||'%' or frazd ILIKE '%'||old_addr_place||'%' OR
   frazi ILIKE '%'||REPLACE(old_addr_place,'.',)||'%' or frazd ILIKE '%'||REPLACE(old_addr_place,'.',)||'%' OR
   frazi ILIKE '%'||REPLACE(old_addr_place,'ß','ss')||'%' or frazd ILIKE '%'||REPLACE(old_addr_place,'ß','ss')||'%' OR 
   '%'||frazi||'%' ILIKE '%'||old_addr_place||'%' or '%'||frazd||'%' ILIKE '%'||old_addr_place||'%' 
   );
   -- dove l'indirizzo AA è strada, civico cerca corrispondenza con buffer
   UPDATE analisi.civici_osm SET aa2osmid=civici_aa.aa2osmid
   FROM analisi.civici_aa 
   WHERE civici_aa.addr_street IS NOT NULL AND civici_aa.addr_place IS NULL AND
   (civici_osm.addr_city = civici_aa.addr_city AND
        (civici_osm.addr_street = civici_aa.addr_street) AND
        civici_osm.old_addr_housenumber ILIKE REPLACE(civici_aa.addr_housenumber,'/',) AND
        st_distance(civici_aa.the_geom,civici_osm.the_geom ) < 0.001
       );
   -- dove l'indirizzo AA è frazione, civico cerca corrispondenza con buffer
   UPDATE analisi.civici_osm SET aa2osmid=civici_aa.aa2osmid
   FROM analisi.civici_aa 
   WHERE civici_aa.addr_street IS NULL AND civici_aa.addr_place IS NOT NULL AND
   (civici_osm.addr_city = civici_aa.addr_city AND
        civici_osm.addr_place = civici_aa.addr_place AND
        civici_osm.old_addr_housenumber ILIKE REPLACE(civici_aa.addr_housenumber,'/',) AND
        st_distance(civici_aa.the_geom,civici_osm.the_geom ) < 0.002
       ) AND civici_osm.aa2osmid IS NULL;  
   -- dove l'indirizzo AA è frazione, strada, civico cerca corrispondenza con buffer
   UPDATE analisi.civici_osm SET aa2osmid=civici_aa.aa2osmid
   FROM analisi.civici_aa 
   WHERE civici_aa.addr_street IS NOT NULL AND civici_aa.addr_place IS NOT NULL AND
   (civici_osm.addr_city = civici_aa.addr_city AND
        civici_osm.addr_street = civici_aa.addr_street AND
        civici_osm.addr_place = civici_aa.addr_place AND
        (civici_osm.old_addr_housenumber ILIKE REPLACE(civici_aa.addr_housenumber,'/',) or civici_osm.old_addr_housenumber = civici_aa.addr_housenumber) AND
        st_distance(civici_aa.the_geom,civici_osm.the_geom ) < 0.001
       ) AND civici_osm.aa2osmid IS NULL;
   --BEGIN;
   -- Considera strade AA = frazioni OSM
   UPDATE analisi.civici_osm SET addr_place=vie_name
   FROM aa_data.wege_vie WHERE civici_osm.gem_id=wege_vie.gem_id AND 
   vie_name IS NOT NULL AND(
   viei ILIKE '%'||old_addr_place||'%' or vied ILIKE '%'||old_addr_place||'%' OR
   viei ILIKE '%'||REPLACE(old_addr_place,'.',)||'%' or vied ILIKE '%'||REPLACE(old_addr_place,'.',)||'%' OR
   viei ILIKE '%'||REPLACE(old_addr_place,'ß','ss')||'%' or vied ILIKE '%'||REPLACE(old_addr_place,'ß','ss')||'%' OR 
   '%'||viei||'%' ILIKE '%'||old_addr_place||'%' or '%'||vied||'%' ILIKE '%'||old_addr_place||'%' 
   ) AND aa2osmid IS NULL ;
   -- ripassa la query dove l'indirizzo AA è strada, civico cerca corrispondenza con buffer
   UPDATE analisi.civici_osm SET aa2osmid=civici_aa.aa2osmid
   FROM analisi.civici_aa 
   WHERE civici_aa.addr_street IS NOT NULL AND civici_aa.addr_place IS NULL AND
   (civici_osm.addr_city = civici_aa.addr_city AND
        civici_osm.addr_street = civici_aa.addr_street AND
        civici_osm.old_addr_housenumber ILIKE REPLACE(civici_aa.addr_housenumber,'/',) AND
        st_distance(civici_aa.the_geom,civici_osm.the_geom ) < 0.001
       );
   -- ripassa la query non considerando strade/frazioni
   UPDATE analisi.civici_osm SET aa2osmid=civici_aa.aa2osmid
   FROM analisi.civici_aa 
   WHERE --civici_aa.addr_street IS NOT NULL AND civici_aa.addr_place IS NULL AND
   (civici_osm.addr_city = civici_aa.addr_city AND
        civici_osm.addr_street = civici_aa.addr_place AND
        civici_osm.old_addr_housenumber ILIKE REPLACE(civici_aa.addr_housenumber,'/',) AND
        st_distance(civici_aa.the_geom,civici_osm.the_geom ) < 0.001
       ) AND civici_osm.aa2osmid IS NULL;
   --
   UPDATE analisi.civici_osm SET aa2osmid=civici_aa.aa2osmid
   FROM analisi.civici_aa 
   WHERE --civici_aa.addr_street IS NOT NULL AND civici_aa.addr_place IS NULL AND
   (civici_osm.addr_city = civici_aa.addr_city AND
        civici_osm.addr_street = civici_aa.addr_street AND
        civici_osm.old_addr_housenumber ILIKE REPLACE(civici_aa.addr_housenumber,'/',) AND
        st_distance(civici_aa.the_geom,civici_osm.the_geom ) < 0.001
       ) AND civici_osm.aa2osmid IS NULL;
   -- crea relazione tramite comune, civico e posizione, solo dove aa2osmid è NULL
   UPDATE analisi.civici_osm SET aa2osmid=civici_aa.aa2osmid
   FROM analisi.civici_aa WHERE civici_osm.aa2osmid IS NULL AND
   (civici_osm.addr_city = civici_aa.addr_city AND 
   civici_osm.old_addr_housenumber ILIKE REPLACE(civici_aa.addr_housenumber,'/',) AND
   st_distance(civici_aa.the_geom,civici_osm.the_geom ) < 0.0005);
   --popola tutti i campi tramite aa2osmid
   UPDATE analisi.civici_osm SET addr_street=civici_aa.addr_street
   FROM analisi.civici_aa WHERE civici_osm.aa2osmid=civici_aa.aa2osmid;
   UPDATE analisi.civici_osm SET addr_street_de=civici_aa.addr_street_de
   FROM analisi.civici_aa WHERE civici_osm.aa2osmid=civici_aa.aa2osmid;
   UPDATE analisi.civici_osm SET addr_street_it=civici_aa.addr_street_it
   FROM analisi.civici_aa WHERE civici_osm.aa2osmid=civici_aa.aa2osmid;
   --
   UPDATE analisi.civici_osm SET addr_place_de=addr_street_de,addr_place_it=addr_street_it WHERE addr_place=addr_street;
   UPDATE analisi.civici_osm SET addr_place=addr_street 
   WHERE addr_street||gem_id in (select distinct addr_place||gem_id FROM analisi.civici_osm) ;
   UPDATE analisi.civici_osm SET addr_place_it=addr_street_it 
   WHERE addr_street||gem_id in (select distinct addr_place||gem_id FROM analisi.civici_osm) AND addr_place_it IS NULL;
   UPDATE analisi.civici_osm SET addr_place_de=addr_street_de 
   WHERE addr_street||gem_id in (select distinct addr_place||gem_id FROM analisi.civici_osm) AND addr_place_de IS NULL;
   --
   UPDATE analisi.civici_osm SET addr_place=civici_aa.addr_place
   FROM analisi.civici_aa WHERE civici_osm.aa2osmid=civici_aa.aa2osmid AND civici_osm.addr_place IS NULL;
   UPDATE analisi.civici_osm SET addr_place_de=civici_aa.addr_place_de
   FROM analisi.civici_aa WHERE civici_osm.aa2osmid=civici_aa.aa2osmid AND civici_osm.addr_place IS NULL;
   UPDATE analisi.civici_osm SET addr_place_it=civici_aa.addr_place_it
   FROM analisi.civici_aa WHERE civici_osm.aa2osmid=civici_aa.aa2osmid AND civici_osm.addr_place IS NULL;
   UPDATE analisi.civici_osm SET addr_housenumber=civici_aa.addr_housenumber
   FROM analisi.civici_aa WHERE civici_osm.aa2osmid=civici_aa.aa2osmid;
   UPDATE analisi.civici_osm SET addr_street_de=NULL,addr_street_it=NULL, addr_street=NULL WHERE addr_place=addr_street;
   --
   UPDATE analisi.civici_osm SET addr_place_it=initcap(frazi)
   FROM aa_data.wege_fraction WHERE civici_osm.gem_id=wege_fraction.gem_id AND fraz_name=addr_place AND addr_place_it IS NULL;
   UPDATE analisi.civici_osm SET addr_place_de=initcap(frazd)
   FROM aa_data.wege_fraction WHERE civici_osm.gem_id=wege_fraction.gem_id AND fraz_name=addr_place AND addr_place_de IS NULL;
   UPDATE analisi.civici_osm SET addr_place_it=initcap(viei)
   FROM aa_data.wege_vie WHERE civici_osm.gem_id=wege_vie.gem_id AND vie_name=addr_place AND addr_place_it IS NULL;
   UPDATE analisi.civici_osm SET addr_place_de=initcap(vied)
   FROM aa_data.wege_vie WHERE civici_osm.gem_id=wege_vie.gem_id AND vie_name=addr_place AND addr_place_de IS NULL;
   --
   UPDATE analisi.civici_osm SET addr_place_de=NULL WHERE addr_place_de=addr_place;
   UPDATE analisi.civici_osm SET addr_place_it=NULL WHERE addr_place_it=addr_place;
   UPDATE analisi.civici_osm SET addr_street_de=NULL WHERE addr_street_de=addr_street;
   UPDATE analisi.civici_osm SET addr_street_it=NULL WHERE addr_street_it=addr_street;

8.5 Create the "link" table

   DROP TABLE analisi.link;
   CREATE TABLE analisi.link AS
   SELECT 
   civici_aa.aa2osmid,
   civici_osm.osm_id,
   civici_osm.osm_way_id,
   REPLACE(
   civici_aa.addr_full,', ,',',') as aa_addr_full,
   REPLACE(
   CASE 
   WHEN civici_osm.old_addr_postcode IS NOT NULL THEN civici_osm.old_addr_postcode||' '
   ELSE 
   END||
   CASE 
   WHEN civici_osm.old_addr_city IS NOT NULL THEN civici_osm.old_addr_city
   ELSE 
   END||', '||
   CASE 
   WHEN civici_osm.old_addr_place IS NOT NULL THEN civici_osm.old_addr_place
   ELSE 
   END||', '||
   CASE 
   WHEN civici_osm.old_addr_street IS NOT NULL THEN civici_osm.old_addr_street
   ELSE 
   END||', '||
   civici_osm.old_addr_housenumber
   ,', , ',', ') 
   AS osm_addr_full,
   ST_Multi(ST_Makeline(civici_aa.the_geom,civici_osm.the_geom)) as the_geom
   FROM analisi.civici_osm
   JOIN analisi.civici_aa USING (aa2osmid);
   --ALTER TABLE analisi.link ADD CONSTRAINT link_pk PRIMARY KEY (aa2osmid );
   ALTER TABLE analisi.link ADD CONSTRAINT enforce_dims_the_geom CHECK (st_ndims(the_geom) = 2);
   ALTER TABLE analisi.link ADD CONSTRAINT enforce_geotype_the_geom CHECK (geometrytype(the_geom) = 'MULTILINESTRING'::text OR the_geom IS NULL);
   ALTER TABLE analisi.link ADD CONSTRAINT enforce_srid_the_geom CHECK (st_srid(the_geom) = 4326);

8.6 Create the final export table

   CREATE TABLE
   export.osm_housenumber AS
   select 
   aa2osmid,
   addr_country AS country,
   addr_postcode as postcode,
   addr_city AS city,
   addr_city_de AS city_de,
   addr_city_it AS city_it,
   addr_city_lld AS city_lld,
   addr_place AS place,
   addr_place_de AS place_de,
   addr_place_it AS place_it,
   addr_place_lld AS place_lld,
   addr_street AS street,
   addr_street_de AS street_de,
   addr_street_it AS street_it,
   addr_street_lld AS street_lld,
   addr_hamlet AS hamlet,
   addr_hamlet_de AS hamlet_de,
   addr_hamlet_it AS hamlet_it,
   NULL AS hamlet_lld,
   CASE WHEN addr_housenumber IS NOT NULL THEN addr_housenumber
   ELSE old_addr_housenumber
   END AS housenumb,
   addr_full AS a_full,
   addr_full_de AS full_de,
   addr_full_it AS full_it,
   addr_full_lld AS full_lld,
   'Provincia autonoma di Bolzano - Autonome Provinz Bozen' AS source,
   'NOT FOUND TO CHECK' AS action,
   the_geom,
   polygon_geom
   FROM analisi.civici_osm
   WHERE aa2osmid is null
   UNION ALL
   select 
   aa2osmid,
   a.addr_country AS country,
   a.addr_postcode as postcode,
   a.addr_city AS city,
   a.addr_city_de AS city_de,
   a.addr_city_it AS city_it,
   a.addr_city_lld AS city_lld,
   a.addr_place AS place,
   a.addr_place_de AS place_de,
   a.addr_place_it AS place_it,
   a.addr_place_lld AS place_lld,
   a.addr_street AS street,
   a.addr_street_de AS street_de,
   a.addr_street_it AS street_it,
   a.addr_street_lld AS street_lld,
   a.addr_hamlet AS hamlet,
   a.addr_hamlet_de AS hamlet_de,
   a.addr_hamlet_it AS hamlet_it,
   a.addr_hamlet_lld AS hamlet_it,
   a.addr_housenumber AS housenumb,
   a.addr_full AS a_full,
   a.addr_full_de AS full_de,
   a.addr_full_it AS full_it,
   NULL AS full_lld,
   'Provincia autonoma di Bolzano - Autonome Provinz Bozen' AS source,
   'FOUND TO UPDATE' AS action,
   o.the_geom,
   NULL::geometry AS polygon_geom
   FROM analisi.civici_osm o
   INNER JOIN analisi.civici_aa a using (aa2osmid)
   UNION ALL
   select 
   aa2osmid,
   addr_country AS country,
   addr_postcode as postcode,
   addr_city AS city,
   addr_city_de AS city_de,
   addr_city_it AS city_it,
   addr_city_lld AS city_lld,
   addr_place AS place,
   addr_place_de AS place_de,
   addr_place_it AS place_it,
   addr_place_lld AS place_lld,
   addr_street AS street,
   addr_street_de AS street_de,
   addr_street_it AS street_it,
   addr_street_lld AS street_lld,
   addr_hamlet AS hamlet,
   addr_hamlet_de AS hamlet_de,
   addr_hamlet_it AS hamlet_it,
   addr_hamlet_lld AS hamlet_it,
   addr_housenumber AS housenumb,
   addr_full AS a_full,
   addr_full_de AS full_de,
   addr_full_it AS full_it,
   NULL AS full_lld,
   'Provincia autonoma di Bolzano - Autonome Provinz Bozen' AS source,
   'NEW INSERT' AS action,
   the_geom,
   NULL::geometry AS polygon_geom
   FROM analisi.civici_aa
   WHERE aa2osmid NOT IN (select aa2osmid FROM analisi.civici_osm WHERE aa2osmid IS NOT NULL);
   ALTER TABLE export.osm_housenumber ADD CONSTRAINT enforce_dims_the_geom CHECK (st_ndims(the_geom) = 2);
   ALTER TABLE export.osm_housenumber ADD CONSTRAINT enforce_geotype_the_geom CHECK (geometrytype(the_geom) = 'POINT'::text OR the_geom IS NULL);
   ALTER TABLE export.osm_housenumber ADD CONSTRAINT enforce_geotype_polygon_geom CHECK (geometrytype(polygon_geom) = 'MULTIPOLYGON'::text OR polygon_geom IS NULL);
   ALTER TABLE export.osm_housenumber ADD CONSTRAINT enforce_srid_the_geom CHECK (st_srid(the_geom) = 4326);
   --droppa I campi inutili
   ALTER TABLE export.osm_housenumber DROP COLUMN aa2osmid;
   ALTER TABLE export.osm_housenumber DROP COLUMN a_full;
   ALTER TABLE export.osm_housenumber DROP COLUMN full_de;
   ALTER TABLE export.osm_housenumber DROP COLUMN full_it;
   ALTER TABLE export.osm_housenumber DROP COLUMN full_lld;
   ALTER TABLE export.osm_housenumber DROP COLUMN source;
   ALTER TABLE export.osm_housenumber DROP COLUMN country;
   DELETE FROM export.osm_housenumber WHERE housenumb LIKE '0%' OR housenumb LIKE '999%';

8.7 Create the export tables for each municipalities:

   SELECT
       'DROP TABLE IF EXISTS export.'||REPLACE(REPLACE(REPLACE(REPLACE(lower(gemi),'.',),' ','_'),'-','_'),'',)||';CREATE TABLE export.'||REPLACE(REPLACE(REPLACE(REPLACE(lower(gemi),'.',),' ','_'),'-','_'),',)||' AS SELECT * FROM export.osm_housenumber WHERE city = ||REPLACE(gem_name,',')|| ORDER BY action,place_it,street_it,housenumb; ALTER TABLE export.'||REPLACE(REPLACE(REPLACE(REPLACE(lower(gemi),'.',),' ','_'),'-','_'),',)||' OWNER TO opengisdata;'
   FROM
   aa_data.gem_4326
   ORDER BY gemi;

The structure of the PostGIS DB

schema aa_data
- boundaries_4326 : administrative boudaries of South Tyrol LAT LONG
- gem_4326 : administrative boundaries of the municipalities LAT LONG
- astat_population : ASTAT table with the list of the South Tyrol municipalitiess with relative percentages of native speakers (IT - DE - LLD)
- wegehausnum_xy : the house numbers to import
schema osm_data
- lines,multilinestrings,multipolygons,other_relations,points: import tables of tool ogr2ogr
- aa_points : result of a custom query, that extract from osm_data.points all the data that intersects aa_data.boundaries_4326 and have tag addr:housenumber NOT NULL
- aa_multipolygons : result of a custom query, that extract from osm_data.multipolygons all the data that intersects aa_data.boundaries_4326 and have tag addr:housenumber NOT NULL
schema analysis
- housenumber_aa : WEGE house numbers normalized with custom query on the OSM documentation (from aa_data.wegehausnum_xy using order from aa_data.astat_population)
- housenumber_osm : OSM house numbers normalized with custom query on the OSM documentation (from osm_data.aa_points and osm_data.aa_multipolygons)
- link ways : result of a custom query that search to join the analysis.housenumber_aa and analysis.housenumber_osm to find the OSM data that will be updated
- duplicated_housenumber : taking analysis.link, this table will contain the house_number that will updated in OSM
- import_housenumber : taking analysis.link, this table will contain the house_number that will imported in OSM with a new insert
schema export
- 117 tables , one for each municipality that contains the house numbers to export in *.osm

Example of normalization

ORIGINAL WEGE DATA

Need to find the correct order to write the tag ( DE - IT or IT - DE) and convert the fraction and the street names from UPPERCASE to Initcap

cap	gemi	gemd	frazi	frazd	viei	vied	nume	barr
39042	Bressanone	Brixen			VIA CASTELLANO	KÖSTLANERSTRASSE	38
39042	Bressanone	Brixen	FRAZ. ELVAS	FRAKT.ELVAS			10
39042	Bressanone	Brixen	FRAZ. S. ANDREA	FRAKT.ST.ANDRÄ	VIA CENTRALE	DORFSTRASSE	25	A

WEGE NORMALIZATION (simple select)

OSM_ID	addr:country	addr:poscode	addr:city	addr:city:de	addr:city:it	addr:place	addr:place:de	addr:place:it	addr:street	addr:street:de	addr:street:it	addr:housenumber
??	IT	39042	Brixen / Bressanone	Brixen	Bressanone				Köstlaner Strasse / Via Castellano	Köstlaner Straße	Via Castellano	38
??	IT	39042	Brixen / Bressanone	Brixen	Bressanone	Elvas						10
??	IT	39042	Brixen / Bressanone	Brixen	Bressanone	Frakt.St.Andrä / Fraz. S. Andrea	Frakt.St.Andrä	Fraz. S. Andrea	Dorfstrasse / Via Centrale	Dorfstrasse	Via Centrale	25/A

OSM data

In this example you can see that in the first row the italian tags are missing. In the second row, missing the postcode and the fraction name is write in the city tag.

OSM_ID	addr:country	addr:poscode	addr:city	addr:city:de	addr:city:it	addr:place	addr:place:de	addr:place:it	addr:street	addr:street:de	addr:street:it	addr:housenumber
231418257	IT	39042	Brixen	Brixen					Köstlaner Straße			38
69747871	IT		Elvas									10

OSM NORMALIZATION (intersect,select,join)

OSM_ID	addr:country	addr:poscode	addr:city	addr:city:de	addr:city:it	addr:place	addr:place:de	addr:place:it	addr:street	addr:street:de	addr:street:it	addr:housenumber
231418257	IT	39042	Brixen / Bressanone	Brixen	Bressanone				Köstlaner Strasse / Via Castellano	Köstlaner Straße	Via Castellano	38
69747871	IT	39042	Brixen / Bressanone	Brixen	Bressanone	Elvas						10

RESULTS

OSM_ID	addr:country	addr:poscode	addr:city	addr:city:de	addr:city:it	addr:place	addr:place:de	addr:place:it	addr:street	addr:street:de	addr:street:it	addr:housenumber	ACTION
231418257	IT	39042	Brixen / Bressanone	Brixen	Bressanone				Köstlaner Strasse / Via Castellano	Köstlaner Straße	Via Castellano	38	FOUND = UPDATE OSM
69747871	IT	39042	Brixen / Bressanone	Brixen	Bressanone	Elvas						10	FOUND = UPDATE OSM
NEW	IT	39042	Brixen / Bressanone	Brixen	Bressanone	Frakt.St.Andrä / Fraz. S. Andrea	Frakt.St.Andrä	Fraz. S. Andrea	Dorfstrasse / Via Centrale	Dorfstrasse	Via Centrale	25/A	NOT FOUND = NEW INSERT

From PostGIS to *.osm

The data will be exported to Shapefiles and coverted to *.osm via ogr2osm

This is the PHP script of export from the municipalities tables:

   <?php
   define('DB_HOST', '127.0.0.1');
   define('DB_USER', 'opengisdata');
   define('DB_PASSWORD', 'yourpassword');
   define('DB_NAME', 'opengisdata');
   define('DB_SCHEMA', 'export');
   define('OGR2OSM_PATH', '/yourpath/ogr2osm.py');
   $dsn = 'pgsql:dbname='.DB_NAME.';host='.DB_HOST;
   $db = new PDO($dsn, DB_USER, DB_PASSWORD);
   $db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
   $sql = "SELECT * FROM information_schema.tables WHERE table_schema = '".DB_SCHEMA."'";
   $sql .= " and table_name != 'osm_housenumber' ";
   //$sql .= " limit 1";
   $tables = $db->query($sql)->fetchAll(PDO::FETCH_ASSOC);
   foreach($tables as $table) {
       $table = $table['table_name'];  
       $cmd = "pgsql2shp -h ".DB_HOST." -u ".DB_USER." -P ".DB_PASSWORD." -f ".$table."_delete.shp ".DB_NAME." \"SELECT city,city_de,city_it,city_lld,place,place_de,place_it,place_lld,street,street_de,street_it,street_lld,housenumb,the_geom FROM export.".$table." WHERE action='TO DELETE'\" ";    
       exec($cmd); 
       file_put_contents($table.'_delete.cpg', 'UTF-8');   
       $cmd = "pgsql2shp -h ".DB_HOST." -u ".DB_USER." -P ".DB_PASSWORD." -f ".$table."_check.shp ".DB_NAME." \"SELECT city,city_de,city_it,city_lld,place,place_de,place_it,place_lld,street,street_de,street_it,street_lld,housenumb,the_geom FROM export.".$table." WHERE action='TO CHECK'\" ";    
       exec($cmd);  
       file_put_contents($table.'_check.cpg', 'UTF-8');    
       $cmd = "pgsql2shp -h ".DB_HOST." -u ".DB_USER." -P ".DB_PASSWORD." -f ".$table."_new.shp ".DB_NAME." \"SELECT city,city_de,city_it,city_lld,place,place_de,place_it,place_lld,street,street_de,street_it,street_lld,housenumb,the_geom FROM export.".$table." WHERE action='NEW INSERT'\" ";    
       exec($cmd);  
       file_put_contents($table.'_new.cpg', 'UTF-8');  
       echo 'ho fatto pgsql2shp per '.$table."\n";  
       if(file_exists($table.'_delete.shp')) {
           $cmd = 'python '.OGR2OSM_PATH.' '.$table.'_delete.shp';
           exec($cmd);
           replace_tags($table.'_delete.osm');
       }
       if(file_exists($table.'_check.shp')) {
           $cmd = 'python '.OGR2OSM_PATH.' '.$table.'_check.shp';
           exec($cmd);
           replace_tags($table.'_check.osm');
       }
       if(file_exists($table.'_new.shp')) {
           $cmd = 'python '.OGR2OSM_PATH.' '.$table.'_new.shp';
           exec($cmd);
           replace_tags($table.'_new.osm');
       }   
   }
   function replace_tags($file) {
       $content = file_get_contents($file);
       //$content = str_replace('k="AA2OSMID"', 'k="addr:aa2osmid"', $content);
       //$content = str_replace('k="COUNTRY"', 'k="addr:country"', $content);
       $content = str_replace('k="CITY_DE"', 'k="addr:city:de"', $content);
       $content = str_replace('k="CITY_IT"', 'k="addr:city:it"', $content);
       $content = str_replace('k="CITY_LLD"', 'k="addr:city:lld"', $content);
       $content = str_replace('k="CITY"', 'k="addr:city"', $content);
       $content = str_replace('k="PLACE_DE"', 'k="addr:place:de"', $content);
       $content = str_replace('k="PLACE_IT"', 'k="addr:place:it"', $content);
       $content = str_replace('k="PLACE_LLD"', 'k="addr:place:lld"', $content);
       $content = str_replace('k="PLACE"', 'k="addr:place"', $content);
       $content = str_replace('k="STREET_DE"', 'k="addr:street:de"', $content);
       $content = str_replace('k="STREET_IT"', 'k="addr:street:it"', $content);
       $content = str_replace('k="STREET_LLD"', 'k="addr:street:lld"', $content);
       $content = str_replace('k="STREET"', 'k="addr:street"', $content);
       $content = str_replace('k="HOUSENUMB"', 'k="addr:housenumber"', $content);
       //$content = str_replace('k="A_FULL"', 'k="addr:full"', $content);
       //$content = str_replace('k="FULL_DE"', 'k="addr:full:de"', $content);
       //$content = str_replace('k="FULL_IT"', 'k="addr:full:it"', $content);
       //$content = str_replace('k="FULL_LLD"', 'k="addr:full:lld"', $content);
       //$content = str_replace('k="SOURCE"', 'k="addr:source"', $content);
       file_put_contents($file, $content);
       echo 'ho sostituito i tags per '.$file."\n";
   ?>

Three *osm files will be generated:

martello_new.osm : will contain the data to be imported as new
martello_check.osm : will contain the data to be checked manually
martello_update.osm : will contain the data to be updated manually

AltoAdige - Südtirol/OpenGisData HouseNumber Import2/Technical Import Documentation

Contents

Import Documentation

The structure of the PostGIS DB

Example of normalization

From PostGIS to *.osm

Navigation menu

AltoAdige - Südtirol/OpenGisData HouseNumber Import2/Technical Import Documentation

Import Documentation

The structure of the PostGIS DB

Example of normalization

From PostGIS to *.osm

Navigation menu

Search