DBI.pm

filter: Statementstmt 90.4% 19 / 21 Branchbran n/a Conditioncond n/a MC/DCmcdc n/a Subroutinesub 100% 7 / 7 Podpod n/a totaltotal 92.8% 26 / 28 CC 1 SCAR 0.4
CC
1
Cov
90%
BEGIN
1.3
BEGIN
1.0
BEGIN
1.0
CRAP 1.0
« DBI/Primitive/Relation.pm ↑ Summary
1 # Encoding and name #_{
2  
3 =encoding utf8
4 =head1 NAME
5  
6 Geo::OSM::DBI - Store Open Street Map data with DBI.
7  
8 =cut
9 package Geo::OSM::DBI;
10 #_}
11 #_{ use …
12 6 ▶use warnings;
Subroutine BEGIN: called CC=1 SCAR=0.0
13 6 ▶use strict;
Subroutine BEGIN: called CC=1 SCAR=0.0
14  
15 6 ▶use DBI;
Subroutine BEGIN: called CC=1 SCAR=0.0
16 6 ▶use Time::HiRes qw(time);
Subroutine BEGIN: called CC=1 SCAR=0.0
17  
18 6 ▶use utf8;
Subroutine BEGIN: called CC=1 SCAR=0.0
19 6 ▶use Carp;
Subroutine BEGIN: called CC=1 SCAR=0.0
20  
21 0 ▶use Geo::OSM::DBI::Primitive::Relation;
Subroutine BEGIN: called CC=1 SCAR=2.6
22  
23 #_}
24 our $VERSION = 0.01;
25 #_{ Synopsis
26  
27 =head1 SYNOPSIS
28  
29 use DBI;
30 use Geo::OSM::DBI;
31  
32 # Create a DBI connection to a database ...
33 my $dbh = DBI->connect("dbi:SQLite:dbname=…", '', '', {sqlite_unicode=>1}) or die;
34  
35 # ... and use the DBI connection to construct an OSM DB:
36 my $osm_db = Geo::OSM::DBI->new{$dbh};
37  
38 $osm_db->create_base_schema_tables(…);
39  
40 # TODO: load schemas with Open Street Map data...
41
42 $osm_db->create_base_schema_indexes();
43  
44 =cut
45 #_}
46 #_{ Overview
47  
48 =head1 OVERVIEW
49  
50 Manage <I>OpenStreetMap</I> data in a L<DBI> database.
51  
52 Originally, the package was thought to be database product agnostic (does the I<I> in C<DBI> not stand for independent?). It turned out, that I was
53 happy if I could make it work with L<DBD::SQLite>, so to call it DB-independent is not correct.
54  
55 =cut
56  
57 #_}
58 #_{ Methods
59  
60 =head1 METHODS
61 =cut
62  
63 sub new { #_{
64 #_{ POD
65  
66 =head2 new
67  
68 my $osm_db = Geo::OSM::DBI->new($dbh);
69  
70 Create and return a C<< Geo::OSM::DBI >> object that will access the Open Street Database referenced by the C<DBI::db> object C<$dbh>).
71 It's unclear to me what a C<DBI::db> object actually is...
72  
73 =cut
74  
75 #_}
76  
77 my $class = shift;
78 my $dbh = shift;
79  
80 croak "dbh is not a DBI object ($dbh)" unless $dbh -> isa('DBI::db');
81  
82 my $self = {};
83 bless $self, $class;
84 croak "Wrong class $class" unless $self->isa('Geo::OSM::DBI');
85  
86 $self->{dbh} = $dbh;
87  
88 return $self;
89  
90 } #_}
91 #_{ Create base schema objects
92 sub create_base_schema_tables { #_{
93 #_{ POD
94  
95 =head2 create_base_schema_tables
96  
97 $osm_db->create_base_schema_tables();
98 $osm_db->create_base_schema_tables({schema => $schema_name);
99  
100 Create the base tables C<nod>, C<nod_way>, C<rel_mem> and C<tag>.
101  
102 After creating the schema, the tables should be filled with C<pbf2sqlite.v2.py>.
103  
104 After filling the tables, the indexes on the tables should be created with L</create_base_schema_indexes>.
105  
106  
107 =cut
108  
109 #_}
110
111 my $self = shift;
112 my $opts = shift;
113  
114 my ($schema, $schema_dot) = _schema_dot_from_opts($opts);
115  
116 $self->_sql_stmt("
117 create table ${schema_dot}nod (
118 id integer primary key,
119 lat real not null,
120 lon real not null
121 )",
122 "create table ${schema_dot}nod"
123 );
124  
125 $self->_sql_stmt("
126 create table ${schema_dot}nod_way (
127 way_id integer not null,
128 nod_id integer not null,
129 order_ integer not null
130 )",
131 "create table ${schema_dot}nod_way");
132  
133 $self->_sql_stmt("
134 create table ${schema_dot}rel_mem (
135 rel_of integer not null,
136 order_ integer not null,
137 nod_id integer,
138 way_id integer,
139 rel_id integer,
140 rol text
141 )",
142 "create table ${schema_dot}rel_mem");
143  
144 # $self->{dbh}->do("
145 $self->_sql_stmt("
146 create table ${schema_dot}tag(
147 nod_id integer null,
148 way_id integer null,
149 rel_id integer null,
150 key text not null,
151 val text not null
152 )",
153 "create table ${schema_dot}tag");
154  
155 } #_}
156 sub create_base_schema_indexes { #_{
157 #_{ POD
158  
159 =head2 create_base_schema_indexes()
160  
161 $osm_db->create_base_schema_tables();
162  
163 # fill tables (as of yet with pbf2sqlite.v2.py
164  
165 $osm_db->create_base_schema_indexes();
166 # or, if create_base_schema_indexes was created in another schema:
167 $osm_db->create_base_schema_indexes({schema=>$schema_name);
168  
169 Create the base tables C<nod>, C<nod_way>, C<rel_mem> and C<tag>.
170  
171 After creating the base schema and filling the tables, the indexes should be created on the base schema tables.
172  
173 =cut
174  
175 my $self = shift;
176 my $opts = shift;
177  
178 my ($schema, $schema_dot) = _schema_dot_from_opts($opts);
179  
180 #
181 # TODO: to put the schema in front of the index name rather than the table name seems
182 # to be very sqlite'ish.
183 #
184 $self->_sql_stmt("create index ${schema_dot}nod_way_ix_way_id on nod_way (way_id)" , "index ${schema_dot}nod_way_ix_way_id");
185
186 $self->_sql_stmt("create index ${schema_dot}tag_ix_val on tag ( val)" , "index ${schema_dot}tag_ix_val" );
187 $self->_sql_stmt("create index ${schema_dot}tag_ix_key_val on tag (key, val)" , "index ${schema_dot}tag_ix_key_val" );
188
189 $self->_sql_stmt("create index ${schema_dot}tag_ix_nod_id on tag (nod_id)" , "index ${schema_dot}tag_ix_nod_id" );
190 $self->_sql_stmt("create index ${schema_dot}tag_ix_way_id on tag (way_id)" , "index ${schema_dot}tag_ix_way_id" );
191 $self->_sql_stmt("create index ${schema_dot}tag_ix_rel_id on tag (rel_id)" , "index ${schema_dot}tag_ix_rel_id" );
192  
193 # 2017-08-28
194 # $self->{dbh}->do("create index ${schema_dot}rel_mem_ix_nod_id on rel_mem (nod_id)" );
195 $self->_sql_stmt("create index ${schema_dot}rel_mem_ix_rel_of on rel_mem (rel_of)" , "index ${schema_dot}rel_mem_ix_rel_of");
196  
197 # 2017-09-11
198 $self->_sql_stmt("analyze $schema", 'analyze');
199  
200 #_}
201 } #_}
202 #_}
203 sub create_table_municipalities { #_{
204 #_{ POD
205  
206 =head2 create_table_municipalities
207  
208 $osm->create_table_municipalities();
209  
210 Creates the table C<municipalites>.
211  
212 =cut
213  
214 #_}
215  
216 my $self = shift;
217  
218 $self -> _sql_stmt("
219 create table municipalities (
220 rel_id integer primary key,
221 name text not null,
222 lat_min real not null,
223 lon_min real not null,
224 lat_max real not null,
225 lon_max real not null,
226 cnt_ways integer not null,
227 cnt_nodes integer not null,
228 cnt_nodes_verification integer not null
229 )",
230 "create table municipalities"
231 );
232  
233 $self->_sql_stmt("commit", "commit");
234  
235  
236 $self -> _sql_stmt("
237 insert into municipalities
238 select
239 admi.rel_id rel_id,
240 name.val name,
241 min (node.lat ) lat_min,
242 min (node.lon ) lon_min,
243 max (node.lat ) lat_max,
244 max (node.lon ) lon_max,
245 count(distinct relm.way_id) cnt_ways,
246 count(distinct node.id ) cnt_nodes,
247 /* cnt_nodes_verification:
248 Must/should be 0 because each way counts one node that another way already counted.
249 Borders that are not 100 % in the database return -1 or so.
250 */
251 count(* ) -
252 count(distinct relm.way_id) -
253 count(distinct node.id ) cnt_nodes_verification
254 from
255 tag admi join
256 tag name on admi.rel_id = name.rel_id join
257 rel_mem relm on admi.rel_id = relm.rel_of join
258 nod_way nodw on relm.way_id = nodw.way_id join
259 nod node on nodw.nod_id = node.id
260 where
261 admi.key = 'admin_level' and
262 admi.val = 8 and
263 name.key = 'name'
264 group by
265 admi.rel_id,
266 name.val
267 order by
268 -- relm.way_id,
269 -- node.id
270 cnt_nodes_verification,
271 name
272 ", "fill table municipalities");
273 #q
274 #q $sth->execute or croak;
275 #q
276 #Qwhile (my @r = $sth->fetchrow_array) {
277 #Q printf "%2d %2d %2d %s\n", $r[0], $r[1], $r[2], $r[3];
278 #Q}
279  
280 } #_}
281 sub create_area_tables { #_{
282 #_{ POD
283  
284 =head2 new
285  
286 $osm_db->create_area_tables(
287 coords => {
288 lat_min => 47,
289 lat_max => 48,
290 lon_min => 7,
291 lon_max => 9
292 },
293 schema_name_to => 'area'
294 });
295  
296 $osm_db->create_area_tables(
297 municipality_rel_id => $rel_id,
298 schema_name_to => 'area'
299 });
300  
301 =cut
302  
303 #_}
304  
305 my $self = shift;
306 my $opts = shift;
307  
308 my $lat_min;
309 my $lat_max;
310 my $lon_min;
311 my $lon_max;
312  
313 if (my $coords = delete $opts->{coords}) {
314  
315 $lat_min = $coords->{lat_min};
316 $lat_max = $coords->{lat_max};
317 $lon_min = $coords->{lon_min};
318 $lon_max = $coords->{lon_max};
319 }
320 elsif (my $municipality_rel_id = delete $opts->{municipality_rel_id}) {
321 my $sth = $self->{dbh}->prepare ('select lat_min, lat_max, lon_min, lon_max from municipalities where rel_id = ?');
322 $sth->execute($municipality_rel_id);
323 my $r = $sth->fetchrow_hashref or croak "No record found for municipality_rel_id $municipality_rel_id";
324  
325 $lat_min = $r->{lat_min};
326 $lat_max = $r->{lat_max};
327 $lon_min = $r->{lon_min};
328 $lon_max = $r->{lon_max};
329  
330 }
331  
332 my ($schema_name_to, $schema_name_to_dot) = _schema_dot_from_opts($opts, 'schema_name_to');
333 croak "Must have a destination schema name" unless $schema_name_to;
334  
335 croak "lat_min not defined" unless defined $lat_min;
336 croak "lat_max not defined" unless defined $lat_max;
337 croak "lon_min not defined" unless defined $lon_min;
338 croak "lon_max not defined" unless defined $lon_max;
339  
340 $self->create_base_schema_tables({schema=>$schema_name_to});
341  
342 #_{ nod
343  
344 # my $f = '%16.13f';
345 my $f = '%s';
346
347 my $stmt = sprintf("
348
349 insert into ${schema_name_to_dot}nod
350 select * from nod
351 where
352 lat between $f and $f and
353 lon between $f and $f
354
355 ", $lat_min, $lat_max, $lon_min, $lon_max);
356
357 $self->_sql_stmt($stmt, "${schema_name_to}nod filled");
358
359  
360 #_}
361 #_{ nod_way
362  
363 $stmt = sprintf("
364
365 insert into ${schema_name_to_dot}nod_way
366 select * from nod_way
367 where
368 nod_id in (
369 select
370 id
371 from
372 ${schema_name_to_dot}nod
373 )
374 ");
375  
376 $self->_sql_stmt($stmt, "${schema_name_to_dot}nod_way filled");
377  
378 #_}
379 #_{ rel_mem
380  
381 $stmt = sprintf("
382
383 insert into ${schema_name_to_dot}rel_mem
384 select * from rel_mem
385 where
386 nod_id in (select id from ${schema_name_to_dot}nod ) or
387 way_id in (select distinct way_id from ${schema_name_to_dot}nod_way) or
388 rel_id in (select distinct rel_id
389 from rel_mem where
390 nod_id in (select id from ${schema_name_to_dot}nod ) or
391 way_id in (select distinct way_id from ${schema_name_to_dot}nod_way)
392 ) or
393 rel_id in (select distinct rel_of
394 from rel_mem where
395 nod_id in (select id from ${schema_name_to_dot}nod ) or
396 way_id in (select distinct way_id from ${schema_name_to_dot}nod_way)
397 )
398 ");
399  
400 $self->_sql_stmt($stmt, "${schema_name_to_dot}.nod_rel filled");
401  
402 #_}
403 #_{ tag
404  
405 $stmt = sprintf("
406  
407 insert into ${schema_name_to_dot}tag
408 select * from tag
409 where
410 nod_id in (select id from ${schema_name_to_dot}nod ) or
411 way_id in (select distinct way_id from ${schema_name_to_dot}nod_way) or
412 rel_id in (select distinct rel_of from ${schema_name_to_dot}rel_mem) or
413 rel_id in (select distinct rel_id from ${schema_name_to_dot}rel_mem)
414 ");
415  
416 $self->_sql_stmt($stmt, "area_db.way_rel filled");
417  
418 #_}
419  
420 $self->create_base_schema_indexes({schema=>$schema_name_to});
421  
422 } #_}
423 sub _schema_dot_from_opts { #_{
424 #_{ POD
425  
426 =head2 _schema_dot_from_opts
427  
428 my ($schema, $schema_dot) = _schema_dot_from_opts($opts );
429 # or
430 my ($schema, $schema_dot) = _schema_dot_from_opts($opts, "opt_name");
431  
432 Returns C<< ('schema_name', 'schema_name.') >> or C<< ('', '') >>.
433  
434 =cut
435  
436 #_}
437  
438 my $opts = shift;
439 my $name = shift // 'schema';
440  
441 my $schema = delete $opts->{$name} // '';
442 my $schema_dot = '';
443 if ($schema) {
444 $schema_dot = "$schema.";
445 }
446 return ($schema, $schema_dot);
447  
448 } #_}
449 sub _sql_stmt { #_{
450 #_{ POD
451  
452 =head2 _sql_stmt
453  
454 $self->_sql_stmt($sql_text, 'dientifiying text')
455  
456 Internal function. executes C<$sql_text>. Prints time it took to complete
457  
458 =cut
459  
460 #_}
461  
462 my $self = shift;
463 my $stmt = shift;
464 my $desc = shift;
465  
466 my $t0 = time;
467 $self->{dbh}->do($stmt) or croak ("Could not execute $stmt");
468 my $t1 = time;
469  
470 printf("SQL: $desc, took %6.3f seconds\n", $t1-$t0);
471  
472 } #_}
473 sub _sth_prepare_ways_of_relation { #_{
474 #_{ POD
475  
476 =head2 _sth_prepare_name
477  
478 my $primitive_type = 'rel'; # or 'way'. or 'node';
479  
480 my sth = $osm_dbi->_sth_prepare_name();
481  
482 $sth->execute($primitive_id);
483  
484 Prepares the statement handle to get the name for a primitive. C<$primitive_type> must be C<node>, C<way> or C<relation>.
485  
486 =cut
487  
488 #_}
489  
490 my $self = shift;
491 my $primitive_type = shift;
492  
493 croak "Unsupported primitive type $primitive_type" unless grep { $_ eq $primitive_type} qw(rel nod way);
494  
495 my $sth = $self->{dbh}->prepare("select val as name from tag where ${primitive_type}_id = ? and key = 'name'") or croak;
496  
497 return $sth;
498  
499 } #_}
500 sub _sth_prepare_name { #_{
501 #_{ POD
502  
503 =head2 _sth_prepare_name
504  
505 my $primitive_type = 'rel'; # or 'way'. or 'node';
506  
507 my sth = $osm_dbi->_sth_prepare_name();
508  
509 $sth->execute($primitive_id);
510  
511 Prepares the statement handle to get the name for a primitive. C<$primitive_type> must be C<node>, C<way> or C<relation>.
512  
513 =cut
514  
515 #_}
516  
517 my $self = shift;
518 my $primitive_type = shift;
519  
520 croak "Unsupported primitive type $primitive_type" unless grep { $_ eq $primitive_type} qw(rel nod way);
521  
522 my $sth = $self->{dbh}->prepare("select val as name from tag where ${primitive_type}_id = ? and key = 'name'") or croak;
523  
524 return $sth;
525  
526 } #_}
527 sub _sth_prepare_name_in_lang { #_{
528 #_{ POD
529  
530 =head2 _sth_prepare_name_in_lang
531  
532 my $primitive_type = 'rel'; # or 'way'. or 'node';
533  
534 my sth = $osm_dbi->_sth_prepare_name_in_lang($primitive_type);
535  
536 my $lang = 'de'; # or 'it' or 'en' or 'fr' or …
537  
538 $sth->execute($primitive_id, "lang:$lang");
539  
540 Prepares the statement handle to get the name for a primitive. C<$primitive_type> must be C<node>, C<way> or C<relation>.
541  
542 =cut
543  
544 #_}
545  
546 my $self = shift;
547 my $primitive_type = shift;
548  
549 croak "Unsupported primitive type $primitive_type" unless grep { $_ eq $primitive_type} qw(rel nod way);
550  
551 my $sth = $self->{dbh}->prepare("select val as name from tag where ${primitive_type}_id = ? and key = ?") or croak;
552  
553 return $sth;
554  
555 } #_}
556 sub rel_ids_ISO_3166_1 { #_{
557  
558 #_{ POD
559  
560 =head2 rel_ids_ISO_3166_1
561  
562 my $two_letter_country_code = 'DE';
563 my @rel_ids = $self->rel_ids_ISO_3166_1($two_letter_country_code);
564  
565 Returns the L<< relation|Geo::OSM::Primitive::Relation >> ids for a country.
566 Apparently, a country can have multiple relation ids. For example, Germany has three (as 2017-09-05).
567 These relations somehow distinguish between land mass and land mass plus sea territories.
568  
569 =cut
570  
571 #_}
572  
573 my $self = shift;
574 my $two_letter_country_code = shift or croak 'Need a two letter country code';
575  
576 my $sth = $self->{dbh}->prepare("select rel_id from tag where key = 'ISO3166-1' and val = ? and rel_id is not null") or croak;
577 $sth->execute($two_letter_country_code) or croak;
578  
579 my @ret;
580  
581 while (my ($rel_id) = $sth->fetchrow_array) {
582 push @ret, $rel_id;
583 # Geo::OSM::DBI::Primitive::Relation->new($rel_id, $self);
584 }
585  
586 return @ret;
587  
588 } #_}
589 sub rels_ISO_3166_1 {
590 #_{ POD
591  
592 =head2 rels_ISO_3166_1
593  
594 my $two_letter_country_code = 'DE';
595 my @rels = $self->rels_ISO_3166_1($two_letter_country_code);
596  
597 Returns the L<< relations|Geo::OSM::Primitive::Relation >> for a country.
598 See L</rels_ISO_3166_1> for more details.
599  
600 =cut
601  
602 #_}
603  
604 my $self = shift;
605 my $two_letter_country_code = shift or croak 'Need a two letter country code';
606  
607 my @rel_ids_ISO_3166_1 = $self->rel_ids_ISO_3166_1($two_letter_country_code);
608  
609 my @ret;
610 for my $rel_id (@rel_ids_ISO_3166_1) {
611 push @ret, Geo::OSM::DBI::Primitive::Relation->new($rel_id, $self);
612 }
613 return @ret;
614  
615 }
616 #_}
617 #_{ POD: Testing
618  
619 =head1 TESTING
620  
621 The package unfortunately only comes with some basic tests.
622  
623 The modules can be tested however by loading the Swiss dataset
624 from L<< geofabrik.cde|http://download.geofabrik.de/europe.html >> with
625 L<< load-country.pl|https://github.com/ReneNyffenegger/OpenStreetMap/blob/master/scripts/load-country.pl >> and then
626 running the script
627 L<< do-Switzerland.pl|https://github.com/ReneNyffenegger/OpenStreetMap/blob/master/scripts/do-Switzerland >>.
628  
629 =cut
630  
631 #_}
632 #_{ POD: Copyright and License
633  
634 =head1 COPYRIGHT and LICENSE
635  
636 Copyright © 2017 René Nyffenegger, Switzerland. All rights reserved.
637  
638 This program is free software; you can redistribute it and/or modify it
639 under the terms of the the Artistic License (2.0). You may obtain a
640 copy of the full license at: L<http://www.perlfoundation.org/artistic_license_2_0>
641  
642 =cut
643  
644 #_}
645 #_{ POD: Source Code
646  
647 =head1 Source Code
648  
649 The source code is on L<< github|https://github.com/ReneNyffenegger/perl-Geo-OSM-DBI >>. Meaningful pull requests are welcome.
650  
651 =cut
652  
653 #_}
654  
655 'tq84';
« DBI/Primitive/Relation.pm ↑ Summary