[postgis-users] Splitting linestring into line segments

[Kevin Neufeld]

Hi Simon,

This is because you are using PointN(geom, int) to iterate over your 
MultiPoint geometry.  PointN is for selecting the Nth point in a 
LineString.  Try using GeometryN instead.

postgis=# select geometryn(g.geom::geometry,
postgis(#                  generate_series(1,npoints(g.geom::geometry)))
postgis-# from (
postgis(#   select
postgis(#    'MULTIPOINT(
postgis'#    326454.7 5455793.7,
postgis'#    326621.3 5455813.7,
postgis'#    326827.7 5455841.2,
postgis'#    326771.6 5455831.6)'::geometry as geom
postgis(#    ) as g;
                 geometryn
--------------------------------------------
 0101000000CDCCCCCCDAEC1341CDCCCC6CECCF5441
 01010000003333333375EF1341CDCCCC6CF1CF5441
 0101000000CDCCCCCCAEF21341CDCCCC4CF8CF5441
 010100000066666666CEF11341666666E6F5CF5441
(4 rows)

Cheers,
Kevin

Simon Greener wrote:
> 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:
>