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

[Paul Ramsey]

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