The London Perl and Raku Workshop takes place on 26th Oct 2024. If your company depends on Perl, please consider sponsoring and/or attending.

Geo::OSM::DBI - Store Open Street Map data with DBI.

SYNOPSIS

use DBI;
use Geo::OSM::DBI;

# Create a DBI connection to a database ...
my $dbh = DBI->connect("dbi:SQLite:dbname=…", '', '', {sqlite_unicode=>1}) or die;

# ... and use the DBI connection to construct an OSM DB:
my $osm_db = Geo::OSM::DBI->new{$dbh};

$osm_db->create_base_schema_tables(…);

# TODO: load schemas with Open Street Map data...

$osm_db->create_base_schema_indexes();

OVERVIEW

Manage <I>OpenStreetMap</I> data in a DBI database.

Originally, the package was thought to be database product agnostic (does the I in DBI not stand for independent?). It turned out, that I was happy if I could make it work with DBD::SQLite, so to call it DB-independent is not correct.

METHODS

new

my $osm_db = Geo::OSM::DBI->new($dbh);

Create and return a Geo::OSM::DBI object that will access the Open Street Database referenced by the DBI::db object $dbh). It's unclear to me what a DBI::db object actually is...

create_base_schema_tables

$osm_db->create_base_schema_tables();
$osm_db->create_base_schema_tables({schema => $schema_name);

Create the base tables nod, nod_way, rel_mem and tag.

After creating the schema, the tables should be filled with pbf2sqlite.v2.py.

After filling the tables, the indexes on the tables should be created with "create_base_schema_indexes".

create_base_schema_indexes()

$osm_db->create_base_schema_tables();

# fill tables (as of yet with pbf2sqlite.v2.py

$osm_db->create_base_schema_indexes();
# or, if create_base_schema_indexes was created in another schema:
$osm_db->create_base_schema_indexes({schema=>$schema_name);

Create the base tables nod, nod_way, rel_mem and tag.

After creating the base schema and filling the tables, the indexes should be created on the base schema tables.

create_table_municipalities

$osm->create_table_municipalities();

Creates the table municipalites.

new

$osm_db->create_area_tables(
  coords           => {
    lat_min => 47,
    lat_max => 48,
    lon_min =>  7,
    lon_max =>  9
  },
  schema_name_to   => 'area'
});

$osm_db->create_area_tables(
  municipality_rel_id =>  $rel_id,
  schema_name_to      => 'area'
});

_schema_dot_from_opts

my ($schema, $schema_dot) = _schema_dot_from_opts($opts            );
# or
my ($schema, $schema_dot) = _schema_dot_from_opts($opts, "opt_name");

Returns ('schema_name', 'schema_name.') or ('', '').

_sql_stmt

$self->_sql_stmt($sql_text, 'dientifiying text')

Internal function. executes $sql_text. Prints time it took to complete

_sth_prepare_name

my $primitive_type = 'rel'; # or 'way'. or 'node';

my sth = $osm_dbi->_sth_prepare_name(); 

$sth->execute($primitive_id);

Prepares the statement handle to get the name for a primitive. $primitive_type must be node, way or relation.

_sth_prepare_name

my $primitive_type = 'rel'; # or 'way'. or 'node';

my sth = $osm_dbi->_sth_prepare_name(); 

$sth->execute($primitive_id);

Prepares the statement handle to get the name for a primitive. $primitive_type must be node, way or relation.

_sth_prepare_name_in_lang

my $primitive_type = 'rel'; # or 'way'. or 'node';

my sth = $osm_dbi->_sth_prepare_name_in_lang($primitive_type); 

my $lang           = 'de';  # or 'it' or 'en' or 'fr' or …

$sth->execute($primitive_id, "lang:$lang");

Prepares the statement handle to get the name for a primitive. $primitive_type must be node, way or relation.

rel_ids_ISO_3166_1

my $two_letter_country_code = 'DE';
my @rel_ids = $self->rel_ids_ISO_3166_1($two_letter_country_code);

Returns the relation ids for a country. Apparently, a country can have multiple relation ids. For example, Germany has three (as 2017-09-05). These relations somehow distinguish between land mass and land mass plus sea territories.

rels_ISO_3166_1

my $two_letter_country_code = 'DE';
my @rels = $self->rels_ISO_3166_1($two_letter_country_code);

Returns the relations for a country. See "rels_ISO_3166_1" for more details.

TESTING

The package unfortunately only comes with some basic tests.

The modules can be tested however by loading the Swiss dataset from geofabrik.cde with load-country.pl and then running the script do-Switzerland.pl.

COPYRIGHT and LICENSE

Copyright © 2017 René Nyffenegger, Switzerland. All rights reserved.

This program is free software; you can redistribute it and/or modify it under the terms of the the Artistic License (2.0). You may obtain a copy of the full license at: http://www.perlfoundation.org/artistic_license_2_0

Source Code

The source code is on github. Meaningful pull requests are welcome.