[postgis-users] Splitting multistrings

[Barend K=?ISO-8859-1?B?9g==?=bben]

Hi ,

Find below an example how to change MultiPolygons to Polygons, Should eb
simple to convert for (mulit)linestrings...


__ 
Barend Köbben
International Institute for Geo-information
Sciences and Earth Observation (ITC)
PO Box 6, 7500AA Enschede (The Netherlands)
ph: +31 (0)53 4874253; fax: +31 (0)53 4874335

+++++++++++++

-- 
-- STEP 1: get maximum of NumGeometries into tmp table
-- 

CREATE TABLE tmp (maxnumgeoms int4) WITH OIDS;

INSERT into tmp 
    (select gid from world where gid <=
        (select max(NumGeometries(the_geom)) from world)
    );

-- 
-- STEP 2: create World_exploded table
-- 

CREATE SEQUENCE world_exploded_id_seq
  INCREMENT 1
  MINVALUE 1
  MAXVALUE 9223372036854775807
  START 1
  CACHE 1;

CREATE TABLE world_exploded
(
  id int4 NOT NULL DEFAULT nextval('world_exploded_id_seq'::regclass),
  gid int4 NOT NULL,
  the_geom geometry,
  CONSTRAINT world_exploded_pkey PRIMARY KEY (id),
  CONSTRAINT enforce_dims_the_geom CHECK (ndims(the_geom) = 2),
  CONSTRAINT enforce_geotype_the_geom CHECK (geometrytype(the_geom) =
'POLYGON'::text OR the_geom IS NULL),
  CONSTRAINT enforce_srid_the_geom CHECK (srid(the_geom) = 4326)
) ;

CREATE INDEX world_exploded_the_geom_gist
  ON world_exploded USING gist(the_geom);
  
CREATE INDEX world_exploded_gid
  ON world_exploded USING btree(gid);

-- 
-- STEP 3: insert exploded polygons
-- 

insert into world_exploded (gid,the_geom)
    select
    gid, GeometryN(world.the_geom,maxnumgeoms)
    from tmp,world