Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
split line with points usando spatilite >4.4 KNN, CloneTable e ElementaryGeometries - by M. Trevisani
--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');
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment