I just discovered windowing functions and think that's a great idea, but I can't think of how that would look. You have any idea (in pseudo code) what that might look like?
Thanks, Tyler Sent from my iPhone > On Oct 27, 2013, at 3:12 PM, Paul Ramsey <[email protected]> wrote: > > Assuming all your polygons are non-overlapping (never a safe > assumption) you could, on each point insert, also insert the current > polygon inclusion information. Then if you want real-time tracking of > enter/leave status, add a trigger to add an enter/leave record in an > event table. Or, if you just want retrospective tracking, you could > run a window function (see relevant PgSQL doco on windowing) on the > table, that finds the transitions from state to state. > > P. > > On Sun, Oct 27, 2013 at 11:52 AM, Tyler DeWitt > <[email protected]> wrote: >> I have a table of geometries (with a geom column of type MultiPolygon Z). >> >> I collect real time location data (1 point a second) and store that in a >> tracked_points table (with a geom column of type PointZ, and user_id column >> of type int). >> >> I'd like to know when a user (tracked by their point) enters a new/different >> geometry. >> >> I can use ST_Contains(geometries.geom, tracked_points.geom) to figure out >> which geometry a user is currently in, but I'd like to find an efficient way >> to check a 24 hour period and say "User 1 went from geometry A to geometry B >> to Geometry C and back to Geometry B". I could find how many points a user >> has in each geometry, and therefore how long they spent in each geometry, >> but I don't know about the path the user took. >> >> >> Thanks, >> Tyler >> >> _______________________________________________ >> postgis-users mailing list >> [email protected] >> http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users > _______________________________________________ > postgis-users mailing list > [email protected] > http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users _______________________________________________ postgis-users mailing list [email protected] http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users
