[postgis-users] Crosses Performance

[Guido Lemoine]

David,

We are doing something similar, but for (fishing) vessel tracks that 
cross statistical
rectangles (1 by 0.5 degree rectangular maritime areas). We need to know 
how long
the vessel stays within a given rectangle. Basically, this requires 
interpolating each
distinct track to get entry and exit times and position. For us, the 
time is important
as well, not just the fact that it is present within a rectangle. We are 
using PLPSQL
functions to do this. This produces a table with total time spent in the 
rectangle, which
we can then plot in MapServer. It's not very fast, but we only have 
several 100s of
tracks max.

Let me know whether this could be of interest.

Guido Lemoine

David Bitner wrote:

> I'm looking at optimizing a query of mine that takes a uniform set of 
> grid cells and determines how many aircraft flight tracks cross each 
> grid cell.  As the lines are long and the area of interest is 
> relatively small, the && and geometry index don't do much, most of the 
> index speed comes from filtering the flight tracks based on time (as 
> of now there are over half a million flight track records and this is 
> likely to increase to over 2 million). 
>
> I have two approaches that I have thought of that might speed up these 
> queries that I wanted to see if anyone could comment on whether they 
> (or any other ideas) are likely to result in any significant 
> performance gain.
>
> The first approach:
>
> The flight track data points that make up the line are taken by radar 
> at an interval of 4 seconds, as most tracks are fairly straight, a 
> simplify operation would decrease the size drastically, but I really 
> need to maintain the high accuracy of the crossing.  My idea is to 
> maintain two geometry columns, the original and a majorly simplified 
> geometry.  I would then do a first pass using the 
> distance(simple_geom,grid)<tolerance where tolerance is the value that 
> I used for simplification.  Then from those results do a 
> distance(first_result_geom,grid)=0.  This adds a whole other step, but 
> I believe the distance query agains a much simpler (I think between 
> one tenth and one third the number of points) might be fast enough to 
> jusitify the additional step.
>
> The second approach:
>
> Maintain all of the points from the lines as a point dataset as well 
> and just do a count on the unique id's of points that fall within the 
> time period and the grid geometry.
>
> The point of this query is to provide a density map of flight tracks 
> over an area during a period of time for display in Mapserver so 
> performance is critical, right now I limit the time period to a single 
> day and I make my grid cells rather large to keep my draw time 
> reasonably fast, but I am really tring to get a nice surface to 
> display for time periods orders of magnitude longer.
>
> Thanks for any pointers or advice,
> David
>
>------------------------------------------------------------------------
>
>_______________________________________________
>postgis-users mailing list
>postgis-users at postgis.refractions.net
>http://postgis.refractions.net/mailman/listinfo/postgis-users
>  
>