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.
CREATE OR REPLACE FUNCTION podd.import_exists_roadsigns(
pid integer,
roadid text,
fmp double precision DEFAULT NULL::double precision,
tmp double precision DEFAULT NULL::double precision,
importtype text DEFAULT 'import-all'::text,
removeself boolean DEFAULT false)
RETURNS boolean AS
$BODY$
fmp_km = plpy.execute(""" select coalesce(%(fmp)s, podd.get_fmp(%(pid)s, '%(roadid)s')) fmp_km""" % {'pid': pid, 'roadid': roadid, 'fmp': fmp})[0]['fmp_km']
tmp_km = plpy.execute(""" select coalesce(%(tmp)s, podd.get_fmp(%(pid)s, '%(roadid)s')) tmp_km""" % {'pid': pid, 'roadid': roadid, 'tmp': tmp})[0]['tmp_km']
signs = plpy.execute("""
with roads as (select geom as road from tbl_roads where road_code = %(road_code)s and roadid = '%(roadid)s'),
raw_signs as (select
ta.id,
km_beg,
geom,
vertical_order,
case when k_s028_1 in (3,4) then 0 else ST_LineCrossingDirection(road, st_extrapolateline_meters(st_shortestline(geom,road),1) ) end as dir,
(select code from podd.dict_roadsigncode tb where name = (select name from dorgis.dict_roadsigncode tc where tc.code = ta.k_s028_3_1)) as code,
k_s028_2 as vis, --направлеине видимости
k_s028_1 as pos, --поперечное положение
sign_text, -- текст знака
width, height,
(select support
from podd.tbl_roadsigns tt
where pid = %(pid)s
and roadid = '%(roadid)s'
and tt.parent_id = ta.id
and substring(tt.sign_code_text from '[ТП\.]*')='' and tt.sign_code_text != 'белолунный') as dislocated
from dorgis.tbl_roadsigns ta,roads
where road_code = %(road_code)s and roadid = '%(roadid)s' and k_s028_3_1 not in (566,565)),
semaphores_raw as (select
ta.id,
km_beg,
geom,
vertical_order,
-- 1 - слева, -1 справа
ST_LineCrossingDirection(road, st_extrapolateline_meters(st_shortestline(geom,road),1)) as dir,
( select code from podd.dict_roadsigncode tc where tb.name = tc.name limit 1 ) as code,
null::text as sign_text,-- текст знака
null::real as width, -- шириныа
null::real as height, -- высота
(select support
from podd.tbl_roadsigns tt
where pid = %(pid)s
and roadid = '%(roadid)s'
and tt.parent_id = ta.id
and (substring(tt.sign_code_text from '[ТП\.]*')!='' or tt.sign_code_text = 'белолунный')) as dislocated
from (dorgis.tbl_semaphores ta left join dorgis.dict_semaphoretype tb on (ta.k_s019_1 = tb.code)), roads
where road_code = %(road_code)s and roadid = '%(roadid)s'
),
semaphores as (
select *,
case when dir = 1 then 4 else 2 end as vis, -- направление видимости
case when dir = 1 then 1 else 2 end as pos, -- поперечное положение
case when dir = 1 then 4 else 2 end as orientation
from semaphores_raw
),
signs_prepared as (select
id, km_beg, geom, vertical_order, dir, code, sign_text, width, height, dislocated,
case when pos = 6 /*на съезде слева*/ and vis = 1 /*прямое*/ then 3 /*вверх*/
when pos = 6 /*на съезде слева*/ and vis = 2 /*обратное*/ then 1 /*вниз*/
when pos = 7 /*на съезде справа*/ and vis = 1 then 1
when pos = 7 and vis = 2 then 3
when vis = 5 /*налево обратное*/ or vis = 4 /*налево*/ then 3
when vis = 6 /*направо обратное*/or vis = 3 /*направо*/then 1
when vis = 2 then 4
else 2 end as orientation
from raw_signs
union all select
id, km_beg, geom, vertical_order, dir, code, sign_text, width, height, dislocated, orientation
from semaphores),
buffers as (select row_number() over (order by buff) as index, buff, dir as bdir from (select (st_dump(st_collectionextract(st_makevalid(st_union(st_buffer_meters(geom,1))),3))).geom as buff, dir from signs_prepared group by dir)tta)
select
id, dislocated, km_beg, dir as side, code, sign_text, orientation, width, height, index, vertical_order
from signs_prepared ta join buffers tb on (st_contains(tb.buff,ta.geom) and ta.dir = tb.bdir) where code is not null
""" % {'pid': pid, 'roadid': roadid, 'road_code': road_code})
if importtype == 'import-all':
plpy.execute(""" delete from podd.tbl_roadsigns
where pid = %(pid)s
and roadid = '%(roadid)s'
and parent_id is not null and status != 2
and %(fmp_km)s <= km_beg and km_beg <= %(tmp_km)s """ % {'pid': pid,
'roadid': roadid,
'fmp_km': fmp_km,
'tmp_km': tmp_km})
plpy.execute(""" delete from podd.tbl_support
where pid = %(pid)s
and roadid = '%(roadid)s'
and coalesce(array_length(signs,1),0) = 0
and %(fmp_km)s <= km_beg and km_beg <= %(tmp_km)s """ % {'pid': pid,
'roadid': roadid,
'fmp_km': fmp_km,
'tmp_km': tmp_km})
supports = {}
#index - номер буффера в котором находится знак
indexes = set([x['index'] for x in signs])
for ind in indexes:
signsFiltered = [x for x in signs if x['index'] == ind]
# существующая стойка для этой группы знаков. Но может получиться так, что она удалена при importtype == 'import-all'
existsSupport = [x['dislocated'] for x in signsFiltered if x['dislocated'] != None]
if len(existsSupport) == 0:
existsSupport = None
else:
existsSupport = existsSupport[0]
sign = signsFiltered[0]
isHidden = True if sign['orientation'] in [1,3] else False
km_beg = sign['km_beg']
side = sign['side']
if (importtype == 'import-all' or existsSupport == None) and fmp_km<=km_beg<=tmp_km:
sid = plpy.execute("""
with edge as (select geom from podd.tbl_roadways_line where pid = %(pid)s and roadid = '%(roadid)s' and side = (case when %(side)s = -1 then 2 else 1 end))
insert into podd.tbl_support(pid, road_code, roadid,km_beg,status,hidden,torientation,geom)
select %(pid)s,
(select road_code from podd.tbl_projects where id = %(pid)s),
'%(roadid)s',
%(km_beg)s,
1,
%(hidden)s,
0,
st_makeline(st_makepoint(%(km_beg)s,podd.get_edge_y(geom, %(km_beg)s)),st_makepoint(%(km_beg)s,podd.get_edge_y(geom, %(km_beg)s)+sign(podd.get_edge_y(geom, %(km_beg)s))*(select sign_level from podd.tbl_projects where id = %(pid)s)))
from edge returning id;
""" % {'pid': pid, 'roadid': roadid, 'km_beg': km_beg, 'side': side, 'hidden': isHidden})
supports[sign['index']] = sid[0]['id']
plpy.notice(pid, roadid,km_beg,side,isHidden, sid[0]['id'])
else:
supports[sign['index']] = existsSupport
signs = list(signs)
signs.sort(key = lambda x: x['vertical_order'])
signs.sort(key = lambda x: x['index'])
for sign in signs:
if (importtype == 'import-all' or sign['dislocated'] == None or sign['dislocated'] == False) and (fmp_km<=sign['km_beg']<=tmp_km):
plpy.execute("""
insert into podd.tbl_roadsigns (pid, road_code, roadid, orientation, sign_code, sign_text, vertical_order, width, height, support, geom, status, parent_id)
select %(pid)s, (select road_code from podd.tbl_projects where id = %(pid)s limit 1), '%(roadid)s', %(orientation)s, %(sign_code)s, %(sign_text)s, %(vertical_order)s, %(width)s, %(height)s, %(support)s, null, 1, %(id)s;
""" % {'id': sign['id'],
'pid': pid,
'roadid': roadid,
'orientation': sign['orientation'],
'sign_code': sign['code'],
'sign_text': ("'"+sign['sign_text']+"'" if sign['sign_text'] != None else 'null'),
'vertical_order': sign['vertical_order'] or 'null',
'width': sign['width'] or 'null',
'height': sign['height'] or 'null',
'support': supports[sign['index']] })
return True
$BODY$
LANGUAGE plpythonu VOLATILE
COST 100;
ALTER FUNCTION podd.import_exists_roadsigns(integer, text, double precision, double precision, text, boolean)
OWNER TO postgres;
