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

[Worth Lutz]

I've done something similar.

I don't have it in front of me at the moment but a postgres select can 
do this.

Look in Postgres for "window"  and "row_number".

You use the postgres "OVER" clause to group the items and then 
row_number to get a key for color.

SELECT
     a,
     b,
     c,
     row_number() OVER (PARTITION BY d) AS color_key
FROM table

The partition is your grouping of three.  row_number would be your 
color_key.  Use a modulus to limit the number of colors.

here is a link which might get you started
http://www.codeproject.com/articles/308281/How-to-Use-ROW-NUMBER-to-Enumerate-and-Partition-R

Note the final SQL statement which shows multiple items inside the 
"OVER" clause.

I hope this helps point you in the right direction.

If I can find a second, I'll look at your problem a little harder.

*Worth Lutz*
------------------

On 12/02/2014 02: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.
>
> 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

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