<html>
<head>
<meta content="text/html; charset=ISO-8859-1"
http-equiv="Content-Type">
</head>
<body bgcolor="#FFFFFF" text="#000000">
<div class="moz-cite-prefix">Just write the 'withs' as seperate
queries that create a table or make them subqueries.<br>
Rest is for you to figure out ;-)<br>
<br>
P.S. I'd recommend (asking for) upgrading your postgres
installation, 8.3 is getting old.....<br>
<br>
On 20-7-2012 10:59, Pedro Doria Meunier wrote:<br>
</div>
<blockquote cite="mid:50091E05.8020500@netmadeira.com" type="cite">
<meta content="text/html; charset=ISO-8859-1"
http-equiv="Content-Type">
<div class="moz-cite-prefix"><font size="-1"><font
face="monospace">Elegant :)<br>
<br>
Turns out I'm stuck with an old server running PG 8.3 and
can't use WITH clause :D<br>
Thanks Tom!<br>
</font></font>
<pre class="moz-signature" cols="72">Pedro Doria Meunier
Telf. +351 291 933 006
GSM +351 915 818 823
Skype: pdoriam</pre>
On 07/20/2012 09:03 AM, Tom van Tilburg wrote:<br>
</div>
<blockquote cite="mid:500910B5.9070101@gmail.com" type="cite">
<meta content="text/html; charset=ISO-8859-1"
http-equiv="Content-Type">
<div class="moz-cite-prefix">Hmm, I would still think that, as
long as you have a single line per-boat, this would solve your
problem.<br>
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.<br>
<br>
Try something like:<br>
--------------------<br>
WITH line AS<br>
(<br>
SELECT <br>
gps.gps_track As boat, ST_MakeLine(gps.the_geom ORDER
BY gps_time) As geom<br>
FROM gps_points As gps <br>
GROUP BY gps.gps_track;<br>
)<br>
,intersections AS<br>
(<br>
SELECT boat, (ST_Dump(ST_Intersection(line.geom,
ST_SetSrid(ST_Makepoint(lon,lat),4326)))).geom<br>
)<br>
SELECT<br>
boat, count(boat) As number_of_passes<br>
FROM intersections<br>
WHERE ST_GeometryType(geom) = 'ST_LineString'<br>
GROUP BY boat<br>
--------------------------------<br>
<br>
if your postgres is below 9.0 the gps.tracks might work
different. See manual.<br>
<br>
Cheers,<br>
Tom<br>
<br>
<br>
On 20-7-2012 9:38, Pedro Doria Meunier wrote:<br>
</div>
<blockquote cite="mid:50090AEB.50808@netmadeira.com" type="cite">
<meta content="text/html; charset=ISO-8859-1"
http-equiv="Content-Type">
<div class="moz-cite-prefix">On 07/20/2012 07:38 AM, Tom van
Tilburg wrote:<br>
</div>
<blockquote cite="mid:5008FCF7.9010000@gmail.com" type="cite">
<meta content="text/html; charset=ISO-8859-1"
http-equiv="Content-Type">
<div class="moz-cite-prefix">Hi Pedro,<br>
<br>
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.<br>
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.<br>
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.<br>
<br>
Cheers,<br>
Tom<br>
</div>
<br>
</blockquote>
<br>
<font face="monospace">Elegant thinking but that would end up
in a *single* line, as I only have the timestamp and
(point)geom to work with.<br>
Thanks for your input though :)<br>
</font><br>
<pre class="moz-signature" cols="72">--
Pedro Doria Meunier
Telf. +351 291 933 006
GSM +351 915 818 823
Skype: pdoriam</pre>
<br>
<fieldset class="mimeAttachmentHeader"></fieldset>
<br>
<pre wrap="">_______________________________________________
postgis-users mailing list
<a moz-do-not-send="true" class="moz-txt-link-abbreviated" href="mailto:postgis-users@postgis.refractions.net">postgis-users@postgis.refractions.net</a>
<a moz-do-not-send="true" class="moz-txt-link-freetext" href="http://postgis.refractions.net/mailman/listinfo/postgis-users">http://postgis.refractions.net/mailman/listinfo/postgis-users</a>
</pre>
</blockquote>
<br>
<br>
<fieldset class="mimeAttachmentHeader"></fieldset>
<br>
<pre wrap="">_______________________________________________
postgis-users mailing list
<a moz-do-not-send="true" class="moz-txt-link-abbreviated" href="mailto:postgis-users@postgis.refractions.net">postgis-users@postgis.refractions.net</a>
<a moz-do-not-send="true" class="moz-txt-link-freetext" href="http://postgis.refractions.net/mailman/listinfo/postgis-users">http://postgis.refractions.net/mailman/listinfo/postgis-users</a>
</pre>
</blockquote>
<br>
<br>
<br>
<fieldset class="mimeAttachmentHeader"></fieldset>
<br>
<pre wrap="">_______________________________________________
postgis-users mailing list
<a class="moz-txt-link-abbreviated" href="mailto:postgis-users@postgis.refractions.net">postgis-users@postgis.refractions.net</a>
<a class="moz-txt-link-freetext" href="http://postgis.refractions.net/mailman/listinfo/postgis-users">http://postgis.refractions.net/mailman/listinfo/postgis-users</a>
</pre>
</blockquote>
<br>
</body>
</html>