queryUtili

Salvo dove indicato diversamente, le query di sotto sono eseguite su un db SQLite, anzi SpatiaLite (SQLite con estensioni spaziali). A mo di esempio si può usare questo db.

Verificare differenze di numero di corse della stessa rotta nelle due direzioni di marcia, in un giorno feriale

Query:

SELECT route_id AS "rotta",quantita_direzione0,quantita_direzione1, ("quantita_direzione0"-"quantita_direzione1") 
AS differenza
FROM (SELECT * FROM (SELECT count(*)AS quantita_direzione0,"route_id"
FROM "trips"
where service_id="FR" AND direction_id=0
GROUP BY route_id) 
JOIN (SELECT count(*)AS quantita_direzione1,"route_id"
FROM "trips"
where service_id="FR" AND direction_id=1
GROUP BY route_id
) using (route_id))
where differenza != 0

Esempio output:

rotta	quantita_direzione0	quantita_direzione1	differenza
101	192	193	-1
210	38	39	-1
309	55	54	1
389	2	1	1
462	34	32	2
616	46	48	-2
628	54	47	7
628P	13	12	1
704	45	47	-2
731	45	48	-3

Numero di corse per rotta in un giorno feriale, in una delle due direzioni di marcia (più lunghezza della rotta)

Query:

SELECT quantità,"route_id" as Rotta,"route_long_name" AS Nome, CAST(ST_Length(ST_Transform(geometry,32633)) AS INTEGER) as "Lunghezza (m.)"
FROM 
(SELECT count(*) AS Quantità, "route_id"
FROM "trips"
where service_id="FR" AND direction_id=0
GROUP BY route_id
order by quantità desc)
JOIN routes as rotte using ("route_id")
order by Quantità DESC

Esempio output::

Quantità	Rotta	Nome	Lunghezza
192	101	STADIO - STAZIONE CENTRALE	12373
96	806	POLITEAMA - MONDELLO (TORRE)	21391
80	ARANC	PORTA FELICE - INDIPENDENZA	8315
75	84	MONDELLO (TORRE) - VALDESI	5476
72	237	PARCHEGGIO ORETO - STAZIONE CENTRALE	5160
64	102	STAZ. NOTARBARTOLO - STAZIONE CENTRALE	8037
63	109	PARCHEGGIO BASILE - STAZIONE CENTRALE	7442
60	EXPR	PARCHEGGIO BASILE - INDIPENDENZA	4894
60	544	JOHN LENNON - MONDELLO	22543
55	309	PARCHEGGIO BASILE - ROCCA	13496
54	628	SFERRACAVALLO - STADIO	19761
...	...	...	...

Tutte le corse della linea 101 in un giorno feriale, in una delle due direzioni previste, ordinate per numero corsa e orario

SELECT "stop_id","stop_name","trip_id",arrival_time
FROM (SELECT "a"."stop_id" AS "stop_id", "a"."stop_code" AS "stop_code",
    "a"."stop_name" AS "stop_name", "b"."trip_id" AS "trip_id",
    "b"."stop_id" AS "stop_id_1",arrival_time
FROM "stops" AS "a"
JOIN "stop_times" AS "b" USING ("stop_id"))
JOIN trips using ("trip_id")
WHERE service_id="FR" and direction_id=0 and route_id=101
order by trip_id,arrival_time

Esempio di output:

stop_id	Nome fermata	trip_id	orario
1970	STADIO	293	05:45:00
560	CROCE ROSSA - DE GASPERI	293	05:47:59
566	CROCE ROSSA - VALDEMONE	293	05:48:40
562	CROCE ROSSA - EMILIA	293	05:49:27
564	CROCE ROSSA - STATUA	293	05:50:34
1031	LIBERTA' - LAZIO	293	05:51:43
1029	LIBERTA' - DON BOSCO	293	05:52:38
1033	LIBERTA' - MATTEOTTI	293	05:53:27
1039	LIBERTA' - UGDULENA	293	05:54:31
1027	LIBERTA' - D'ANNUNZIO	293	05:55:19
1041	LIBERTA' - VILLA PAINO	293	05:56:17
...	...	...	...

Numero di passaggi per fermata

SELECT count(*) AS "quantità", stop_id,"stop_name" FROM (SELECT * from (SELECT "a"."stop_id" AS "stop_id",
    "a"."stop_name" AS "stop_name", "b"."trip_id", "b"."arrival_time"
FROM "stops" AS "a"
JOIN "stop_times" AS "b" USING ("stop_id")) AS "c"
JOIN "trips" AS "b" USING ("trip_id")
WHERE service_id="FR")
group BY stop_id
order by "quantità" DESC

Esempio di output:

Numero di passaggi	stop_id	Nome
666	912	INDIPENDENZA - PALAZZO REALE
592	1037	LIBERTA' - QUINTINO SELLA
574	1985	STAZIONE CENTRALE - PENSILINA INTERNA
491	390	CAVOUR
478	2019	TETI
473	1492	PIAZZA PAPA GIOVANNI PAOLO II
473	2115	VILLA SOFIA - CROCE ROSSA
467	1582	POLITEAMA - TURATI
445	1035	LIBERTA' - NOTARBARTOLO
426	1605	PRINCIPE DI SCALEA - MONDELLO
426	1624	REGINA ELENA - CIRCE
426	1629	REGINA ELENA - STABILIMENTO
426	1630	REGINA ELENA - TETI
412	1759	ROMA - BELMONTE
412	1770	ROMA - STABILE
...	...	...

Numero di fermate per UPL

Nota bene, la query di sotto non è applicabile al db di esempio, quindi al momento vale come spunto teorico

SELECT a.*, b.Totale AS Popolazione, (("a"."totale" * 1.0) / ("b"."Totale" * 1.0)) AS fermatePop
FROM (SELECT CAST("poligoni"."UPL" AS INTEGER) AS "UPL", nome, count(punti.geometry) AS totale, 
CAST(ST_AREA(ST_Transform(poligoni.geometry,32633)) AS INTEGER) AS "Area(m2)"
FROM "00UPL" as poligoni LEFT JOIN stops as punti
ON st_contains(poligoni.geometry,punti.geometry) 
GROUP BY poligoni.UPL) AS "a"
JOIN "00PopolazioneUpl2011" as "b" using ("UPL")
ORDER by "fermatePop" DESC

tutte le linee che passano da ogni fermata

SELECT "a".*,b.stop_name AS "nomeFermata",b.geometry
FROM (SELECT stop_id,GROUP_CONCAT(route_id) AS linee
FROM (SELECT stop_id,route_id
FROM (SELECT "trip_id", "stop_id","route_id"
FROM "stop_times"
JOIN trips using("trip_id")
order by stop_id,trip_id)
group by stop_id,route_id)
group by stop_id) AS "a"
JOIN stops as "b" using("stop_id")

Esempio di output:

stop_id	linee	nomeFermata
1	104,118	A. AMEDEO - COLONNA ROTTA
10	100	ACC. A29 MONTE - WELLS
100	645,87	APOLLO - AGLAIA
1002	534	LEONARDO DA VINCI - SILVESTRI
1004	513,H2	LEONARDO DA VINCI - UDITORE
1006	107,603	LEONI - FAVORITA
1007	107,603	LEONI - FAVORITA
1008	243	LEVRIERE - ANTILOPE
1009	243	LEVRIERE - BASSOTTO
...	...	...