[postgis-users] Need help constructing a query

[pcreso at pcreso.com]

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 <pdoria at netmadeira.com> wrote:

From: Pedro Doria Meunier <pdoria at netmadeira.com>
Subject: [postgis-users] Need help constructing a query
To: "PostGIS Users Discussion" <postgis-users at 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 at postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20120720/e152566f/attachment.html>