import-xref.sql 4.35 KB
Newer Older
1
-- spatialite "osmlinzaddr.sqlite" < import-xref.sql
Eliot Blennerhassett's avatar
Eliot Blennerhassett committed
2 3 4 5
-- as main

.trace ON

6
-- ---------------------------------------------------------------------
7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41
ATTACH DATABASE "nz_address-osm_raw.sqlite" AS "osm";

DELETE FROM osm_addr;
-- Gathering the OSM addresses as points
INSERT INTO osm_addr
SELECT
  1 as is_way,
  w.way_id as osm_id,
  t1.v as housenumber,
  t2.v as street,
  ST_Centroid(ST_Collect(n.Geometry)) as geom
FROM osm.osm_ways as w
INNER JOIN osm_way_tags as t1 on (w.way_id = t1.way_id and t1.k = "addr:housenumber")
LEFT JOIN osm.osm_way_tags as t2 on (w.way_id = t2.way_id and t2.k = "addr:street")
INNER JOIN osm.osm_way_refs as r on (r.way_id = w.way_id)
INNER JOIN osm.osm_nodes as n on (r.node_id = n.node_id)
GROUP BY w.way_id
;

INSERT INTO osm_addr
SELECT
  0 as is_way,
  n.node_id as osm_id,
  t1.v as housenumber,
  t2.v as street,
  Geometry as geom
FROM osm.osm_nodes as n
INNER JOIN osm.osm_node_tags as t1 on (n.node_id = t1.node_id and t1.k = "addr:housenumber")
LEFT JOIN osm.osm_node_tags as t2 on (n.node_id = t2.node_id and t2.k = "addr:street")
;

DETACH osm;

-- ---------------------------------------------------------------------

Eliot Blennerhassett's avatar
Eliot Blennerhassett committed
42 43 44 45 46 47
ATTACH DATABASE "nz-street-address.gpkg" as "linz";
--SELECT AutoGPKGStart();

-- gpkg doesn't contain this one?!
-- INSERT into linz.spatial_ref_sys SELECT * FROM ol."spatial_ref_sys" WHERE srid = 4326

48
DELETE FROM linz_addr;
Eliot Blennerhassett's avatar
Eliot Blennerhassett committed
49 50 51 52 53 54 55

-- Convert LINZ geometry from NZ2000 to WGS84 SRID 4326
INSERT INTO linz_addr
SELECT
    address_id as linz_id,
    change_id,
    address_type as addr_type,
56 57 58 59
    unit_value,
    address_number,
    address_number_suffix,
    address_number_high,
Eliot Blennerhassett's avatar
Eliot Blennerhassett committed
60 61 62 63 64 65 66 67 68
    suburb_locality,
    town_city,
    full_address_number,
    full_road_name,
    MakePoint(gd2000_xcoord, gd2000_ycoord, 4326) as geom
FROM linz.nz_street_address;

DETACH linz;

69 70 71
-- ---------------------------------------------------------------------

drop table if exists places;
72 73 74 75 76 77 78 79
create table places as
select
  town_city,
  suburb_locality,
  count(*) as num_addresses
from linz_addr
where addr_type = 'Road'
group by town_city, suburb_locality
80
order by town_city, suburb_locality;
81

82
-- ---------------------------------------------------------------------
Eliot Blennerhassett's avatar
Eliot Blennerhassett committed
83 84 85 86 87

-- ---------------------------------------------------------------------
-- matching linz vs OSM nodes on housenumber and empirically determined distance
-- (Will take quite a long time!)
--
88 89 90
DELETE FROM osm_linz;

-- first, exact match on road, number; close proximity
Eliot Blennerhassett's avatar
Eliot Blennerhassett committed
91 92
INSERT INTO osm_linz
SELECT
93
  o.is_way as is_way,
Eliot Blennerhassett's avatar
Eliot Blennerhassett committed
94 95
  o.osm_id as osm_id,
  l.linz_id as linz_id,
96
  Distance(o.geom, l.geom, 0) as D
Eliot Blennerhassett's avatar
Eliot Blennerhassett committed
97 98 99
FROM osm_addr as o
JOIN linz_addr as l on
  o.housenumber = l.full_address_number
100
  and o.street = l.full_road_name
101
  and  D < 50;
102 103 104 105

-- remaining OSM entries with streets
-- 6539/46858
create table temp.inexact as
106
select
107
  osm_addr.is_way,
108 109 110 111
  osm_addr.osm_id,
  housenumber,
  street,
  geom
112 113 114 115 116 117 118 119 120
from osm_addr
left outer join osm_linz as done
 on osm_addr.osm_id = done.osm_id
where done.osm_id is null and street is not null
order by street, housenumber
;

-- and without streets
insert into temp.inexact
121
select
122
  osm_addr.is_way,
123 124 125 126
  osm_addr.osm_id,
  housenumber,
  street,
  geom
127 128
from osm_addr
left outer join osm_linz as done
129
  on osm_addr.osm_id = done.osm_id
130 131 132 133 134 135 136
where done.osm_id is null and street is null
order by street, housenumber
;
--
-- Inexact matches with LINZ data on housenumber and proximity
CREATE TABLE temp.linz_inexact AS
SELECT
137
  o.is_way as is_way,
138 139
  o.osm_id as osm_id,
  l.linz_id as linz_id,
140
  Distance(o.geom, l.geom, 0) as D
141 142 143
FROM temp.inexact as o
JOIN linz_addr as l on
  o.housenumber = l.full_address_number
144
  and  D < 40
Eliot Blennerhassett's avatar
Eliot Blennerhassett committed
145 146
;

147 148 149 150 151
-- Can be reviewed, then
INSERT INTO osm_linz
SELECT * from temp.linz_inexact;

-- The LINZ addresses identified as not already in OSM
152
drop table if exists linz_not_osm;
153 154 155 156 157 158 159 160

create table linz_not_osm as
select
  linz_addr.linz_id,
  linz_addr.full_address_number as housenumber,
  linz_addr.full_road_name as street,
  X(linz_addr.geom) as x,
  Y(linz_addr.geom) as y,
161 162 163 164 165 166
  linz_addr.unit_value as unit,
  linz_addr.address_number as number,
  linz_addr.address_number_suffix as suffix,
  linz_addr.address_number_high as high,
  linz_addr.suburb_locality as suburb,
  linz_addr.town_city as city
167 168
from linz_addr
left outer join osm_linz
169
  on (osm_linz.linz_id = linz_addr.linz_id)
170 171 172
where
  osm_linz.linz_id is NULL
  and linz_addr.addr_type != "Water"
173
  order by linz_addr.town_city, linz_addr.suburb_locality
174
;