Hi Everyone<br><br>Thank you!!<br>That was very helpful, I might be able to pull it off.<br>I'm still struggling to implement it but will report back when I get a result.<br>If anyone else has more suggestions I'm all ears.<br>
<br>Masanao Y<br><br><div class="gmail_quote">On Wed, Oct 7, 2009 at 12:42 AM, Suhr, Ralf <span dir="ltr"><<a href="mailto:Ralf.Suhr@itc-halle.de">Ralf.Suhr@itc-halle.de</a>></span> wrote:<br><blockquote class="gmail_quote" style="border-left: 1px solid rgb(204, 204, 204); margin: 0pt 0pt 0pt 0.8ex; padding-left: 1ex;">
I think the best solution a join from the inersect line and the gps-points group by min/max time. Because the intersect line havn't M-values and the first and the last point from line haven't corrospondending gps-points.<br>
<br>
Gr Ralf<br>
<br>
-----Ursprüngliche Nachricht-----<br>
Von: <a href="mailto:postgis-users-bounces@postgis.refractions.net">postgis-users-bounces@postgis.refractions.net</a> [mailto:<a href="mailto:postgis-users-bounces@postgis.refractions.net">postgis-users-bounces@postgis.refractions.net</a>] Im Auftrag von Simon Greener<br>
Gesendet: Mittwoch, 7. Oktober 2009 07:38<br>
An: PostGIS Users Discussion<br>
Betreff: Re: [postgis-users] PostGIS to calculate time spent within polygon<br>
<div><div></div><div class="h5"><br>
I haven't used the Linear referencing funcitons of PostGIS but perhaps this might help.<br>
<br>
First find the intersection points of the measured line and the polygon using ST_Intersection. Don't worry about loosing the M in the returned data - what you want is the point at which the line enters (ST_StartPoint) and leaves (ST_EndPoint).<br>
<br>
Then use these points and the appropriate Linear referencing functions (<a href="http://postgis.refractions.net/documentation/manual-1.4/ch07.html#Linear_Referencing" target="_blank">http://postgis.refractions.net/documentation/manual-1.4/ch07.html#Linear_Referencing</a>) to compute the time measure values where the line enters the polygon and when it leaves.<br>
<br>
regards<br>
Simon<br>
<br>
<a href="http://postgis.refractions.net/documentation/manual-1.4/ST_Line_Locate_Point.html" target="_blank">http://postgis.refractions.net/documentation/manual-1.4/ST_Line_Locate_Point.html</a><br>
On Wed, 07 Oct 2009 14:19:37 +1100, George Silva <<a href="mailto:georger.silva@gmail.com">georger.silva@gmail.com</a>> wrote:<br>
<br>
> You can select the interval between minimun and maximum timestamp outside<br>
> each polygon. Interpolate to get a value "when entering" the polygon<br>
> (intersection between line and polygon) and when leaving.<br>
><br>
> Then you can subtract both values to get an estimate of time.<br>
><br>
> You probably can write a simple function for this.<br>
><br>
> George<br>
><br>
> On Tue, Oct 6, 2009 at 11:18 PM, Masanao Yajima <<a href="mailto:yajiyajijp@yahoo.co.jp">yajiyajijp@yahoo.co.jp</a>>wrote:<br>
><br>
>> Thank you for your response.<br>
>><br>
>> However, would it be possible to elaborate on the 2nd part?<br>
>> If my reading of the manual is correct,<br>
>> I believe st_intersection is not implemented to handle the 4th dimension.<br>
>><br>
>> Masanao Yajima<br>
>><br>
>> On Tue, Oct 6, 2009 at 6:47 AM, Suhr, Ralf <<a href="mailto:Ralf.Suhr@itc-halle.de">Ralf.Suhr@itc-halle.de</a>> wrote:<br>
>><br>
>>> Hello Masanao Yajima,<br>
>>><br>
>>><br>
>>><br>
>>> for db-design you can go with ogr2ogr (GPX -> PostGIS). Ogr2ogr creates<br>
>>> point and line geometrys from gpx files. In case of linestring you have to<br>
>>> add the 4. Dimension from the point table.<br>
>>><br>
>>> A optimal query would be the intersection from gps-linestrings and your<br>
>>> polygon area. The result can be many sublinestrings with start and end time.<br>
>>><br>
>>><br>
>>><br>
>>> Gr Ralf<br>
>>><br>
>>><br>
>>><br>
>>> *Von:* <a href="mailto:postgis-users-bounces@postgis.refractions.net">postgis-users-bounces@postgis.refractions.net</a> [mailto:<br>
>>> <a href="mailto:postgis-users-bounces@postgis.refractions.net">postgis-users-bounces@postgis.refractions.net</a>] *Im Auftrag von *Masanao<br>
>>> Yajima<br>
>>> *Gesendet:* Montag, 5. Oktober 2009 23:03<br>
>>> *An:* <a href="mailto:postgis-users@postgis.refractions.net">postgis-users@postgis.refractions.net</a><br>
>>> *Betreff:* [postgis-users] PostGIS to calculate time spent within polygon<br>
>>><br>
>>><br>
>>><br>
>>> Dear PostGIS users<br>
>>><br>
>>> I am new to PostGIS and I would like expert opinion on how to deal with my<br>
>>> situation.<br>
>>><br>
>>> I have 2D GPS data and time associated with each points and they<br>
>>> represents line of a trip.<br>
>>> I also have few geographic polygon data representing some area on a map.<br>
>>><br>
>>> what I want to get out is length of time that is spent within polygon area<br>
>>> for a trip.<br>
>>><br>
>>> What is the optimal way to design a database and what kind of query would<br>
>>> you use to get such a number.<br>
>>><br>
>>> One constraint is the time interval for GPS is not accurate so sampling<br>
>>> rate vary alot with in a trip.<br>
>>><br>
>>> I thought of using time as 4th dimension but I believe st_intersection is<br>
>>> not implemented on the 4th dimension..<br>
>>><br>
>>> I could do this by bruit force but I am looking for something fast.<br>
>>><br>
>>> I appreciate your help.<br>
>>><br>
>>> --<br>
>>> Masanao Yajima<br>
>>><br>
>>> _______________________________________________<br>
>>> postgis-users mailing list<br>
>>> <a href="mailto:postgis-users@postgis.refractions.net">postgis-users@postgis.refractions.net</a><br>
>>> <a href="http://postgis.refractions.net/mailman/listinfo/postgis-users" target="_blank">http://postgis.refractions.net/mailman/listinfo/postgis-users</a><br>
>>><br>
>>><br>
>><br>
>><br>
>> --<br>
>> Masanao Yajima<br>
>><br>
>> _______________________________________________<br>
>> postgis-users mailing list<br>
>> <a href="mailto:postgis-users@postgis.refractions.net">postgis-users@postgis.refractions.net</a><br>
>> <a href="http://postgis.refractions.net/mailman/listinfo/postgis-users" target="_blank">http://postgis.refractions.net/mailman/listinfo/postgis-users</a><br>
>><br>
>><br>
><br>
><br>
<br>
<br>
--<br>
SpatialDB Advice and Design, Solutions Architecture and Programming,<br>
Oracle Database 10g Administrator Certified Associate; Oracle Database 10g SQL Certified Professional<br>
Oracle Spatial, SQL Server, PostGIS, MySQL, ArcSDE, Manifold GIS, FME, Radius Topology and Studio Specialist.<br>
39 Cliff View Drive, Allens Rivulet, 7150, Tasmania, Australia.<br>
Website: <a href="http://www.spatialdbadvisor.com" target="_blank">www.spatialdbadvisor.com</a><br>
Email: <a href="mailto:simon@spatialdbadvisor.com">simon@spatialdbadvisor.com</a><br>
Voice: +61 362 396397<br>
Mobile: +61 418 396391<br>
Skype: sggreener<br>
Longitude: 147.20515 (147° 12' 18" E)<br>
Latitude: -43.01530 (43° 00' 55" S)<br>
GeoHash: r22em9r98wg<br>
NAC:W80CK 7SWP3<br>
_______________________________________________<br>
postgis-users mailing list<br>
<a href="mailto:postgis-users@postgis.refractions.net">postgis-users@postgis.refractions.net</a><br>
<a href="http://postgis.refractions.net/mailman/listinfo/postgis-users" target="_blank">http://postgis.refractions.net/mailman/listinfo/postgis-users</a><br>
_______________________________________________<br>
postgis-users mailing list<br>
<a href="mailto:postgis-users@postgis.refractions.net">postgis-users@postgis.refractions.net</a><br>
<a href="http://postgis.refractions.net/mailman/listinfo/postgis-users" target="_blank">http://postgis.refractions.net/mailman/listinfo/postgis-users</a><br>
</div></div></blockquote></div><br><br clear="all"><br>-- <br>Masanao Yajima<br>