[Gfoss] SQL aggiornamento colonna tramite query spaziale

[a.furieri a lqt.it]

On Wed, 19 Nov 2014 18:08:06 +0100, Luca Delucchi wrote:
> Ciao a tutti,
>
> sto cercando di aggiornare la colonna di una tabella utilizzando i
> valori di un'altra.
> Nello specifico ho un tabella con le autostrade europee e vorrei
> aggiungere la nazione in cui si trovano (seconda tabella).
> Ho provato questa query, che sta girando da stamattina....
>
> update highway_roads set nation = subquery.name from (select a.name
> from europe_nations as a join highway_roads h ON st_contains(a.way,
> h.way)) as subquery where highway='motorway';
>
> pensate sia giusta e perciò la lascio continuare o è sbagliata e
> perciò la fermo?
>

ciao Luca,

la tua query SQL non mi convince; nella inner sub-query stai valutando
le intersezioni tra tutte le motorway e tutte le nazioni senza nessun
altro vincolo.
finisce che ti troverai sempre con la solita nazione (l'ultima che 
trova)
assegnata indiscriminatamente per tutte quante le motorway.

io ho messo su un piccolo test SpatiaLite con un grafo stradale OSM
limitato piu' o meno alla Toscana (e dintorni) andando poi ad assegnare
la regione di competenza. ed ho usato questa query SQL:

UPDATE road_arcs SET regione =
(
     SELECT r.nome
     FROM regioni AS r
     WHERE ST_Intersects(r.geometry, road_arcs.geometry) = 1
)
WHERE type = 'motorway';

le tratte motorway sono circa 2.000 e ci impiega quasi 2 minuti.
ovviamente migliora di un pelo (*) usando lo Spatial Index e scende
a circa 1 minuto e 30 secondi:

UPDATE road_arcs SET regione =
(
     SELECT r.nome
     FROM regioni AS r
     WHERE ST_Intersects(r.geometry, road_arcs.geometry) = 1
          AND r.ROWID IN
          (
              SELECT ROWID
              FROM SpatialIndex
              WHERE f_table_name = 'regioni'
                   AND search_frame = road_arcs.geometry
          )
)
WHERE type = 'motorway';

(*) il miglioramento in questo caso e' limitato da fatto che le regioni
sono solo 20 e le tratte del grafo sono molto brevi.
in queste condizioni gia' il semplice filtraggio basato sui BBOX basta
ad assicurare una velocita' decente; quindi lo Spatial Index ha margini
di ottimizzazione abbastanza ristretti.

ciao Sandro