<html><head></head><body style="word-wrap: break-word; -webkit-nbsp-mode: space; -webkit-line-break: after-white-space; ">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 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-top: 0px; margin-right: 0px; margin-bottom: 0px; margin-left: 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 href="mailto:bfraser@geoanalytic.com">bfraser@geoanalytic.com</a>><br></span></div><div style="margin-top: 0px; margin-right: 0px; margin-bottom: 0px; margin-left: 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-top: 0px; margin-right: 0px; margin-bottom: 0px; margin-left: 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 href="mailto:lists@remoteinformation.com.au">lists@remoteinformation.com.au</a>><br></span></div><div style="margin-top: 0px; margin-right: 0px; margin-bottom: 0px; margin-left: 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 href="mailto:mapserver-users@lists.osgeo.org">mapserver-users@lists.osgeo.org</a><br></span></div><div style="margin-top: 0px; margin-right: 0px; margin-bottom: 0px; margin-left: 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" cols="72">Best Regards,
Brent Fraser</pre><br></blockquote></div><br></div></body></html>