[postgis-users] Need help constructing a query

[Pedro Doria Meunier]

I'm going to be extra-cheeky here ... :D
Must be getting old as I can't wrap my head around the 8.3 construct of
the query below.. :S

This, as you might imagine, is for a regatta and I need to count the
laps made around a single buoy for each boat.
This info is going to be overlayed on Google Earth where the boats are
being tracked in real-time (a pro bono service ;)).

I'll even throw in a "Thanks to Tom van Tilburg" there if you can help
me with the 8.3 construct! ;)

TIA,

Pedro Doria Meunier
Telf. +351 291 933 006
GSM  +351 915 818 823
Skype: pdoriam

On 07/20/2012 10:21 AM, Tom van Tilburg wrote:
> Just write the 'withs' as seperate queries that create a table or make
> them subqueries.
> Rest is for you to figure out ;-)
>
> P.S. I'd recommend (asking for) upgrading your postgres installation,
> 8.3 is getting old.....
>
> On 20-7-2012 10:59, Pedro Doria Meunier wrote:
>> 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 at postgis.refractions.net
>>>> http://postgis.refractions.net/mailman/listinfo/postgis-users
>>>
>>>
>>>
>>> _______________________________________________
>>> postgis-users mailing list
>>> postgis-users at postgis.refractions.net
>>> http://postgis.refractions.net/mailman/listinfo/postgis-users
>>
>>
>>
>>
>> _______________________________________________
>> postgis-users mailing list
>> postgis-users at postgis.refractions.net
>> http://postgis.refractions.net/mailman/listinfo/postgis-users
>
>
>
> _______________________________________________
> 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/c5830602/attachment.html>