On Jan 22, 2009, at 6:48 PM, Sergej Schwarz wrote: > Dear all, > > I'm using RTree to have FAST possibility to extract all the > polygons, which intersect a specified window, overlap this specified > window or are completely in this window. Please see the picture at > http://home.arcor.de/schwar_s/images/polygons.jpg > . The idea is to find all the polygons are in the neighbor hood to > the polygon in the center of the window. The polygons in the > neighborhood are never diagonal but some of them are vertically > oriented, some of them are horizontally oriented.
SELECT * FROM LDB_RtreePolygons WHERE x1 <= window.x2 AND x2 >= window.x1 AND y1 <= window.y2 AND y2 >= window.y1 > > > I could not find any possibilities to find all these polygons by > using "just" one SQL query, so that I have to execute 6 different > queries. These here: > > //OVERLAP POLYGONS : horizontal overlap > SELECT PolyID, X1, X2, Y1, Y2 FROM LDB_RtreePolygons > WHERE PolyID != target.PolygonID > AND X1< window.x1 AND X2> window.x2 > AND Y1>= window.y1 AND Y2<= window.y2 > > //OVERLAP POLYGONS : vertical overlap > SELECT PolyID, X1, X2, Y1, Y2 FROM LDB_RtreePolygons > WHERE PolyID != target.PolygonID > AND Y1< window.x1 AND Y2> window.y2 > AND X1>= window.x1 AND X2<= window.x2 > > //POLYGONS INTERSECT top/left corner > SELECT PolyID, X1, X2, Y1, Y2 FROM LDB_RtreePolygons > WHERE PolyID != target.PolygonID > AND X2>= window.x1 AND X2<= window.x2 > AND Y1>= window.y1 AND Y1<= window.y2 > > //POLYGONS INTERSECT top/right corner > SELECT PolyID, X1, X2, Y1, Y2 FROM LDB_RtreePolygons > WHERE PolyID != target.PolygonID > AND X1>= window.x1 AND X1<= window.x2 > AND Y1>= window.y1 AND Y1<= window.y2 > > //POLYGONS INTERSECT bottom/left corner > SELECT PolyID, X1, X2, Y1, Y2 FROM LDB_RtreePolygons > WHERE PolyID != target.PolygonID > AND X2>= window.x1 AND X2<= window.x2 > AND Y2>= window.y1 AND Y2<= window.y2 > > //POLYGONS INTERSECT bottom/right corner > SELECT PolyID, X1, X2, Y1, Y2 FROM LDB_RtreePolygons > WHERE PolyID != target.PolygonID > AND X1>= window.x1 AND X1<= window.x2 > AND Y2>= window.y1 AND Y2<= window.y2 > > My 1st question is: > ---------------------- > Is this the most effective way to find the neighborhood?. Do I > really have to specify these 6 queries? > > The second question (or problem): > ---------------------------------------- > As long the database file is located locally (at the same computer > on which the program is executed) the performance is OK. If the > database is placed on another server (in really fast LAN) the > performance goes down dramatically (by factor 20x). > Even if I "just" extract all existing polygons (without RTree) from > the database and then clarify neighborhood by own simple > implementation, the performace such solution (in my LAN) is faster > by factor 2 in comparation to the RTree-Concept. Are there some > RTree-Caching concepts? Any ideas? > > Thanks in advance! > > S > -- > Psssst! Schon vom neuen GMX MultiMessenger gehört? Der kann`s mit > allen: http://www.gmx.net/de/go/multimessenger > _______________________________________________ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
