PostgresOSM¶

class pydriosm.ios.PostgresOSM(host=None, port=None, username=None, password=None, database_name=None, data_source='Geofabrik', max_tmpfile_size=None, data_dir=None, **kwargs)[source]¶

Implement storage I/O of OpenStreetMap data with PostgreSQL.

Parameters:

host (str | None) – host name/address of a PostgreSQL server, e.g. 'localhost' or '127.0.0.1' (default by installation of PostgreSQL); when host=None (default), it is initialized as 'localhost'
port (int | None) – listening port used by PostgreSQL; when port=None (default), it is initialized as 5432 (default by installation of PostgreSQL)
username (str | None) – username of a PostgreSQL server; when username=None (default), it is initialized as 'postgres' (default by installation of PostgreSQL)
password (str | int | None) – user password; when password=None (default), it is required to mannually type in the correct password to connect the PostgreSQL server
database_name (str | None) – name of a database; when database=None (default), it is initialized as 'postgres' (default by installation of PostgreSQL)
confirm_db_creation – whether to prompt a confirmation before creating a new database (if the specified database does not exist), defaults to False
data_source (str) – name of data source, defaults to 'Geofabrik'; options include {'Geofabrik', 'BBBike'}
max_tmpfile_size (int | None) – defaults to None, see also the function pyhelpers.settings.gdal_configurations()
data_dir (str | None) – directory where the data file is located/saved, defaults to None; when data_dir=None, it should be the same as the directory specified by the corresponding downloader/reader
kwargs – [optional] parameters of the class pyhelpers.sql.PostgreSQL

Variables:

data_source (str) – name of data sources, options include {'Geofabrik', 'BBBike'}

Examples:

>>> from pydriosm.ios import PostgresOSM

>>> osmdb = PostgresOSM(database_name='osmdb_test', verbose=True)
Password (postgres@localhost:5432): ***
Creating a database: "osmdb_test" ... Done.
Connecting postgres:***@localhost:5432/osmdb_test ... Successfully.

>>> osmdb.data_source
'Geofabrik'
>>> type(osmdb.downloader)
pydriosm.downloader._wrapper.Downloader
>>> type(osmdb.reader)
pydriosm.reader._wrapper.Reader

>>> # Change the data source
>>> osmdb.data_source = 'BBBike'
>>> type(osmdb.downloader)
pydriosm.downloader._wrapper.Downloader
>>> type(osmdb.reader)
pydriosm.reader._wrapper.Reader

>>> # Delete the database 'osmdb_test'
>>> osmdb.drop_database(verbose=True)
To drop the database "osmdb_test" from postgres:***@localhost:5432
? [No]|Yes: yes
Dropping "osmdb_test" ... Done.

Attributes

`BUILTIN_SCHEMAS`	Built-in schemas of PostgreSQL.
`DATA_SOURCES`	Names of the data sources.
`DATA_TYPES`	Specify a data-type dictionary for data or columns corresponding to Pandas.
`DEFAULT_DATABASE`	Default database name (usually created during PostgreSQL installation).
`DEFAULT_DIALECT`	Default dialect used by SQLAlchemy to communicate with PostgreSQL; see also [DBMS-PS-1].
`DEFAULT_DRIVER`	Default name of the database driver.
`DEFAULT_HOST`	Default host name/address (typically localhost).
`DEFAULT_PORT`	Default listening port used by PostgreSQL.
`DEFAULT_SCHEMA`	Default schema name created during PostgreSQL installation.
`DEFAULT_USERNAME`	Default username.
`LONG_NAME`	Name of the current property `downloader`.
`NAME`	Name of the current property `downloader`.
`URL`	Homepage URL of data resource for current property `downloader`.
`downloader`	Instance of either the class `GeofabrikDownloader` or `BBBikeDownloader`, depending on the specified `data_source` for creating an instance of the class `PostgresOSM`.
`reader`	Instance of either `GeofabrikReader` or `BBBikeReader`, depending on the specified `data_source` for creating an instance of the calss `PostgresOSM`.

Methods

`add_primary_keys`(primary_keys, table_name[, ...])	Adds a primary key or multiple primary keys to a table.
`alter_table_schema`(table_name, schema_name, ...)	Moves a table from one schema to another within the currently-connected database.
`connect_database`([database_name, verbose, ...])	Establishes a connection to a database.
`create_database`(database_name[, verbose])	Creates a database.
`create_schema`(schema_name[, verbose, ...])	Creates a schema.
`create_table`(table_name, column_specs[, ...])	Creates a table.
`database_exists`([database_name])	Checks if a specified database exists.
`decode_pbf_layer`(layer_dat[, decode_geojson])	Process raw data of a PBF layer retrieved from database.
`disconnect_all_others`()	Terminates connections to all databases except the currently-connected one.
`disconnect_database`([database_name, ...])	Disconnects from a database.
`drop_database`([database_name, ...])	Deletes/drops a database.
`drop_schema`(schema_names[, ...])	Deletes/drops one or multiple schemas.
`drop_subregion_tables`(subregion_names[, ...])	Delete all or specific schemas/layers of subregion data from the database being connected.
`drop_table`(table_name[, schema_name, ...])	Deletes/drops a specified table.
`fetch_data`(subregion_name[, layer_names, ...])	Fetch OSM data (of one or multiple layers) of a geographic (sub)region.
`get_column_dtype`(table_name[, column_names, ...])	Retrieves information about data types of all or specific columns of a table.
`get_column_info`(table_name[, schema_name, ...])	Retrieves information about columns of a table.
`get_column_names`(table_name[, schema_name])	Retrieves column names of a table.
`get_database_names`([names_only])	Retrieves the names of all existing databases.
`get_database_size`([database_name])	Retrieves the size of a database.
`get_primary_keys`(table_name[, schema_name, ...])	Retrieves the primary keys of a table.
`get_schema_info`([names_only, include_all, ...])	Retrieves information about existing schemas.
`get_table_column_info`(subregion_name, layer_name)	Get information about columns of a specific schema and table data for a geographic (sub)region.
`get_table_name`(subregion_name[, ...])	Get the default table name for a specific geographic (sub)region.
`get_table_names`([schema_name, verbose])	Retrieves the names of all tables in a schema.
`import_data`(data, table_name[, schema_name, ...])	Imports tabular data into the database.
`import_osm_data`(osm_data, table_name[, ...])	Import OSM data into a database.
`import_osm_layer`(layer_data, table_name, ...)	Import one layer of OSM data into a table.
`import_osm_pbf`(subregion_names[, data_dir, ...])	Import data of geographic (sub)region(s) that do not have (sub-)subregions into a database.
`null_text_to_empty_string`(table_name[, ...])	Converts null values (in text columns) to empty strings.
`postprocess_pdf_layer`(layer_dat[, ...])	Post-process the data of a specific layer.
`psql_insert_copy`(sql_table, sql_db_engine, ...)	Callable function using PostgreSQL `COPY` clause for executing data insertion.
`read_sql_query`(sql_query[, method, ...])	Reads table data by executing a SQL query (recommended for large tables).
`read_table`(table_name[, schema_name, ...])	Reads data from a specified table.
`schema_exists`(schema_name)	Checks if a schema exists.
`subregion_table_exists`(subregion_name, ...)	Check if a table (for a geographic (sub)region) exists.
`table_exists`(table_name[, schema_name])	Checks if a table exists.
`validate_column_names`(table_name[, ...])	Validates column names for a query statement.