René Nyffenegger

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.