<table cellspacing="0" cellpadding="0" border="0" ><tr><td valign="top" style="font: inherit;">You have not just a spatial dataset but a temporal one as well.<br><br>You need to define how near the buoy you consider is going past it (a "pass")<br><br>You could wrap an SQL around this, but if you were to use a script to carry out the query, the logic would be something like:<br><br>1. get the minimum time for the point dataset<br>2. get the time for first point within the required distance from the buoy<br> where the time is > minimum time <br>3. get the time for the next point where the point is > minimum distance<br> (this completes one "pass")<br>4. set the minimum time to this & repeat until you run out of points <br><br>as a very rough shell script (which is how I typically work) off the top of my head & untested:<br><br><span style="font-family: courier,monaco,monospace,sans-serif;"># set min dist to
establish a pass</span><br style="font-family: courier,monaco,monospace,sans-serif;"><span style="font-family: courier,monaco,monospace,sans-serif;">DIST=0.001</span><br style="font-family: courier,monaco,monospace,sans-serif;"><br style="font-family: courier,monaco,monospace,sans-serif;"><span style="font-family: courier,monaco,monospace,sans-serif;"></span><span style="font-family: courier,monaco,monospace,sans-serif;"># get MIN time as timestamp</span><br style="font-family: courier,monaco,monospace,sans-serif;"><span style="font-family: courier,monaco,monospace,sans-serif;">TIME=`</span><span style="font-family: courier,monaco,monospace,sans-serif;">psql -d db -Atc "select min(time) from vessel points;"`</span><br style="font-family: courier,monaco,monospace,sans-serif;"><br><span style="font-family: courier,monaco,monospace,sans-serif;"></span><span style="font-family: courier,monaco,monospace,sans-serif;"># specify buoy
position<br>BOUY=ST_SetSrid(ST_Makepoint(lon,lat),4326)<br></span><br><span style="font-family: courier,monaco,monospace,sans-serif;"># inelegant loop... <br># will not exit at end of data, press CTRL-C when output stops</span><br style="font-family: courier,monaco,monospace,sans-serif;"><span style="font-family: courier,monaco,monospace,sans-serif;">while [ 1 = 1 ] ; do</span><br style="font-family: courier,monaco,monospace,sans-serif;"><span style="font-family: courier,monaco,monospace,sans-serif;"> # get the time of the start of the next pass<br> PASS_TIME=`psql -d db -c "select min(time)<br> from point_table<br style="font-family: courier,monaco,monospace,sans-serif;"></span><span style="font-family:
courier,monaco,monospace,sans-serif;"> where ST_Distance($BOUY,point)<$DIST<br> and time > '$TIME'::timestamp;"`<br><br> # write the time & position for this<br> psql -d db -Atc "select time, ST_Astext(point)<br> from point_table<br> where time='$PASS_TIME'::timestamp;"<br><br> # get time for end of pass <br> # (= min time for search for start of next pass)<br>
TIME=`psql -d db -Atc "select min(time)<br> from point_table<br> where time > $PASS_TIME<br> and </span><span style="font-family: courier,monaco,monospace,sans-serif;">ST_Distance($BOUY,point)>$DIST;"`<br></span><br style="font-family: courier,monaco,monospace,sans-serif;"><span style="font-family: courier,monaco,monospace,sans-serif;">done</span><br style="font-family: courier,monaco,monospace,sans-serif;"><br><br><br><br><br><br>--- On <b>Fri, 7/20/12, Pedro Doria Meunier
<i><pdoria@netmadeira.com></i></b> wrote:<br><blockquote style="border-left: 2px solid rgb(16, 16, 255); margin-left: 5px; padding-left: 5px;"><br>From: Pedro Doria Meunier <pdoria@netmadeira.com><br>Subject: [postgis-users] Need help constructing a query<br>To: "PostGIS Users Discussion" <postgis-users@postgis.refractions.net><br>Date: Friday, July 20, 2012, 6:03 AM<br><br><div id="yiv1027046152">
<div>
<font face="monospace">Hi,<br>
<br>
Need help/pointers on how to best construct the following:<br>
<br>
1 known point in a table. (call it buoy)<br>
<br>
another table containing the history (with a geom column) of where
a particular object (a boat) has been in time/space<br>
<br>
What I need to construct is a query that gives me the count of how
many times that boat has gone past that buoy.<br>
(there *will* be more than 1 rows giving proximity of the object
to the buoy -- need to get rid of those ;) -- since the boat is
slow moving (2-10 knots))<br>
<br>
Btw, both tables' geoms srid=4326.<br>
<br>
Any help highly appreciated ;)<br>
<br>
TIA,<br>
<br>
</font>
<pre class="yiv1027046152moz-signature">--
Pedro Doria Meunier
Telf. +351 291 933 006
GSM +351 917 999 236
Skype: pdoriam</pre>
</div>
</div><br>-----Inline Attachment Follows-----<br><br><div class="plainMail">_______________________________________________<br>postgis-users mailing list<br><a ymailto="mailto:postgis-users@postgis.refractions.net" href="/mc/compose?to=postgis-users@postgis.refractions.net">postgis-users@postgis.refractions.net</a><br><a href="http://postgis.refractions.net/mailman/listinfo/postgis-users" target="_blank">http://postgis.refractions.net/mailman/listinfo/postgis-users</a><br></div></blockquote></td></tr></table>