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

[Stephen Woodbridge]

On 12/2/2014 2:56 PM, Basques, Bob (CI-StPaul) wrote:
> 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.

If the goal is to just display them, then you do not need to create a 
multi-segment line, just display them as individual lines with the color 
attribute.

I you need them 'glued together' as a multilines for some other reason. 
then union should work.

select color, union(geom) from lines group by color order by timestamp;

> 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
>
>
>
> _______________________________________________
> mapserver-users mailing list
> mapserver-users at lists.osgeo.org
> http://lists.osgeo.org/mailman/listinfo/mapserver-users
>