Elegant :) Turns out I'm stuck with an old server running PG 8.3 and can't use WITH clause :D Thanks Tom!
Pedro Doria Meunier Telf. +351 291 933 006 GSM +351 915 818 823 Skype: pdoriam On 07/20/2012 09:03 AM, Tom van Tilburg wrote: > 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
_______________________________________________ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
