[postgis-users] Need help constructing a query

[Pedro Doria Meunier]

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


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