How to show lines between PostGIS data of type POINT

[Russ Bradford]

Hi Steve,

I have tried all manner of slight changes to the code to try to get it
to work. This morning I went back to the basics and tried starting out
with the basic subquery:

geom from ( select geom from shark_pos where shark_name = 'Bomber') as
foo using unique oid using srid = -1

This did not work. The error was "column oid does not exist". Now I know
that the oid column does exist and that in fact it is the indexed
column. So I tried using a different column as unique (pos_time). Again,
I get an error stating that column pos_time does not exist.

So I returned to the original DATA definition: DATA "geom from
shark_pos" and FILTER "shark_name = 'Bomber'"

This works - and I can even label the points with pos_time if I so wish.


If I look at what MapServer supports I can see "INPUT=POSTGIS" so I am
assuming I have Postgis installed and configured correctly. I am using
MapServer V 4.6.0

I am at a loss to see where I am going wrong. Any direction would be
greatly appreciated.

Cheers, Russ.

-----Original Message-----
From: Stephen Woodbridge [mailto:woodbri at swoodbridge.com] 
Sent: Thursday, 10 November 2005 2:04 PM
To: Bradford, Russ (CMAR, Hobart)
Cc: PostGIS Users Discussion
Subject: Re: [UMN_MAPSERVER-USERS] How to show lines between PostGIS
data of type POINT


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
>>>>
>>
>>
>