[Gfoss] SpatiaLite Cookbook: SQL error: no such function: RTreeIntersects
cesare gerbino
cesaregerbino a gmail.com
Dom 5 Gen 2014 11:55:14 CET
Ciao Andrea,
grazie della risposta sollecita: nella tua select c'era un errore, una
parentesi tonda in più, la versione sintatticamente corretta è questa
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='MAIN=local_councils' AND
search_frame=lc1.geometry
)
ORDER BY c1.county_name, lc1.lc_name;
Ora non dà più errore anche se al momento mi restituisce un insieme vuoto
..... riproverò.
Grazie
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 11:24, Andrea Peri <aperi2007 a gmail.com> ha
scritto:
> Premesso che la query in questione sembra scritta per far casino :)
> Infatti usa due volte la medesima tabella local_councils e quindi un
> utente che deve imparare non capisce se nell'indice deve fare riferimento
> alla prima o alla seconda tabella (sono entrambe le stesse!).
>
> Premesso anche che non ho il tempo di provarla, io credo che questa
> dovrebbe essere quella corretta.
> Eventualmente si tratta di cambiare da lc1.geometry a lc2.geometry.
>
> Questa è la tua query originale, opportunamente indentata per evidenziarne
> la struttura:
>
>
> 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 pkid FROM idx_local_councils_geometry
> WHERE pkid MATCH RTreeIntersects(
> MbrMinX(lc1.geometry),
> MbrMinY(lc1.geometry),
> MbrMaxX(lc1.geometry),
> MbrMaxY(lc1.geometry)
> )
> )
> ORDER BY c1.county_name, lc1.lc_name;
>
>
> Questa altra è come io proverei a tradformarla per usare le ultime novita'
> di spatialite.
> Ovviamente si parla di un db generato con la medesima ultima versione di
> SL.
>
>
> 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='MAIN=local_councils' AND
> search_frame=lc1.geometry
> )
> )
> ORDER BY c1.county_name, lc1.lc_name;
>
>
> se invece che nel medesimo db la tabella è in un db allegato, allora fai:
> WHERE f_table_name='DB=allegato.local_councils' AND
> search_frame=lc1.geometry
>
>
> Saluti,
>
>
>
> Il giorno 05 gennaio 2014 11:08, cesare gerbino <cesaregerbino a gmail.com>ha scritto:
>
>> Ciao a tutti,
>>
>> ho ripreso per approfondire una cosa SpatialLite Cookbook e sto usando
>> l'ultima versione di spatialite_gui.
>>
>> Provando ad eseguire la ricetta 12 "Dintorni" che è quella che mi
>> interesserebbe ho notato che provando ad eseguirla ottengo un errore che è
>> il seguente:
>>
>> SQL error: no such function: RTreeIntersects
>>
>> Cercando un po nella rete sembra che sia dovuto al fatto che la funzione
>> in questione sia "deprecata" se non ho inteso male.
>>
>> Vista quindi la select riportata nel manuale ......
>>
>> 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 pkid
>> FROM idx_local_councils_geometry
>> WHERE pkid MATCH RTreeIntersects(
>> MbrMinX(lc1.geometry),
>> MbrMinY(lc1.geometry),
>> MbrMaxX(lc1.geometry),
>> MbrMaxY(lc1.geometry)))
>> ORDER BY c1.county_name, lc1.lc_name;
>>
>> come posso modificarla per ottenere lo stesso risultato?
>>
>> Grazie mille anticipatamente
>>
>> 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
>>
>>
>> _______________________________________________
>> 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
>>
>
>
>
> --
> -----------------
> Andrea Peri
> . . . . . . . . .
> qwerty àèìòù
> -----------------
>
-------------- parte successiva --------------
Un allegato HTML è stato rimosso...
URL: <http://lists.gfoss.it/pipermail/gfoss/attachments/20140105/c1c171bc/attachment-0001.html>
Maggiori informazioni sulla lista
Gfoss