<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>There is at least (and for now) 100 vehicles with update rate of 5-15 minute.<br>That' why i'm so careful abut table design.<br><br>Anyway, thanks a million for all your helpful answers.<br><br>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> Brent Fraser <bfraser@geoanalytic.com><br><b><span style="font-weight: bold;">To:</span></b> Saka Royban <sakaroyban@yahoo.com><br><b><span style="font-weight: bold;">Cc:</span></b> MapServer <mapserver-users@lists.osgeo.org><br><b><span style="font-weight: bold;">Sent:</span></b> Tue, June 21, 2011 11:14:14
PM<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">
<title></title>
Mainly because adding a new vehicle is trivial from a site
maintenance perspective if you have all vehicles in one table.
Adding a table would likely mean you would need to add a layer in
Mapserver, but it depends on your user interface needs.<br>
<br>
For my first implementation, I did have some concerns regarding
performance if I put all the historical positions in a table with
the current positions. That's why I had two tables:
current_position table with about 6 to 20 rows (since I have 6 to 20
vehicles), and an archive_positions table that grew over time.
Since the user was usually just concerned with displaying the
current position, queries were handled very quickly.<br>
<br>
As it turned out, the archive_positions table grew to only several
hundred thousand records (maybe a million?), and Postgresql had no
problems with that. So if I did it again, I'd likely use one table,
containing the current and historical positions.<br>
<br>
But as I mentioned earlier, it might depend on how may vehicles
you anticipate tracking, and how often they report (and to some
extent how fast they move). Do you have that information?<br>
<pre class="moz-signature">Best Regards,
Brent Fraser</pre>
<br>
On 6/21/2011 12:21 PM, Saka Royban wrote:
<blockquote type="cite">
<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 <a rel="nofollow" class="moz-txt-link-rfc2396E" ymailto="mailto:lists@remoteinformation.com.au" target="_blank" href="mailto:lists@remoteinformation.com.au"><lists@remoteinformation.com.au></a><br>
<b><span style="font-weight: bold;">To:</span></b>
<a rel="nofollow" class="moz-txt-link-abbreviated" 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> 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>
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>
</div>
</div>
</div>
<pre><fieldset class="mimeAttachmentHeader"></fieldset>
_______________________________________________
mapserver-users mailing list
<a rel="nofollow" class="moz-txt-link-abbreviated" ymailto="mailto:mapserver-users@lists.osgeo.org" target="_blank" href="mailto:mapserver-users@lists.osgeo.org">mapserver-users@lists.osgeo.org</a><span>
<a target="_blank" href="http://lists.osgeo.org/mailman/listinfo/mapserver-users">http://lists.osgeo.org/mailman/listinfo/mapserver-users</a>
</span></pre>
</blockquote>
<meta http-equiv="x-dns-prefetch-control" content="on"></div></div>
</div></body></html>