PostgresOSM.import_osm_layer

PostgresOSM.import_osm_layer(layer_data, table_name, schema_name, table_named_as_subregion=False, schema_named_as_layer=False, if_exists='fail', force_replace=False, chunk_size=None, confirmation_required=True, verbose=False, **kwargs)[source]

Import one layer of OSM data into a table.

Parameters:
  • layer_data (pandas.DataFrame | geopandas.GeoDataFrame) – one layer of OSM data

  • schema_name (str) – name of a schema (or name of a PBF layer)

  • table_name (str) – name of a table

  • table_named_as_subregion (bool) – whether to use subregion name as a table name, defaults to False

  • schema_named_as_layer (bool) – whether a schema is named as a layer name, defaults to False

  • if_exists (str) – if the table already exists, defaults to 'fail'; valid options include {'replace', 'append', 'fail'}

  • force_replace (bool) – whether to force to replace existing table, defaults to False

  • chunk_size (int | None) – the number of rows in each batch to be written at a time, defaults to None

  • confirmation_required (bool) – whether to prompt a message for confirmation to proceed, defaults to True

  • verbose (bool) – whether to print relevant information in console as the function runs, defaults to False

  • kwargs – [optional] parameters of pyhelpers.sql.PostgreSQL.dump_data()

Examples:

>>> from pydriosm.ios import PostgresOSM
>>> from pyhelpers.dirs import delete_dir

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

>>> subrgn_name = 'Rutland'  # name of a subregion
>>> dat_dir = "tests\osm_data"  # name of a data directory where the subregion data is

Example 1 - Import data of the ‘points’ layer of a PBF file:

>>> # First, read the PBF data of Rutland (from Geofabrik free download server)
>>> # (If the data file is not available, it'll be downloaded by confirmation)
>>> raw_pbf = osmdb.reader.read_osm_pbf(subrgn_name, data_dir=dat_dir, verbose=True)
Downloading "rutland-latest.osm.pbf"
    to "tests\osm_data\rutland\" ... Done.
Reading "tests\osm_data\rutland\rutland-latest.osm.pbf" ... Done.
>>> type(raw_pbf)
dict
>>> list(raw_pbf.keys())
['points', 'lines', 'multilinestrings', 'multipolygons', 'other_relations']

>>> # Get the data of 'points' layer
>>> points_key = 'points'
>>> raw_pbf_points = raw_pbf[points_key]
>>> type(raw_pbf_points)
list
>>> type(raw_pbf_points[0])
osgeo.ogr.Feature

>>> # Now import the data of 'points' into the PostgreSQL server
>>> osmdb.import_osm_layer(
...     layer_data=raw_pbf_points, table_name=subrgn_name, schema_name=points_key,
...     verbose=True)
To import data into "points"."Rutland" at postgres:***@localhost:5432/osmdb_test
? [No]|Yes: yes
Creating a schema: "points" ... Done.
Importing the data into the table "points"."Rutland" ... Done.

>>> tbl_col_info = osmdb.get_table_column_info(subrgn_name, points_key)
>>> tbl_col_info.head()
                    column_0
table_catalog     osmdb_test
table_schema          points
table_name           Rutland
column_name           points
ordinal_position           1

>>> # Parse the 'geometry' of the PBF data of Rutland
>>> parsed_pbf = osmdb.reader.read_osm_pbf(
...     subregion_name=subrgn_name, data_dir=dat_dir, expand=True, parse_geometry=True)
>>> type(parsed_pbf)
dict
>>> list(parsed_pbf.keys())
['points', 'lines', 'multilinestrings', 'multipolygons', 'other_relations']
>>> parsed_pbf_points = parsed_pbf[points_key]  # Get the parsed data of 'points' layer
>>> type(parsed_pbf_points)
pandas.core.series.Series
>>> parsed_pbf_points.head()
         id  ...                                         properties
