[postgis-users] converting multipolygon to polygon

Barend Köbben kobben at itc.nl
Sun Nov 15 02:20:51 PST 2009


Why not use just SQL to achieve this, see the snippet below for an example:

(where provincie is the multipolygon table to be 'exploded', gid its Unique
key, geom_rd the geometry column
)

-- 
Barend Köbben
International Institute for Geo-Information
Sciences and Earth Observation (ITC)
PO Box 6, 7500AA Enschede, The Netherlands
+31 (0)53 4874253

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

CREATE TABLE tmp (maxnumgeoms int4);

INSERT into tmp 
    (select gid from provincie where gid <= (select
max(NumGeometries(geom_rd)) from provincie));

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

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

CREATE TABLE provincie_parts
(
  part_id serial,
  gid int4 NOT NULL,
  prov integer,
  geom_rd geometry,
  CONSTRAINT provincie_parts_pkey PRIMARY KEY (part_id),
  CONSTRAINT enforce_dims_geom_rd CHECK (ndims(geom_rd) = 2),
  CONSTRAINT enforce_geotype_geom_rd CHECK (geometrytype(geom_rd) =
'POLYGON'::text OR geom_rd IS NULL),
  CONSTRAINT enforce_srid_geom_rd CHECK (srid(geom_rd) = 28992)
) 
WITHOUT OIDS;

CREATE INDEX provincie_parts_geom_rd_gist
  ON provincie_parts USING gist(geom_rd);
  
CREATE INDEX provincie_parts_gid
  ON provincie_parts USING btree(gid);

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

insert into provincie_parts (gid,prov,geom_rd)
    select
    gid, prov, GeometryN(provincie.geom_rd,maxnumgeoms)
    from tmp,provincie;

drop table tmp;

International Institute for Geo-Information Science and Earth Observation (ITC)
Chamber of Commerce: 410 27 560

E-mail disclaimer
The information in this e-mail, including any attachments, is intended for the addressee only. If you are not the intended recipient, you are hereby notified that any disclosure, copying, distribution or action in relation to the content of this information is strictly prohibited. If you have received this e-mail by mistake, please delete the message and any attachment and inform the sender by return e-mail. ITC accepts no liability for any error or omission in the message content or for damage of any kind that may arise as a result of e-mail transmission.



More information about the postgis-users mailing list