<html>
<head>
<meta http-equiv="content-type" content="text/html; charset=ISO-8859-1">
</head>
<body bgcolor="#FFFFFF" text="#666666">
<font face="monospace">Ok. followup on progress here<br>
<br>
This query gives me the buoy which I want to count the laps with:<br>
<br>
SELECT ST_Buffer(transform(geometry,32628),50)<br>
FROM <br>
customer_pois <br>
WHERE<br>
label = 'myBuoy'<br>
<br>
This query constructs the line for the boat track:<br>
<br>
SELECT<br>
unit_imei As boat, ST_MakeLine(coordinates) As geom<br>
FROM units_history<br>
WHERE <br>
unit_imei = 'theImei' AND <br>
zulu_timestamp >= '2012-07-18' AND zulu_timestamp <=
'2012-07-19'<br>
GROUP BY unit_imei<br>
<br>
<br>
Now I need to transform Tom's query (compatible with PG 8.4+)<br>
*many thanks to Tom for this elegant approach ;)*<br>
<i>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</i><br>
<br>
Into one (</font><font face="monospace">compatible with PG 8.3 by
means of sub-queries</font><font face="monospace">) performing an
ST_Intersection for those 2 sets<br>
giving me the number of times that line crosses the buffered
point.<br>
<br>
TIA,<br>
<br>
</font>
<pre class="moz-signature" cols="72">--
Pedro Doria Meunier
Telf. +351 291 933 006
GSM +351 915 818 823
Skype: pdoriam</pre>
</body>
</html>