<html><body><div style="color:#000; background-color:#fff; font-family:times new roman, new york, times, serif;font-size:12pt"><div><span>Hi 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>ST_Dump() returns an array of the components of a geometrycollection.</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>by adding the suffix ".geom" we are just retrieving the geomtery value in the array, which can be stuck in a geometry column.</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>A view will ensure the gps tracklines remain current, but will incur substantial processing overhead every time you query one, and you'll potentially be generating many thousands of records per query... do you create a view that provides a 24 hr trackline for every user for every day in the dataset? How many users & years of data will you be managing in the database?</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>Without in depth knowlegde of your exact system, I think a query creating the geometries you need from the points when you need them is likely to bea better option - but I could be wrong.</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>A trigger to add a polygon_id to each gps point as it is inserted might be useful, perhaps extended to:</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>check the polygon compared with the previous point for that user and</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>if the same, update the user/polygon trackline<br></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>if different, check if it is the first point this time in the polygon, if not then create/update the user/polygon trackline <br></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;"><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>While this might create some load while capturing data, it does generate all the tracklines you need to generate the reports you want, automatically & in near real time. Note that any case of a user who enters a neighbouring polygon for just one point will not create a trackline, as one point cannot be a trackline. The solution here would be to create the trackline from the lat point in the previos polygon to the first point in the next, then clip it to the polygon. Such tracks will have at least three points. But yet more real time processing...</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>Generating these as required for reporting might take some time, but should have less impact on your data capture process. <br></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>Cheers,</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> Brent<br></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> </span></div><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" <postgis-users@lists.osgeo.org> <br><b><span style="font-weight: bold;">Cc:</span></b> Brent Wood <pcreso@pcreso.com> <br> <b><span style="font-weight: bold;">Sent:</span></b> Wednesday, October 30, 2013 4:45 AM<br> <b><span style="font-weight: bold;">Subject:</span></b> Re: [postgis-users]
Check If Point Is In New Geometry<br> </font> </div> <div class="y_msg_container"><br><div id="yiv3757188333"><div>Hi Brent,<div><br clear="none"></div><div>I'm putting this back on the list so people can see our continued discussion.</div><div><br clear="none"></div><div>I'm confused by what ST_Dump() does in this example. Could you explain a little more what it is doing?</div><div><br clear="none"></div><div>I agree with you and Paul that windowing might be my best approach. I'm looking at how I might accomplish my goals with windowing now. Do you have much experience windowing? It might be useful to talk about some ideas.</div><div><br clear="none"></div><div>As far as points not extending to the boundaries, I'm not too concerned. I actually collect 10 points a second and a couple inches here and there is not going to affect me. I just need to know big picture, "Which zones are a person in?".</div><div><br
clear="none"></div><div>Also, do you think it would be useful to turn the segments and line tracks into views instead of tables so that they are updated as someone adds/removes points from the gps_tracks table?</div><div><br clear="none"></div><div>Thanks,</div><div>Tyler</div><div class="yiv3757188333yqt7961057668" id="yiv3757188333yqt25494"><div><br clear="none"><div><div>On Oct 28, 2013, at 1:22 PM, Brent Wood <<a rel="nofollow" shape="rect" ymailto="mailto:pcreso@pcreso.com" target="_blank" href="mailto:pcreso@pcreso.com">pcreso@pcreso.com</a>> wrote:</div><br class="yiv3757188333Apple-interchange-newline" clear="none"><blockquote type="cite"><div style="background-color:rgb(255, 255, 255);font-family:'times new roman', 'new york', times, serif;font-size:12pt;">Hi Tyler,<br clear="none"><br clear="none">I missed out pasting this bit in the last email:<br clear="none"><br clear="none"># add a record identifier for grouping by later<br
clear="none">psql -d gps -c "alter table segments<br clear="none"> add column id serial primary key;"<br clear="none"><br clear="none"><div><span>just before the last SQL.</span></div><div style="font-size:16px;font-family:'times new roman', 'new york', times, serif;background-color:transparent;font-style:normal;"><br clear="none"><span></span></div><div style="font-size:16px;font-family:'times new roman', 'new york', times, serif;background-color:transparent;font-style:normal;"><span>This does not do everything you might need, but I figured it might provide some ideas...</span></div><div style="font-size:16px;font-family:'times new roman', 'new york', times, serif;background-color:transparent;font-style:normal;"><br clear="none"><span></span></div><div style="font-size:16px;font-family:'times new roman', 'new york', times,
serif;background-color:transparent;font-style:normal;"><span>The timestamp of the last point was indeed the same, but it was outside the polygon - to check the spatial & temporal filters work OK.</span></div><div style="font-size:16px;font-family:'times new roman', 'new york', times, serif;background-color:transparent;font-style:normal;"><br clear="none"><span></span></div><div style="font-size:16px;font-family:'times new roman', 'new york', times, serif;background-color:transparent;font-style:normal;"><span>I have changed the SQL to generate the segments - it now saves the output from ST_Dump() so each traversal of a polygon is a distinct linestring. </span>This has a few issues in this context
as you can see... segments are clipped where they cross each other to creatre only simple linestrings - which is not what you want.<br clear="none"></div><div style="font-size:16px;font-family:'times new roman', 'new york', times, serif;background-color:transparent;font-style:normal;"><br clear="none"></div><div style="font-size:16px;font-family:'times new roman', 'new york', times, serif;background-color:transparent;font-style:normal;"># get intersections<br clear="none">psql -d gps -c "create table segments as<br clear="none"> SELECT t.user_id,<br clear="none"> t.track_date,<br clear="none">
p.name,<br clear="none"> (ST_Dump(ST_Intersection(t.trackline, p.poly))).geom as segment<br clear="none"> FROM trackline t,<br clear="none"> polygons p;"<br clear="none"><br clear="none"># add a record identifier for grouping by later<br clear="none">psql -d gps -c "alter table segments<br clear="none"> add column id serial primary key;"<br clear="none"><br clear="none"></div><div style="font-size:16px;font-family:'times new roman', 'new york', times, serif;background-color:transparent;font-style:normal;"># get time
of arrival & no minutes in each polygon<br clear="none">psql -d gps -c "SELECT
s.id,<br clear="none"> s.user_id,<br clear="none"> s.name,<br clear="none"> s.track_date,<br clear="none"> min(g.point_time) as arrival_time,<br clear="none"> (ST_NPoints(segment)/60.0)::decimal(6,2) as minutes<br
clear="none"> FROM segments
s,<br clear="none"> gps_points g<br clear="none"> WHERE ST_intersects(g.gps_point,s.segment)<br clear="none"> GROUP BY s.id,<br clear="none"> s.user_id,<br clear="none"> s.name,<br clear="none"> s.track_date<br
clear="none"> ORDER BY
arrival_time;"<br clear="none"></div><div style="font-size:16px;font-family:'times new roman', 'new york', times, serif;background-color:transparent;font-style:normal;"><br clear="none"></div><div style="font-size:16px;font-family:'times new roman', 'new york', times, serif;background-color:transparent;font-style:normal;"><br clear="none"></div><div style="font-size:16px;font-family:'times new roman', 'new york', times, serif;background-color:transparent;font-style:normal;">I had hoped that using generic Postgis functions in this way might be able to do what you want... but I'm beginning to think they will not, and a function to navigate along the points in the 24hr trackline building up the segment as you go might be more robust. One issue here is that such segments will start & end at the first/last points in each polygon rather than extending to the polygon boundaries. As Paul suggested - windowing might work...</div><div
style="font-size:16px;font-family:'times new roman', 'new york', times, serif;background-color:transparent;font-style:normal;"><br clear="none"></div><div style="font-size:16px;font-family:'times new roman', 'new york', times, serif;background-color:transparent;font-style:normal;">I have worked with vessel (at sea) gps data in postgis for several years now, and have never trid to do quite what you are :-)</div><div style="font-size:16px;font-family:'times new roman', 'new york', times, serif;background-color:transparent;font-style:normal;"><br clear="none"></div><div style="font-size:16px;font-family:'times new roman', 'new york', times, serif;background-color:transparent;font-style:normal;">Brent<br clear="none"><span></span></div><div><br clear="none"></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 <<a rel="nofollow" shape="rect" ymailto="mailto:tyler.scott.dewitt@gmail.com" target="_blank" href="mailto:tyler.scott.dewitt@gmail.com">tyler.scott.dewitt@gmail.com</a>><br clear="none"> <b><span style="font-weight:bold;">To:</span></b> Brent Wood <<a rel="nofollow" shape="rect" ymailto="mailto:pcreso@pcreso.com" target="_blank" href="mailto:pcreso@pcreso.com">pcreso@pcreso.com</a>> <br clear="none"> <b><span style="font-weight:bold;">Sent:</span></b> Tuesday, October 29, 2013 3:26 AM<br clear="none"> <b><span style="font-weight:bold;">Subject:</span></b> Re: [postgis-users] Check If Point Is In New Geometry<br clear="none"> </font> </div> <div class="yiv3757188333y_msg_container"><br clear="none"><div id="yiv3757188333">Brent,<div><br clear="none"></div><div>I had to make a slight change to the script:</div><div><br clear="none"></div><div><span
style="font-family:'times new roman', 'new york', times, serif;font-size:16px;background-color:rgb(255, 255, 255);"># get time of arrival & no minutes in each polygon</span><br style="font-family:'times new roman', 'new york', times, serif;font-size:16px;" clear="none"><span style="font-family:'times new roman', 'new york', times, serif;font-size:16px;background-color:rgb(255, 255, 255);">psql -d gps -c "SELECT s.user_id,</span><br style="font-family:'times new roman', 'new york', times, serif;font-size:16px;" clear="none"><span style="font-family:'times new roman', 'new york', times, serif;font-size:16px;background-color:rgb(255, 255, 255);"> s.name,</span><br style="font-family:'times new roman', 'new york', times, serif;font-size:16px;" clear="none"><span style="font-family:'times new roman', 'new york', times,
serif;font-size:16px;background-color:rgb(255, 255, 255);"> s.track_date,</span><br style="font-family:'times new roman', 'new york', times, serif;font-size:16px;" clear="none"><span style="font-family:'times new roman', 'new york', times, serif;font-size:16px;background-color:rgb(255, 255, 255);"> min(g.point_time) as arrival_time,</span><br style="font-family:'times new roman', 'new york', times, serif;font-size:16px;" clear="none"><span style="font-family:'times new roman', 'new york', times, serif;font-size:16px;background-color:rgb(255, 255, 255);">
(ST_NPoints(segment)/60.0)::decimal(6,2) as minutes</span><br style="font-family:'times new roman', 'new york', times, serif;font-size:16px;" clear="none"><span style="font-family:'times new roman', 'new york', times, serif;font-size:16px;background-color:rgb(255, 255, 255);"> FROM segments s,</span><br style="font-family:'times new roman', 'new york', times, serif;font-size:16px;" clear="none"><span style="font-family:'times new roman', 'new york', times, serif;font-size:16px;background-color:rgb(255, 255, 255);"> gps_points g</span><br style="font-family:'times new roman', 'new york', times, serif;font-size:16px;" clear="none"><span style="font-family:'times new roman', 'new york', times, serif;font-size:16px;background-color:rgb(255, 255,
255);"> WHERE ST_intersects(g.gps_point,s.segment)</span><br style="font-family:'times new roman', 'new york', times, serif;font-size:16px;" clear="none"><span style="font-family:'times new roman', 'new york', times, serif;font-size:16px;background-color:rgb(255, 255, 255);"> GROUP BY s.segment,</span><br style="font-family:'times new roman', 'new york', times, serif;font-size:16px;" clear="none"><span style="font-family:'times new roman', 'new york', times, serif;font-size:16px;background-color:rgb(255, 255, 255);"> s.user_id,</span><br style="font-family:'times new roman', 'new york', times, serif;font-size:16px;" clear="none"><span
style="font-family:'times new roman', 'new york', times, serif;font-size:16px;background-color:rgb(255, 255, 255);">
s.name,</span><br style="font-family:'times new roman', 'new york', times, serif;font-size:16px;" clear="none"><span style="font-family:'times new roman', 'new york', times, serif;font-size:16px;background-color:rgb(255, 255, 255);"> s.track_date</span><br style="font-family:'times new roman', 'new york', times, serif;font-size:16px;" clear="none"><span style="font-family:'times new roman', 'new york', times, serif;font-size:16px;background-color:rgb(255, 255, 255);"> ORDER BY arrival_time;"</span></div><div><font face="times new roman, new york, times, serif"><br clear="none"></font></div><div><font face="times new roman, new york, times, serif">The GROUP
BY included a reference to s.id, which doesn't exist. I think you meant s.segment?</font></div><div><font face="times new roman, new york, times, serif"><br clear="none"></font></div><div><br clear="none"></div><div><font face="times new roman, new york, times, serif">With that change, the query runs and I get the correct time spent in each zone, but when I tried adding more points to poly1 (after poly2), I am not getting a separate segment, it just adds onto the poly1 segment, so even though the points travel from poly1 to poly2 and back to poly1, I am only seeing 2 segments.</font></div><div><font face="times new roman, new york, times, serif"><br clear="none"></font></div><div><font face="times new roman, new york, times, serif">Here are the points I
added:</font></div><div><font face="times new roman, new york, times, serif"></font><div><span style="font-size:16px;">insert into gps_points values</span></div><div><span style="font-size:16px;"> (default,</span></div><div><span style="font-size:16px;"> 1,</span></div><div><span style="font-size:16px;"> '2013-10-01 00:00:30'::timestamp,</span></div><div><span style="font-size:16px;"> ST_SetSRID(ST_MakePoint(0.01,0.01),4326));</span></div><div><span style="font-size:16px;"><br clear="none"></span></div><div><span style="font-size:16px;">insert into gps_points values</span></div><div><span style="font-size:16px;"> (default,</span></div><div><span
style="font-size:16px;"> 1,</span></div><div><span style="font-size:16px;"> '2013-10-01 00:00:31'::timestamp,</span></div><div><span style="font-size:16px;"> ST_SetSRID(ST_MakePoint(0.02,0.02),4326));</span></div><div><span style="font-size:16px;"><br clear="none"></span></div><div><div style="font-family:Helvetica;"><font face="times new roman, new york, times, serif">Also, the last gps_point you inserted had the same timestamp as another point, so I changed its time (don't think that has any real affect).</font></div></div><div><font face="times new roman, new york, times, serif"><br clear="none"></font></div><div><font face="times new roman, new york, times, serif">Thanks,</font></div><div><font face="times new roman, new york, times,
serif">Tyler</font></div><div><span style="font-size:16px;"><br clear="none"></span></div><div class="yiv3757188333yqt1806229823" id="yiv3757188333yqt64106"><div>On Oct 27, 2013, at 7:46 PM, Brent Wood <<a rel="nofollow" shape="rect" ymailto="mailto:pcreso@pcreso.com" target="_blank" href="mailto:pcreso@pcreso.com">pcreso@pcreso.com</a>> wrote:</div><br class="yiv3757188333Apple-interchange-newline" clear="none"><blockquote type="cite"><div style="background-color:rgb(255, 255, 255);font-family:'times new roman', 'new york', times, serif;font-size:12pt;"><div><span>Tyler,</span></div><div style="font-size:16px;font-family:'times new roman', 'new york', times, serif;background-color:transparent;font-style:normal;"><br clear="none"><span></span></div><div style="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="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="yiv3757188333yui_3_7_2_30_1382895260656_148" style="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="yiv3757188333yui_3_7_2_30_1382895260656_148" style="font-size:16px;font-family:'times new roman', 'new york', times, serif;background-color:transparent;font-style:normal;"><br clear="none"><span></span></div><div class="yiv3757188333yui_3_7_2_30_1382895260656_148" style="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 clear="none"><span></span></div><div class="yiv3757188333yui_3_7_2_30_1382895260656_148" style="font-size:16px;font-family:'times new roman', 'new york', times, serif;background-color:transparent;font-style:normal;"><br clear="none"><span></span></div><div class="yiv3757188333yui_3_7_2_30_1382895260656_148" style="font-size:16px;font-family:'times new roman', 'new york', times, serif;background-color:transparent;font-style:normal;">Cheers,</div><div class="yiv3757188333yui_3_7_2_30_1382895260656_148" style="font-size:16px;font-family:'times new roman', 'new york', times, serif;background-color:transparent;font-style:normal;"><br clear="none"></div><div class="yiv3757188333yui_3_7_2_30_1382895260656_148" style="font-size:16px;font-family:'times new roman', 'new york',
times, serif;background-color:transparent;font-style:normal;"> Brent Wood<br clear="none"><span></span></div><div class="yiv3757188333yui_3_7_2_30_1382895260656_148" style="font-size:16px;font-family:'times new roman', 'new york', times, serif;background-color:transparent;font-style:normal;"><br clear="none"><span></span></div><div class="yiv3757188333yui_3_7_2_30_1382895260656_148" style="font-size:16px;font-family:'times new roman', 'new york', times, serif;background-color:transparent;font-style:normal;"><span><br clear="none"></span></div><div class="yiv3757188333yui_3_7_2_30_1382895260656_148" style="font-size:16px;font-family:'times new roman', 'new york', times, serif;background-color:transparent;font-style:normal;">#! /bin/bash<br clear="none"># gps points to track, clipped by polygons, B Wood,
Oct 2013<br clear="none"><br clear="none"># create new database to test<br clear="none">dropdb gps<br clear="none">createdb gps<br clear="none">psql -d gps -c "create extension postgis;" <br clear="none"><br clear="none"># create table with some gps points<br clear="none">psql -d gps -c "create table gps_points<br clear="none"> (id serial primary key,<br clear="none"> user_id
integer,<br clear="none"> point_time timestamp,<br clear="none"> gps_point geometry(point,4326));"<br clear="none"><br clear="none">psql -d gps -c "insert into gps_points values<br clear="none"> (default,<br clear="none"> 1,<br clear="none"> '2013-10-01 00:00:00'::timestamp,<br clear="none"> ST_SetSRID(ST_MakePoint(0.0,0.0),4326));" <br clear="none">psql -d gps -c "insert into gps_points
values<br clear="none">
(default,<br clear="none"> 1,<br clear="none"> '2013-10-01 00:00:01'::timestamp,<br clear="none"> ST_SetSRID(ST_MakePoint(0.01,0.01),4326));" <br clear="none">psql -d gps -c "insert into gps_points values<br clear="none"> (default,<br clear="none"> 1,<br clear="none"> '2013-10-01 00:00:02'::timestamp,<br
clear="none"> ST_SetSRID(ST_MakePoint(0.02,0.02),4326));" <br clear="none">psql -d gps -c "insert into
gps_points values<br clear="none"> (default,<br clear="none"> 1,<br clear="none"> '2013-10-01 00:00:03'::timestamp,<br clear="none"> ST_SetSRID(ST_MakePoint(0.03,0.03),4326));" <br clear="none">psql -d gps -c "insert into gps_points values<br clear="none"> (default,<br clear="none"> 1,<br clear="none">
'2013-10-01
00:00:04'::timestamp,<br clear="none"> ST_SetSRID(ST_MakePoint(0.04,0.04),4326));" <br clear="none">psql -d gps -c "insert into gps_points values<br clear="none"> (default,<br clear="none"> 1,<br clear="none"> '2013-10-01 00:00:05'::timestamp,<br clear="none"> ST_SetSRID(ST_MakePoint(0.05,0.05),4326));" <br clear="none">psql -d gps -c "insert into gps_points values<br clear="none"> (default,<br
clear="none">
1,<br clear="none"> '2013-10-01 00:00:06'::timestamp,<br clear="none"> ST_SetSRID(ST_MakePoint(0.06,0.06),4326));" <br clear="none">psql -d gps -c "insert into gps_points values<br clear="none"> (default,<br clear="none"> 1,<br clear="none"> '2013-10-01 00:00:07'::timestamp,<br clear="none"> ST_SetSRID(ST_MakePoint(0.07,0.07),4326));" <br clear="none">psql -d gps -c "insert into gps_points
values<br clear="none">
(default,<br clear="none"> 1,<br clear="none"> '2013-10-01 00:00:08'::timestamp,<br clear="none"> ST_SetSRID(ST_MakePoint(0.08,0.08),4326));" <br clear="none">psql -d gps -c "insert into gps_points values<br clear="none"> (default,<br clear="none"> 1,<br clear="none"> '2013-10-01 00:00:09'::timestamp,<br
clear="none"> ST_SetSRID(ST_MakePoint(0.09,0.09),4326));" <br clear="none"><br clear="none">psql -d gps -c "insert into
gps_points values<br clear="none"> (default,<br clear="none"> 1,<br clear="none"> '2013-10-01 00:00:10'::timestamp,<br clear="none"> ST_SetSRID(ST_MakePoint(0.10,0.10),4326));" <br clear="none"><br clear="none">psql -d gps -c "insert into gps_points values<br clear="none"> (default,<br clear="none"> 1,<br
clear="none"> '2013-10-01
00:00:11'::timestamp,<br clear="none"> ST_SetSRID(ST_MakePoint(0.11,0.11),4326));" <br clear="none"><br clear="none">psql -d gps -c "insert into gps_points values<br clear="none"> (default,<br clear="none"> 1,<br clear="none"> '2013-10-02 00:00:10'::timestamp,<br clear="none"> ST_SetSRID(ST_MakePoint(0.12,0.12),4326));" <br clear="none"><br clear="none"><br clear="none"># create table & some polygons<br clear="none">psql -d gps -c "create table polygons<br
clear="none"> ( id serial primary
key,<br clear="none"> name varchar(10),<br clear="none"> poly geometry(POLYGON,4326));"<br clear="none"><br clear="none">psql -d gps -c "insert into polygons values<br clear="none"> (default,<br clear="none"> 'poly1',<br clear="none"> ST_PolygonFromText(<br clear="none"> 'POLYGON((0.0 0.0, 0.0 0.05, 0.05 0.05, 0.05 0.0, 0.0 0.0))',4326));"<br clear="none"><br clear="none">psql -d gps -c "insert into polygons values<br
clear="none">
(default,<br clear="none"> 'poly2',<br clear="none"> ST_PolygonFromText(<br clear="none"> 'POLYGON((0.05 0.05, 0.05 0.1, 0.1 0.1, 0.1 0.05, 0.05 0.05))',4326));"<br clear="none"><br clear="none"><br clear="none"># query to get trackline<br clear="none">psql -d gps -c "create table trackline as <br clear="none"> SELECT gps.user_id, <br clear="none"> gps.point_time::date as track_date, <br clear="none">
ST_MakeLine(gps.gps_point) as
trackline<br clear="none"> FROM (SELECT user_id, gps_point, point_time <br clear="none"> FROM gps_points<br clear="none"> WHERE user_id = 1<br clear="none"> AND point_time::date = '2013-10-01'::date<br
clear="none">
ORDER BY point_time) as gps<br clear="none"> GROUP BY gps.user_id,<br clear="none"> track_date;"<br clear="none"><br clear="none"># get intersections<br clear="none">psql -d gps -c "create table segments as<br clear="none"> SELECT t.user_id,<br clear="none"> t.track_date,<br clear="none">
p.name,<br clear="none"> ST_Intersection(t.trackline, p.poly) as segment<br clear="none"> FROM trackline t,<br clear="none"> polygons p;"</div><div class="yiv3757188333yui_3_7_2_30_1382895260656_148" style="font-size:16px;font-family:'times new roman', 'new york', times, serif;background-color:transparent;font-style:normal;"><br clear="none"></div># get time of arrival & no minutes in each polygon<br clear="none">psql -d gps -c "SELECT s.user_id,<br clear="none">
s.name,<br clear="none"> s.track_date,<br clear="none"> min(g.point_time) as arrival_time,<br clear="none"> (ST_NPoints(segment)/60.0)::decimal(6,2) as minutes<br clear="none"> FROM segments s,<br clear="none"> gps_points g<br clear="none"> WHERE
ST_intersects(g.gps_point,s.segment)<br clear="none"> GROUP BY s.id,<br clear="none"> s.user_id,<br clear="none"> s.name,<br clear="none"> s.track_date<br clear="none"> ORDER BY arrival_time;"<br clear="none"><br clear="none"><br clear="none"><br clear="none"><br clear="none"><div><br clear="none"></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 <<a rel="nofollow" shape="rect" ymailto="mailto:tyler.scott.dewitt@gmail.com" target="_blank" href="mailto:tyler.scott.dewitt@gmail.com">tyler.scott.dewitt@gmail.com</a>><br clear="none"> <b><span style="font-weight:bold;">To:</span></b> <a rel="nofollow" shape="rect" ymailto="mailto:postgis-users@lists.osgeo.org" target="_blank" href="mailto:postgis-users@lists.osgeo.org">postgis-users@lists.osgeo.org</a> <br clear="none"> <b><span style="font-weight:bold;">Sent:</span></b> Monday, October 28, 2013 7:52 AM<br clear="none"> <b><span style="font-weight:bold;">Subject:</span></b> [postgis-users] Check If Point Is In New Geometry<br clear="none"> </font> </div> <div class="yiv3757188333y_msg_container"><br
clear="none">I have a table of geometries (with a geom column of type MultiPolygon Z).<br clear="none"><br clear="none">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 clear="none"><br clear="none">I'd like to know when a user (tracked by their point) enters a new/different geometry.<br clear="none"><br clear="none">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 clear="none"><br clear="none"><br clear="none">Thanks,<br clear="none">Tyler<br clear="none"><br clear="none">_______________________________________________<br clear="none">postgis-users mailing list<br clear="none"><a rel="nofollow" shape="rect" ymailto="mailto:postgis-users@lists.osgeo.org" target="_blank" href="mailto:postgis-users@lists.osgeo.org">postgis-users@lists.osgeo.org</a><br clear="none"><a rel="nofollow" shape="rect" target="_blank" href="http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users">http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users</a><br clear="none"><br clear="none"><br clear="none"></div> </div> </div>
</div></blockquote></div><br clear="none"></div></div><br clear="none"><br clear="none"></div> </div> </div> </div></blockquote></div><br clear="none"></div></div></div></div><br><br></div> </div> </div> </div></body></html>