Skip to content

WIP_map_v1

Jump to bottom
Salvatore edited this page Aug 3, 2016 · 5 revisions

Spazializzare fermate e rotte (utilizzando DB spatialite)

fermate (DB openamatv_10.sqlite)

ho notato nel field stop_id: 9000<=stop_id<=9100 sono solo fermate TRAM il complementare saranno solo fermate bus

script sql - fermate TRAM

select *,MakePoint(CAST(stop_lon AS float),CAST(stop_lat AS float),4326) AS geom
from stops
where stop_id >=9000 and stop_id <=10000 
order by PK_UID

NB: durante l'importazione dei dati GTFS nel DB spatialite (spatialite_gui - load file csv/txt) viene creata una chiave primaria PK_UID molto utile!!!

NB: SRID 4326 è il Sistema di riferimento dei dati GTFS: se non esplicitato QGIS lo richiederà durante il caricamento della query.

script sql - fermate BUS

select *,MakePoint(CAST(stop_lon AS float),CAST(stop_lat AS float),4326) AS geom
from stops
where not stop_id >=9000 and stop_id <=10000 
order by PK_UID

not operatore booleano che nega la condizione where quindi prende i dati complementari.

NB: durante l'importazione nel DB spatialite viene creata una chiave primaria PK_UID molto utile!!!

NB: SRID 4326 (è stato aggiunto)

entrambe le query possono essere utilizzare nel DBmanager di QGIS (occorre aver realizzato il collegamento ad DB spatialite)

rotte (DB openamatv_10.sqlite)

è lo stesso script sql di @aborruso qui con in più la condizione

-- rotte solo tram
select r.route_id, r.agency_id, r.route_short_name, r.route_long_name, r.route_type, r.route_url, trip_headsign, l.*
from 
(select shape_id, MakeLine(pt) AS geometry
    from 
    (select shape_id, CAST(shape_pt_sequence AS Integer) AS seq,
        MakePoint(CAST(shape_pt_lon AS float),CAST(shape_pt_lat AS float),4326) AS pt
        from shapes
		where shape_id like 'TRAM%' -- condizione per ottenere solo tram
        order by shape_id, seq) AS tpt
    group by shape_id) AS l
join 
(select distinct route_id, trip_headsign, shape_id from trips) AS t 
on t.shape_id == l.shape_id
join 
routes AS r on r.route_id == t.route_id;
-- rotte solo BUS
select r.route_id, r.agency_id, r.route_short_name, r.route_long_name, r.route_type, r.route_url, trip_headsign, l.*
from 
(select shape_id, MakeLine(pt) AS geometry
    from 
    (select shape_id, CAST(shape_pt_sequence AS Integer) AS seq,
        MakePoint(CAST(shape_pt_lon AS float),CAST(shape_pt_lat AS float),4326) AS pt
        from shapes
		where not shape_id like 'TRAM%'-- condizione per ottenere solo bus
        order by shape_id, seq) AS tpt
    group by shape_id) AS l
join 
(select distinct route_id, trip_headsign, shape_id from trips) AS t 
on t.shape_id == l.shape_id
join 
routes AS r on r.route_id == t.route_id;

Quartieri e UPL (DB quart_UPL_palermo.sqlite)

  • vettore quartieri è stato scaricato dal sito istituzionale di Palermo e successivamente importato nel DB; EPSG: 4326;

  • vettore UPL_2011 prelevato da qui, EPSG 4326;

  • tabella pop_UPL_2011_PA prelevata da qui;

layer v_quartieri_pop 👍 trasferisce il dato popolazione UPL nei quartieri

select id,nome,  CAST (sum(kk.tot ) AS Integer) as tot_pop, q.geom
from
   (select k.UPL,centroid (k.geom) as geom, k.totale as tot
    from
      (select CAST(u.UPL AS Integer) AS UPL,u.nome,CAST(p.Totale as Integer) as totale,u.geom
       from UPL_2011 as u,pop_UPL_2011_PA as p
    where u.UPL == p.UPL) as k) as kk, quartieri AS q
where Contains (q.geom, kk.geom)
group by id,nome
Clone this wiki locally