0    488432  ...  {'osm_id': '488432', 'name': None, 'barrier': ...
1    488658  ...  {'osm_id': '488658', 'name': 'Tickencote Inter...
2  13883868  ...  {'osm_id': '13883868', 'name': None, 'barrier'...
3  14049101  ...  {'osm_id': '14049101', 'name': None, 'barrier'...
4  14558402  ...  {'osm_id': '14558402', 'name': None, 'barrier'...
[5 rows x 3 columns]

>>> # Import the parsed 'points' data into the PostgreSQL database
>>> osmdb.import_osm_layer(
...     layer_data=parsed_pbf_points, table_name=subrgn_name, schema_name=points_key,
...     verbose=True, if_exists='replace')
To import data into "points"."Rutland" at postgres:***@localhost:5432/osmdb_test
? [No]|Yes: yes
The table "points"."Rutland" already exists and is replaced.
Importing the data into the table "points"."Rutland" ... Done.

>>> # Get the information of the table "points"."Rutland"
>>> tbl_col_info = osmdb.get_table_column_info(subrgn_name, points_key)
>>> tbl_col_info.head()
                    column_0    column_1    column_2
table_catalog     osmdb_test  osmdb_test  osmdb_test
table_schema          points      points      points
table_name           Rutland     Rutland     Rutland
column_name               id    geometry  properties
ordinal_position           1           2           3

Example 2 - Import data of the ‘railways’ layer of a shapefile*:

>>> # Read the data of 'railways' layer and delete the extracts
>>> lyr_name = 'railways'
>>> rutland_railways_shp = osmdb.reader.read_shp_zip(
...     subregion_name=subrgn_name, layer_names=lyr_name, data_dir=dat_dir,
...     rm_extracts=True, verbose=True)
Downloading "rutland-latest-free.shp.zip"
    to "tests\osm_data\rutland\" ... Done.
Extracting the following layer(s):
    'railways'
    from "tests\osm_data\rutland\rutland-latest-free.shp.zip"
      to "tests\osm_data\rutland\rutland-latest-free-shp\" ... Done.
Reading "tests\osm_data\rutland\rutland-latest-free-shp\gis_osm_railways_free_1.s...
Deleting the extracts "tests\osm_data\rutland\rutland-latest-free-shp\" ... Done.
>>> type(rutland_railways_shp)
collections.OrderedDict
>>> list(rutland_railways_shp.keys())
['railways']

>>> # Get the data of 'railways' layer
>>> rutland_railways_shp_ = rutland_railways_shp[lyr_name]
>>> rutland_railways_shp_.head()
    osm_id  code  ...                                        coordinates shape_type
0  2162114  6101  ...  [(-0.4528083, 52.6993402), (-0.4521571, 52.698...          3
1  3681043  6101  ...  [(-0.6531215, 52.5730787), (-0.6531793, 52.572...          3
2  3693985  6101  ...  [(-0.7323403, 52.6782102), (-0.7319059, 52.678...          3
3  3693986  6101  ...  [(-0.6173072, 52.6132317), (-0.6241869, 52.614...          3
4  4806329  6101  ...  [(-0.4576926, 52.7035194), (-0.4565358, 52.702...          3
[5 rows x 9 columns]

>>> # Import the 'railways' data into the PostgreSQL database
>>> osmdb.import_osm_layer(
...     layer_data=rutland_railways_shp_, table_name=subrgn_name, schema_name=lyr_name,
...     verbose=True)
To import data into "railways"."Rutland" at postgres:***@localhost:5432/osmdb_test
? [No]|Yes: yes
Creating a schema: "railways" ... Done.
Importing the data into the table "railways"."Rutland" ... Done.

>>> # Get the information of the table "railways"."Rutland"
>>> tbl_col_info = osmdb.get_table_column_info(subrgn_name, lyr_name)
>>> tbl_col_info.head()
                    column_0    column_1  ...     column_7    column_8
table_catalog     osmdb_test  osmdb_test  ...   osmdb_test  osmdb_test
table_schema        railways    railways  ...     railways    railways
table_name           Rutland     Rutland  ...      Rutland     Rutland
column_name           osm_id        code  ...  coordinates  shape_type
ordinal_position           1           2  ...            8           9
[5 rows x 9 columns]

Delete the test database and downloaded data files:

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

>>> # Delete the downloaded data files
>>> delete_dir(dat_dir, verbose=True)
To delete the directory "tests\osm_data\" (Not empty)
? [No]|Yes: yes
Deleting "tests\osm_data\" ... Done.