Return to README CVS log

Up to [ELWIX - Embedded LightWeight unIX -] / embedaddon / sqlite3 / ext / rtree

Annotation of embedaddon/sqlite3/ext/rtree/README, revision 1.1

1.1     ! misho       1: 
        !             2: This directory contains an SQLite extension that implements a virtual 
        !             3: table type that allows users to create, query and manipulate r-tree[1] 
        !             4: data structures inside of SQLite databases. Users create, populate 
        !             5: and query r-tree structures using ordinary SQL statements.
        !             6: 
        !             7:     1.  SQL Interface
        !             8: 
        !             9:         1.1  Table Creation
        !            10:         1.2  Data Manipulation
        !            11:         1.3  Data Querying
        !            12:         1.4  Introspection and Analysis
        !            13: 
        !            14:     2.  Compilation and Deployment
        !            15: 
        !            16:     3.  References
        !            17: 
        !            18: 
        !            19: 1. SQL INTERFACE
        !            20: 
        !            21:   1.1 Table Creation.
        !            22: 
        !            23:     All r-tree virtual tables have an odd number of columns between
        !            24:     3 and 11. Unlike regular SQLite tables, r-tree tables are strongly 
        !            25:     typed. 
        !            26: 
        !            27:     The leftmost column is always the pimary key and contains 64-bit 
        !            28:     integer values. Each subsequent column contains a 32-bit real
        !            29:     value. For each pair of real values, the first (leftmost) must be 
        !            30:     less than or equal to the second. R-tree tables may be 
        !            31:     constructed using the following syntax:
        !            32: 
        !            33:       CREATE VIRTUAL TABLE <name> USING rtree(<column-names>)
        !            34: 
        !            35:     For example:
        !            36: 
        !            37:       CREATE VIRTUAL TABLE boxes USING rtree(boxno, xmin, xmax, ymin, ymax);
        !            38:       INSERT INTO boxes VALUES(1, 1.0, 3.0, 2.0, 4.0);
        !            39: 
        !            40:     Constructing a virtual r-tree table <name> creates the following three
        !            41:     real tables in the database to store the data structure:
        !            42: 
        !            43:       <name>_node
        !            44:       <name>_rowid
        !            45:       <name>_parent
        !            46: 
        !            47:     Dropping or modifying the contents of these tables directly will
        !            48:     corrupt the r-tree structure. To delete an r-tree from a database,
        !            49:     use a regular DROP TABLE statement:
        !            50: 
        !            51:       DROP TABLE <name>;
        !            52: 
        !            53:     Dropping the main r-tree table automatically drops the automatically
        !            54:     created tables. 
        !            55: 
        !            56:   1.2 Data Manipulation (INSERT, UPDATE, DELETE).
        !            57: 
        !            58:     The usual INSERT, UPDATE or DELETE syntax is used to manipulate data
        !            59:     stored in an r-tree table. Please note the following:
        !            60: 
        !            61:       * Inserting a NULL value into the primary key column has the
        !            62:         same effect as inserting a NULL into an INTEGER PRIMARY KEY
        !            63:         column of a regular table. The system automatically assigns
        !            64:         an unused integer key value to the new record. Usually, this
        !            65:         is one greater than the largest primary key value currently
        !            66:         present in the table.
        !            67: 
        !            68:       * Attempting to insert a duplicate primary key value fails with
        !            69:         an SQLITE_CONSTRAINT error.
        !            70: 
        !            71:       * Attempting to insert or modify a record such that the value
        !            72:         stored in the (N*2)th column is greater than that stored in
        !            73:         the (N*2+1)th column fails with an SQLITE_CONSTRAINT error.
        !            74: 
        !            75:       * When a record is inserted, values are always converted to 
        !            76:         the required type (64-bit integer or 32-bit real) as if they
        !            77:         were part of an SQL CAST expression. Non-numeric strings are
        !            78:         converted to zero.
        !            79: 
        !            80:   1.3 Queries.
        !            81: 
        !            82:     R-tree tables may be queried using all of the same SQL syntax supported
        !            83:     by regular tables. However, some query patterns are more efficient
        !            84:     than others.
        !            85: 
        !            86:     R-trees support fast lookup by primary key value (O(logN), like 
        !            87:     regular tables).
        !            88: 
        !            89:     Any combination of equality and range (<, <=, >, >=) constraints
        !            90:     on spatial data columns may be used to optimize other queries. This
        !            91:     is the key advantage to using r-tree tables instead of creating 
        !            92:     indices on regular tables.
        !            93: 
        !            94:   1.4 Introspection and Analysis.
        !            95: 
        !            96:     TODO: Describe rtreenode() and rtreedepth() functions.
        !            97: 
        !            98: 
        !            99: 2. COMPILATION AND USAGE
        !           100: 
        !           101:   The easiest way to compile and use the RTREE extension is to build
        !           102:   and use it as a dynamically loadable SQLite extension. To do this
        !           103:   using gcc on *nix:
        !           104: 
        !           105:     gcc -shared rtree.c -o libSqliteRtree.so
        !           106: 
        !           107:   You may need to add "-I" flags so that gcc can find sqlite3ext.h
        !           108:   and sqlite3.h. The resulting shared lib, libSqliteRtree.so, may be
        !           109:   loaded into sqlite in the same way as any other dynamicly loadable
        !           110:   extension.
        !           111: 
        !           112: 
        !           113: 3. REFERENCES
        !           114: 
        !           115:   [1]  Atonin Guttman, "R-trees - A Dynamic Index Structure For Spatial 
        !           116:        Searching", University of California Berkeley, 1984.
        !           117: 
        !           118:   [2]  Norbert Beckmann, Hans-Peter Kriegel, Ralf Schneider, Bernhard Seeger,
        !           119:        "The R*-tree: An Efficient and Robust Access Method for Points and
        !           120:        Rectangles", Universitaet Bremen, 1990.