<html><body><div style="color:#000; background-color:#fff; font-family:times new roman, new york, times, serif;font-size:12pt"><div><span>Tyler,</span></div><div style="color: rgb(0, 0, 0); font-size: 16px; font-family: times new roman,new york,times,serif; background-color: transparent; font-style: normal;"><br><span></span></div><div style="color: rgb(0, 0, 0); font-size: 16px; font-family: times new roman,new york,times,serif; background-color: transparent; font-style: normal;"><span>Convert your 24 hours worth of points to a linestring</span></div><div style="color: rgb(0, 0, 0); font-size: 16px; font-family: times new roman,new york,times,serif; background-color: transparent; font-style: normal;"><span>Clip the linestring by your polygons to generate separate lines per polygon per entry tinto the polygon</span></div><div class="yui_3_7_2_30_1382895260656_148" style="color: rgb(0, 0, 0); font-size: 16px; font-family: times new roman,new
york,times,serif; background-color: transparent; font-style: normal;"><span>Count the vertices in each linestring to determine how long each period in each polygon was.</span></div><div class="yui_3_7_2_30_1382895260656_148" style="color: rgb(0, 0, 0); font-size: 16px; font-family: times new roman,new york,times,serif; background-color: transparent; font-style: normal;"><br><span></span></div><div class="yui_3_7_2_30_1382895260656_148" style="color: rgb(0, 0, 0); font-size: 16px; font-family: times new roman,new york,times,serif; background-color: transparent; font-style: normal;">The following shell script might give you some ideas... it works fine here, but is a bit simplistic in terms of input data...<br><span></span></div><div class="yui_3_7_2_30_1382895260656_148" style="color: rgb(0, 0, 0); font-size: 16px; font-family: times new roman,new york,times,serif; background-color: transparent; font-style: normal;"><br><span></span></div><div
class="yui_3_7_2_30_1382895260656_148" style="color: rgb(0, 0, 0); font-size: 16px; font-family: times new roman,new york,times,serif; background-color: transparent; font-style: normal;">Cheers,</div><div class="yui_3_7_2_30_1382895260656_148" style="color: rgb(0, 0, 0); font-size: 16px; font-family: times new roman,new york,times,serif; background-color: transparent; font-style: normal;"><br></div><div class="yui_3_7_2_30_1382895260656_148" style="color: rgb(0, 0, 0); font-size: 16px; font-family: times new roman,new york,times,serif; background-color: transparent; font-style: normal;"> Brent Wood<br><span></span></div><div class="yui_3_7_2_30_1382895260656_148" style="color: rgb(0, 0, 0); font-size: 16px; font-family: times new roman,new york,times,serif; background-color: transparent; font-style: normal;"><br><span></span></div><div class="yui_3_7_2_30_1382895260656_148" style="color: rgb(0, 0, 0); font-size: 16px; font-family: times new
roman,new york,times,serif; background-color: transparent; font-style: normal;"><span><br></span></div><div class="yui_3_7_2_30_1382895260656_148" style="color: rgb(0, 0, 0); font-size: 16px; font-family: times new roman,new york,times,serif; background-color: transparent; font-style: normal;">#! /bin/bash<br># gps points to track, clipped by polygons, B Wood, Oct 2013<br><br># create new database to test<br>dropdb gps<br>createdb gps<br>psql -d gps -c "create extension postgis;" <br><br># create table with some gps points<br>psql -d gps -c "create table gps_points<br> (id serial primary key,<br> user_id
integer,<br> point_time timestamp,<br> gps_point geometry(point,4326));"<br><br>psql -d gps -c "insert into gps_points values<br> (default,<br> 1,<br> '2013-10-01 00:00:00'::timestamp,<br> ST_SetSRID(ST_MakePoint(0.0,0.0),4326));" <br>psql -d gps -c "insert into gps_points values<br>
(default,<br> 1,<br> '2013-10-01 00:00:01'::timestamp,<br> ST_SetSRID(ST_MakePoint(0.01,0.01),4326));" <br>psql -d gps -c "insert into gps_points values<br> (default,<br> 1,<br> '2013-10-01 00:00:02'::timestamp,<br> ST_SetSRID(ST_MakePoint(0.02,0.02),4326));" <br>psql -d gps -c "insert into
gps_points values<br> (default,<br> 1,<br> '2013-10-01 00:00:03'::timestamp,<br> ST_SetSRID(ST_MakePoint(0.03,0.03),4326));" <br>psql -d gps -c "insert into gps_points values<br> (default,<br> 1,<br> '2013-10-01
00:00:04'::timestamp,<br> ST_SetSRID(ST_MakePoint(0.04,0.04),4326));" <br>psql -d gps -c "insert into gps_points values<br> (default,<br> 1,<br> '2013-10-01 00:00:05'::timestamp,<br> ST_SetSRID(ST_MakePoint(0.05,0.05),4326));" <br>psql -d gps -c "insert into gps_points values<br> (default,<br>
1,<br> '2013-10-01 00:00:06'::timestamp,<br> ST_SetSRID(ST_MakePoint(0.06,0.06),4326));" <br>psql -d gps -c "insert into gps_points values<br> (default,<br> 1,<br> '2013-10-01 00:00:07'::timestamp,<br> ST_SetSRID(ST_MakePoint(0.07,0.07),4326));" <br>psql -d gps -c "insert into gps_points values<br>
(default,<br> 1,<br> '2013-10-01 00:00:08'::timestamp,<br> ST_SetSRID(ST_MakePoint(0.08,0.08),4326));" <br>psql -d gps -c "insert into gps_points values<br> (default,<br> 1,<br> '2013-10-01 00:00:09'::timestamp,<br> ST_SetSRID(ST_MakePoint(0.09,0.09),4326));" <br><br>psql -d gps -c "insert into
gps_points values<br> (default,<br> 1,<br> '2013-10-01 00:00:10'::timestamp,<br> ST_SetSRID(ST_MakePoint(0.10,0.10),4326));" <br><br>psql -d gps -c "insert into gps_points values<br> (default,<br> 1,<br> '2013-10-01
00:00:11'::timestamp,<br> ST_SetSRID(ST_MakePoint(0.11,0.11),4326));" <br><br>psql -d gps -c "insert into gps_points values<br> (default,<br> 1,<br> '2013-10-02 00:00:10'::timestamp,<br> ST_SetSRID(ST_MakePoint(0.12,0.12),4326));" <br><br><br># create table & some polygons<br>psql -d gps -c "create table polygons<br> ( id serial primary
key,<br> name varchar(10),<br> poly geometry(POLYGON,4326));"<br><br>psql -d gps -c "insert into polygons values<br> (default,<br> 'poly1',<br> ST_PolygonFromText(<br> 'POLYGON((0.0 0.0, 0.0 0.05, 0.05 0.05, 0.05 0.0, 0.0 0.0))',4326));"<br><br>psql -d gps -c "insert into polygons values<br>
(default,<br> 'poly2',<br> ST_PolygonFromText(<br> 'POLYGON((0.05 0.05, 0.05 0.1, 0.1 0.1, 0.1 0.05, 0.05 0.05))',4326));"<br><br><br># query to get trackline<br>psql -d gps -c "create table trackline as <br> SELECT gps.user_id, <br> gps.point_time::date as track_date, <br> ST_MakeLine(gps.gps_point) as
trackline<br> FROM (SELECT user_id, gps_point, point_time <br> FROM gps_points<br> WHERE user_id = 1<br> AND point_time::date = '2013-10-01'::date<br>
ORDER BY point_time) as gps<br> GROUP BY gps.user_id,<br> track_date;"<br><br># get intersections<br>psql -d gps -c "create table segments as<br> SELECT t.user_id,<br> t.track_date,<br>
p.name,<br> ST_Intersection(t.trackline, p.poly) as segment<br> FROM trackline t,<br> polygons p;"</div><div class="yui_3_7_2_30_1382895260656_148" style="color: rgb(0, 0, 0); font-size: 16px; font-family: times new roman,new york,times,serif; background-color: transparent; font-style: normal;"><br></div># get time of arrival & no minutes in each polygon<br>psql -d gps -c "SELECT s.user_id,<br>
s.name,<br> s.track_date,<br> min(g.point_time) as arrival_time,<br> (ST_NPoints(segment)/60.0)::decimal(6,2) as minutes<br> FROM segments s,<br> gps_points g<br> WHERE
ST_intersects(g.gps_point,s.segment)<br> GROUP BY s.id,<br> s.user_id,<br> s.name,<br> s.track_date<br> ORDER BY arrival_time;"<br><br><br><br><br><div><br></div> <div style="font-family: times new roman, new york, times, serif; font-size: 12pt;"> <div style="font-family: times new roman, new york, times, serif; font-size: 12pt;"> <div dir="ltr"> <hr
size="1"> <font size="2" face="Arial"> <b><span style="font-weight:bold;">From:</span></b> Tyler DeWitt <tyler.scott.dewitt@gmail.com><br> <b><span style="font-weight: bold;">To:</span></b> postgis-users@lists.osgeo.org <br> <b><span style="font-weight: bold;">Sent:</span></b> Monday, October 28, 2013 7:52 AM<br> <b><span style="font-weight: bold;">Subject:</span></b> [postgis-users] Check If Point Is In New Geometry<br> </font> </div> <div class="y_msg_container"><br>I have a table of geometries (with a geom column of type MultiPolygon Z).<br><br>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).<br><br>I'd like to know when a user (tracked by their point) enters a new/different geometry.<br><br>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.<br><br><br>Thanks,<br>Tyler<br><br>_______________________________________________<br>postgis-users mailing list<br><a ymailto="mailto:postgis-users@lists.osgeo.org" href="mailto:postgis-users@lists.osgeo.org">postgis-users@lists.osgeo.org</a><br><a href="http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users" target="_blank">http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users</a><br><br><br></div> </div> </div> </div></body></html>