CTRN 5000: Sentieri CAI

From OpenStreetMap Wiki
Jump to: navigation, search

Classi CTRN e altri dati coinvolti

5LSI - SENTIERO CAI

5TTS - SCRITTURE SENTIERI

Chi si è occupato del lavoro

Stefano Salvador: elaborazione dati e upload

iiizio: correzione errori dopo l'upload

Procedura

Il punto di partenza sono gli shapefile ottenuti dalla trasformazione dei file FCN originali distribuiti dalla regione.

I layer utilizzati sono stati:

  • 5LSI SENTIERO CAI
  • 5TTS SCRITTURE SENTIERI

questi shape sono stati importati in un database PostGIS tramite l'utility shp2pgsql in modo da poter elaborare i dati con alcune query spaziali.

In particolare la riga di comando per creare la tabella in PostGIS è stata la seguente:

echo "CREATE SCHEMA cai;" > cai.sql
echo "SET CLIENT_ENCODING TO 'ISO 8859-1';" >> cai.sql
shp2pgsql -D -c -i -I -k -s 5LSI.shp cai.5lsi >> cai.sql
shp2pgsql -D -c -i -I -k -s 5TTS.shp cai.5tts >> cai.sql

Successivamente i dati sono stati elaborati con il seguente script SQL (il codice è un po' sporco e mancano alcuni commenti ...):

-- crea una tabella pronta per essere salvata in OSM
DROP TABLE cai.osm;
CREATE TABLE cai.osm
(
   id serial NOT NULL, 
   "name" character varying, 
   ref character varying, 
    PRIMARY KEY (id)
);

SELECT AddGeometryColumn('cai','osm','the_geom','4326','MULTILINESTRING',3);

----------
-- unisci le label in modo euristico -- in realtà non serve
drop view "cai_p2" cascade;
create view "cai_p2" as
select gid, ST_Translate(the_geom, 0.0001*cos(radians((angle/1000)*6)), 0.0001*sin(radians((angle/1000)*6)) ) as the_geom from cai."5TTS";

create view "cai_l" as
select a.gid, ST_MakeLine(a.the_geom, b.the_geom) as the_geom, a.content
from cai."5TTS" a
join cai_p2 b on a.gid = b.gid;

create view "cai_lb" as
select cai_l.gid, ST_Buffer(cai_l.the_geom, 0.00001) as the_geom, cai_l.content
from cai_l
----------
-- crea un buffer adeguato attorno ai punti
drop view "cai_pb" cascade;
create view "cai_pb" as
select gid, ST_Buffer(the_geom, 0.0003) as the_geom, content
from cai."5TTS";

----------
-- conta le label distinte e verifica sia andato tutto bene
SELECT content, count(*) FROM cai."5TTS" GROUP BY content;

----------
-- trova le intersezioni
DROP TABLE cai.intersections;
CREATE TABLE cai.intersections
(
  gid integer NOT NULL,
  "name" character varying
);

TRUNCATE cai.intersections;

INSERT INTO cai.intersections (gid, name)
(SELECT cai."5LSI".gid, cai_pb.content FROM cai."5LSI"
JOIN cai_pb ON ST_Intersects(cai_pb.the_geom, cai."5LSI".the_geom)
GROUP BY cai."5LSI".gid, cai_pb.content);

SELECT count(*) FROM cai.intersections GROUP BY gid; -- 1199 rows

-- normalizza ref
SELECT * FROM cai.intersections ORDER BY trimmed_name, name;
SELECT * FROM cai.intersections WHERE name ~ 'CAI[0-9A]+$';
SELECT * FROM cai.intersections WHERE name LIKE 'C.A.I.   9%';
SELECT name, trim(substring(name from '[0-9A]+$')) FROM cai.intersections WHERE name ~ 'CAI[0-9A]+$';

UPDATE cai.intersections SET trimmed_name = name WHERE name ~ '^[0-9A]+$';
UPDATE cai.intersections SET trimmed_name = trim(substring(name from ' [0-9A]+')) WHERE name ~ 'CAI  [0-9A]+';
UPDATE cai.intersections SET trimmed_name = trim(substring(name from ' [0-9A]+')) WHERE name ~ 'CAI [0-9A]+';
UPDATE cai.intersections SET trimmed_name = trim(substring(name from '[0-9A]+$')) WHERE name ~ 'CAI[0-9A]+$';
UPDATE cai.intersections SET trimmed_name = '987' WHERE name = 'C.A.I.   987';
UPDATE cai.intersections SET trimmed_name = NULL WHERE name = '- 988';
UPDATE cai.intersections SET comment = 'TRAVERSATA CARNICA' WHERE name = 'TRAVERSATA' OR name = 'CARNICA' OR gid = 351;
UPDATE cai.intersections SET comment = 'STRADA DEGLI ALPINI' WHERE name = 'ALP' OR name = 'INI' OR name = 'STRADA' OR name = 'DEGLI' OR name = 'ALPINI';
UPDATE cai.intersections SET comment = 'R. 967-983' WHERE name = '-' OR name = 'R. 967' OR name = 'R. 967 - 983';
UPDATE cai.intersections SET comment = 'CAI 986-988' where gid = 2376;
UPDATE cai.intersections SET comment = 'CENGLE DAL BEC' WHERE name = 'CENGLE DAL BEC';
UPDATE cai.intersections SET comment = 'S.N. PLANGIARA - ALBINS' WHERE gid = 1378 OR gid = 1379;
UPDATE cai.intersections SET comment = 'S. BIVACCO FRISACCO' WHERE gid = 1189 OR gid = 1313 OR gid = 1314;
UPDATE cai.intersections SET comment = 'S.N. M. CIAVAC' WHERE gid = 1443 OR gid = 1376 OR gid = 1371 OR gid = 1441;
UPDATE cai.intersections SET comment = 'S.N. COL DEI PIAIS' WHERE gid IN (1044, 1049, 1041, 1044, 1076, 1077, 1072, 1078, 1043);
UPDATE cai.intersections SET comment = 'S. COSTA GRANDE' WHERE gid IN (2226, 2429, 2425, 2426);
UPDATE cai.intersections SET comment = 'S. COSTA LONGA' WHERE gid IN (2190, 2199, 2255, 2257, 2234, 2233, 2338, 2317, 2319, 2339, 2341);
UPDATE cai.intersections SET comment = 'VIA FERRATA DEI CINQUANTA' WHERE gid IN (148);
UPDATE cai.intersections SET comment = 'S. VAL D''AGA' WHERE gid IN (2222, 2401, 2404, 2405, 2406);
UPDATE cai.intersections SET comment = 'TRUI DAL SCIARBON' WHERE gid IN (889, 892, 925, 936);
UPDATE cai.intersections SET comment = 'S. VIATH LONG' WHERE gid IN (2239, 2240, 2251, 2252, 2253);
UPDATE cai.intersections SET comment = 'S. VAL STORTA' WHERE gid IN (2431, 2259, 2496, 2497);
UPDATE cai.intersections SET comment = 'S. VAL STORTA' WHERE gid IN (2431, 2259, 2496, 2497);
DELETE FROM cai.intersections WHERE name='TOL. 37';
UPDATE cai.intersections SET comment = name WHERE trimmed_name IS NULL AND comment IS NULL AND name != 'CAI';

SELECT * FROM cai.intersections WHERE trimmed_name IS NULL AND comment IS NULL AND name != 'CAI' ORDER BY name; -- 170 rows

SELECT count(*) FROM cai."5LSI"; -- 2537 rows 
SELECT count(*) FROM cai."5LSI" WHERE gid NOT IN (SELECT gid FROM cai.intersections); -- 1338 rows 

----------
-- associa le label alle way, facendo il merge di quelli con la stessa label
UPDATE cai."5LSI" c5 SET ref = 
(SELECT trimmed_name FROM cai.intersections ci WHERE ci.gid = c5.gid LIMIT 1)

UPDATE cai."5LSI" c5 SET name = 
(SELECT comment FROM cai.intersections ci WHERE ci.gid = c5.gid LIMIT 1)

TRUNCATE cai.osm;
INSERT INTO cai.osm (the_geom, ref, name)
(
 SELECT
 ST_Force_3d(ST_Multi(ST_Union(the_geom))), ref, MAX(name)
 FROM cai."5LSI" GROUP BY ref HAVING ref IS NOT NULL
);

INSERT INTO cai.osm (the_geom, ref, name)
(
 SELECT
 ST_Force_3d(ST_Multi(ST_Union(the_geom))), MAX(ref), name
 FROM cai."5LSI" GROUP BY name HAVING name IS NOT NULL
);

INSERT INTO cai.osm (the_geom, ref, name)
(
 SELECT
 ST_Force_3d(the_geom), ref, name
 FROM cai."5LSI" WHERE name IS NULL AND ref IS NULL
);

SELECT count(*) from cai.osm;

DROP VIEW cai_export;
CREATE VIEW cai_export AS
  SELECT gid, COALESCE(ref, name) AS name, the_geom FROM cai."5LSI";


SELECT trimmed_name, count(*) FROM cai.intersections GROUP BY trimmed_name, comment ORDER BY trimmed_name, comment; -- 433 rows


SELECT distinct(ref) FROM cai.osm;
SELECT distinct(name) FROM cai.osm;

DROP VIEW cai_s;
CREATE VIEW cai_s AS
  SELECT id, ref, name, ST_Simplify(the_geom, 0.0001) AS the_geom from cai.osm;

-- fatto !!!

A questo punto i dati sono stati estratti dal database con uno script Ruby che si appoggia a ActiveRecord e al plugin spatial adapter.

TBD

Il risultato è una serie di file osmChange pronti per essere caricati in OSM. Anche per questo compito è stato utilizzato uno script Ruby:

TBD