[postgis-users] Splitting linestring into line segments

[Simon Greener]

PostGIS users,

I am having difficulties iterativing over multipoint geometries using a method similar to this one posted by Kevin Neufeld:

> If your linestrings aren't too large (ie < 1000pts), you can actually do
> this with a simple query.  The reason I say this is because
> generate_series doesn't scale well.
>postgis=# CREATE TEMP TABLE mylines AS
> postgis-#   SELECT 'LINESTRING(0 0, 0 1, 1 1, 1 0, 0 0)'::geometry AS geom;
> SELECT
> postgis=# SELECT AsText( MakeLine(sp,ep) )
> postgis-# FROM (
> postgis(#   SELECT pointn(geom, generate_series(1, npoints(geom)-1)) as sp,
> postgis(#          pointn(geom, generate_series(2, npoints(geom)  )) as ep
> postgis(#   FROM mylines ) AS foo;
>        astext
> ---------------------
>  LINESTRING(0 0,0 1)
>  LINESTRING(0 1,1 1)
>  LINESTRING(1 1,1 0)
>  LINESTRING(1 0,0 0)
> (4 rows)

When I enter the following into PgAdmin:

select pointn(g.geom::geometry,generate_series(1,npoints(g.geom::geometry)))
    from (select
'MULTIPOINT(
326454.7 5455793.7,
326621.3 5455813.7,
326827.7 5455841.2,
326771.6 5455831.6)'::geometry as geom) as g;

I get all records reported as being NULL. Checking with:

select case when p.point is null then 'NULL' else 'VALUE' end
   from (select pointn(g.geom::geometry,generate_series(1,npoints(g.geom::geometry))) as point
           from (select
'MULTIPOINT(
326454.7 5455793.7,
326621.3 5455813.7,
326827.7 5455841.2,
326771.6 5455831.6)'::geometry as geom) as g ) as p;

All values are reported as NULL.

Any advice? I am running PostgreSQL 8.2.5 and PostGIS 1.3.1 GEOS 3.0.0rc4-CAPI-1.3.3 PROJ 4.5.0

regards
Simon

On Fri, 07 Mar 2008 07:02:01 +1100, <postgis-users-request at postgis.refractions.net> wrote:
-- 
SpatialDB Advice and Solutions Architecture,
Oracle Database 10g Administrator Certified Associate; Oracle Database 10g SQL Certified Professional
Oracle Spatial, SQL Server, PostGIS, MySQL, ArcSDE, Manifold GIS, Radius Topology and Studio Specialist.
39 Cliff View Drive, Allens Rivulet, 7150, Tasmania, Australia.
Website: www.spatialdbadvisor.com
   Email: simon at spatialdbadvisor.com
   Voice: +613 9016 3910
Mobile: +61 418 396391
Skype: sggreener
Longitude: 147.20515 (147° 12' 18" E)
Latitude: -43.01530 (43° 00' 55" S)
NAC:W80CK 7SWP3