-
Notifications
You must be signed in to change notification settings - Fork 1
/
notes.txt
200 lines (165 loc) · 6.52 KB
/
notes.txt
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
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
Paris / Marseille TGV:
line:OCE:SN-87751008-87686006
route:OCE:SN-TGV-87686006-87751008
Ligne Marseille/Aix/Perthuis
line:OCE:340
Aix TGV :
stop_area:OCE:SA:87319012
stop_point:OCE:SP:TGV-87319012
Meyrargues :
stop_area:OCE:SA:87751370
stop_point:OCE:SP:TrainTER-87751370
Marseille St Charles:
stop_area:OCE:SA:87751008
stop_point:OCE:SP:CorailIntercité-87751008
stop_point:OCE:SP:TGV-87751008
stop_point:OCE:SP:TrainTER-87751008
Marseille -> Paris not stopping at Aix
Lundi 30/01 Durée 03h07 Départ à : 08h38 DE LA GARE DE : MARSEILLE SAINT CHARLES TRANSPORTEUR : TGV Numéro du train :6108
Arrivée à : 11h45 A la gare de : PARIS GARE DE LYON
Marseille -> Paris not stopping at Aix
Lundi 30/01 Durée 03h07 Départ à : 08h38 DE LA GARE DE : MARSEILLE SAINT CHARLES TRANSPORTEUR : TGV Numéro du train :6108
Arrivée à : 11h45 A la gare de : PARIS GARE DE LYON
declare @g geography = 'POLYGON((5.2734375 43.259580971072275,5.41351318359375 43.1614915129406,5.4986572265625 43.295574211963746,5.5810546875 43.42936191764414,5.90789794921875 43.57678451504994,5.877685546875 43.74766111392921,5.88043212890625 43.86064850339098,5.62225341796875 43.75559702541283,5.4327392578125 43.670230832122314,5.27069091796875 43.58474304793296,5.23773193359375 43.431356514362626,5.2734375 43.259580971072275))'
Good routes to test :
route:OCE:104647-TrainTER-87581009-87592006
------------------------------------------
select T.ID_RTE500 as ID_TRONCON
, N.ID_RTE500 AS ID_NOEUD
from dbo.TRONCON_VOIE_FERREE_2154 T
left join dbo.NOEUD_FERRE_2154 N
ON T.geom2154.STIntersects(N.geom2154) =1
ORDER BY T.ID_RTE500
select N.ID_RTE500 AS ID_NOEUD
, T.ID_RTE500 as ID_TRONCON
from dbo.NOEUD_FERRE_2154 N
left join dbo.TRONCON_VOIE_FERREE_2154 T
ON N.geom2154.STIntersects(T.geom2154) =1
ORDER BY N.ID_RTE500
-------------------------------------------------
declare @bufferEnvelope float = 5000
declare @bufferTroncon float = 30
declare @bufferTronconResult float = 500
declare @bufferStopPoint float = 750
declare @bufferStartStop float = @bufferStopPoint*2
declare @ids table (seq int identity, id int);
insert into @ids (id) values
(2815),(2496),(3782),(3162),(3238),(4334),(3460),(1836),(4335),(2235)
declare @resultids table (seq int identity, idTroncon int);
insert into @resultids (idTroncon) values
(3307),(3306),(5203),(5204),(1772),(3052),(3053),(3054),(3051),(3048),(3049),(3050),(3047),(1804),(1805),(1801),(1799),(1796),(1795),(1802),(1803),(1798),(1797),(1800),(1653),(1650),(1656),(1657),(1654),(1655),(1651),(1652),(336),(335),(334),(2221)
declare @startId int
declare @endId int
select top 1 @startId = id from @ids order by seq
select top 1 @endId = id from @ids order by seq desc
declare @env geometry
select @env = geometry::UnionAggregate(geom2154) from NOEUD_FERRE_2154
where ID_RTE500 in (select id from @ids)
select @env = @env.STEnvelope().STBuffer(@bufferEnvelope).STEnvelope()
---- All nodes
--select N.ID_RTE500 AS ID_NOEUD
-- , N.geom2154.STBuffer(200)
-- , 'Node' + cast(ID_RTE500 as varchar(10)) as Label
-- from NOEUD_FERRE_2154 N
-- where N.geom2154.STIntersects(@env) = 1
--union all
-- All troncons
select ID_RTE500 AS ID_TRONCON
,geom2154.STBuffer(@bufferTroncon).Reduce(10)
, 'Troncon ' + cast(ID_RTE500 as varchar(10)) as Label
from TRONCON_VOIE_FERREE_2154
where geom2154.STIntersects(@env) = 1
union all
-- All stopareas
select N.ID_RTE500 AS ID_NOEUD
, N.geom2154.STBuffer(@bufferStopPoint)
, 'StopArea' as Label
from NOEUD_FERRE_2154 N
where N.ID_RTE500 in (select id from @ids)
and N.ID_RTE500 NOT IN (@startId,@endId)
union all
-- Start
select N.ID_RTE500 AS ID_NOEUD
, N.geom2154.STBuffer(@bufferStartStop)
, 'Start' as Label
from NOEUD_FERRE_2154 N
where N.ID_RTE500 = @startId
--union all -- debug to see troncon
-- select ID_RTE500 AS ID_TRONCON
-- ,geom2154.Reduce(100).STBuffer(800)
-- , 'Troncon ' + cast(ID_RTE500 as varchar(10)) as Label
-- from TRONCON_VOIE_FERREE_2154
-- where ID_RTE500 = 2920
union all
-- Stop
select N.ID_RTE500 AS ID_NOEUD
, N.geom2154.STBuffer(@bufferStartStop)
, 'Stop' as Label
from NOEUD_FERRE_2154 N
where N.ID_RTE500 = @endId
union all
-- CHECK RESULT
select T.ID_RTE500 AS ID_NOEUD
, T.geom2154.STBuffer(@bufferTronconResult).Reduce(10)
, cast(R.seq as varchar(5)) as Label
from TRONCON_VOIE_FERREE_2154 T
join @resultids R
ON R.idTroncon = T.ID_RTE500
--order by 1
----------------------------------------
declare @t geometry
select @t = geom2154 from dbo.TRONCON_VOIE_FERREE_2154 where ID_RTE500 = 5621
select * from dbo.TRONCON_VOIE_FERREE_2154 where geom2154.STDistance(@t) < 2000
select @t.STBuffer(200)
union all
select geom2154 from dbo.TRONCON_VOIE_FERREE_2154 where geom2154.STDistance(@t) < 2000
union all
select geom2154.STBuffer(50) from dbo.NOEUD_FERRE_2154 where geom2154.STDistance(@t) < 2000
-----------------------------------
-- Pro Spatial
declare @ids table (seq int identity, id int);
insert into @ids (id) values (2112),(3705),(2598),(2600),(3691),(3573),(2067),(2068),(1478),(2722),(2792),(2286),(2791),(3399)
declare @startId int
declare @endId int
select top 1 @startId = id from @ids order by seq
select top 1 @endId = id from @ids order by seq desc
DECLARE @Start geometry --= geometry::STPointFromText('POINT (1.297851 52.6292)', 4326),
DECLARE @End geometry --= geometry::STPointFromText('POINT (1.29887 52.62802)', 4326);
select @Start = geom2154 FROM NOEUD_FERRE_2154 WHERE ID_RTE500 = @startId
select @End = geom2154 FROM NOEUD_FERRE_2154 WHERE ID_RTE500 = @endId
declare @env geometry
select @env = geometry::UnionAggregate(geom2154) from NOEUD_FERRE_2154
where ID_RTE500 in (select id from @ids)
select @env = @env.STEnvelope().STBuffer(5000);
WITH Paths
AS
(
-- Anchor member
SELECT
@Start AS [From],
geom2154.STEndPoint() AS [To],
geom2154,
geom2154 As RunningSegment
FROM dbo.TRONCON_VOIE_FERREE_2154
WHERE
geom2154.STStartPoint().STEquals(@Start) = 1
-- Recursive member
UNION ALL
SELECT
p.[To] AS [From],
ss.geom2154.STEndPoint() AS [To],
ss.geom2154,
dbo.Extend(p.RunningSegment, ss.geom2154, 1)
FROM Paths p
JOIN dbo.TRONCON_VOIE_FERREE_2154 ss ON ss.geom2154.STStartPoint().STEquals(p.[To]) = 1
and ss.geom2154.STIntersects(@env) = 1
WHERE p.[To].STEquals(@End) = 0 -- Stop recursion when we reach the end point
--AND p.RunningSegment.STContains(ss.geom2154) = 0 -- Make sure we haven't already included this path in the route
)
SELECT
ROW_NUMBER() OVER(ORDER BY RunningSegment.STLength()) AS 'Route Num',
RunningSegment.STLength() AS Length,
RunningSegment.STAsText() AS RouteWKT,
RunningSegment.STBuffer(RunningSegment.STLength()/100) AS Route
FROM Paths
WHERE [To].STEquals(@End) = 1;