[Gfoss] SpatiaLite Cookbook: SQL error: no such function: RTreeIntersects

[cesare gerbino]

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 <aperi2007 a 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 a 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
>
-------------- parte successiva --------------
Un allegato HTML ? stato rimosso...
URL: <http://lists.gfoss.it/pipermail/gfoss/attachments/20140105/0d2900ae/attachment.html>