[postgis-users] Re: [UMN_MAPSERVER-USERS] How to show lines between PostGIS data of type POINT

[Stephen Woodbridge]

Hi Nicol,

I'm going to throw this back to the list. I didn't actually run the 
query, but it is what I would have tried. You might need to pass the 
results of the sub-query to collect like:

select setsrid(makeline(collect((select ...))),4326);

Although, reading the documentation again implies that the query below 
should work just fine, and in fact refers to using a sub-query.

Did I miss something? Do you guys have any comments on this?

-Steve W.

Nicol Hermann wrote:
> Stephen,
> 
> please excuse me that i contact you direct but i have a question to a
> post of you which is about one month old.
> You suggest the querys below to generate a line geometry from a table
> with point geometries. I tried the querys with a table of mine:
> 
> select setsrid(makeline( (select setsrid( makepoint(lon,lat), 4326) from
> sights order by id)), 4326);
> 
> and get the following error message.
> ERROR:  more than one row returned by a subquery used as an expression
> 
> Do you have only on point in your table or how do you prevent this error
> message?
> 
> Thanks for clarification.
> Nicol
> 
> 
> Am Dienstag, den 08.11.2005, 23:23 -0500 schrieb Stephen Woodbridge:
> 
>>Hi Russ,
>>
>>Have you looked at 
>>http://ms.gis.umn.edu/community/conferences/MUM3/workshop/postgis/
>>
>>this is a great intro to PostGIS and mapserver.
>>
>>If your points are in a table as point geometry (as apposed to a lat and 
>>lon column) then you can doe something like:
>>
>>select setsrid(makeline((select the_geom from points order by id)), 4326);
>>
>>if you data is in lat, lon columns then:
>>
>>select setsrid(
>>          makeline(
>>            (select setsrid(
>>                      makepoint(log,lat), 4326
>>                    )
>>               from points order by id
>>             )
>>          ), 4326
>>        );
>>
>>you may need to change the SRID from 4326 to whatever your data is in.
>>
>>-Steve
>>
>>Russ Bradford wrote:
>>
>>>Hi list,
>>>I too have a database filled with point data for which I would like
>>>to "join-the-dots". Would anyone have a working example of how to do this
>>>using MapServer and PostgreSQL that they would like to share? The original
>>>thread here mentioned looking into Collect(), MakeLine() and AddPoint(). I
>>>have searched through the documentation and come up blank with all except
>>>Collect(). And being very new to MapServer (and PostgreSQL) I would like a
>>>little more direction in how to apply these solutions. If it makes any
>>>difference, I am using a Windows machine with Apache and the latest
>>>version of Mapserver.
>>>Cheers, Russ.
>>>
>>>
>>>On Tue, 26 Jul 2005 08:50:52 -0500, David Bitner <osgis.lists at GMAIL.COM>
>>>wrote:
>>>
>>>
>>>
>>>>To show lines but query points, you would need to have your data in
>>>>Mapserver as both lines and points.  To convert your points to lines
>>>>in Postgis (can be done on the fly) look into Collect(), MakeLine(),
>>>>and AddPoint().  You'll need to be sure to order your points by
>>>>timestamp before aggregating them to the line.
>>>>
>>>>On 7/26/05, Sebastian Albrecht <albrecht at fielax.de> wrote:
>>>>
>>>>
>>>>>Hello,
>>>>>
>>>>>is it possible to show lines between POINT data results from a
>>>>>PostGIS-DB? Is it possible to somehow convert one point and the next
>>>>>one following to a line on-the-fly each time the Mapserver queries the
>>>>>database?
>>>>>
>>>>>My problem is I have a lot of single latlon points which have
>>>>>different timestamps and further values. The user should be able to
>>>>>query these values of a single point but lines should be shown in the
>>>>>map. Using a LINE type would not be very consistent because the user
>>>>>would not know which end of the line is the point the values belong
>>>>>to.
>>>>>
>>>>>One knows something? ;)
>>>>>
>>>>>Best regards,
>>>>>Sebastian
>>>>>
>>>
>>>
>>
> 
>