Aggiustamenti ai dati VMap0

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';

--
-- 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';