forked from baditaflorin/osm-postgis-scripts
-
Notifications
You must be signed in to change notification settings - Fork 0
/
extract_turn_restrictions.sql
153 lines (121 loc) · 4.62 KB
/
extract_turn_restrictions.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
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
-- #Version: 0.1
-- #Author: Florin Badita
-- #Date: 12.07.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 extract all the turn restrictions */
-- #Start Code
/* #TODO #FIXME
None yet */
/* Abrevations list
relation_members = rl_
relations = r_
ways = w_
way_nodes = wn_
nodes = n_
ways_or_nodes = w_or_n_
*/
select rl_id,ST_Collect(geom),count(*) from (
select
--relation_id,count(*)
--*,
-- #relations_members
relation_members.relation_id rl_id,
relation_members.member_id as rl_member_id,
relation_members.member_role as rl_member_role,
relation_members.sequence_id as rl_sequence,
relation_members.member_type as rl_momber_type,
-- #relations
relations.id as r_id,
relations.version as r_version,
relations.tstamp as r_tstamp,
relations.user_id as r_user_id,
-- relation tstamp minus ways tstamp
age (relations.tstamp,ways.tstamp) as diff_rel_ways_or_nodes,
-- The day of the week as Sunday (0) to Saturday (6)
extract(dow from relations.tstamp) as r_day_of_the_week,
-- Create a column for ways that will count the number of keys in each row.
array_length(avals(ways.tags), 1) AS w_or_n_count_keys,
-- done -- Max nodes per way segment
ST_NPoints(ways.linestring) AS w_or_n_points_per_way,
-- ways --
-- #Internal mappers tags
-- #leave empty the end of the last tag, don`t end it with the semicolon
ways.version as w_or_n_version,
ways.changeset_id as w_or_n_changeset_id,
ways.user_id as w_or_n_user_id,
ways.tstamp as w_or_n_tstamp,
date_trunc('day',ways.tstamp) as w_or_n_day,
date_trunc('month',ways.tstamp) as w_or_n_month,
date_part('year', ways.tstamp) as w_or_n_year,
-- # ways - General Relevance Tags
-- #Keep the semicolon at the end, we will leave empty at the last section, internal mappers tags
ways.id as w_or_n_id,
ways.tags->'name' As w_or_n_name,
-- #Specific Tags
-- #Keep the semicolon at the end, we will leave empty at the last section, internal mappers tags
ways.tags as w_or_n_tags,
ways.tags->'ref' As w_or_n_ref,
ways.tags->'highway' As w_or_n_highway,
-- we add the name geom to ways.linestring so that Qgis will know automaticly that this is the column that holds geometry data
ways.linestring as geom
from relation_members,ways,relations--,nodes
where (relation_members.member_id = ways.id)
--OR (relation_members.member_id = nodes.id)
AND (relation_members.relation_id = relations.id)
AND (relations.tags ->'type'in ('restriction'))
--limit 5
--group by relation_id
UNION ALL
select
--relation_id,count(*)
-- #relations_members
relation_members.relation_id rl_id,
relation_members.member_id as rl_member_id,
relation_members.member_role as rl_member_role,
relation_members.sequence_id as rl_sequence,
relation_members.member_type as rl_momber_type,
-- #relations
relations.id as r_id,
relations.version as r_version,
relations.tstamp as r_tstamp,
relations.user_id as r_user_id,
-- relation tstamp minus ways tstamp
age (relations.tstamp,nodes.tstamp) as diff_rel_ways_or_nodes,
-- The day of the week as Sunday (0) to Saturday (6)
extract(dow from relations.tstamp) as r_day_of_the_week,
-- Create a column for ways that will count the number of keys in each row.
array_length(avals(nodes.tags), 1) AS w_or_n_count_keys,
-- done -- Max nodes per way segment
ST_NPoints(nodes.geom) AS w_or_n_points_per_way,
-- ways --
-- #Internal mappers tags
-- #leave empty the end of the last tag, don`t end it with the semicolon
nodes.version as w_or_n_version,
nodes.changeset_id as w_or_n_changeset_id,
nodes.user_id as w_or_n_user_id,
nodes.tstamp as w_or_n_tstamp,
date_trunc('day',nodes.tstamp) as w_or_n_day,
date_trunc('month',nodes.tstamp) as w_or_n_month,
date_part('year', nodes.tstamp) as w_or_n_year,
-- # ways - General Relevance Tags
-- #Keep the semicolon at the end, we will leave empty at the last section, internal mappers tags
nodes.id as w_or_n_id,
nodes.tags->'name' As w_or_n_name,
-- #Specific Tags
-- #Keep the semicolon at the end, we will leave empty at the last section, internal mappers tags
nodes.tags as w_or_n_tags,
nodes.tags->'ref' As w_or_n_ref,
nodes.tags->'highway' As w_or_n_highway,
-- we add the name geom to nodes.geom so that Qgis will know automaticly that this is the column that holds geometry data
nodes.geom as geom
from relation_members,relations,nodes
where --(relation_members.member_id = ways.id)
(relation_members.member_id = nodes.id)
AND (relation_members.relation_id = relations.id)
AND (relations.tags ->'type'in ('restriction'))
) subquery
group by subquery.rl_id
order by count desc
-- #End Code