<html>
<head>
<meta content="text/html; charset=windows-1252"
http-equiv="Content-Type">
</head>
<body bgcolor="#FFFFFF" text="#000000">
<div class="moz-cite-prefix">I've done something similar. <br>
<br>
I don't have it in front of me at the moment but a postgres select
can do this.<br>
<br>
Look in Postgres for "window" and "row_number". <br>
<br>
You use the postgres "OVER" clause to group the items and then
row_number to get a key for color.<br>
<br>
SELECT<br>
a,<br>
b,<br>
c,<br>
row_number() OVER (PARTITION BY d) AS color_key<br>
FROM table<br>
<br>
The partition is your grouping of three. row_number would be your
color_key. Use a modulus to limit the number of colors.<br>
<br>
here is a link which might get you started<br>
<a class="moz-txt-link-freetext" href="http://www.codeproject.com/articles/308281/How-to-Use-ROW-NUMBER-to-Enumerate-and-Partition-R">http://www.codeproject.com/articles/308281/How-to-Use-ROW-NUMBER-to-Enumerate-and-Partition-R</a><br>
<br>
Note the final SQL statement which shows multiple items inside the
"OVER" clause.<br>
<br>
I hope this helps point you in the right direction.<br>
<br>
If I can find a second, I'll look at your problem a little harder.<br>
<div class="moz-signature">
<br>
<b>Worth Lutz</b><br>
------------------<br>
<br>
</div>
On 12/02/2014 02:56 PM, Basques, Bob (CI-StPaul) wrote:<br>
</div>
<blockquote
cite="mid:C9CEA89772DA74488B5A5B6EB17DC481068D03B8@055-CH1MPN1-003.055d.mgd.msft.net"
type="cite">
<meta http-equiv="Content-Type" content="text/html;
charset=windows-1252">
<meta name="Generator" content="Microsoft Word 14 (filtered
medium)">
<style><!--
/* Font Definitions */
@font-face
{font-family:Wingdings;
panose-1:5 0 0 0 0 0 0 0 0 0;}
@font-face
{font-family:Wingdings;
panose-1:5 0 0 0 0 0 0 0 0 0;}
@font-face
{font-family:Calibri;
panose-1:2 15 5 2 2 2 4 3 2 4;}
@font-face
{font-family:"Comic Sans MS";
panose-1:3 15 7 2 3 3 2 2 2 4;}
/* Style Definitions */
p.MsoNormal, li.MsoNormal, div.MsoNormal
{margin:0in;
margin-bottom:.0001pt;
font-size:11.0pt;
font-family:"Calibri","sans-serif";}
a:link, span.MsoHyperlink
{mso-style-priority:99;
color:blue;
text-decoration:underline;}
a:visited, span.MsoHyperlinkFollowed
{mso-style-priority:99;
color:purple;
text-decoration:underline;}
p.MsoListParagraph, li.MsoListParagraph, div.MsoListParagraph
{mso-style-priority:34;
margin-top:0in;
margin-right:0in;
margin-bottom:0in;
margin-left:.5in;
margin-bottom:.0001pt;
font-size:11.0pt;
font-family:"Calibri","sans-serif";}
span.EmailStyle17
{mso-style-type:personal-compose;
font-family:"Comic Sans MS";
color:windowtext;}
.MsoChpDefault
{mso-style-type:export-only;
font-family:"Calibri","sans-serif";}
@page WordSection1
{size:8.5in 11.0in;
margin:1.0in 1.0in 1.0in 1.0in;}
div.WordSection1
{page:WordSection1;}
/* List Definitions */
@list l0
{mso-list-id:1468741646;
mso-list-type:hybrid;
mso-list-template-ids:1184646652 67698689 67698691 67698693 67698689 67698691 67698693 67698689 67698691 67698693;}
@list l0:level1
{mso-level-number-format:bullet;
mso-level-text:\F0B7;
mso-level-tab-stop:none;
mso-level-number-position:left;
text-indent:-.25in;
font-family:Symbol;}
@list l0:level2
{mso-level-number-format:bullet;
mso-level-text:o;
mso-level-tab-stop:none;
mso-level-number-position:left;
text-indent:-.25in;
font-family:"Courier New";}
@list l0:level3
{mso-level-number-format:bullet;
mso-level-text:\F0A7;
mso-level-tab-stop:none;
mso-level-number-position:left;
text-indent:-.25in;
font-family:Wingdings;}
@list l0:level4
{mso-level-number-format:bullet;
mso-level-text:\F0B7;
mso-level-tab-stop:none;
mso-level-number-position:left;
text-indent:-.25in;
font-family:Symbol;}
@list l0:level5
{mso-level-number-format:bullet;
mso-level-text:o;
mso-level-tab-stop:none;
mso-level-number-position:left;
text-indent:-.25in;
font-family:"Courier New";}
@list l0:level6
{mso-level-number-format:bullet;
mso-level-text:\F0A7;
mso-level-tab-stop:none;
mso-level-number-position:left;
text-indent:-.25in;
font-family:Wingdings;}
@list l0:level7
{mso-level-number-format:bullet;
mso-level-text:\F0B7;
mso-level-tab-stop:none;
mso-level-number-position:left;
text-indent:-.25in;
font-family:Symbol;}
@list l0:level8
{mso-level-number-format:bullet;
mso-level-text:o;
mso-level-tab-stop:none;
mso-level-number-position:left;
text-indent:-.25in;
font-family:"Courier New";}
@list l0:level9
{mso-level-number-format:bullet;
mso-level-text:\F0A7;
mso-level-tab-stop:none;
mso-level-number-position:left;
text-indent:-.25in;
font-family:Wingdings;}
ol
{margin-bottom:0in;}
ul
{margin-bottom:0in;}
--></style><!--[if gte mso 9]><xml>
<o:shapedefaults v:ext="edit" spidmax="1026" />
</xml><![endif]--><!--[if gte mso 9]><xml>
<o:shapelayout v:ext="edit">
<o:idmap v:ext="edit" data="1" />
</o:shapelayout></xml><![endif]-->
<div class="WordSection1">
<p class="MsoNormal"><span
style="font-size:8.0pt;font-family:"Comic Sans
MS"">All,<o:p></o:p></span></p>
<p class="MsoNormal"><span
style="font-size:8.0pt;font-family:"Comic Sans
MS""><o:p> </o:p></span></p>
<p class="MsoNormal"><span
style="font-size:8.0pt;font-family:"Comic Sans
MS"">I have a bunch of individual LINE segments in
Postgres and want to display them via MapServer like so:<o:p></o:p></span></p>
<p class="MsoNormal"><span
style="font-size:8.0pt;font-family:"Comic Sans
MS""><o:p> </o:p></span></p>
<p class="MsoNormal"><span
style="font-size:8.0pt;font-family:"Comic Sans
MS"">First:<o:p></o:p></span></p>
<p class="MsoListParagraph"
style="text-indent:-.25in;mso-list:l0 level1 lfo1"><!--[if !supportLists]--><span
style="font-size:8.0pt;font-family:Symbol"><span
style="mso-list:Ignore">·<span style="font:7.0pt
"Times New Roman"">
</span></span></span><!--[endif]--><span
style="font-size:8.0pt;font-family:"Comic Sans
MS"">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)<o:p></o:p></span></p>
<p class="MsoListParagraph"
style="text-indent:-.25in;mso-list:l0 level1 lfo1"><!--[if !supportLists]--><span
style="font-size:8.0pt;font-family:Symbol"><span
style="mso-list:Ignore">·<span style="font:7.0pt
"Times New Roman"">
</span></span></span><!--[endif]--><span
style="font-size:8.0pt;font-family:"Comic Sans
MS"">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)<o:p></o:p></span></p>
<p class="MsoListParagraph"
style="text-indent:-.25in;mso-list:l0 level1 lfo1"><!--[if !supportLists]--><span
style="font-size:8.0pt;font-family:Symbol"><span
style="mso-list:Ignore">·<span style="font:7.0pt
"Times New Roman"">
</span></span></span><!--[endif]--><span
style="font-size:8.0pt;font-family:"Comic Sans
MS"">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)<o:p></o:p></span></p>
<p class="MsoListParagraph"
style="text-indent:-.25in;mso-list:l0 level1 lfo1"><!--[if !supportLists]--><span
style="font-size:8.0pt;font-family:Symbol"><span
style="mso-list:Ignore">·<span style="font:7.0pt
"Times New Roman"">
</span></span></span><!--[endif]--><span
style="font-size:8.0pt;font-family:"Comic Sans
MS"">Have results output as three rows, one for each
multi-segment line.<o:p></o:p></span></p>
<p class="MsoNormal"><span
style="font-size:8.0pt;font-family:"Comic Sans
MS""><o:p> </o:p></span></p>
<p class="MsoNormal"><span
style="font-size:8.0pt;font-family:"Comic Sans
MS"">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.<o:p></o:p></span></p>
<p class="MsoNormal"><span
style="font-size:8.0pt;font-family:"Comic Sans
MS""><o:p> </o:p></span></p>
<p class="MsoNormal"><span
style="font-size:8.0pt;font-family:"Comic Sans
MS"">Anyone have examples of something like this to
start from?<o:p></o:p></span></p>
<p class="MsoNormal"><span
style="font-size:8.0pt;font-family:"Comic Sans
MS""><o:p> </o:p></span></p>
<p class="MsoNormal"><span
style="font-size:8.0pt;font-family:"Comic Sans
MS"">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):<o:p></o:p></span></p>
<p class="MsoNormal"><span
style="font-size:8.0pt;font-family:"Comic Sans
MS""><o:p> </o:p></span></p>
<p class="MsoNormal"><span style="font-family:"Courier
New"">DATA "the_line FROM (select the_line, vname,
acqtime, trunc(random() * 4 + 1) rnum from loc) as foo USING
unique vname using SRID=4326"<br>
<br>
FILTER " vname in (%veh_id%) AND acqtime
> '%time_idx1%' AND acqtime <'%time_idx2%' "<br>
<br>
VALIDATION<br>
'time_idx1' '.*'<br>
'time_idx2' '.*'<br>
'veh_id' '.*' ##
veh_id=’100’,’200’,’300’ . . .<br>
END<br>
<br>
CLASSITEM "rnum"<br>
<br>
CLASS ## Use the RNUM value to color code
the line strings randomly.<br>
STYLE<br>
COLOR 255 0 0<br>
WIDTH 5<br>
END<br>
EXPRESSION '1'<br>
END<br>
CLASS<br>
STYLE<br>
COLOR 0 255 0<br>
WIDTH 5<br>
END<br>
EXPRESSION '2'<br>
END<br>
CLASS<br>
STYLE<br>
COLOR 0 0 255<br>
WIDTH 5<br>
END<br>
EXPRESSION '3'<br>
END<br>
CLASS<br>
STYLE<br>
COLOR 255 255 0<br>
WIDTH 5<br>
END<br>
EXPRESSION '4'<br>
END<br>
CLASS<br>
NAME "Segments"<br>
STYLE<br>
COLOR 0 255 0<br>
WIDTH 5<br>
END<br>
END<br>
<br>
</span><span style="font-size:8.0pt;font-family:"Courier
New""><o:p></o:p></span></p>
<p class="MsoNormal"><span
style="font-size:8.0pt;font-family:"Comic Sans
MS""><o:p> </o:p></span></p>
<p class="MsoNormal"><span
style="font-size:8.0pt;font-family:"Comic Sans
MS"">Thanks<o:p></o:p></span></p>
<p class="MsoNormal"><span
style="font-size:8.0pt;font-family:"Comic Sans
MS""><o:p> </o:p></span></p>
<p class="MsoNormal"><span
style="font-size:8.0pt;font-family:"Comic Sans
MS"">Bobb<o:p></o:p></span></p>
<p class="MsoNormal"><span
style="font-size:8.0pt;font-family:"Comic Sans
MS""><o:p> </o:p></span></p>
<p class="MsoNormal"><span
style="font-size:8.0pt;font-family:"Comic Sans
MS""><o:p> </o:p></span></p>
<p class="MsoNormal"><span
style="font-size:8.0pt;font-family:"Comic Sans
MS""><o:p> </o:p></span></p>
</div>
<br>
<fieldset class="mimeAttachmentHeader"></fieldset>
<br>
<pre wrap="">_______________________________________________
mapserver-users mailing list
<a class="moz-txt-link-abbreviated" href="mailto:mapserver-users@lists.osgeo.org">mapserver-users@lists.osgeo.org</a>
<a class="moz-txt-link-freetext" href="http://lists.osgeo.org/mailman/listinfo/mapserver-users">http://lists.osgeo.org/mailman/listinfo/mapserver-users</a></pre>
</blockquote>
<br>
</body>
</html>