[mapserver-users] An interesting SQL linestring building problem from postgres.

[Basques, Bob (CI-StPaul)]

All,

I have a bunch of individual LINE segments in Postgres and want to display them via MapServer like so:

First:

*          Get a chunk of them based on a set of IDs (a set of three for example)  and a begin/end timestamp (got this figured out already)

*          Take the result and combine up the separate line strings into multi-segment (grouped) lines ordered by ID, timestamp (got this figured out for individual IDs)

*          Add a random number to each grouping of multi-line string rows for dynamic color-coding of each group. (got this figured out for individual IDs)

*          Have results output as three rows, one for each multi-segment line.

I'm lost on the method to do the last piece.  Mostly it relates about how to approach which piece first.  Seems like a straightforward SQL, but I've tried a few different things, and it falls part each time for me.

Anyone have examples of something like this to start from?

The pertinent Mapfile fragment, I know this only color codes each individual segment at the moment.  VEH_IDs are passed into the MapFile from CGI (vname=veh_id in DB):

DATA "the_line FROM (select the_line, vname, acqtime, trunc(random() * 4 + 1) rnum from loc) as foo USING unique vname using SRID=4326"

                FILTER " vname in (%veh_id%) AND acqtime > '%time_idx1%' AND acqtime <'%time_idx2%' "

                VALIDATION
                        'time_idx1' '.*'
                        'time_idx2' '.*'
                        'veh_id' '.*'     ## veh_id='100','200','300' . . .
                END

                CLASSITEM "rnum"

                CLASS ## Use the RNUM value to color code the line strings randomly.
                        STYLE
                                COLOR 255 0 0
                                WIDTH 5
                        END
                        EXPRESSION '1'
                END
                CLASS
                        STYLE
                                COLOR 0 255 0
                                WIDTH 5
                        END
                        EXPRESSION '2'
                END
                CLASS
                        STYLE
                                COLOR 0 0 255
                                WIDTH 5
                        END
                        EXPRESSION '3'
                END
                CLASS
                        STYLE
                                COLOR 255 255 0
                                WIDTH 5
                        END
                        EXPRESSION '4'
                END
                CLASS
                        NAME "Segments"
                        STYLE
                                COLOR 0 255 0
                                WIDTH 5
                        END
                END


Thanks

Bobb



-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/mapserver-users/attachments/20141202/99b15cbe/attachment.htm>