[mapserver-users] Asking for guidelines about a project

[Ben Madin]

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
> 

-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/mapserver-users/attachments/20110621/ee9374af/attachment.htm>