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

[Stephen Woodbridge]

Russ,

1) Try making 'FROM' as 'from' there is/was a stupid bug on this.
2) the function setsrid(geom, 4326) requires two arguments you only have one
3) not sure where the query is getting oid, I'll cc the list on this and 
see if they have input on that

-Steve

Russ.Bradford at csiro.au wrote:
> Hi Steve,
> 
> I hope you do not mind me asking you directly before I post to the list.
> I tried using your suggestion for creating a line to join the dots. I
> consistently come up with an error no matter which way I try to define
> the DATA. I have tried:
> 
> LAYER		
> CONNECTIONTYPE postgis
> NAME "Bomber"
> CONNECTION "user=postgres password=***** dbname=WhiteShark
> host=localhost port=5432"
> DATA "geom FROM (SELECT setsrid (makeline (SELECT geom FROM shark_pos
> ORDER BY pos_date))) as foo using SRID=4326 USING UNIQUE oid"
> STATUS ON
> TYPE POINT
> FILTER "shark_name = 'Bomber'"
> ...
> 
> 
> The above definition is not what you suggested, but I did try that as
> well. I have also added a couple of other things (such as 'as foo') that
> are suggested in the Refractions tutorial. In all cases the error
> message runs along the lines of:
> 
> msDrawMap(): Image handling error. Failed to draw layer named 'Bomber'.
> prepare_database(): Query error. Error executing POSTGIS DECLARE (the
> actual query) statement: 'DECLARE mycursor BINARY CURSOR FOR SELECT
> pos_date::text,asbinary(force_collection(force_2d(geom)),'NDR'),oid::tex
> t from (SELECT setsrid (makeline (select geom FROM shark_pos ORDER BY
> pos_date))) as foo WHERE (shark_name = 'Bomber') and (geom && setSRID(
> 'BOX3D(110 -48,156 -2)'::BOX3D,4326) )'
> 
> Postgresql reports the error as 'ERROR: syntax error at or near "select"
> at character 149 '
> 
> In some of the errors I noticed that there were not enough, or too many,
> matching parentheses. That is why I have added some to the DATA
> definition above.
> 
> The table I am using has an oid column for uniqueness and logically the
> dots should be joined in date order. Pos_date is of date type and the
> geom is geometry with the constraints of "POINT"::text OR geom IS NULL &
> SRID(geom)=4326.
> 
> Do you have any ideas of what I am doing wrong here. Once I have this
> working I will post it to the list for any others who may want to do a
> similar thing.
> 
> Many thanks, and again I hope you do not mind the direct email approach
> in the first instance.
> 
> Russ.
> 
> -----Original Message-----
> From: UMN MapServer Users List [mailto:MAPSERVER-USERS at LISTS.UMN.EDU] On
> Behalf Of Stephen Woodbridge
> Sent: Wednesday, 9 November 2005 3:24 PM
> To: MAPSERVER-USERS at LISTS.UMN.EDU
> Subject: Re: [UMN_MAPSERVER-USERS] How to show lines between PostGIS
> data of type POINT
> 
> 
> 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
>>>>
>>
>>
>