You have not just a spatial dataset but a temporal one as well. You need to define how near the buoy you consider is going past it (a "pass")
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: 1. get the minimum time for the point dataset 2. get the time for first point within the required distance from the buoy where the time is > minimum time 3. get the time for the next point where the point is > minimum distance (this completes one "pass") 4. set the minimum time to this & repeat until you run out of points as a very rough shell script (which is how I typically work) off the top of my head & untested: # set min dist to establish a pass DIST=0.001 # get MIN time as timestamp TIME=`psql -d db -Atc "select min(time) from vessel points;"` # specify buoy position BOUY=ST_SetSrid(ST_Makepoint(lon,lat),4326) # inelegant loop... # will not exit at end of data, press CTRL-C when output stops while [ 1 = 1 ] ; do # get the time of the start of the next pass PASS_TIME=`psql -d db -c "select min(time) from point_table where ST_Distance($BOUY,point)<$DIST and time > '$TIME'::timestamp;"` # write the time & position for this psql -d db -Atc "select time, ST_Astext(point) from point_table where time='$PASS_TIME'::timestamp;" # get time for end of pass # (= min time for search for start of next pass) TIME=`psql -d db -Atc "select min(time) from point_table where time > $PASS_TIME and ST_Distance($BOUY,point)>$DIST;"` done --- On Fri, 7/20/12, Pedro Doria Meunier <pdo...@netmadeira.com> wrote: From: Pedro Doria Meunier <pdo...@netmadeira.com> Subject: [postgis-users] Need help constructing a query To: "PostGIS Users Discussion" <postgis-users@postgis.refractions.net> Date: Friday, July 20, 2012, 6:03 AM Hi, Need help/pointers on how to best construct the following: 1 known point in a table. (call it buoy) another table containing the history (with a geom column) of where a particular object (a boat) has been in time/space What I need to construct is a query that gives me the count of how many times that boat has gone past that buoy. (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)) Btw, both tables' geoms srid=4326. Any help highly appreciated ;) TIA, -- Pedro Doria Meunier Telf. +351 291 933 006 GSM +351 917 999 236 Skype: pdoriam -----Inline Attachment Follows----- _______________________________________________ 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