[postgis-users] Check If Point Is In New Geometry

[Tyler DeWitt]

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 <pramsey at cleverelephant.ca> 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
> <tyler.scott.dewitt at gmail.com> 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
>> postgis-users at lists.osgeo.org
>> http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users
> _______________________________________________
> postgis-users mailing list
> postgis-users at lists.osgeo.org
> http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users