<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
<meta content="text/html; charset=ISO-8859-1"
http-equiv="Content-Type">
<title></title>
</head>
<body text="#000000" bgcolor="#ffffff">
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 <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>
On 6/19/2011 7:31 AM, Ben Madin wrote:
<blockquote
cite="mid:1DA4BFA0-9284-4721-801A-740F2EB468EF@remoteinformation.com.au"
type="cite">I've done similar to Brent's suggestions, but tracking
boats with 2 hourly updates. We just stored all the location data
in one table with a reference id to a boat name and details in
another project.
<div><br>
</div>
<div>The default setting showed the current location and the last
72 hours of track... Using a query like select blah order by
recordtime desc limit 36. we then created a linestring and
mapped those. It was a bit heavy on the poor old (really old)
server, but there were only about 70 boats all up.</div>
<div><br>
</div>
<div>In retrospect (and if I had more boats or users), I would
create two tables, one holding the all the point locations, and
one holding the default tracks. The default tracks would be
updated by a trigger on insertion of the new records into the
locations table, rather than all being computed on the fly.</div>
<div><br>
</div>
<div>Anyway, 101 ways to skin a cat. </div>
<div><br>
</div>
<div>cheers</div>
<div><br>
</div>
<div>Ben</div>
<div><br>
</div>
<div><br>
</div>
<div><br>
</div>
<div><br>
</div>
<div><br>
<div>
<div>On 18/06/2011, at 11:31 AM, <a moz-do-not-send="true"
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"><span class="Apple-style-span"
style="border-collapse: separate; font-family: Helvetica;
font-style: normal; font-variant: normal; font-weight:
normal; letter-spacing: normal; line-height: normal;
orphans: 2; text-indent: 0px; text-transform: none;
white-space: normal; widows: 2; word-spacing: 0px;
font-size: medium;">
<blockquote
cite="mid:873468.91062.qm@web121607.mail.ne1.yahoo.com"
type="cite">
<div style="margin: 0px; font-family: 'times new
roman','new york',times,serif; font-size: 12pt;">
<div style="margin: 0px; font-family: 'times new
roman','new york',times,serif; font-size: 12pt;">
<div style="margin: 0px; font-family: 'times new
roman','new york',times,serif; font-size: 12pt;">
<blockquote type="cite">
<div style="margin: 0px; font-family: 'times new
roman','new york',times,serif; font-size:
12pt;">
<div style="margin: 0px; font-family: 'times
new roman','new york',times,serif;
font-size: 12pt;">
<div style="margin: 0px; font-family: 'times
new roman','new york',times,serif;
font-size: 12pt;">
<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 moz-do-not-send="true"
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;">18 June 2011
3:27:00 AM 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;">Saka Royban <<a
moz-do-not-send="true"
href="mailto:sakaroyban@yahoo.com">sakaroyban@yahoo.com</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;">MapServer <<a
moz-do-not-send="true"
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>
Well, it's up to you how to model
the objects (that's the great thing
about relational databases). Typically,
I've added a table of "Historical
Positions" with basically the same
structure as the "vehicle" table (but in
that case the unique key is not the
vehicle ID, but instead just a system
assigned key like OID). As for the
points passed by, I would not save that
information in a table, instead consider
it the result of a spatial query (say in
a PostGIS view) at display time.<br>
<br>
A system like this usually takes a
large of amount of time for
requirements, investigation, design, and
prototyping. And of course,
construction, testing, documentation,
and training. If you want something
more packaged you can just download and
install, you may want to do some
searching on the web.<br>
<br>
<pre class="moz-signature" cols="72">Best Regards,
Brent Fraser</pre>
<br>
On 6/17/2011 10:56 AM, Saka Royban
wrote:
<blockquote
cite="mid:873468.91062.qm@web121607.mail.ne1.yahoo.com"
type="cite">
<div style="margin: 0px; font-family:
'times new roman','new
york',times,serif; font-size: 12pt;">
<div style="margin: 0px;">Thanks for
ur answer and also Josh's answer.<br>
In fact, i should be able to
display path of vehicles (usually
more than 1 vehicle in display
area) and also be able to save
points passed by vehicles and time
of passing (for some queries).
Cause of that, i thought maybe i
need one table per vehicle.<br>
Do u think any more idea?<br>
<br>
Best Regards<br>
</div>
<div style="margin: 0px;
font-family: 'times new
roman','new york',times,serif;
font-size: 12pt;"><br>
<div style="margin: 0px;
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><span
class="Apple-converted-space"> </span>Brent Fraser<span
class="Apple-converted-space"> </span><a
moz-do-not-send="true"
class="moz-txt-link-rfc2396E"
href="mailto:bfraser@geoanalytic.com"><bfraser@geoanalytic.com></a><br>
<b><span style="font-weight:
bold;">To:</span></b><span
class="Apple-converted-space"> </span>Saka Royban<span
class="Apple-converted-space"> </span><a
moz-do-not-send="true"
class="moz-txt-link-rfc2396E"
href="mailto:sakaroyban@yahoo.com"><sakaroyban@yahoo.com></a><br>
<b><span style="font-weight:
bold;">Cc:</span></b><span
class="Apple-converted-space"> </span>MapServer<span
class="Apple-converted-space"> </span><a
moz-do-not-send="true"
class="moz-txt-link-rfc2396E"
href="mailto:mapserver-users@lists.osgeo.org"><mapserver-users@lists.osgeo.org></a><br>
<b><span style="font-weight:
bold;">Sent:</span></b><span
class="Apple-converted-space"> </span>Fri, June 17, 2011 7:49:20 PM<br>
<b><span style="font-weight:
bold;">Subject:</span></b><span
class="Apple-converted-space"> </span>Re: [mapserver-users] Asking for
guidelines about a project<br>
</font><br>
The database schema depends on
what you want to do with the
data (display current position?
A track of historical
positions?), and how much data
you expect (how may objects will
be tracked? how often will they
report?).<br>
<br>
A simple schema would have one
table (e.g. "vehicles"), with
one row per tracked object<br>
<br>
Table: Vehicles<br>
<br>
<tt>ID: Name:
Position: Status:
UpdateTime:<br>
1 My Car
point(51,-115) active
2011-06-16 10:23:54+02<br>
2 Another Car
point(52,-116) inactive <span
class="Apple-converted-space"> </span></tt><tt>2010-05-15 11:13:12+02</tt><br>
<tt><br>
</tt>With this schema, when a
new position arrives for "My
Car", an update statement is
issued to change the row's
Position and UpdateTime (and
possibly Status).<br>
<br>
The table is treated as a
PostGIS point layer by
mapserver.<br>
<br>
In my implementations, the
position data was updated every
30 to 60 minutes (they were
ships and construction
vehicles), so there was no need
to ask mapserver to regenerate a
graphic every second; an updated
map was shown whenever a user
did a pan or zoom. You may be
able to configure OpenLayers to
do a CGI request to mapserver
every few seconds, but I've
never done that.<br>
<br>
<pre class="moz-signature">Best Regards,
Brent Fraser</pre>
<br>
On 6/16/2011 11:52 PM, Saka
Royban wrote:
<blockquote type="cite">
<div style="margin: 0px;
font-family: 'times new
roman','new
york',times,serif;
font-size: 12pt;">
<div style="margin: 0px;">Thanks
a lot brent<br>
I checked provided link
and i;m gonna give it a
try.<br>
About ur helpful advices,
i'm confused about how to
save data in PostGIS? with
what schema?<span
class="Apple-converted-space"> </span><br>
I mean i have to keep
track of each paced point
by moving objects. Should
i record each point as a
record, so it means one
table per moving object ?<br>
Also for displaying these
varaiant data, there
should some sort of plan.
I don't think i'm able to
say to MapServer CGI at
each second connect to
PostGIS and read the new
data, then i have to use
MapScript. Am i right?
(Can this process be done
via OpenLayers?)<br>
<br>
Best Regards<br>
</div>
<div style="margin: 0px;
font-family: 'times new
roman','new
york',times,serif;
font-size: 12pt;"><br>
<div style="margin: 0px;
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><span
class="Apple-converted-space"> </span>Brent Fraser<span
class="Apple-converted-space"> </span><a
moz-do-not-send="true" rel="nofollow" class="moz-txt-link-rfc2396E"
ymailto="mailto:bfraser@geoanalytic.com"
target="_blank"
href="mailto:bfraser@geoanalytic.com"><bfraser@geoanalytic.com></a><br>
<b><span
style="font-weight:
bold;">To:</span></b><span
class="Apple-converted-space"> </span>Saka Royban<span
class="Apple-converted-space"> </span><a
moz-do-not-send="true" rel="nofollow" class="moz-txt-link-rfc2396E"
ymailto="mailto:sakaroyban@yahoo.com"
target="_blank"
href="mailto:sakaroyban@yahoo.com"><sakaroyban@yahoo.com></a><br>
<b><span
style="font-weight:
bold;">Cc:</span></b><span
class="Apple-converted-space"> </span>MapServer<span
class="Apple-converted-space"> </span><a
moz-do-not-send="true" rel="nofollow" class="moz-txt-link-rfc2396E"
ymailto="mailto:mapserver-users@lists.osgeo.org"
target="_blank"
href="mailto:mapserver-users@lists.osgeo.org"><mapserver-users@lists.osgeo.org></a><br>
<b><span
style="font-weight:
bold;">Sent:</span></b><span
class="Apple-converted-space"> </span>Tue, June 14, 2011 7:14:08 PM<br>
<b><span
style="font-weight:
bold;">Subject:</span></b><span
class="Apple-converted-space"> </span>Re: [mapserver-users] Asking for
guidelines about a
project<br>
</font><br>
Saka,<br>
<br>
I've had good success
using PostGIS for data
storage in implementing
tracking systems. While
you'll need to create
your own code for
updating the positions
in the database,
mapserver can read
points from PostGIS and
can render the points
into a map image for use
with the OpenLayers
client.<br>
<br>
<span><span> There are
other solutions too,
like OpenGTS (<a
moz-do-not-send="true"
target="_blank"
href="http://opengts.sourceforge.net/index.html">http://opengts.sourceforge.net/index.html</a>)</span></span><br>
<pre class="moz-signature">Best Regards,
Brent Fraser</pre>
<br>
On 6/14/2011 12:08 AM,
Saka Royban wrote:
<blockquote type="cite">
<div style="margin:
0px; font-family:
'times new
roman','new
york',times,serif;
font-size: 12pt;">
<div style="margin:
0px;">Hi all.<br>
Strictly Speaking,
i have a project
that i should
display some
moving objects on
a map via browser.<br>
Generally, i don't
know how to start?
i mean how should
i save these
time-based objects
in DB and how to
retrieve and
display them via
Mapserver? where
should i use
client side
scripting like
OpneLayers?<br>
Any help woudbe
greatly
appreciated.<br>
<br>
Thanks in advance<br>
</div>
</div>
</blockquote>
</div>
</div>
</div>
</blockquote>
</div>
</div>
</div>
</blockquote>
<br class="Apple-interchange-newline">
</blockquote>
</div>
</div>
</div>
</blockquote>
</div>
</div>
</div>
</blockquote>
</span></blockquote>
</div>
<br>
</div>
<pre wrap="">
<fieldset class="mimeAttachmentHeader"></fieldset>
_______________________________________________
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>
</body>
</html>