SPLIT LINES WITH POINTS, SPATIALITE WAY – USING KNN

Circa un mese fa scrissi, in questo articolo, come dividere una linea con dei punti (SPLIT LINES WITH POINTS, SPATIALITE WAY) utilizzando spatialite 4.3, oggi scrivo come ottimizzare (e velocizzare enormemente) lo script utilizzando le virtual KNN introdotte da A. Furieri a partire da spatialite 4.4, qui  trovate un mio articolo su come usare le KNN nei trigger di spatialite.

Ottimizzazione, realizzata da M. Trevisani (che ringrazio), utilizzando (con maestria) le KNN, la CloneTable e  la  ElementaryGeometries, rivoluzionando il mio script e scrivendone un altro, il risultato ottenuto è straordinario: utilizzando lo stesso database il tempo necessario per svolgere l’intero processo è di 19 secondi, contro i 20 minuti nella prima edizione:

QUESTA FORMULAZIONE VERIFICA SE ESISTE E LO PRODUCE SOLO SE NON ESISTESSE
(ENTRAMBE LE FUNZIONI RESTITUISCONO 1, MA SE LA PRIMA RITORNA 1, LA SECONDA NON VIENE ESEGUITA)
SELECT 'Creazione indice spaziale su ', 'nodes_all','geom',
coalesce(checkspatialindex('nodes_all','geom'),CreateSpatialIndex('nodes_all','geom'));
SELECT 'Creazione indice spaziale su ', 'strade','geom',
coalesce(checkspatialindex('strade','geom'),CreateSpatialIndex('strade','geom'));
INDIVIDUA, PER CIASCUN NODO, LA LINESTRING DI STRADE PIU' VICINA
SELECT dropgeotable('nearest_strade_to_nodes_all');
CREATE TABLE nearest_strade_to_nodes_all as
SELECT c.pk_uid, c.id_all, c.id_nodes, d.distance as dist, d.fid as strade_pk
FROM nodes_all as c
JOIN
(SELECT a.fid as fid, a.distance as distance, zz.pk_uid as pk_uid
FROM knn as a JOIN nodes_all as zz
WHERE f_table_name = 'strade' AND f_geometry_column = 'geom' AND ref_geometry = zz.geom AND max_items = 1) as d
ON (d.pk_uid =c.pk_uid )
ORDER BY c.pk_uid;
CALCOLO DELLA PROIEZIONE DEL NODO SULLA STRADA
SELECT addgeometrycolumn('nearest_strade_to_nodes_all','geom',
(SELECT cast(srid as integer)
FROM geometry_columns
WHERE lower(f_table_name) = lower('strade')
AND lower(f_geometry_column) = lower('geom')),'point', 'xy');
PER CIASCUN NODO DI nodes_all CALCOLA IL ClosestPoint SULLA LINESTRING DI strade PIU' VICINA
UPDATE nearest_strade_to_nodes_all SET geom =
(SELECT ST_ClosestPoint(a.geom, b.geom)
FROM strade as a, nodes_all as b
WHERE a.pk=nearest_strade_to_nodes_all.strade_pk
AND b.pk_uid=nearest_strade_to_nodes_all.pk_uid);
SELECT 'Creazione indice spaziale su ', 'nearest_strade_to_nodes_all','geom',
coalesce(checkspatialindex('nearest_strade_to_nodes_all','geom'),
CreateSpatialIndex('nearest_strade_to_nodes_all','geom'));
A QUESTO PUNTO LA TABELLA nearest_strade_to_nodes_all CONTIENE NELLA COLONNA geom LA PROIEZIONE SULLA STRADA PIU' VICINA,
IN CORRISPONDENZA DI CASCUN PUNTO DI nodes_all. TALI PUNTI, CHE DUNQUE SONO SICURAMENTE SULLE LINESTRINGS DI STRADE,
POSSONO ESSRE USATI PER TAGLIARE LE LINESTRINGS DI strade CON LA ST_SPLIT
GENERA UNA TABELLA DI APPOGGIO DOVE CONSERVARE LE MULTILINESTRING PRODOTTE DALLA ST_SPLIT
OCCORRE QUINDI SNAPPARE strade ALLE PROIEZIONI DI nodes_all PRODUCENDO UNA NUOVA VERSIONE strade_snapped_to_projections_of_nodes_all
LA CLONETABLE CREA UNA COPIA DI UNA TABELLA
(IN QUESTA FORMA FORZANDO A MULTILINESTRING LA GEOMETRIA ORIGINALE DI strade CHE ERA LINESTRING)
SELECT DropGeoTable('strade_snapped_to_projections_of_nodes_all');
SELECT CloneTable('main', 'strade', 'strade_snapped_to_projections_of_nodes_all', 1,'::cast2multi::geom');
LA ST_SNAP PROVOCA LA AGGIUNTA DI UN VERTICE A strade_snapped_to_projections_of_nodes_all IN CORRISPONDENZA
DEI PUNTI DI nearest_strade_to_nodes_all CHE RICADONO ENTRO LA TOLLERANZA (IO HO IMPOSTATO UN CENTIMETRO)
UPDATE strade_snapped_to_projections_of_nodes_all SET geom=
CastToMulti(
RemoveRepeatedPoints(
ST_Snap(
strade_snapped_to_projections_of_nodes_all.geom,
(SELECT casttomultipoint(st_collect(b.geom))
FROM nearest_strade_to_nodes_all as b
WHERE b.strade_pk = strade_snapped_to_projections_of_nodes_all.pk
GROUP BY b.strade_pk) , 0.01
), 0.01
)
)
WHERE EXISTS(
SELECT 1 FROM nearest_strade_to_nodes_all as b
WHERE b.strade_pk = strade_snapped_to_projections_of_nodes_all.pk limit 1
);
LA ST_SPLIT PROVOCA LA SPEZZATURA DELLE LINESTRING DI strade_snapped_to_projections_of_nodes_all
IN CORRISPONDENZA DEI PUNTI DI nearest_strade_to_nodes_all CHE RICADONO IN CORRISPONDENZA DI SUOI VERTICI
(AGGIUNTI IN PRECEDENZA CON LA ST_SNAP)
UPDATE strade_snapped_to_projections_of_nodes_all SET geom=
CastToMulti(
ST_Split(
strade_snapped_to_projections_of_nodes_all.geom,
(SELECT CastToMultiPoint(st_collect(b.geom))
FROM nearest_strade_to_nodes_all as b
WHERE b.strade_pk = strade_snapped_to_projections_of_nodes_all.pk
GROUP BY b.strade_pk)
)
)
WHERE EXISTS(
SELECT 1 FROM nearest_strade_to_nodes_all as b
WHERE b.strade_pk = strade_snapped_to_projections_of_nodes_all.pk limit 1
);
A QUESTO PUNTO POSSIAMO GENERARE LE COMPONENTI ELEMENTARI TRAMITE LA ElementaryGeometries CHE PRODUCE LA lines_split
ESPLODENDO LE LINESTRINGS ORIGINARIE DI strade SNAPPATE E SPLITTATE ALLE PROIEZIONI DEI PUNTI DI nodes_all SULLE strade STESSE
SELECT DropGeoTable('lines_split');
SELECT ElementaryGeometries( 'strade_snapped_to_projections_of_nodes_all' ,
'geom' , 'lines_split' ,'out_pk' , 'out_multi_id', 1 ) as num, 'lines splitted' as label;
SELECT 'Creazione indice spaziale su ', 'strade','geom',
coalesce(checkspatialindex('lines_split','geom'),CreateSpatialIndex('lines_split','geom'));
SELECT UpdateLayerStatistics('lines_split');
SELECT DropGeoTable('strade_snapped_to_projections_of_nodes_all');
SELECT DropGeoTable('nearest_strade_to_nodes_all');

 

 


riferimenti:

[1] https://www.gaia-gis.it/fossil/libspatialite
[2] http://www.gaia-gis.it/gaia-sins/about-fossil.html
[3] http://www.gaia-gis.it/gaia-sins/mingw32_how_to.html
[4] http://www.gaia-gis.it/gaia-sins/mingw64_how_to.html

2 pensieri su “SPLIT LINES WITH POINTS, SPATIALITE WAY – USING KNN

Rispondi

Inserisci i tuoi dati qui sotto o clicca su un'icona per effettuare l'accesso:

Logo di WordPress.com

Stai commentando usando il tuo account WordPress.com. Chiudi sessione /  Modifica )

Google photo

Stai commentando usando il tuo account Google. Chiudi sessione /  Modifica )

Foto Twitter

Stai commentando usando il tuo account Twitter. Chiudi sessione /  Modifica )

Foto di Facebook

Stai commentando usando il tuo account Facebook. Chiudi sessione /  Modifica )

Connessione a %s...

Questo sito utilizza Akismet per ridurre lo spam. Scopri come vengono elaborati i dati derivati dai commenti.