| 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'; | ||