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

[Tyler DeWitt]

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