[postgis-users] Need help constructing a query

[Tom van Tilburg]

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 at postgis.refractions.net
> http://postgis.refractions.net/mailman/listinfo/postgis-users

-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20120720/47957a2a/attachment.html>