[postgis-users] points to polyline or polygon

[chodgson@refractions.net]

You can use functions like "pointn()" to get the the nth point in the object... 
but really, the best thing is to parse the WKT outside of the database and 
manipulate it with the language of your choice (real easy with perl or php), 
and then put it back in when you're done.

The Collect() aggregate function was the first one that I ever wrote for 
postgis, (or for postgres for that matter) and so I am somewhat partial to it 
myself. But unfortunately, the lack of any guarantee of order makes it somewhat 
unreliable and less useful. I believe this is true of all aggregate functions - 
however, if you select them based on a completely indexed query ie. SELECT 
COLLECT(the_geom) FROM table WHERE id1=# and id2=#, and there is an index like 
unique(id1, id2), then you are virtually guaranteed that the rows will be 
retrieved in the order of the index (ie. sorted) and that should virtually 
gaurantee that the collection is in either forward or backward, but not mixed 
up...

It's still a nice little hack, Tyler!

Chris

Quoting Tyler Mitchell <TMitchell@lignum.com>:

> 
> 
> 
> 
> 
> Chris, I'm having fun here and didn't need those details ;)
> Darn!  I was really having fun with this one.  Here's what I did, but
> unfortunately, I used COLLECT() :(
> 
> # select 'SRID=-1;LINESTRING' ||
> substr(astext(collect(the_geom)),11,length(astext(collect(the_geom)))) as
> the_geom, line_id from test_pt group by line_id;
> 
> I take the WKT collection and replace the geometry type with linestring
> instead.  I know it's hokey, but it worked.  What do you mean point order
> is important for constructing your line?  :)
> 
> If some one can tell me a better way using SQL to get an array of the
> points, that'd be helpful.
> 
> Does this help at all Randy.
> 
> On a similar note can anyone comment or provide direction on writing custom
> functions to help do this kind of thing?  I'd be particularly interested in
> how the proces compares to Oracle's pl/sql.  I guess taking a peek at
> postgis.sql would be a good place for me to start eh?  Although I see a lot
> of your functions are bundled into postgis.dll right?
> 
> 
> Tyler
> 
> 
> 
>                       chodgson@refractions.net
>                       Sent by:                                  To:      
> PostGIS Users Discussion
>                       postgis-users-bounces@postgis.refr        
> <postgis-users@postgis.refractions.net>
>                       actions.net                               cc:
>                                                                 Fax to:
>                                                                 Subject:  Re:
> [postgis-users] points to polyline or polygon
>                       07/15/2003 01:59 PM
>                       Please respond to PostGIS Users
>                       Discussion
> 
> 
> 
> 
> 
> You definitely do NOT want to do this - the COLLECT() aggregate function
> doesn't gauarantee any sort of order in the points it has collected.
> Furthermore, a Multipoint object is not necessarily ordered (it will always
> 
> list the points in the same order, but not by necessity, only because it is
> 
> simplest).
> 
> The best way to solve this problem is to select your list of points from
> the
> table (they will also need to have an "order" column in order to make sure
> they
> are made into a line in the correct order) and use PHP or your language of
> choice to build a new WKT string.
> 
> HTH.
> 
> Chris
> 
> > This is an interesting question to me.  If I understand correctly, you
> want
> > to "cast" a set of points into a linestring.  Getting your list of points
> > should be as easy as:
> >
> > SELECT COLLECT(the_geom) FROM mytable GROUP BY somefield.
> >
> > But this returns the geometry as MULTIPOINT, which isn't really want you
> > want, but if you see the text representation, it does look like a
> > linestring:
> >
> > SRID=-1;MULTIPOINT(596681.75 5830977.5,532989.625 5869892,547653.0625
> > 5808612,608081.1875 5799629)
> >
> > So can anyone else give a pointer on taking the above output and casting
> it
> > into a linestring instead?
> >
> > Tyler
> 
> 
> 
> 
> _______________________________________________
> postgis-users mailing list
> postgis-users@postgis.refractions.net
> http://postgis.refractions.net/mailman/listinfo/postgis-users
> 
> 
> 
> _______________________________________________
> postgis-users mailing list
> postgis-users@postgis.refractions.net
> http://postgis.refractions.net/mailman/listinfo/postgis-users
>