forked from baditaflorin/osm-postgis-scripts
-
Notifications
You must be signed in to change notification settings - Fork 0
/
nodes_ways_addr_housenumbers.sql
108 lines (89 loc) · 4.46 KB
/
nodes_ways_addr_housenumbers.sql
1
2
3
4
5
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
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
-- #Version: 0.1
-- #Author: Florin Badita
-- #Date: 01.12.16
-- #Website: https://www.openstreetmap.org/user/baditaflorin
-- #Email: [email protected]
-- #Licence AGPLv3+ - https://github.com/baditaflorin/osm-postgis-scripts/blob/master/LICENSE
/* #Example of Use: This is usefull when you want to produce statistics about the housenumbers, to see in a visual way in Qgis who added housenumbers in a city, etc */
-- #Start Code
/* #TODO #FIXME
This will load the nodes and ways, and will convert the ways using ST_centroid to get a node for each way. This script will not tell you if the original was a way or a node */
/* Abrevations list
relation_members = rl_
relations = r_
ways = w_
nodes = n_
users = u_
*/
-- # This is created so you don`t have problems loading the file into QGIS, and also so that QGIS will recognize this Column as the column that have only unique values
-- # We create the ROW_NUMBER() here, because if not it will generate 2 starting sequences, one for the nodes and one for the ways, and then QGIS will not allow us to load the data, because we don`t have a unique ID. SOURCE http://stackoverflow.com/questions/5190240/row-number-with-union-query
drop table if exists addr_housenumbers;
Create table addr_housenumbers as (
SELECT ROW_NUMBER() over () as id,* from
( SELECT user_id, tstamp, changeset_id, version, ways.id as ways_ids,
-- # this will make the column name be geom, that will enable Qgis DB manager to figure it out that this is the column that holds geometry data
ST_centroid(ways.linestring) as geom,
users.id as u_ids,users.name as osm_name,
-- # General Relevance Tags
-- #leave empty the end of the last tag, don`t end it with the semicolon
ways.tags->'name' As n_name,
-- #Specific Tags
-- #leave empty the end of the last tag, don`t end it with the semicolon
ways.tags->'addr:housenumber' As n_addr_housenumber,
ways.tags->'addr:interpolation' As n_addr_interpolation,
ways.tags->'addr:housename' As n_addr_housename,
ways.tags->'addr:city' As n_addr_city,
ways.tags->'addr:postcode' As n_addr_postcode,
-- #Internal mappers tags
-- #leave empty the end of the last tag, don`t end it with the semicolon
ways.tags->'source' As n_source,
ways.tags->'attribution' As n_attribution,
ways.tags->'comment' As n_comment,
ways.tags->'fixme' As n_fixme,
ways.tags->'created_by' As n_created_by
FROM ways,users
WHERE users.id=ways.user_id AND ((tags->'addr:housenumber')) is not null
UNION ALL
-- #Version: 0.1
-- #Author: Florin Badita
-- #Date: 01.12.16
-- #Website: https://www.openstreetmap.org/user/baditaflorin
-- #Email: [email protected]
-- #Licence AGPLv3+ - https://github.com/baditaflorin/osm-postgis-scripts/blob/master/LICENSE
/* #Example of Use: This is usefull when you want to produce statistics about the housenumbers, to see in a visual way in Qgis who added housenumbers in a city, etc */
-- #Start Code
/* #TODO #FIXME
This will only load the nodes, but housenumbers are made out of ways and nodes.
In this form, this script will only load the nodes, not the ways */
/* Abrevations list
relation_members = rl_
relations = r_
ways = w_
nodes = n_
users = u_
*/
-- # This is created so you don`t have problems loading the file into QGIS, and also so that QGIS will recognize this Column as the column that have only unique values
SELECT user_id, tstamp, changeset_id, version, nodes.id as node_ids,
-- # this will make the column name be geom, that will enable Qgis DB manager to figure it out that this is the column that holds geometry data
nodes.geom as geom,
users.id as u_ids,users.name as osm_name,
-- # General Relevance Tags
-- #leave empty the end of the last tag, don`t end it with the semicolon
nodes.tags->'name' As n_name,
-- #Specific Tags
-- #leave empty the end of the last tag, don`t end it with the semicolon
nodes.tags->'addr:housenumber' As n_addr_housenumber,
nodes.tags->'addr:interpolation' As n_addr_interpolation,
nodes.tags->'addr:housename' As n_addr_housename,
nodes.tags->'addr:city' As n_addr_city,
nodes.tags->'addr:postcode' As n_addr_postcode,
-- #Internal mappers tags
-- #leave empty the end of the last tag, don`t end it with the semicolon
nodes.tags->'source' As n_source,
nodes.tags->'attribution' As n_attribution,
nodes.tags->'comment' As n_comment,
nodes.tags->'fixme' As n_fixme,
nodes.tags->'created_by' As n_created_by
FROM nodes,users
WHERE ST_GeometryType(geom) = 'ST_Point' AND users.id=nodes.user_id AND ((tags->'addr:housenumber')) is not null ) nodes_and_ways);
select * from addr_housenumbers