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

[Brent Wood]

Tyler,

Convert your 24 hours worth of points to a linestring
Clip the linestring by your polygons to generate separate lines per polygon per entry tinto the polygon
Count the vertices in each linestring to determine how long each period in each polygon was.

The following shell script might give you some ideas... it works fine here, but is a bit simplistic in terms of input data...


Cheers,

   Brent Wood



#! /bin/bash
# gps points to track, clipped by polygons, B Wood, Oct 2013

# create new database to test
dropdb gps
createdb gps
psql -d gps -c "create extension postgis;" 

# create table with some gps points
psql -d gps -c "create table gps_points
                (id         serial primary key,
                 user_id    integer,
                 point_time timestamp,
                 gps_point  geometry(point,4326));"

psql -d gps -c "insert into gps_points values
                (default,
                 1,
                 '2013-10-01 00:00:00'::timestamp,
                 ST_SetSRID(ST_MakePoint(0.0,0.0),4326));" 
psql -d gps -c "insert into gps_points values
                (default,
                 1,
                 '2013-10-01 00:00:01'::timestamp,
                 ST_SetSRID(ST_MakePoint(0.01,0.01),4326));" 
psql -d gps -c "insert into gps_points values
                (default,
                 1,
                 '2013-10-01 00:00:02'::timestamp,
                 ST_SetSRID(ST_MakePoint(0.02,0.02),4326));" 
psql -d gps -c "insert into gps_points values
                (default,
                 1,
                 '2013-10-01 00:00:03'::timestamp,
                 ST_SetSRID(ST_MakePoint(0.03,0.03),4326));" 
psql -d gps -c "insert into gps_points values
                (default,
                 1,
                 '2013-10-01 00:00:04'::timestamp,
                 ST_SetSRID(ST_MakePoint(0.04,0.04),4326));" 
psql -d gps -c "insert into gps_points values
                (default,
                 1,
                 '2013-10-01 00:00:05'::timestamp,
                 ST_SetSRID(ST_MakePoint(0.05,0.05),4326));" 
psql -d gps -c "insert into gps_points values
                (default,
                 1,
                 '2013-10-01 00:00:06'::timestamp,
                 ST_SetSRID(ST_MakePoint(0.06,0.06),4326));" 
psql -d gps -c "insert into gps_points values
                (default,
                 1,
                 '2013-10-01 00:00:07'::timestamp,
                 ST_SetSRID(ST_MakePoint(0.07,0.07),4326));" 
psql -d gps -c "insert into gps_points values
                (default,
                 1,
                 '2013-10-01 00:00:08'::timestamp,
                 ST_SetSRID(ST_MakePoint(0.08,0.08),4326));" 
psql -d gps -c "insert into gps_points values
                (default,
                 1,
                 '2013-10-01 00:00:09'::timestamp,
                 ST_SetSRID(ST_MakePoint(0.09,0.09),4326));" 

psql -d gps -c "insert into gps_points values
                (default,
                 1,
                 '2013-10-01 00:00:10'::timestamp,
                 ST_SetSRID(ST_MakePoint(0.10,0.10),4326));" 

psql -d gps -c "insert into gps_points values
                (default,
                 1,
                 '2013-10-01 00:00:11'::timestamp,
                 ST_SetSRID(ST_MakePoint(0.11,0.11),4326));" 

psql -d gps -c "insert into gps_points values
                (default,
                 1,
                 '2013-10-02 00:00:10'::timestamp,
                 ST_SetSRID(ST_MakePoint(0.12,0.12),4326));" 


# create table & some polygons
psql -d gps -c "create table polygons
                ( id     serial primary key,
                  name   varchar(10),
                  poly   geometry(POLYGON,4326));"

psql -d gps -c "insert into polygons values
                (default,
                 'poly1',
                 ST_PolygonFromText(
  'POLYGON((0.0 0.0, 0.0 0.05, 0.05 0.05, 0.05 0.0, 0.0 0.0))',4326));"

psql -d gps -c "insert into polygons values
                (default,
                 'poly2',
                 ST_PolygonFromText(
  'POLYGON((0.05 0.05, 0.05 0.1, 0.1 0.1, 0.1 0.05, 0.05 0.05))',4326));"


# query to get trackline
psql -d gps -c "create table trackline as 
                SELECT gps.user_id, 
                       gps.point_time::date as track_date, 
                       ST_MakeLine(gps.gps_point) as trackline
                       FROM (SELECT user_id, gps_point, point_time 
                             FROM gps_points
                             WHERE user_id = 1
                               AND point_time::date = '2013-10-01'::date
                             ORDER BY point_time) as gps
                       GROUP BY gps.user_id,
                                track_date;"

# get intersections
psql -d gps -c "create table segments as
                SELECT t.user_id,
                       t.track_date,
                       p.name,
                       ST_Intersection(t.trackline, p.poly) as segment
                FROM trackline t,
                     polygons p;"
# get time of arrival & no minutes in each polygon
psql -d gps -c "SELECT s.user_id,
                       s.name,
                       s.track_date,
                       min(g.point_time) as arrival_time,
                       (ST_NPoints(segment)/60.0)::decimal(6,2) as minutes
                FROM segments s,
                     gps_points g
                WHERE ST_intersects(g.gps_point,s.segment)
                GROUP BY s.id,
                         s.user_id,
                         s.name,
                         s.track_date
                ORDER BY arrival_time;"







________________________________
 From: Tyler DeWitt <tyler.scott.dewitt at gmail.com>
To: postgis-users at lists.osgeo.org 
Sent: Monday, October 28, 2013 7:52 AM
Subject: [postgis-users] Check If Point Is In New Geometry
 

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
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20131027/ec4c40d6/attachment.html>