-
Notifications
You must be signed in to change notification settings - Fork 1
/
create-segments-table.sql
95 lines (88 loc) · 3.08 KB
/
create-segments-table.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
/*
This SQL script is designed to create a duplicate of the edge table that has
been imported with osm2po, adding fields from the table imported with
osm2pgsql, which is the one that has all the actual meaningful attributes.
It them modifies the duplicate table to remove edges that shouldn't be in
the network graph.
After this has been done, and in the middle of this script, run tarjan.php
to identify biconnected subcomponents. Once that has run, minor dangling
paths can be dropped as well.
*/
-- add all fields useful to rendering highways to a split line table
SELECT --lets just enumerate all the columns here
id AS edge_id,
osm_id,
ST_Transform(geom_way,3735) AS the_geom,
source,
target,
--we'll populate these from the other table in just a moment
--for now they are empty
null::varchar AS highway,
null::varchar AS label,
null::varchar AS cycleway,
null::varchar AS "cycleway:left",
null::varchar AS "cycleway:right",
null::varchar AS surface,
null::varchar AS bicycle,
null::varchar AS access,
null::real AS all_lanes,
null::varchar AS mph,
null::varchar AS oneway,
null::varchar AS tunnel,
null::varchar AS bridge,
null::varchar AS layer,
null::varchar AS service,
null::varchar AS service_is_in,
-- this is a spatial measure of length in miles
ST_Length(ST_Transform(geom_way,3735)) / 5280 AS miles,
--is part of primary biconnected component? TRUE = no
false::boolean AS dangling
INTO cincy_segments
FROM c_2po_4pgr; --<<----------<<---------SOURCE EDGE TABLE NAME--------<<--
-- for the join
CREATE INDEX ON cincy_segments (osm_id);
--get attributes from the cincy_line table (the one imported with osm2pgsql)
UPDATE cincy_segments AS cs
SET
highway = cl.highway,
label = cl.label,
cycleway = cl.cycleway,
"cycleway:left" = cl."cycleway:left",
"cycleway:right" = cl."cycleway:right",
surface = cl.surface,
bicycle = cl.bicycle,
access = cl.access,
all_lanes = cl.all_lanes,
mph = cl.mph,
oneway = cl.oneway,
tunnel = cl.tunnel,
bridge = cl.bridge,
layer = cl.layer,
service = cl.service,
service_is_in = cl.service_is_in
FROM cincy_line AS cl
WHERE cl.osm_id = cs.osm_id;
-- now index to speed things up
CREATE INDEX ON cincy_segments USING GIST(the_geom);
CREATE INDEX ON cincy_segments (source);
CREATE INDEX ON cincy_segments (target);
--drop stuff we don't want in here
DELETE FROM cincy_segments
WHERE
service IN ('ramp','parking_aisle','spur','drive-through')
OR (
highway IN ('motorway','motorway_link','footway','trunk','trunk_link','track')
AND (bicycle IS NULL OR bicycle NOT IN ('yes','designated'))
)
OR
(access IN ('no','emergency','private','official') AND
highway != 'residential')
OR
service_is_in IN ('golf_course')
OR
bicycle IN ('no');
----------------------------------------------------------------------------
/*--------------------------------------------------------------------------
-----RUN tarjan.php at this point to detect dangling components-------------
---------------------------------------------------------------------------*/
---------------------------------------------------------------------------