61 lines
1.7 KiB
SQL
61 lines
1.7 KiB
SQL
create view visible_streets_from_cam as
|
|
with
|
|
fields as (
|
|
select
|
|
c.node_id as camera_id,
|
|
h.way_id as street_id,
|
|
-- Changer ici pour la résolution du test d'intersection
|
|
ST_Segmentize(h.geom, 2) as street_geom,
|
|
c.geom as camera_coord
|
|
from
|
|
cameras c
|
|
left join
|
|
-- Changer ici pour la distance à la caméra
|
|
highways h on ST_DWithin(c.geom, h.geom, 100)
|
|
--where c.node_id = 9760071131
|
|
),
|
|
segments as (
|
|
select
|
|
camera_id,
|
|
street_id,
|
|
ST_MakeLine(camera_coord,p) as seg_line,
|
|
seg_id
|
|
from (
|
|
select
|
|
fields.camera_id,
|
|
fields.street_id,
|
|
fields.camera_coord,
|
|
generate_series(1, ST_NPoints(fields.street_geom)) as seg_id,
|
|
ST_PointN(fields.street_geom, generate_series(1, ST_NPoints(fields.street_geom))) as p
|
|
from fields
|
|
) as s
|
|
),
|
|
line_of_sight as (
|
|
select
|
|
segments.seg_id,
|
|
segments.camera_id,
|
|
segments.street_id,
|
|
buildings.area_id as building_id,
|
|
ST_Intersects(seg_line, buildings.geom) as inter
|
|
from segments
|
|
left join buildings
|
|
on ST_Intersects(seg_line, buildings.geom)
|
|
),
|
|
visible_street as (
|
|
select
|
|
camera_id,
|
|
street_id,
|
|
seg_id,
|
|
not((inter is not null) and inter) as is_visible,
|
|
building_id
|
|
from line_of_sight
|
|
)
|
|
select
|
|
distinct
|
|
street_id,
|
|
camera_id
|
|
from visible_street
|
|
where is_visible;
|
|
|
|
select STRING_AGG(distinct(street_id::text), ',') from visible_streets_from_cam;
|