example-queries.sql 818 Bytes
Newer Older
Eliot Blennerhassett's avatar
Eliot Blennerhassett committed
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19
-- Examples
-- spatialite osmlinzaddr.sqlite < example-queries.sql

-- Get linz addresses with ids that aren't in osm_linz
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,
  linz_addr.suburb_locality as s,
  linz_addr.town_city as c
FROM linz_addr
LEFT OUTER JOIN osm_linz
  on (linz_addr.linz_id = osm_linz.linz_id)
WHERE
  osm_linz.linz_id is NULL
  and linz_addr.addr_type != "Water"
ORDER BY linz_addr.town_city, linz_addr.suburb_locality
20
LIMIT 25; -- for testing
Eliot Blennerhassett's avatar
Eliot Blennerhassett committed
21

Eliot Blennerhassett's avatar
Eliot Blennerhassett committed
22

23
-- OSM items  had no identified LINZ equivalent
Eliot Blennerhassett's avatar
Eliot Blennerhassett committed
24
SELECT "type", a."osm_id", "housenumber", "street", "geom"
25 26
FROM osm_addr as a
outer left join osm_linz as b
Eliot Blennerhassett's avatar
Eliot Blennerhassett committed
27 28
on a.osm_id = b.osm_id
where b.osm_id is NULL
29
order by street, housenumber