SQLiteStore

tiatoolbox . annotation . storage . SQLiteStore

class SQLiteStore(*args, **kwargs)

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.

Initialize SQLiteStore.

Methods

add_area_column	Add a column to store the area of the geometry.
append_many	Appends new annotations to specified keys.
bquery	Query the store for annotation bounding boxes.
clear	Remove all annotations from the store.
close	Closes SQLiteStore from file pointer or path.
commit	Commit any in-memory changes to disk.
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	Serialise a copy of the whole store to a file-like object.
dumps	Serialise and return a copy of store as a string or bytes.
features	Return annotations as a list of geoJSON features.
get_connection	Get a connection to the database.
indexes	Return a list of the names of all indexes in the store.
iquery	Query the store for annotation keys.
items	Return iterable (generator) over key and annotations.
keys	Return an iterable (usually generator) of all keys in the store.
open	Opens SQLiteStore from file pointer or path.
optimize	Optimize the database with VACUUM and ANALYZE.
patch_many	Bulk patch of annotations.
pquery	Query the store for annotation properties.
query	Runs Query.
remove_area_column	Remove the area column from the store.
remove_many	Bulk removal of annotations by keys.
serialise_geometry	Serialise a geometry to WKB with optional compression.
to_dataframe	Converts AnnotationStore to pandas.DataFrame.
values	Return an iterable of all annotation in the store.

Parameters:

• connection (Path | str | IO)

• compression (str)

• compression_level (int)

• auto_commit (bool)

add_area_column(*, mk_index=True)

Add a column to store the area of the geometry.

Parameters:

• self (SQLiteStore)

• mk_index (bool)

Return type:

None

append_many(annotations, keys=None)

Appends new annotations to specified keys.

Parameters:

• self (SQLiteStore)

• annotations (Iterable[Annotation])

• keys (Iterable[str] | None)

Return type:

list[str]

bquery(geometry=None, where=None)

Query the store for annotation bounding boxes.

Acts similarly to AnnotationStore.query except it checks for intersection between stored and query geometry bounding boxes. This may be faster than a regular query in some cases, e.g. for SQliteStore with a large number of annotations.

Note that this method only checks for bounding box intersection and therefore may give a different result to using AnnotationStore.query with a box polygon and the “intersects” geometry predicate. Also note that geometry predicates are not supported for this method.

Parameters:

• 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 from the 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. Additionally, the same string can be used across different backends (e.g. the previous example predicate string is valid for both DictionaryStore and a SQliteStore). On the other hand it has many more limitations. 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.

• self (SQLiteStore)

Returns:

A list of bounding boxes for each Annotation.

Return type:

list

Example

>>> from tiatoolbox.annotation.storage import SQLiteStore
>>> from shapely.geometry import Polygon
>>> store = SQLiteStore()
>>> store.append(
...     Annotation(
...         geometry=Polygon.from_bounds(0, 0, 1, 1),
...         properties={"class": 42},
...     ),
...     key="foo",
... )
>>> store.bquery(where="props['class'] == 42")
{'foo': (0.0, 0.0, 1.0, 1.0)}

clear()

Remove all annotations from the store.

Parameters:

self (SQLiteStore)

Return type:

None

close()

Closes SQLiteStore from file pointer or path.

Parameters:

self (SQLiteStore)

Return type:

None

commit()

Commit any in-memory changes to disk.

Parameters:

self (SQLiteStore)

Return type:

None

static compile_options()

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)

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).

An SQLite expression indexes require SQLite version 3.9.0 or higher.

Parameters:

• name (str) – Name of the index to create.

• where (str | bytes) – The predicate used to create the index.

• analyze (bool) – Whether to run the “ANALYZE” command after creating the index.

• self (SQLiteStore)

Return type:

None

deserialize_geometry(data)

Deserialize a geometry from a string or bytes.

Parameters:

• data (bytes or str) – The serialised representation of a Shapely geometry.

