SQLiteStore#

tiatoolbox . annotation . storage . SQLiteStore

class SQLiteStore(connection=':memory:', compression='zlib', compression_level=9, auto_commit=True)[source]#

SQLite backed annotation store.

Uses and rtree index for fast spatial queries.

Version History: 1.0.0:

Initial version.

1.0.1 (07/10/2022):: Added optional “area” column and queries sorted/filtered by area.

Methods

`add_area_column`	Add a column to store the area of the geometry.
`append_many`
`bquery`
`clear`	Remove all annotations from the store.
`close`
`commit`
`compile_options`	Get the list of options that sqlite3 was compiled with.
`create_index`	Create an SQLite expression index based on the provided predicate.
`deserialize_geometry`	Deserialize a geometry from a string or bytes.
`drop_index`	Drop an index from the store.
`dump`
`dumps`
`features`
`indexes`	Returns a list of the names of all indexes in the store.
`iquery`
`items`
`keys`
`open`
`optimize`	Optimize the database with VACUUM and ANALYZE.
`patch_many`
`pquery`	Query the store for annotation properties.
`query`
`remove_area_column`	Remove the area column from the store.
`remove_many`
`serialise_geometry`	Serialise a geometry to WKB with optional compression.
`to_dataframe`
`values`

Parameters:

connection (Union[Path, str, IO]) –
compression (str) –
compression_level (int) –
auto_commit (bool) –

add_area_column(mk_index=True)[source]#: Add a column to store the area of the geometry.

clear()[source]#

Remove all annotations from the store.

Return type:: None

static compile_options()[source]#

Get the list of options that sqlite3 was compiled with.

Example

>>> for opt in SQLiteRTreeStore.compile_options():
>>>     print(opt)
COMPILER=gcc-7.5.0
ENABLE_COLUMN_METADATA
ENABLE_DBSTAT_VTAB
ENABLE_FTS3
ENABLE_FTS3_PARENTHESIS
ENABLE_FTS3_TOKENIZER
ENABLE_FTS4
ENABLE_FTS5
ENABLE_JSON1
ENABLE_LOAD_EXTENSION
ENABLE_PREUPDATE_HOOK
ENABLE_RTREE
ENABLE_SESSION
ENABLE_STMTVTAB
ENABLE_UNLOCK_NOTIFY
ENABLE_UPDATE_DELETE_LIMIT
HAVE_ISNAN
LIKE_DOESNT_MATCH_BLOBS
MAX_SCHEMA_RETRY=25
MAX_VARIABLE_NUMBER=250000
OMIT_LOOKASIDE
SECURE_DELETE
SOUNDEX
TEMP_STORE=1
THREADSAFE=1

Return type:: List[str]

create_index(name, where, analyze=True)[source]#

Create an SQLite expression index based on the provided predicate.

Note that an expression index will only be used if the query expression (in the WHERE clause) exactly matches the expression used when creating the index (excluding minor inconsequential changes such as whitespace).

SQLite expression indexes require SQLite version 3.9.0 or higher.

Parameters:

name (str) – Name of the index to create.
where (Union[str, bytes]) – The predicate used to create the index.
analyze (bool) – Whether to run the “ANALYZE” command after creating the index.

Return type:

None

deserialize_geometry(data)[source]#

Deserialize a geometry from a string or bytes.

Parameters:: data (bytes or str) – The serialised representation of a Shapely geometry.
Returns:: The deserialized Shapely geometry.
Return type:: Geometry

drop_index(name)[source]#

Drop an index from the store.

Parameters:: name (str) – The name of the index to drop.
Return type:: None

indexes()[source]#

Returns a list of the names of all indexes in the store.

Returns:: The list of index names.
Return type:: List[str]

optimize(vacuum=True, limit=1000)[source]#

Optimize the database with VACUUM and ANALYZE.

Parameters:

vacuum (bool) – Whether to run VACUUM.
limit (int) – The approximate maximum number of rows to examine when running ANALYZE. If zero or negative, not limit will be used. For more information see https://www.sqlite.org/pragma.html#pragma_analysis_limit.

Return type:

None

pquery(select, geometry=None, where=None, geometry_predicate='intersects', unique=True, squeeze=True)[source]#

Query the store for annotation properties.

Acts similarly to AnnotationStore.query but returns only the value defined by select.

Parameters:

select (str or bytes or Callable) – A statement defining the value to look up from the annotation properties. If select = “*”, all properties are returned for each annotation (unique must be False).
geometry (Geometry or Iterable) – Geometry to use when querying. This can be a bounds (iterable of length 4) or a Shapely geometry (e.g. Polygon). If a geometry is provided, the bounds of the geometry will be used for the query. Full geometry intersection is not used for the query method.
where (str or bytes or Callable) – A statement which should evaluate to a boolean value. Only annotations for which this predicate is true will be returned. Defaults to None (assume always true). This may be a string, callable, or pickled function as bytes. Callables are called to filter each result returned the from annotation store backend in python before being returned to the user. A pickle object is, where possible, hooked into the backend as a user defined function to filter results during the backend query. Strings are expected to be in a domain specific language and are converted to SQL on a best-effort basis. For supported operators of the DSL see tiatoolbox.annotation.dsl. E.g. a simple python expression props[“class”] == 42 will be converted to a valid SQLite predicate when using SQLiteStore and inserted into the SQL query. This should be faster than filtering in python after or during the query. It is important to note that untrusted user input should never be accepted to this argument as arbitrary code can be run via pickle or the parsing of the string statement.
geometry_predicate (str) – A string defining which binary geometry predicate to use when comparing the query geometry and a geometry in the store. Only annotations for which this binary predicate is true will be returned. Defaults to “intersects”. For more information see the shapely documentation on binary predicates.
unique (bool) – If True, only unique values for each selected property will be returned as a list of sets. If False, all values will be returned as a dictionary mapping keys values. Defaults to True.
squeeze (bool) – If True, when querying for a single value with unique=True, the result will be a single set instead of a list of sets.

Return type:

Union[Dict[str, Any], Set[Any]]

Examples

>>> from tiatoolbox.annotation.storage import AnnotationStore
>>> from shapely.geometry import Point
>>> store = AnnotationStore()
>>> annotation =  Annotation(
...     geometry=Point(0, 0),
...     properties={"class": 42},
... )
>>> store.add(annotation, "foo")
>>> store.pquery("*", unique=False)
... {'foo': {'class': 42}}

>>> from tiatoolbox.annotation.storage import AnnotationStore
>>> from shapely.geometry import Point
>>> store = AnnotationStore()
>>> annotation =  Annotation(
...     geometry=Point(0, 0),
...     properties={"class": 42},
... )
>>> store.add(annotation, "foo")
>>> store.pquery("props['class']")
... {42}
>>> annotation =  Annotation(Point(1, 1), {"class": 123})
>>> store.add(annotation, "foo")
>>> store.pquery("props['class']")
... {42, 123}

remove_area_column()[source]#: Remove the area column from the store.

serialise_geometry(geometry)[source]#

Serialise a geometry to WKB with optional compression.

Converts shapely geometry objects to well-known binary (WKB) and applies optional compression.

Parameters:: geometry (Geometry) – The Shapely geometry to be serialised.
Returns:: The serialised geometry.
Return type:: bytes or str