[postgis-users] Crosses Performance

[David Bitner]

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
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20050920/4df2b2c6/attachment.html>