<html>
<head>
<meta content="text/html; charset=ISO-8859-1"
http-equiv="Content-Type">
</head>
<body bgcolor="#FFFFFF" text="#666666">
<div class="moz-cite-prefix"><font size="-1"><font face="monospace">I'm
going to be extra-cheeky here ... :D<br>
Must be getting old as I can't wrap my head around the 8.3
construct of the query below.. :S<br>
<br>
This, as you might imagine, is for a regatta and I need to
count the laps made around a single buoy for each boat.<br>
This info is going to be overlayed on Google Earth where the
boats are being tracked in real-time (a pro bono service ;)).<br>
<br>
I'll even throw in a "Thanks to Tom van Tilburg" there if you
can help me with the 8.3 construct! ;)<br>
<br>
TIA,<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 10:21 AM, Tom van Tilburg wrote:<br>
</div>
<blockquote cite="mid:50092330.4000503@gmail.com" type="cite">
<meta content="text/html; charset=ISO-8859-1"
http-equiv="Content-Type">
<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 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 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>
<br>
</body>
</html>