Dan, unfortunately, this does not work, the RTree documentation by SQLite leads to misunderstanding of the concept... The query would results in an empty set.
Here is the table described in the picture http://home.arcor.de/schwar_s/images/polygons.jpg create virtual table demo using rtree (id, x1, x2, y1, y2); INSERT INTO demo VALUES( 1, 3, 10, 14, 16); INSERT INTO demo VALUES( 2, 11, 21, 14, 16); INSERT INTO demo VALUES( 3, 1, 2, 12, 13); INSERT INTO demo VALUES( 4, 4, 12, 12, 13); INSERT INTO demo VALUES( 5, 13, 15, 12, 13); INSERT INTO demo VALUES( 6, 16, 19, 12, 13); INSERT INTO demo VALUES( 7, 20, 21, 12, 13); INSERT INTO demo VALUES( 8, 1, 6, 10, 11); INSERT INTO demo VALUES( 9, 7, 21, 10, 11); INSERT INTO demo VALUES(10, 1, 7, 8, 9); INSERT INTO demo VALUES(11, 15, 21, 8, 9); INSERT INTO demo VALUES(12, 1, 9, 6, 7); INSERT INTO demo VALUES(13, 10, 17, 6, 7); INSERT INTO demo VALUES(14, 18, 21, 6, 7); INSERT INTO demo VALUES(15, 1, 21, 4, 5); INSERT INTO demo VALUES(16, 1, 5, 1, 3); INSERT INTO demo VALUES(17, 6, 21, 1, 3); INSERT INTO demo VALUES(30, 8, 14, 8, 9); select * from demo; //your query select * from demo where x1>=3 and x2<=19 and y1>=2 and y2<=15; , where the window coordinates are (x1=3, x2=19, y1=2, y2=15). To my queries: //(1) OVERLAP POLYGONS : horizontal overlap; SELECT id FROM demo WHERE x1<3 AND x2>19 AND y1>=2 AND y2<=15; //polygons= 15 //(2) POLYGONS INTERSECT top/left corner; SELECT id FROM demo WHERE x2>=3 AND x2<=19 AND y1>=2 AND y1<=15; //polygons= 1 4 5 6 8 10 12 13 30 // and 18 21 from picture b) //(3) POLYGONS INTERSECT top/right corner; SELECT id FROM demo WHERE x1>=3 AND x1<=19 AND y1>=2 AND y1<=15; //polygons= 1 2 4 5 6 9 11 13 14 30 // and 21 from picture b) //(4) POLYGONS INTERSECT bottom/left corner; SELECT id FROM demo WHERE x2>=3 AND x2<=19 AND y2>=2 AND y2<=15; //polygons= 4 5 6 8 10 12 13 16 30 // and 19 22 from picture b) //(5) POLYGONS INTERSECT bottom/right corner SELECT id FROM demo WHERE x1>=3 AND x1<=19 AND y2>=2 AND y2<=15; //polygons= 4 5 6 9 11 13 14 17 30 // and 22 from picture b) //(6) OVERLAP POLYGONS : vertical overlap; SELECT id FROM demo WHERE x1>=3 AND x2<=19 AND y1<2 AND y2>15; -> to get vertical overlapping polygons 20 from the picture b) If I add all the reported polygons to a set<int>, then I have an unique set of the neighbors... Guys, any other ideas??? What is about the network problem??? I'm using SQLite 3.6.1... Thanks, S -------- Original-Nachricht -------- > Datum: Thu, 22 Jan 2009 19:13:04 +0700 > Von: Dan <danielk1...@gmail.com> > An: General Discussion of SQLite Database <sqlite-users@sqlite.org> > Betreff: Re: [sqlite] Effective way to use RTree to find the neighborhood ??? > > 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 -- NUR NOCH BIS 31.01.! GMX FreeDSL - Telefonanschluss + DSL für nur 16,37 EURO/mtl.!* http://dsl.gmx.de/?ac=OM.AD.PD003K11308T4569a _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
