geodatabase

This modules currently has this unique class: Geodatabase.

The Geodatabase class makes it possible to interact with spatialite databases. Example of projects using this package: kad-buildings or kad-clustering.

Created on Wed Dec 23 17:31:33 2020

@author: Youri.Baeyens

class geodatabase.Geodatabase(db: str = ':memory:', srid: int = 31370, recreate: bool = False)[source]

Geodatabase.

Available methods:

attach(path: str, alias: str)[source]

Attach external db.

Example

>>> attach('c:\path o\mydb.sqlite','mydb')

checksForTable(table: str) → bool[source]

Detects table without rowid or with shadowed rowid.

Raises: Exception – Exception raised if either shadowed rowid or absence of rowid raises are detected.

close()[source]: Closes the geodatabase.

correctGeometry(table: str, geometry: str = 'geometry')[source]: Correct geometries

createSpatialIndex(table: str, geometry: str = 'geometry')[source]

Create a spatial index.

Raises: exception – Exception is thrown if the creation of spatial index failed.

executeScript(query: str, **queryParameters) → list[source]

Execute a sql script. The script can contain many SQLs separated by ;.

Parameters: query – Path to the sql query. Do not specify the .sql extension.
QueryParameters: list of “parameter=value” parameters that can be used inside the query.

findConnectedComponents(inputTable: str, edgesColumnNames: [<class 'str'>, <class 'str'>], outputTable: str) → pandas.core.frame.DataFrame[source]

Finds connected components of an undirected graph.

Parameters

inputTable (str) – DESCRIPTION
edgesColumnNames (list) – DESCRIPTION
outputTable (str) – DESCRIPTION

Returns

DESCRIPTION

Return type

TYPE

Example

>>> db.findConnectedComponents('t01_edges',['elt_a','elt_b'],'t02_cc')

getGeometriesMetadata() → pandas.core.frame.DataFrame[source]

Get geometries metadata.

Returns

DataFrame with available metadata on geometries.

Example

>>> db.getGeometriesMetadata()

               f_geometry_column  geometry_type  coord_dimension   srid  spatial_index_enabled
f_table_name                                                                                  
t01_zip                 geometry           3003                4  31370                      1
t04_zip_sample          geometry              3                2  31370                      1

Example

>>> db.getGeometriesMetadata().loc['t01_zip',:]
f_geometry_column        geometry
geometry_type                3003
coord_dimension                 4
srid                        31370
spatial_index_enabled           1
Name: t01_zip, dtype: object

getListOfColumns(table: str, withLengths: bool = False, withDistincts: bool = False) → pandas.core.frame.DataFrame[source]

Documents the structure and content of a table. The returned pandas DataFrame has got this structure:

cd_column_type: [‘INTEGER’,’DOUBLE’,’TEXT’,’POLYGON’,…]

fl_notnull_constraint: 1=nulls are not allowed, 0=nulls allowed

default_value

fl_primary_key: 1=a primary key, 0=not a primary key

ms_max_length: max length of column expressed in characters or bytes

Example

>>> db.getListOfColumns('t01_zip',withLengths=True)

               cd_column_type   fl_primary_key  ms_max_length
tx_column_name                                                                                   
pk_uid                INTEGER                1            4.0
join_count            INTEGER                0            5.0
nouveau_po               TEXT                0            4.0
frequency             INTEGER                0            2.0
cp_speciau            INTEGER                0            1.0
shape_leng             DOUBLE                0           13.0
shape_area             DOUBLE                0           13.0
geometry              POLYGON                0       142708.0

getListOfTables() → pandas.core.frame.DataFrame[source]

Get list of tables in geodatabase (including indexes and count of rows).

Example

>>> db.getListOfTables()

               ms_countOf_geometries   ms_countOf_spatial_indexes   ms_countOf_rows
f_table_name
T01_zip                             1                           1           1268.0
T02_PIP                             0                           0         225263.0
t03_sample                          0                           0         225262.0
t04_sample                          0                           0              2.0
t04_zip                             1                           1             10.0

getTableCountOfRows(table: str) → int[source]

Counts the number of rows in table.

Example

>>> db.getTableCountOfRows('T01_ZIP')
1268

inspectGeometry(table: str, geometry: str = 'geometry') → list[source]

Inspects the nature of a table geometry.

Returns

List of (geomType, srid, coordDimension, ms_countof_rows) t-uples.

Example

>>> db.inspectGeometry('T01_ZIP')
[('POLYGON ZM', 31370, 'XYZM', 1268)]

Warning

a Geometry can contain various types of geometries (eg: both Polygons and Multipolygons). As a consequence the returned list can contain multiple elements.

loadCsv(csvFile: str, table: str, if_exists: Literal['fail', 'replace', 'append'] = 'replace', **readParameters)[source]

Loads csv file and into a table.

Parameters

csvFile – Path to csv file. File extension should be mentionned.
table – Table name.
if_exists – What to do if table already exists.
readParameters – Parameters for the pandas.read_csv function.

