init_osmlinzaddr_spatialite.sql 1.05 KB
Newer Older
1
-- initialise (as spatialite database)
Eliot Blennerhassett's avatar
Eliot Blennerhassett committed
2 3 4

-- Subset of LINZ address data,
CREATE TABLE linz_addr(
5
    linz_id mediumint primary key,
Eliot Blennerhassett's avatar
Eliot Blennerhassett committed
6 7
    change_id mediumint,
    addr_type text(20),
8 9 10 11
    'unit_value' TEXT(70),
    'address_number' MEDIUMINT,
    'address_number_suffix' TEXT(10),
    'address_number_high' MEDIUMINT,
Eliot Blennerhassett's avatar
Eliot Blennerhassett committed
12 13 14 15 16
    suburb_locality text(80),
    town_city text(80),
    full_address_number text(100),
    full_road_name text(250)
);
17

Eliot Blennerhassett's avatar
Eliot Blennerhassett committed
18 19 20 21
SELECT AddGeometryColumn('linz_addr', "geom", 4326, "POINT", "XY", 1);

-- OSM address data
CREATE TABLE osm_addr(
22
    is_way BOOLEAN,
Eliot Blennerhassett's avatar
Eliot Blennerhassett committed
23 24
    osm_id INT,
    housenumber TEXT,
25 26
    street TEXT,
    primary key (is_way, osm_id)
Eliot Blennerhassett's avatar
Eliot Blennerhassett committed
27 28 29 30 31
);
SELECT AddGeometryColumn('osm_addr', "geom", 4326, "POINT", "XY", 1);

-- Cross reference of items representing the same address
CREATE TABLE osm_linz(
32
    'is_way' boolean,
Eliot Blennerhassett's avatar
Eliot Blennerhassett committed
33 34
    'osm_id' int,
    'linz_id' int,
35 36 37
    'D' real,  -- distance between OSM and LINZ address (? degrees ?)
    foreign key (is_way, osm_id) references osm_addr(is_way, osm_id),
    foreign key (linz_id) references linz_addr(linz_id)
Eliot Blennerhassett's avatar
Eliot Blennerhassett committed
38
);