Miscellaneous - NLExtract

NLExtract provides Open Source datasets and tools for Dutch geodata.
They provide some great tools and documentation for PostgresSQL and PostGIS. Check it out at http://www.nlextract.nl

To access the BAG (Basisregistratie Adressen en Gebouwen) CSV data using an Oracle database (on a Linux server), I did the following:

Linux, as OS user root:

cd /
mkdir bag_import
chown oracle:dba bag_import

Linux, as OS user oracle:
Download the latest CSV data file (.zip) here: http://data.nlextract.nl/
Extract it, and copy the bagadres.csv file to /bag_import

As sys:
Create a new schema/user BAG.

create or replace directory bag_import_dir as '/bag_import';
grant read on directory bag_import_dir to bag;
grant write on directory bag_import_dir to bag;

As bag:

create table BAG_ADRESSEN_EXT
(
  openbareruimte       VARCHAR2(100),
  huisnummer           NUMBER,
  huisletter           VARCHAR2(10),
  huisnummertoevoeging VARCHAR2(10),
  postcode             VARCHAR2(6),
  woonplaats           VARCHAR2(100),
  gemeente             VARCHAR2(100),
  provincie            VARCHAR2(100),
  object_id            NUMBER,
  object_type          VARCHAR2(10),
  nevenadres           VARCHAR2(10),
  x                    NUMBER,
  y                    NUMBER,
  lon                  NUMBER,
  lat                  NUMBER
)
organization external
(
  type ORACLE_LOADER
  default directory BAG_IMPORT_DIR
  access parameters 
  (
    records delimited by newline
    skip 1
    fields terminated by ';' optionally enclosed by '"'
    missing field values are null
      ( openbareruimte
      , huisnummer
      , huisletter
      , huisnummertoevoeging
      , postcode
      , woonplaats
      , gemeente
      , provincie
      , object_id
      , object_type
      , nevenadres
      , x
      , y
      , lon
      , lat
      )
  )
  location (BAG_IMPORT_DIR:'bagadres.csv')
)
reject limit UNLIMITED;

Now you can query the data like this:

select *
from bag_adressen_ext t
where t.postcode = '1355AC'

And make a real table out of it like this (much faster, especially if you add the right indexes):

create table bag_adressen as
  select *
  from bag_adressen_ext
;
create index bag_ads_idx_01 on bag_adressen (postcode, huisnummer);

Some of the data seems incorrect - this is a first attempt to find some obvious errors:
As bag:

with a0 as
(
  select a.postcode
  ,      min(a.provincie) as provincie_min
  ,      max(a.provincie) as provincie_max
  ,      count(distinct a.provincie) as n
  from bag_adressen a
  group by a.postcode
  having count(distinct a.provincie)  > 1
)
, a_min as
(
  select a2.postcode
  ,      avg(a2.x) as x
  ,      avg(a2.y) as y
  from a0
  ,    bag_adressen a2
  where a2.postcode  = a0.postcode
  and   a2.provincie = a0.provincie_min
  group by a2.postcode
)
, a_max as
(
  select a2.postcode
  ,      avg(a2.x) as x
  ,      avg(a2.y) as y
  from a0
  ,    bag_adressen a2
  where a2.postcode  = a0.postcode
  and   a2.provincie = a0.provincie_max
  group by a2.postcode
)
select a_min.postcode as postcode
,      sqrt( power(a_max.x - a_min.x, 2) + power(a_max.y - a_min.y, 2) ) / 1000 as km
from a_min
,    a_max
where a_min.postcode = a_max.postcode
order by 2 desc

Info