Hmm, I would still think that, as long as you have a single line
per-boat, this would solve your problem.
Because you can find out how often this single line crosses the bouy
area. The combination of ST_Intersection and ST_Dump would do that for you.
Try something like:
--------------------
WITH line AS
(
SELECT
gps.gps_track As boat, ST_MakeLine(gps.the_geom ORDER BY
gps_time) As geom
FROM gps_points As gps
GROUP BY gps.gps_track;
)
,intersections AS
(
SELECT boat, (ST_Dump(ST_Intersection(line.geom,
ST_SetSrid(ST_Makepoint(lon,lat),4326)))).geom
)
SELECT
boat, count(boat) As number_of_passes
FROM intersections
WHERE ST_GeometryType(geom) = 'ST_LineString'
GROUP BY boat
--------------------------------
if your postgres is below 9.0 the gps.tracks might work different. See
manual.
Cheers,
Tom
On 20-7-2012 9:38, Pedro Doria Meunier wrote:
On 07/20/2012 07:38 AM, Tom van Tilburg wrote:
Hi Pedro,
You might try to create lines from the boat tracks (see examples in
manual for ST_MakeLine) and circles (st_buffer) from the proximity
areas around the buoy.
Now do a ST_Intersection(boatline, bouycircle). The result should be
a set of lines within the bouycircle. Every line stands for one pass
along the bouy.
I think ST_Intersection might result in a geometry collection of
lines and points so you would have to make a dump of the result with
ST_Dump and then select only the lines.
Cheers,
Tom
Elegant thinking but that would end up in a *single* line, as I only
have the timestamp and (point)geom to work with.
Thanks for your input though :)
--
Pedro Doria Meunier
Telf. +351 291 933 006
GSM +351 915 818 823
Skype: pdoriam
_______________________________________________
postgis-users mailing list
postgis-users@postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users
_______________________________________________
postgis-users mailing list
postgis-users@postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users