98 lines
2.5 KiB
Python
98 lines
2.5 KiB
Python
import re
|
|
import psycopg2
|
|
|
|
conn = psycopg2.connect(host="localhost",
|
|
user="postgres",
|
|
password="password")
|
|
|
|
cursor = conn.cursor()
|
|
|
|
cursor.execute("""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 distinct(street_id::text) from visible_streets_from_cam;
|
|
""")
|
|
|
|
print("Request done !")
|
|
|
|
ids = cursor.fetchall()
|
|
idsToEditSet = set()
|
|
for id in ids:
|
|
idsToEditSet.add("w"+str(id[0]))
|
|
|
|
idsToEdit = tuple(idsToEditSet)
|
|
|
|
print(len(a),"roads to edits")
|
|
|
|
inputFile = "st_quentin.opl"
|
|
outputFile = "output.opl"
|
|
|
|
fo = open(outputFile,"w")
|
|
|
|
with open(inputFile) as f:
|
|
for line in f:
|
|
if line.startswith(idsToEdit):
|
|
#index = index + 1
|
|
newLine = re.sub(r'( T)', r'\1camera=yes,', line)
|
|
if line == newLine:
|
|
print(line)
|
|
fo.write(newLine)
|
|
else:
|
|
fo.write(line)
|