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
