tecnica:gps_cartografia_gis:vmap0_clean
Table of Contents
Aggiustamenti ai dati VMap0
Unire un set di multipoligoni in uno solo
Abbiamo due record con una geometria contigua:
SELECT the_geom FROM vmap0 WHERE id = 5108 OR id = 5502;
Si vuole creare un nuovo record che contenga una geometria unione. Prima si devono fare alcuni controlli:
- Controllare che i MULTIPOLYGON siano in realta' dei POLYGON
- Controllare che i POLYGON non abbiano Interior Rings (buchi)
- Controllare che l'unione sia un nuovo MULTIPOLYGON
SELECT NumGeometries(the_geom) FROM vmap0 WHERE id = 5108 OR id = 5502; -- numgeometries -- --------------- -- 1 -- 1 SELECT nrings(GeometryN(the_geom, 1)) FROM vmap0 WHERE id = 5108 OR id = 5502; -- nrings -- -------- -- 1 -- 1 SELECT GeometryType(Multi(GeomUnion(the_geom))) FROM vmap0 WHERE id = 5108 OR id = 5502; -- Restituisce: MULTIPOLYGON
Se tutti i controlli danno esito positivo, si unisce le due geometrie in un nuovo record:
UPDATE vmap0 SET the_geom = ( SELECT Multi(GeomUnion(the_geom)) FROM vmap0 WHERE id = 5108 OR id = 5502 ) WHERE nam = 'NUOVO_RECORD';
Importazione dati VMap0
-- -- Import only Italian boundaries. -- INSERT INTO vmap0_polbnda (id_vmap0, f_code, nam, na2, na3, tile_id, fac_id, bnd) SELECT id, f_code, nam, na2, na3, tile_id, fac_id, the_geom FROM vmap0_tmp WHERE na2 = 'I'; -- -- Remove the Isle of Man. -- DELETE FROM vmap0_polbnda WHERE id_vmap0 = 3166; DELETE FROM vmap0_polbnda WHERE id_vmap0 = 3169; -- -- Join tiled regions. -- INSERT INTO vmap0_polbnda (f_code, nam, na2, bnd) SELECT 'FA00', 'SICILIA', 'I', Multi(GeomUnion(the_geom)) FROM vmap0_tmp WHERE id = 5108 OR id = 5502; INSERT INTO vmap0_polbnda (f_code, nam, na2, bnd) SELECT 'FA00', 'MOLISE', 'I', Multi(GeomUnion(the_geom)) FROM vmap0_tmp WHERE id = 5064 OR id = 5330; INSERT INTO vmap0_polbnda (f_code, nam, na2, bnd) SELECT 'FA00', 'PUGLIA', 'I', Multi(GeomUnion(the_geom)) FROM vmap0_tmp WHERE id = 5066 OR id = 5337; INSERT INTO vmap0_polbnda (f_code, nam, na2, bnd) SELECT 'FA00', 'CAMPANIA', 'I', Multi(GeomUnion(the_geom)) FROM vmap0_tmp WHERE id = 5213 OR id = 5348; INSERT INTO vmap0_polbnda (f_code, nam, na2, bnd) SELECT 'FA00', 'PIEMONTE', 'I', Multi(GeomUnion(the_geom)) FROM vmap0_tmp WHERE id = 3928 OR id = 4988; INSERT INTO vmap0_polbnda (f_code, nam, na2, bnd) SELECT 'FA00', 'LOMBARDIA', 'I', Multi(GeomUnion(the_geom)) FROM vmap0_tmp WHERE id = 3927 OR id = 4989 OR id = 4991; INSERT INTO vmap0_polbnda (f_code, nam, na2, bnd) SELECT 'FA00', 'EMILIA-ROMAGNA', 'I', Multi(GeomUnion(the_geom)) FROM vmap0_tmp WHERE id = 3970 OR id = 4990; INSERT INTO vmap0_polbnda (f_code, nam, na2, bnd) SELECT 'FA00', 'VENETO', 'I', Multi(GeomUnion(the_geom)) FROM vmap0_tmp WHERE id = 3919 OR id = 4992; INSERT INTO vmap0_polbnda (f_code, nam, na2, bnd) SELECT 'FA00', 'ISOLA VULCANO#SICILIA', 'I', Multi(GeomUnion(the_geom)) FROM vmap0_tmp WHERE id = 5102 OR id = 5482; -- -- Delete joined components. -- DELETE FROM vmap0_polbnda WHERE id_vmap0 = 5108 OR id_vmap0 = 5502; DELETE FROM vmap0_polbnda WHERE id_vmap0 = 5064 OR id_vmap0 = 5330; DELETE FROM vmap0_polbnda WHERE id_vmap0 = 5066 OR id_vmap0 = 5337; DELETE FROM vmap0_polbnda WHERE id_vmap0 = 5213 OR id_vmap0 = 5348; DELETE FROM vmap0_polbnda WHERE id_vmap0 = 3928 OR id_vmap0 = 4988; DELETE FROM vmap0_polbnda WHERE id_vmap0 = 3927 OR id_vmap0 = 4989 OR id_vmap0 = 4991; DELETE FROM vmap0_polbnda WHERE id_vmap0 = 3970 OR id_vmap0 = 4990; DELETE FROM vmap0_polbnda WHERE id_vmap0 = 3919 OR id_vmap0 = 4992; DELETE FROM vmap0_polbnda WHERE id_vmap0 = 5102 OR id_vmap0 = 5482; -- -- Add the San Marino country. -- INSERT INTO vmap0_polbnda (id_vmap0, f_code, nam, na2, na3, tile_id, fac_id, bnd) SELECT id, f_code, nam, na2, na3, tile_id, fac_id, the_geom FROM vmap0_tmp WHERE nam = 'SAN MARINO';
tecnica/gps_cartografia_gis/vmap0_clean.txt · Last modified: 2006/12/06 12:06 by 127.0.0.1