[postgis-users] Check If Point Is In New Geometry
Brent Wood
pcreso at pcreso.com
Tue Oct 29 10:22:37 PDT 2013
Hi Tyler,
ST_Dump() returns an array of the components of a geometrycollection.
by adding the suffix ".geom" we are just retrieving the geomtery value in the array, which can be stuck in a geometry column.
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?
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.
A trigger to add a polygon_id to each gps point as it is inserted might be useful, perhaps extended to:
check the polygon compared with the previous point for that user and
if the same, update the user/polygon trackline
if different, check if it is the first point this time in the polygon, if not then create/update the user/polygon trackline
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...
Generating these as required for reporting might take some time, but should have less impact on your data capture process.
Cheers,
Brent
________________________________
From: Tyler DeWitt <tyler.scott.dewitt at gmail.com>
To: "postgis-users at lists.osgeo.org" <postgis-users at lists.osgeo.org>
Cc: Brent Wood <pcreso at pcreso.com>
Sent: Wednesday, October 30, 2013 4:45 AM
Subject: Re: [postgis-users] Check If Point Is In New Geometry
Hi Brent,
I'm putting this back on the list so people can see our continued discussion.
I'm confused by what ST_Dump() does in this example. Could you explain a little more what it is doing?
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.
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?".
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?
Thanks,
Tyler
On Oct 28, 2013, at 1:22 PM, Brent Wood <pcreso at pcreso.com> wrote:
Hi Tyler,
>
>I missed out pasting this bit in the last email:
>
># add a record identifier for grouping by later
>psql -d gps -c "alter table segments
> add column id serial primary key;"
>
>
>just before the last SQL.
>
>
>This does not do everything you might need, but I figured it might provide some ideas...
>
>
>The timestamp of the last point was indeed the same, but it was outside the polygon - to check the spatial & temporal filters work OK.
>
>
>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. 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.
>
>
>
># get intersections
>psql -d gps -c "create table segments as
> SELECT t.user_id,
> t.track_date,
>
p.name,
> (ST_Dump(ST_Intersection(t.trackline, p.poly))).geom as segment
> FROM trackline t,
> polygons p;"
>
># add a record identifier for grouping by later
>psql -d gps -c "alter table segments
> add column id serial primary key;"
>
>
># get time of arrival & no minutes in each polygon
>psql -d gps -c "SELECT
s.id,
> 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;"
>
>
>
>
>
>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...
>
>
>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 :-)
>
>
>Brent
>
>
>
>
>________________________________
> From: Tyler DeWitt <tyler.scott.dewitt at gmail.com>
>To: Brent Wood <pcreso at pcreso.com>
>Sent: Tuesday, October 29, 2013 3:26 AM
>Subject: Re: [postgis-users] Check If Point Is In New Geometry
>
>
>
>Brent,
>
>
>I had to make a slight change to the script:
>
>
># 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.segment,
> s.user_id,
> s.name,
> s.track_date
> ORDER BY arrival_time;"
>
>
>The GROUP BY included a reference to s.id, which doesn't exist. I think you meant s.segment?
>
>
>
>
>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.
>
>
>Here are the points I added:
>insert into gps_points values
> (default,
> 1,
> '2013-10-01 00:00:30'::timestamp,
> ST_SetSRID(ST_MakePoint(0.01,0.01),4326));
>
>
>insert into gps_points values
> (default,
> 1,
> '2013-10-01 00:00:31'::timestamp,
> ST_SetSRID(ST_MakePoint(0.02,0.02),4326));
>
>
>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).
>
>
>Thanks,
>Tyler
>
>
>On Oct 27, 2013, at 7:46 PM, Brent Wood <pcreso at pcreso.com> wrote:
>
>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/20131029/330b10bf/attachment.html>
More information about the postgis-users
mailing list