[postgis-users] MultiLineString to Point Geometry

[Barend Köbben]

Try something like this:
 
-- 
-- STEP 1: get maximum of NumGeometries into tmp table
-- 
CREATE TABLE tmp (maxnumgeoms int4);
INSERT into tmp 
 (select [someIDvalue] from [yourLinestringTable] where [someIDvalue] <= 
  (select max(NumGeometries(the_geom)) from [yourLinestringTable] )
 );

-- 
-- STEP 2: create [yourPointsTable]
-- 
CREATE SEQUENCE  [yourPointsTable]_id_seq
  INCREMENT 1
  MINVALUE 1
  MAXVALUE 9223372036854775807
  START 1
  CACHE 1;
 
CREATE TABLE  [yourPointsTable]
(
  id int4 NOT NULL DEFAULT nextval('[yourPointsTable]_id_seq'::regclass),
  the_geom geometry,
  CONSTRAINT [yourPointsTable]_pkey PRIMARY KEY (id),
  CONSTRAINT enforce_dims_the_geom CHECK (ndims(the_geom) = 2),
  CONSTRAINT enforce_geotype_the_geom CHECK (geometrytype(the_geom) = 'POINT'::text OR the_geom IS NULL),
  CONSTRAINT enforce_srid_the_geom CHECK (srid(the_geom) = <yourSRID>)
) 
WITH OIDS;
CREATE INDEX [yourPointsTable]_the_geom_gist
  ON [yourPointsTable] USING gist(the_geom);
  
-- 
-- STEP 3: insert exploded polylines
-- 
insert into [yourPointsTable] (the_geom)
 select [someIDvalue] , GeometryN([yourMultiStringTable].the_geom,maxnumgeoms)
 from tmp , [yourMultiStringTable]
 
____________________________ 
Barend Köbben 
International Institute for Geo-information Sciences and  Earth Observation (ITC) 
PO Box 6, 7500AA Enschede (The Netherlands) 
ph: +31-(0)534874253; fax: +31-(0)534874335 
_____________________________ 
 
-------------- next part --------------
A non-text attachment was scrubbed...
Name: winmail.dat
Type: application/ms-tnef
Size: 4838 bytes
Desc: not available
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20060130/48ee5248/attachment.bin>