[postgis-users] Need help constructing a query

[Tom van Tilburg]

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

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