<html dir="ltr">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=Windows-1252">
<style id="owaParaStyle" type="text/css">P {margin-top:0;margin-bottom:0;}</style>
</head>
<body ocsi="0" fpstyle="1" bgcolor="#FFFFFF">
<div style="direction: ltr;font-family: Tahoma;color: #000000;font-size: 10pt;"><font face="Courier New">This is a lot faster!!<br>
<br>
<br>
select <br>
row_number() over () as rnum,<br>
vname, <br>
st_multi(st_union(the_line)) as the_line<b>,</b><br>
<b>acqtime</b><br>
from<br>
loc<br>
where<br>
vname in ('463','462')<br>
and<br>
acqtime > '2014-12-01 08:00'<br>
and<br>
acqtime <= '2014-12-01 10:00'<br>
group by<br>
vname<b>, acqtime<br>
order by<br>
acqtime</b><br>
<br>
<br>
</font>
<div style="font-family: Times New Roman; color: #000000; font-size: 16px">
<hr tabindex="-1">
<div style="direction: ltr;" id="divRpF792148"><font size="2" face="Tahoma" color="#000000"><b>From:</b> Basques, Bob (CI-StPaul)<br>
<b>Sent:</b> Wednesday, December 03, 2014 10:06 PM<br>
<b>To:</b> mapserver-users@lists.osgeo.org<br>
<b>Subject:</b> RE: [mapserver-users] An interesting SQL linestring building problem from postgres.<br>
</font><br>
</div>
<div></div>
<div>
<div style="direction:ltr; font-family:Tahoma; color:#000000; font-size:10pt">All,<br>
<br>
Made some progress:<br>
<br>
<font face="Courier New">select <br>
row_number() over () as rnum,<br>
vname, <br>
st_multi(st_union(the_line)) as the_line<br>
from<br>
loc<br>
where<br>
vname in ('463','462')<br>
and<br>
acqtime > '2014-12-01 08:00'<br>
and<br>
acqtime <= '2014-12-01 10:00'<br>
group by<br>
vname<br>
</font><br>
But it's a lot slower than the original query for some reason. A couple of line strings at a 1000 or so segments take almost a minute.<br>
<br>
Still working it . . . I wonder if making the groups into something other than a multi would be better?<br>
<br>
bobb<br>
<br>
<div style="font-family:Times New Roman; color:#000000; font-size:16px">
<hr tabindex="-1">
<div id="divRpF337527" style="direction:ltr"><font size="2" face="Tahoma" color="#000000"><b>From:</b> mapserver-users-bounces@lists.osgeo.org [mapserver-users-bounces@lists.osgeo.org] on behalf of Worth Lutz [wal3@mindspring.com]<br>
<b>Sent:</b> Tuesday, December 02, 2014 4:00 PM<br>
<b>To:</b> mapserver-users@lists.osgeo.org<br>
<b>Subject:</b> Re: [mapserver-users] An interesting SQL linestring building problem from postgres.<br>
</font><br>
</div>
<div></div>
<div>
<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" target="_blank">
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 type="cite"><style>
<!--
@font-face
{font-family:Wingdings}
@font-face
{font-family:Wingdings}
@font-face
{font-family:Calibri}
@font-face
{font-family:"Comic Sans MS"}
p.MsoNormal, li.MsoNormal, div.MsoNormal
{margin:0in;
margin-bottom:.0001pt;
font-size:11.0pt;
font-family:"Calibri","sans-serif"}
a:link, span.MsoHyperlink
{color:blue;
text-decoration:underline}
a:visited, span.MsoHyperlinkFollowed
{color:purple;
text-decoration:underline}
p.MsoListParagraph, li.MsoListParagraph, div.MsoListParagraph
{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
{font-family:"Comic Sans MS";
color:windowtext}
.MsoChpDefault
{font-family:"Calibri","sans-serif"}
@page WordSection1
{margin:1.0in 1.0in 1.0in 1.0in}
ol
{margin-bottom:0in}
ul
{margin-bottom:0in}
body
{direction:ltr;
font-family:Tahoma;
color:#000000;
font-size:10pt}
p
{margin-top:0;
margin-bottom:0}
body
{scrollbar-base-color:undefined;
scrollbar-highlight-color:undefined;
scrollbar-darkshadow-color:undefined;
scrollbar-arrow-color:undefined}
body
{scrollbar-base-color:undefined;
scrollbar-highlight-color:undefined;
scrollbar-darkshadow-color:undefined;
scrollbar-arrow-color:undefined}
-->
BODY {direction: ltr;font-family: Tahoma;color: #000000;font-size: 10pt;}</style>
<div class="WordSection1">
<p class="MsoNormal"><span style="">All,</span></p>
<p class="MsoNormal"><span style=""> </span></p>
<p class="MsoNormal"><span style="">I have a bunch of individual LINE segments in Postgres and want to display them via MapServer like so:</span></p>
<p class="MsoNormal"><span style=""> </span></p>
<p class="MsoNormal"><span style="">First:</span></p>
<p class="MsoListParagraph" style="text-indent:-.25in"><span style="font-size:8.0pt; font-family:Symbol"><span style="">·<span style="font:7.0pt
"Times New Roman"">
</span></span></span><span style="">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)</span></p>
<p class="MsoListParagraph" style="text-indent:-.25in"><span style="font-size:8.0pt; font-family:Symbol"><span style="">·<span style="font:7.0pt
"Times New Roman"">
</span></span></span><span style="">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)</span></p>
<p class="MsoListParagraph" style="text-indent:-.25in"><span style="font-size:8.0pt; font-family:Symbol"><span style="">·<span style="font:7.0pt
"Times New Roman"">
</span></span></span><span style="">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)</span></p>
<p class="MsoListParagraph" style="text-indent:-.25in"><span style="font-size:8.0pt; font-family:Symbol"><span style="">·<span style="font:7.0pt
"Times New Roman"">
</span></span></span><span style="">Have results output as three rows, one for each multi-segment line.</span></p>
<p class="MsoNormal"><span style=""> </span></p>
<p class="MsoNormal"><span style="">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.</span></p>
<p class="MsoNormal"><span style=""> </span></p>
<p class="MsoNormal"><span style="">Anyone have examples of something like this to start from?</span></p>
<p class="MsoNormal"><span style=""> </span></p>
<p class="MsoNormal"><span style="">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):</span></p>
<p class="MsoNormal"><span style=""> </span></p>
<p class="MsoNormal"><span style="">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=""></span></p>
<p class="MsoNormal"><span style=""> </span></p>
<p class="MsoNormal"><span style="">Thanks</span></p>
<p class="MsoNormal"><span style=""> </span></p>
<p class="MsoNormal"><span style="">Bobb</span></p>
<p class="MsoNormal"><span style=""> </span></p>
<p class="MsoNormal"><span style=""> </span></p>
<p class="MsoNormal"><span style=""> </span></p>
</div>
<br>
<fieldset class="mimeAttachmentHeader" target="_blank"></fieldset> <br>
<pre>_______________________________________________
mapserver-users mailing list
<a class="moz-txt-link-abbreviated" href="mailto:mapserver-users@lists.osgeo.org" target="_blank">mapserver-users@lists.osgeo.org</a>
<a class="moz-txt-link-freetext" href="http://lists.osgeo.org/mailman/listinfo/mapserver-users" target="_blank">http://lists.osgeo.org/mailman/listinfo/mapserver-users</a></pre>
</blockquote>
<br>
</div>
</div>
</div>
</div>
</div>
</div>
</body>
</html>