[mapserver-users] Asking for guidelines about a project

[Brent Fraser]

     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.

     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.

   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.

   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?

Best Regards,
Brent Fraser


On 6/21/2011 12:21 PM, Saka Royban wrote:
> Thanks a lot for your informative answers.
> 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.
> 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?
> (because i have to deal a growing number of vehicles, this is of 
> importance to me)
>
> Dear Ben
> Also, I'm so sorry, but i didn't find Regina Obe comments on running 
> tracks in internet. Do u have any more information?
>
> With best wishes
> Best Regards
>
> ------------------------------------------------------------------------
> *From:* Ben Madin <lists at remoteinformation.com.au>
> *To:* mapserver-users at lists.osgeo.org
> *Sent:* Tue, June 21, 2011 8:59:46 AM
> *Subject:* Re: [mapserver-users] Asking for guidelines about a project
>
> Brent et al,
>
> Becoming off topic, but ours went something like :
>
> DATA "the_move FROM (select ST_MakeLine(the_geom) as the_move, sq.polltime
> FROM (SELECT the_geom, CAST(polltime AS date) as polltime
> FROM vms
> WHERE vesselname like '%pg_sql%'
> ORDER BY polltime LIMIT 36) sq
> GROUP BY sq.polltime) AS foo USING UNIQUE polltime USING SRID=4326"
>
> but all credit for the concept goes to Regina Obe  - I think she was 
> showing running tracks! try postgresonline or the postgis list.
>
> cheers
>
> Ben
>
>
>
> On 21/06/2011, at 11:15 AM, mapserver-users-request at lists.osgeo.org 
> <mailto:mapserver-users-request at lists.osgeo.org> wrote:
>
>> *From:*Brent Fraser <bfraser at geoanalytic.com 
>> <mailto:bfraser at geoanalytic.com>>
>> *Date:*20 June 2011 11:43:52 PM AEST
>> *To:*Ben Madin <lists at remoteinformation.com.au 
>> <mailto:lists at remoteinformation.com.au>>
>> *Cc:*mapserver-users at lists.osgeo.org 
>> <mailto:mapserver-users at lists.osgeo.org>
>> *Subject:**Re: [mapserver-users] Asking for guidelines about a project*
>>
>>
>> Ben,
>>
>>   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:
>>
>> CREATE VIEW archive_v AS
>>             SELECT
>>                    archive_location.vessel_id,
>>                    archive_location.time_fix,
>>                    archive_location.speed,
>>                    archive_location.heading,
>>                    archive_location.vessel_coordinate,
>>                    archive_location.archive_sequence,
>>                    vessel_cfg.vessel_name,
>>                    owner.org_name
>>             FROM archive_location,owner,vessel_cfg
>>             WHERE archive_location.owner_id=owner.org_id AND
>>                   archive_location.vessel_id=vessel_cfg.vessel_id AND
>>                   ((now() AT TIME ZONE 'utc') - 
>> (archive_location.time_fix)) <=  '24 hour';
>>
>> 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.
>> Best Regards,
>> Brent Fraser
>>
>
>
> _______________________________________________
> mapserver-users mailing list
> mapserver-users at lists.osgeo.org
> http://lists.osgeo.org/mailman/listinfo/mapserver-users
-------------- next part --------------
An HTML attachment was scrubbed...
URL: http://lists.osgeo.org/pipermail/mapserver-users/attachments/20110621/fa464cce/attachment-0001.html