Cameras

Importer les données avec OSM2PGSQL

local cameras = osm2pgsql.define_node_table('cameras', {
    { column = 'id', sql_type = 'serial', create_only = true },
    { column = 'geom', type = 'point' },
})

local highways = osm2pgsql.define_way_table('highways', {
    { column = 'id', sql_type = 'serial', create_only = true },
    { column = 'geom', type = 'linestring' },
})

local buildings = osm2pgsql.define_area_table('buildings', {
    { column = 'id', sql_type = 'serial', create_only = true },
    { column = 'geom', type = 'polygon' },
})

function osm2pgsql.process_node(object)
    if object.tags.man_made == 'surveillance' then
        cameras:insert({
            geom = object:as_point( )
        })
    end
end

function osm2pgsql.process_way(object)
    if object.is_closed and object.tags.building then
        buildings:insert({
            geom = object:as_polygon()
        })
    end

    if object.tags.highway then
        highways:insert({
            geom = object:as_linestring()
        })
    end
end

function osm2pgsql.process_relation(object)
    if object.tags.type == 'multipolygon' and object.tags.building then
        local geom = object:as_multipolygon()

        for g in geom:geometries() do
            buildings:insert({
                geom = g,
            })
        end

    end
end

osm2pgsql -d toto -U toto -W -H localhost -O flex -S extarct_features.lua st_quentin.osm.pbf

Utiliser postgis

Calculer une distance

with c1 as (select geom as g from cameras where id = 1), c2 as (select geom as g from cameras where id = 2) select ST_Distance(c1.g, c2.g) from c1, c2 ;

calculer les ways qui sont a moins de 100m d'une camera

select c.node_id, h.way_id from cameras c left join highways h on ST_DWithin(c.geom, h.geom, 100) where c.id = 1

Calculer les ways visible d'une camera

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;

Editer du pbf avec osmium

Convertis en format opl (format textuelle, éditable)

osmium cat st_quentin.osm.pbf -f opl > st_quentin.opl

L'inverse

osmium cat st_quentin.opl -f pbf > st_quentin.osm.pbf

Avoir un diff

osmium diff st_quentin.osm.pbf merge.osm.pbf -f opl > diff.txt
cat diff.txt | grep "^+" # ajout
cat diff.txt | grep "^-" # suppression

Appliquer un fichier de changeset (attention écrase toute la relation ou tout le noeud)

osmium apply-changes st_quentin.osm.pbf osmChange.xml -o new.osm.pbf

un peu de python...

import re

inputFile = "st_quentin.opl"
outputFile = "output.txt"

idsToEdit = ("w1087846855","w1087920777","w1087920778","w1087920779","w1087932398","w1087942997","w1087952021","w1087952022","w1087952024","w1087952025","w1087960194","w1087960196","w1087960210","w1087960223","w1087960224","w1089446799","w1089446800","w1112075298","w113961904","w113961905","w114536713","w114666459","w116382851","w116382861","w117355631","w11773344","w11773350","w122006038","w126094356","w126871656","w126871657","w126871658","w126871664","w126871669","w126871675","w126871690","w129705116","w147848058","w148736510","w148825323","w150550805","w155480974","w15802834","w15802836","w165807675","w168295763","w170146623","w170146625","w172022081","w172023915","w172023916","w173256365","w173256368","w173256369","w173256729","w173256730","w173256731","w173832779","w174488908","w177853034","w177853035","w180280918","w180461913","w180465548","w180468951","w182628812","w182629658","w185057057","w189637127","w193375648","w19844790","w19847245","w19848882","w202737485","w202970121","w203030595","w217421358","w222393302","w22341574","w22343527","w22343529","w22345126","w225625930","w22568170","w22568182","w22568375","w22568378","w22568386","w22568388","w22568424","w22568488","w22568523","w22588043","w22588046","w22604437","w22673740","w22674191","w22674884","w22675194","w22698778","w227400308","w227400309","w23025712","w277863607","w28353561","w29619599","w29619604","w315383203","w31662553","w31662878","w31662879","w31662880","w31662964","w31662965","w31662968","w31663124","w330925134","w330925135","w330926195","w331028474","w331028475","w339715316","w339726718","w339726719","w346027918","w349277858","w349531415","w35542048","w363185344","w364068225","w365390727","w370956707","w38742540","w38742541","w40372065","w40425070","w40425074","w4229251","w443334075","w443366920","w447751613","w447751614","w48225928","w492147752","w49835957","w49835959","w49835960","w50673023","w509055318","w509246741","w509246744","w509246745","w509246746","w509246747","w509246753","w51341167","w51341168","w515490602","w51692324","w52275569","w52303054","w52303060","w525662191","w525662193","w52613839","w52613863","w526190596","w530531180","w530531181","w530531182","w531463493","w531692522","w531692526","w531692527","w536092843","w536092847","w553315235","w587903986","w587903987","w587908623","w587908627","w587908630","w587908634","w59854247","w628788987","w630445718","w630446538","w630455616","w636627411","w636627412","w641622109","w641622111","w641622112","w641622114","w641622115","w641622116","w641622118","w650553749","w662006651","w662006652","w662006654","w694476166","w698066552","w705274809","w705274810","w705274811","w708338741","w764629037","w770613905","w770613907","w829119358","w829119361","w829119362","w829119363","w829119365","w829119366","w829180626","w829180633","w829252460","w85552224","w87565425","w879402866","w880518011","w881591456","w881764531","w881764532","w881764537","w881764538","w881764539","w881764540","w881764543","w881764545","w881764546","w881764547","w881764549","w881764552","w88646884","w88646885","w88646897","w89578796","w89685774","w89685814","w89685825","w930154230","w930154231","w944384994","w963397962","w963397963","w991218943","w991218944","w991218945","w991218946","w991218947")

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=true,', line)
            if line == newLine:
                print(line)
            fo.write(newLine)
        else:
            fo.write(line)