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