[postgis-users] Multipolygons to Polygons

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

The steps to take, with some (pseudo)SQL:


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

CREATE TABLE tmp (maxnumgeoms int4);

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

-- 
-- STEP 2: create exploded table
-- 

CREATE SEQUENCE multipolies _exploded_id_seq
  INCREMENT 1
  MINVALUE 1
  MAXVALUE 9223372036854775807
  START 1
  CACHE 1;

CREATE TABLE polies
(
  gid int4 NOT NULL,
  <other columns you want copied>,
  the_geom geometry,
  CONSTRAINT polies_pkey PRIMARY KEY (gid),
  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) = <your srid>)
) 
WITHOUT OIDS;

CREATE INDEX polies_the_geom_gist
  ON buildingblocks USING gist(the_geom);
  
CREATE INDEX polies_gid
  ON polies USING btree(gid);

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

insert into polies (gid,<other columns you want copied>,the_geom)
    select
    gid, <other columns you want copied>,
GeometryN(multipolies.the_geom,maxnumgeoms)
    from tmp,multipolies

drop table tmp;

__ 
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