<html><head><style type="text/css"><!-- DIV {margin:0px;} --></style></head><body><div style="font-family:times new roman,new york,times,serif;font-size:12pt"><div>Thanks a lot for your informative answers.<br>As u mentioned, it's going to be off topic (of mapserver, Postgis list sounds a better place for it), but unfortunately I'm still confused.<br>According to what i understood from your Select(s), you are saving all vehicles with their points and timestamps in one table. Why not to partition this into some tables for preventing a large table?<br>(because i have to deal a growing number of vehicles, this is of importance to me)<br><br>Dear Ben<br>Also, I'm so sorry, but i didn't find Regina Obe comments on running tracks in internet. Do u have any more information?<br><br>With best wishes<br>Best Regards<br></div><div style="font-family: times new roman,new york,times,serif; font-size: 12pt;"><br><div style="font-family: times new roman,new
york,times,serif; font-size: 12pt;"><font face="Tahoma" size="2"><hr size="1"><b><span style="font-weight: bold;">From:</span></b> Ben Madin <lists@remoteinformation.com.au><br><b><span style="font-weight: bold;">To:</span></b> mapserver-users@lists.osgeo.org<br><b><span style="font-weight: bold;">Sent:</span></b> Tue, June 21, 2011 8:59:46 AM<br><b><span style="font-weight: bold;">Subject:</span></b> Re: [mapserver-users] Asking for guidelines about a project<br></font><br>
<meta http-equiv="x-dns-prefetch-control" content="off">Brent et al,<div><br></div><div>Becoming off topic, but ours went something like :</div><div><br></div><div><div>DATA "the_move FROM (select ST_MakeLine(the_geom) as the_move, sq.polltime</div><div><span class="Apple-tab-span" style="white-space: pre;"> </span>FROM (SELECT the_geom, CAST(polltime AS date) as polltime</div><div><span class="Apple-tab-span" style="white-space: pre;"> </span>FROM vms</div><div><span class="Apple-tab-span" style="white-space: pre;"> </span>WHERE vesselname like '%pg_sql%'</div><div><span class="Apple-tab-span" style="white-space: pre;"> </span>ORDER BY polltime LIMIT 36) sq</div><div><span class="Apple-tab-span" style="white-space: pre;"> </span>GROUP BY sq.polltime) AS foo USING UNIQUE polltime USING SRID=4326"</div></div><div><br></div><div>but all credit for the concept goes to Regina Obe - I think she was showing running tracks! try postgresonline or the
postgis list.</div><div><br></div><div>cheers</div><div><br></div><div>Ben</div><div><br></div><div><br></div><div><br><div><div>On 21/06/2011, at 11:15 AM, <a rel="nofollow" ymailto="mailto:mapserver-users-request@lists.osgeo.org" target="_blank" href="mailto:mapserver-users-request@lists.osgeo.org">mapserver-users-request@lists.osgeo.org</a> wrote:</div><br class="Apple-interchange-newline"><blockquote type="cite"><div style="margin: 0px;"><span style="font-family: Helvetica; font-size: medium; color: rgb(127, 127, 127);"><b>From:<span class="Apple-converted-space"> </span></b></span><span style="font-family: Helvetica; font-size: medium;">Brent Fraser <<a rel="nofollow" ymailto="mailto:bfraser@geoanalytic.com" target="_blank" href="mailto:bfraser@geoanalytic.com">bfraser@geoanalytic.com</a>><br></span></div><div style="margin: 0px;"><span style="font-family: Helvetica; font-size: medium; color: rgb(127, 127, 127);"><b>Date:<span
class="Apple-converted-space"> </span></b></span><span style="font-family: Helvetica; font-size: medium;">20 June 2011 11:43:52 PM AEST<br></span></div><div style="margin: 0px;"><span style="font-family: Helvetica; font-size: medium; color: rgb(127, 127, 127);"><b>To:<span class="Apple-converted-space"> </span></b></span><span style="font-family: Helvetica; font-size: medium;">Ben Madin <<a rel="nofollow" ymailto="mailto:lists@remoteinformation.com.au" target="_blank" href="mailto:lists@remoteinformation.com.au">lists@remoteinformation.com.au</a>><br></span></div><div style="margin: 0px;"><span style="font-family: Helvetica; font-size: medium; color: rgb(127, 127, 127);"><b>Cc:<span class="Apple-converted-space"> </span></b></span><span style="font-family: Helvetica; font-size: medium;"><a rel="nofollow" ymailto="mailto:mapserver-users@lists.osgeo.org" target="_blank"
href="mailto:mapserver-users@lists.osgeo.org">mapserver-users@lists.osgeo.org</a><br></span></div><div style="margin: 0px;"><span style="font-family: Helvetica; font-size: medium; color: rgb(127, 127, 127);"><b>Subject:<span class="Apple-converted-space"> </span></b></span><span style="font-family: Helvetica; font-size: medium;"><b>Re: [mapserver-users] Asking for guidelines about a project</b><br></span></div><br><br>Ben,<br><br> Our setup was similar. We had a current_location table and a archive_location table. To filter the archive_location for positions in the last 24 hours we had a view:<br><br>CREATE VIEW archive_v AS<br> SELECT<span class="Apple-converted-space"> </span><br>
archive_location.vessel_id,<br> archive_location.time_fix,<br> archive_location.speed,<br> archive_location.heading,<br> archive_location.vessel_coordinate,<br> archive_location.archive_sequence,<br>
vessel_cfg.vessel_name,<br> owner.org_name<br> FROM archive_location,owner,vessel_cfg<br> WHERE archive_location.owner_id=owner.org_id AND<br> archive_location.vessel_id=vessel_cfg.vessel_id AND<br> ((now() AT TIME ZONE 'utc') - (archive_location.time_fix)) <= '24 hour';<br><br>I can't recall how we created linestrings for the tracks from the above view, but I think we used the archive_sequence number (this was assigned at insert time, per vessel) to order the points into lines.<br><pre
class="moz-signature">Best Regards,
Brent Fraser</pre><br></blockquote></div><br></div><meta http-equiv="x-dns-prefetch-control" content="on"></div></div>
</div></body></html>