• self (SQLiteStore)

Returns:

The deserialized Shapely geometry.

Return type:

Geometry

drop_index(name)

Drop an index from the store.

Parameters:

• name (str) – The name of the index to drop.

• self (SQLiteStore)

Return type:

None

dump(fp)

Serialise a copy of the whole store to a file-like object.

Parameters:

• fp (Path or str or IO) – A file path or file handle object for output to disk.

• self (SQLiteStore)

Return type:

None

dumps()

Serialise and return a copy of store as a string or bytes.

Returns:

The serialised store.

Return type:

str or bytes

Parameters:

self (SQLiteStore)

features()

Return annotations as a list of geoJSON features.

Returns:

List of features as dictionaries.

Return type:

list

Parameters:

self (SQLiteStore)

get_connection(thread_id)

Get a connection to the database.

Parameters:

• self (SQLiteStore)

• thread_id (int)

Return type:

Connection

indexes()

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

Returns:

The list of index names.

Return type:

List[str]

Parameters:

self (SQLiteStore)

iquery(geometry=None, where=None, geometry_predicate='intersects', distance=0)

Query the store for annotation keys.

Acts the same as AnnotationStore.query except returns keys instead of annotations.

Parameters:

• 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).

• 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 from the 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. Additionally, the same string can be used across different backends (e.g. the previous example predicate string is valid for both DictionaryStore `and a `SQliteStore). On the other hand it has many more limitations. 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 which define 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.

• distance (float) – Distance used when performing a distance based query. E.g. “centers_within_k” geometry predicate.

• self (SQLiteStore)

Returns:

A list of keys for each Annotation.

Return type:

list

items()

Return iterable (generator) over key and annotations.

Parameters:

self (SQLiteStore)

Return type:

Iterable[tuple[int, Annotation]]

keys()

Return an iterable (usually generator) of all keys in the store.

Returns:

An iterable of keys.

Return type:

Iterable[str]

Parameters:

self (SQLiteStore)

classmethod open(fp)

Opens SQLiteStore from file pointer or path.

Parameters:

fp (Path | str)

Return type:

SQLiteStore

optimize(limit=1000, *, vacuum=True)

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.

• self (SQLiteStore)

Return type:

None

patch_many(keys, geometries=None, properties_iter=None)

Bulk patch of annotations.

This may be more efficient than calling patch repeatedly in a loop.

Parameters:

• geometries (iter(Geometry)) – An iterable of geometries to update.

• properties_iter (iter(dict)) – An iterable of properties to update.

• keys (iter(str)) – An iterable of keys for each annotation to be updated.

• self (SQLiteStore)

Return type:

None

pquery(select, geometry=None, where=None, geometry_predicate='intersects', *, unique=True, squeeze=True)

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 from the 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.

• self (SQLiteStore)

Return type:

dict[str, object] | set[object]

Examples

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

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

query(geometry=None, where=None, geometry_predicate='intersects', min_area=None, distance=0)

Runs Query.

Parameters:

• self (SQLiteStore)

• geometry (QueryGeometry | None)

• where (Predicate | None)

• geometry_predicate (str)

• min_area (float / None)

• distance (float)

Return type:

dict[str, Annotation]

remove_area_column()

Remove the area column from the store.

Parameters:

self (SQLiteStore)

Return type:

None

remove_many(keys)

Bulk removal of annotations by keys.

Parameters:

• keys (iter(str)) – An iterable of keys for the annotation to be removed.

• self (SQLiteStore)

Return type:

None

serialise_geometry(geometry)

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.

• self (SQLiteStore)

Returns:

The serialised geometry.

Return type:

bytes or str

to_dataframe()

Converts AnnotationStore to pandas.DataFrame.

Parameters:

self (SQLiteStore)

Return type:

DataFrame

values()

Return an iterable of all annotation in the store.

Returns:

An iterable of annotations.

Return type:

Iterable[Annotation]

Parameters:

self (SQLiteStore)