Ciao Sandro e Andrea,
allora ho provato e direi che ora la situazione è questa.
La select suggerita da Sandro è ok e funziona .....
SELECT
lc1.lc_name AS "Tuscan Local Council",
c1.county_name AS "Tuscan County",
lc2.lc_name AS "Neighbour LC",
c2.county_name AS County,
r2.region_name AS Region
FROM
local_councils AS lc1,
local_councils AS lc2
JOIN counties AS c1 ON (c1.county_id = lc1.county_id)
JOIN counties AS c2 ON (c2.county_id = lc2.county_id)
JOIN regions AS r1 ON (r1.region_id = c1.region_id)
JOIN regions AS r2 ON (r2.region_id = c2.region_id)
WHERE
r1.region_name LIKE 'toscana'
AND r1.region_id <> r2.region_id
AND ST_Touches(lc1.geometry, lc2.geometry)
AND lc2.ROWID IN (
SELECT ROWID FROM SpatialIndex
WHERE f_table_name='DB=main.local_councils' AND
search_frame=lc1.geometry
)
ORDER BY c1.county_name, lc1.lc_name;
La select di Andrea aveva qualche piccolo errore che mi sembra ora di aver
corretto e quindi la versione definitiva è la seguente:
select
z."Tuscan Local Council",
z."Tuscan County",
z."Neighbour LC",
z.County,
z.Region,
z.geometry
from
(
SELECT
lc1.lc_name AS "Tuscan Local Council",
c1.county_name AS "Tuscan County",
lc2.lc_name AS "Neighbour LC",
c2.county_name AS County,
r2.region_name AS Region,
lc1.geometry AS geometry
FROM
local_councils AS lc1,
local_councils AS lc2
JOIN counties AS c1 ON (c1.county_id = lc1.county_id)
JOIN counties AS c2 ON (c2.county_id = lc2.county_id)
JOIN regions AS r1 ON (r1.region_id = c1.region_id)
JOIN regions AS r2 ON (r2.region_id = c2.region_id)
WHERE
r1.region_name LIKE 'toscana'
AND r1.region_id <> r2.region_id
AND ST_Touches(lc1.geometry, lc2.geometry)
AND lc2.ROWID IN (
SELECT ROWID FROM SpatialIndex
WHERE f_table_name='DB=main.local_councils' AND
search_frame=lc1.geometry
)
) as z
ORDER BY z."Tuscan County", z."Neighbour LC";
Grazie mille per il sollecito supporto
Cesare
Cesare Gerbino
http://cesaregerbino.wordpress.com/
http://www.facebook.com/cesare.gerbino
http://www.facebook.com/pages/Cesare-Gerbino-GIS-Blog/246234455498174?ref=hl
https://twitter.com/CesareGerbino
http://www.linkedin.com/pub/cesare-gerbino/56/494/77b
Il giorno 05 gennaio 2014 13:15, aperi2007 <aperi2...@gmail.com> ha scritto:
> Alessandro,
> grazie per la correzione.
> L'errore causava una errata dichiarazione di tabelle che in spatialite
> equivale a d avere sempre risultato vuoto.
>
>
> se si riscrive la query semplicemente omettendo del tutto la
>
>> ORDER BY si scopre invece che la query torna a funzionare a tutta
>> manetta (proprio come avveniva con le vecchie versioni di sqlite).
>> tempi oggettivi misurati:
>>
>> con ORDER BY: circa 3 minuti
>> senza ORDER BY: poco meno di 10 secondi :-D
>>
>
> organizzandola con una subquery che esegua il lavoro spaziale e una query
> che esegue solamente l'ordinamento,
> il query planner non dovrebbe far eseguire l'ordinamento prima del
> filtraggio con l'indice spaziale.
>
> select
> z."Tuscan Local Council",
> z."Tuscan County",
> z."Neighbour LC",
> z.County,
> z.Region,
> z.geometry
> from
>
> (
> SELECT
> lc1.lc_name AS "Tuscan Local Council",
> c1.county_name AS "Tuscan County",
> lc2.lc_name AS "Neighbour LC",
> c2.county_name AS County,
> r2.region_name AS Region
> FROM
> local_councils AS lc1,
> local_councils AS lc2
> JOIN counties AS c1 ON (c1.county_id = lc1.county_id)
> JOIN counties AS c2 ON (c2.county_id = lc2.county_id)
> JOIN regions AS r1 ON (r1.region_id = c1.region_id)
> JOIN regions AS r2 ON (r2.region_id = c2.region_id)
> WHERE
> r1.region_name LIKE 'toscana'
> AND r1.region_id <> r2.region_id
> AND ST_Touches(lc1.geometry, lc2.geometry)
> AND lc2.ROWID IN (
> SELECT ROWID FROM SpatialIndex
> WHERE f_table_name='DB=main.local_councils' AND
> search_frame=lc1.geometry
> )
> ) as z
> ORDER BY z.county_name, z.lc_name;
>
> Andrea.
>
>
> _______________________________________________
> Gfoss@lists.gfoss.it
> http://lists.gfoss.it/cgi-bin/mailman/listinfo/gfoss
> Questa e' una lista di discussione pubblica aperta a tutti.
> I messaggi di questa lista non hanno relazione diretta con le posizioni
> dell'Associazione GFOSS.it.
> 666 iscritti al 22.7.2013
>
_______________________________________________
Gfoss@lists.gfoss.it
http://lists.gfoss.it/cgi-bin/mailman/listinfo/gfoss
Questa e' una lista di discussione pubblica aperta a tutti.
I messaggi di questa lista non hanno relazione diretta con le posizioni
dell'Associazione GFOSS.it.
666 iscritti al 22.7.2013
