Delphi Firedac under the Sqlite (10) using R-tree Search

R-tree is mainly used in three-dimensional space search, it is said that the search algorithm is very fast, even if the millions record is a blink of an eye! SQLite supports 1-5 dimensions, and FIREDAC also provides tfdsqlitertree controls to facilitate the definition of callback functions. For simplicity, I used a two-dimensional table for successful testing. A specific syntax is required to establish a R-tree table (index), for example: Fdconnection1.execsql (' CREATE VIRTUAL table myrtreetable using Rtree (Id, MinX, MaxX, Miny, M Axy) '); Must be VIRTUAL table//using Rtree, is necessary; can also be USING rtree_i32//id, MinX, MaxX, Miny, Maxy; This is the ID and two-dimensional space data, there is no need to specify the parameter type; Because the parameter type is a default: The Id is a 64-bit unsigned shape (and is a primary key), and the following data is a 32-bit floating-point//if the RTREE_I32 definition is used, the subsequent data is 32 for shaping; In addition, if the sqlite_rtree_int_only parameter is specified, no matter how it is defined, the interior is calculated with the plastic.

For this I have done two examples, the first example not using Tfdsqlitertree (that is, no callback). In this case, in addition to using Tfdconnection, Tfdphyssqlitedriverlink, Tfdguixwaitcursor, Tdatasource, TDBGrid, there is also a tpaintbox for drawing and tapping tests, Use of its OnPaint and OnMouseUp events. You can quickly complete the form design by pasting the following code directly onto a blank form:<textarea rows="1" cols="25">object Paintbox1:tpaintbox left = 408 top = Width = 617 Height = 473 OnMouseUp = Paintbox1mouseup Onpa int = Paintbox1paint End object Dbgrid1:tdbgrid left = 0 top = 0 Width = 393 Height = 503 Align = Alleft Data Source = DataSource1 TabOrder = 0 Titlefont.charset = Default_charset titlefont.color = Clwindowtext Titlefont.hei ght = -11 titlefont.name = ' Tahoma ' Titlefont.style = [] End object Fdconnection1:tfdconnection left = top = 2 4 End Object Fdphyssqlitedriverlink1:tfdphyssqlitedriverlink left = 143 top =-End Object Fdguixwaitcursor1:tfdgui Xwaitcursor Provider = ' Forms ' left = the top = "End object Fdquery1:tfdquery Connection = FDConnection1 Left = top = The end of object Datasource1:tdatasource DataSet = FDQuery1 left = 132 top = The End object Fdsqlitertree 1:tfdsqlitertree Driverlink = FDPhysSQLiteDriverLink1 left = 256 top =</textarea> the end

Code:

varVbitmap:tbitmap; As a memory canvasprocedureTform1.formcreate (Sender:tobject);ConstW = 50; H = 30;varI,x,y,x1,x2,y1,y2:integer;beginFDCONNECTION1.PARAMS.ADD (' Driverid=sqlite '); Fdconnection1.execsql (' CREATE VIRTUAL TABLE myrtreetable USING rtree (Id, MinX, MaxX, Miny, Maxy) ');

  Build table fdconnection1.connected: = True;
  {Add analog data for the database} Fdconnection1.starttransaction;Try forI: = 0 to100Todobeginx: = Random (Paintbox1.width);
      Y: = Random (paintbox1.height); Fdconnection1.execsql (' INSERT into myrtreetable VALUES (: ID,: x1,: x2,: y1,: y2) ', [I, X, X+w, Y, y+h]); End; Fdconnection1.commit;exceptFdconnection1.rollback; End;
  Rendering Fdquery1.open (' SELECT * from myrtreetable '); forI: = 0 toDbgrid1.columns.count-1TodoDbgrid1.columns[i]. Width: = 66;
  The default grid column is too wide to handle. {Draw a memory image from the data just added} vbitmap: = Tbitmap.create;
  Vbitmap.setsize (Paintbox1.width, paintbox1.height);
  VBitmap.Canvas.Brush.Color: = Clwhite;

  VBitmap.Canvas.FillRect (Rect (0, 0, vbitmap.width, vbitmap.height)); Fdquery1.first; while notFdquery1.eofTodo
  beginX1: = fdquery1.fields[1].
    Asinteger; x2: = fdquery1.fields[2].
    Asinteger; Y1: = Fdquery1.fields[3].
    Asinteger; Y2: = fdquery1.fields[4].
    Asinteger;
    VBitmap.Canvas.Brush.Color: = Random ($EEEEEE);
    VBitmap.Canvas.FillRect (Rect (x1, y1, x2, y2)); Fdquery1.next; End; End; {R-tree Search} was executed in the OnMouseUp eventprocedureTform1.paintbox1mouseup (Sender:tobject; Button:tmousebutton; Shift:tshiftstate; X, Y:integer);varI:integer;beginCaption: = Format ('%d,%d ', [X, Y]); Fdquery1.open (' SELECT * from myrtreetable WHERE MinX <=: X and MaxX >: x and Miny <=: Y and Maxy >: Y ', [x,y]) ; [X,x,y,y]? forI: = 0 toDbgrid1.columns.count-1TodoDbgrid1.columns[i]. Width: = 66; This line only narrows the column width End; {Renders the previously drawn memory picture}procedureTform1.paintbox1paint (Sender:tobject);beginPaintBox1.Canvas.Draw (0, 0, vbitmap); End;procedureTform1.formdestroy (Sender:tobject);beginVbitmap.free; End;

Test Effect Diagram: