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@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
_______________________________________________
postgis-users mailing list
postgis-users@postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users
