[mapserver-users] Asking for guidelines about a project

[Saka Royban]

There is at least (and for now) 100 vehicles with update rate of 5-15 minute.
That' why i'm so careful abut table design.

Anyway, thanks a million for all your helpful answers.

Regards




________________________________
From: Brent Fraser <bfraser at geoanalytic.com>
To: Saka Royban <sakaroyban at yahoo.com>
Cc: MapServer <mapserver-users at lists.osgeo.org>
Sent: Tue, June 21, 2011 11:14:14 PM
Subject: Re: [mapserver-users] Asking for guidelines about a project

      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 wrote:
>
>From: Brent                       Fraser <bfraser at geoanalytic.com>
>>
>>Date: 20                       June 2011 11:43:52 PM AEST
>>
>>To: Ben                       Madin <lists at remoteinformation.com.au>
>>
>>Cc: 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/e9245da7/attachment.htm>