Returns

void

Example

>>> dtype={
>>>        'ADR_ID':'str', 
>>>        'ADR_CREAT':'str', 
>>>        'ADR_MODIF':'str',
>>>        'X':np.float64, 
>>>        'Y':np.float64
>>>       }
>>> parse_dates=['ADR_CREAT', 'ADR_MODIF']
>>> db.loadCsv('myFile.csv',
>>>            'myTable',
>>>            dtype=dtype,
>>>            parse_dates=parse_dates,
>>>            sep='|')

loadEmbeddedCsv(file: str, table: str, if_exists: Literal['fail', 'replace', 'append'] = 'replace', **readParameters)[source]

Loads an embedded csv file and into a table.

Parameters

file – Embedded csv file.
table – Table name.
if_exists – What to do if table already exists.
readParameters – Parameters for the pandas.read_csv function.

Returns

void

Example

>>> file='''
>>> cd_sex|tx_sex
>>> M|Male
>>> F|Female
>>> '''
>>> db.loadEmbeddedCsv(file,'t_sex',sep='|')

loadShp(shapeFile: str, table: str, encoding: str = 'CP1252', srid: Optional[int] = None)[source]

Loads a shapefile and spatially indexes the resulting table.

Parameters

shapeFile – Path to shapefile. File extension should be .shp and cannot be mentionned in shapeFile.
table – Name of the table to be created. This table will be automatically spatially indexed.
encoding – The character encoding adopted by the DBF member, as e.g. UTF-8 or CP1252.
srid – EPSG SRID value.

Returns

void

Example

>>> import geodatabase as gdb 
>>> db=gdb.geodatabase()
>>> db.loadShp('C:/.../postaldistricts','T01_zip') 
Loading shapefile at 'C:/.../postaldistricts' into SQLite table 'T01_zip'
Inserted 1268 rows into 'T01_zip' from SHAPEFILE

pointInPolygon(points: str, polygons: str, outputTable: str)[source]

Assigns point to polygons.

Parameters

points – Name of the table containing points.
polygons – Name of the table containing polygons.
outputTable – Name of the output table.
queryParameters – List of “parameter=value”. Those parameters can be used inside the query.

Returns

void.

Warning

Do not VACUUM your database ! If you do, you risk to break your link table.

recoverGeometry(table: str, geometry: str = 'geometry')[source]

Recovers geometry, ie, registers the geometry in spatialite’s metadata.

Parameters

table – Name of the table containing the geometry.
geometry – Name of the geometry column.

Raises

exception – Exception is raised if geometry cannot be recovered.

Returns

void

recoverSpatialIndex(table: str, geometry: str = 'geometry')[source]

Recover Spatial Index.

A delete operation followed by a VACCUM operation could leave an index in an inconsistent state.

This method checks for the consistency of the spatial index. If the spatial index is inconsistent then try to recover the index (ie, rebuild). If index recovery failed then an exception is thrown.

Raises: Exception – Raises an Exception if geometry is inconsistent.

Warning

Inconsistent indexes can have a dramatic impact. Read this note on SpatialIndexes carefully! Please, run this method if you have a doubt on the integrity of your spatial index.

reproject(inputTable: str, srid: int, outputTable: Optional[str] = None, if_exists: Literal['fail', 'replace'] = 'fail')[source]

Reproject.

This method reprojects a geometry.

Parameters

inputTable – Name of the table containing the geometry to reproject.
srid – Reproject into the Reference System identified by srid.
outputTable – Name of the output table containing the reprojected geometry.

Returns

void

toCsv(table: str, archive_name: Optional[str] = None, mode: Literal['w', 'a'] = 'w', outputDirectory: str = '.', columnsToExclude: list[str] = ['geometry'])[source]

Exports a table to a csv file.

Parameters

table – Table name.
outputDirectory – Output directory.
columnsToExclude – Columns to exclude from export.

Returns

void

Example

>>> db.toCsv('T01_table','../output')

toSas(table: str, archive_name: Optional[str] = None, outputDirectory: str = '.', columnsToExclude: list[str] = ['geometry'])[source]

Create as sas program to import the csv export of a table.

Parameters

table – Table name.
outputDirectory – Output directory. The default is ‘.’.
columnsToExclude – Columns to exclude from sas import. The geometry column is excluded by default as geometries cannot be imported in sas.

Returns

void

Example

>>> db.toSas('t03_pip_sample')

Content of ./t01_zip.sas

filename myZip ZIP 't03_pip_sample.zip';

data t03_pip_sample(compress=YES);
length 
        id_rec                                      8         
        rowid_point                                 8         
        rowid_polygon                               8         
        ;
infile myZip(t03_pip_sample.csv) 
       dsd 
       delimiter='|' 
       encoding='utf8' 
       firstobs=2 
       missover 
       lrecl=10000;
input
        id_rec                                  
        rowid_point                             
        rowid_polygon                           
        ;
run;