ETL for generating data in (simple) alternative encoding

[arbakker]

INSPIRE addresses in alternative encoding have been generated using the following SQL query (assuming databases with normalized datamodel voor INSPIRE addresses) according to these transformation rules:

create schema oaf_poc_2021;
create view oaf_poc_2021.simple_inspire_ad as
select ''                                                                         as alternativeIdentifier,
       ad.validfrom,
       ad.validto,
       null                                                                       as beginLifespanVersion,
       null                                                                       as endLifespanVersion,
       ''                                                                         as building,
       tfname.thoroughfarename                                                    as component_ThoroughfareName,
       pdesc.postaldescriptor                                                     as component_PostalDescriptor,
       adaname.addressareaname                                                    as component_AddressAreaName,
       ''                                                                         as component_AdminUnitName_1,
       ''                                                                         as component_AdminUnitName_2,
       ''                                                                         as component_AdminUnitName_3,
       ''                                                                         as component_AdminUnitName_4,
       ''                                                                         as component_AdminUnitName_5,
       ''                                                                         as component_AdminUnitName_6,
       ad.designator_type_two                                                     as locator_designator_addressNumber,
       ad.designator_type_three                                                   as locator_designator_addressNumberExtension,
       ad.designator_type_four                                                       locator_designator_addressNumber2ndExtension,
       'unit level'                                                               as locator_level,
       'http://inspire.ec.europa.eu/codelist/LocatorLevelValue/unitLevel'         as locator_href,
       ''                                                                         as locator_designator_buildingIdentifier,
       ''                                                                         as locator_designator_buildingIdentifierPrefix,
       ''                                                                         as locator_designator_cornerAddress1stIdentifier,
       ''                                                                         as locator_designator_cornerAddress2ndIdentifier,
       ''                                                                         as locator_designator_entranceDoorIdentifier,
       ''                                                                         as locator_designator_floorIdentifier,
       ''                                                                         as locator_designator_kilometrePoint,
       ''                                                                         as locator_designator_postalDeliveryIdentifier,
       ''                                                                         as locator_designator_staircaseIdentifier,
       ''                                                                         as locator_designator_unitIdentifier,
       ''                                                                         as locator_name,
       ''                                                                         as parcel,
       ''                                                                         as parentAddress,
       ad.geom                                                                    as geom,
       'entrance'                                                                 as position_specification,
       'http://inspire.ec.europa.eu/codelist/GeometrySpecificationValue/entrance' as position_specification_href,
       'by administrator'                                                         as position_method,
       'http://inspire.ec.europa.eu/codelist/GeometryMethodValue/byAdministrator' as position_method_href,
       true                                                                       as position_default,
       ''                                                                         as status,
       ''                                                                         as status_href
from inspire_ad
         .address ad
         INNER JOIN inspire_ad.thoroughfarename tfname on ad.thoroughfarename_id = tfname.localid
         INNER JOIN inspire_ad.postaldescriptor pdesc on ad.postaldescriptor_id = pdesc.localid
         INNER JOIN inspire_ad.addressareaname adaname on ad.addressareaname_id = adaname.localid
;

Using this view a GeoPackage can be generated with ogr2ogr:

export PGPASSWORD="postgres"
export PGCONN="PG:dbname='pdok' host='my-db-host' port='5432` user='postgres'"; ogr2ogr -f GPKG data.gpkg "$PGCONN" -oo ACTIVE_SCHEMA=oaf_poc_2021  -sql "select * from simple_inspire_ad where geom && 
ST_MakeEnvelope(4.6445,52.9685,4.9466,53.2027,4258)" -nln simple_inspire_ad

Note that with the above command the data is still in its source projection, namely EPSG:4258.

Cluster data on geohash for better usability when browsing data (features will be in same area when paging through features):

CREATE INDEX simple_inspire_ad_texel_geohash ON oaf_poc_2021.simple_inspire_ad_texel (ST_GeoHash(ST_Transform(geom,4326)));
CLUSTER oaf_poc_2021.simple_inspire_ad_texel USING simple_inspire_ad_texel_geohash;