| 1 | #_{ Encoding and name | ||
| 2 | |||
| 3 | =encoding utf8 | ||
| 4 | =head1 NAME | ||
| 5 | Geo::OSM::DBI::CH | ||
| 6 | |||
| 7 | Store Open Street Map data with DBI, especially for Switzerland | ||
| 8 | =cut | ||
| 9 | |||
| 10 | package Geo::OSM::DBI::CH; | ||
| 11 | |||
| 12 | #_} | ||
| 13 | #_{ use ... | ||
| 14 | use warnings; | ||
| 15 | use strict; | ||
| 16 | |||
| 17 | use DBI; | ||
| 18 | |||
| 19 | use utf8; | ||
| 20 | use Carp; | ||
| 21 | |||
| 22 | use Geo::OSM::DBI; | ||
| 23 | our @ISA = qw(Geo::OSM::DBI); | ||
| 24 | |||
| 25 | #_} | ||
| 26 | our $VERSION = 0.01; | ||
| 27 | #_{ Synopsis | ||
| 28 | |||
| 29 | =head1 SYNOPSIS | ||
| 30 | use Geo::OSM::DBI; | ||
| 31 | # or ... | ||
| 32 | use Geo::OSM::DBI::CH; | ||
| 33 | |||
| 34 | The exact specifica are yet to be defined. | ||
| 35 | =cut | ||
| 36 | #_} | ||
| 37 | #_{ Methods | ||
| 38 | |||
| 39 | =head1 METHODS | ||
| 40 | =cut | ||
| 41 | |||
| 42 | sub new { #_{ | ||
| 43 | #_{ POD | ||
| 44 | |||
| 45 | =head2 new | ||
| 46 | |||
| 47 | my $osm_db_ch = Geo::OSM::DBI::CH->new($dbh); | ||
| 48 | |||
| 49 | Create and return a C<< Geo::OSM::DBI::CH >> object that will access the Open Street Database referenced by the C<DBI::db> object C<$dbh>). | ||
| 50 | It's unclear to me what a C<DBI::db> object actually is... | ||
| 51 | |||
| 52 | =cut | ||
| 53 | |||
| 54 | #_} | ||
| 55 | |||
| 56 | my $class = shift; | ||
| 57 | my $dbh = shift; | ||
| 58 | |||
| 59 | # croak "dbh is not a DBI object ($dbh)" unless $dbh -> isa('DBI::db'); | ||
| 60 | |||
| 61 | my $self = $class->SUPER::new($dbh); | ||
| 62 | |||
| 63 | croak "Wrong class $class" unless $self->isa('Geo::OSM::DBI::CH'); | ||
| 64 | |||
| 65 | return $self; | ||
| 66 | |||
| 67 | } #_} | ||
| 68 | sub create_table_municipalities_ch { #_{ | ||
| 69 | #_{ POD | ||
| 70 | |||
| 71 | =head2 create_table_municipalities_ch | ||
| 72 | |||
| 73 | $osm_db_ch->create_table_municipalities_ch(); | ||
| 74 | |||
| 75 | |||
| 76 | First creates the table C<municipalities> by calling the | ||
| 77 | L<< parent's class|Geo::OSM::DBI >> L<< create_table_municipalities|Geo::OSM::DBI/create_table_municipalities >>. | ||
| 78 | Then, it uses the data in table C<municipalities> to create C<municipalities_ch>. | ||
| 79 | Finanlly, it creates the view C<municipalities_ch_v>. | ||
| 80 | |||
| 81 | =cut | ||
| 82 | |||
| 83 | #_} | ||
| 84 | |||
| 85 | my $self = shift; | ||
| 86 | |||
| 87 | # Call method in parent class: | ||
| 88 | $self->create_table_municipalities(); | ||
| 89 | |||
| 90 | $self -> _sql_stmt(" | ||
| 91 | create table municipalities_ch ( | ||
| 92 | rel_id integer primary key, | ||
| 93 | bfs_no integer not null | ||
| 94 | )", | ||
| 95 | "create table municipalities_ch"); | ||
| 96 | |||
| 97 | $self -> _sql_stmt(" | ||
| 98 | insert into municipalities_ch | ||
| 99 | select | ||
| 100 | mun.rel_id rel_id, | ||
| 101 | bfs.val bfs_no | ||
| 102 | from | ||
| 103 | municipalities mun join | ||
| 104 | tag bfs on mun.rel_id = bfs.rel_id | ||
| 105 | where | ||
| 106 | bfs.key = 'swisstopo:BFS_NUMMER' | ||
| 107 | ", "fill table municipalities_ch"); | ||
| 108 | |||
| 109 | |||
| 110 | $self -> _sql_stmt(" | ||
| 111 | create view municipalities_ch_v as | ||
| 112 | select | ||
| 113 | rel_id, | ||
| 114 | name, | ||
| 115 | lat_min, | ||
| 116 | lon_min, | ||
| 117 | lat_max, | ||
| 118 | lon_max, | ||
| 119 | bfs_no | ||
| 120 | from | ||
| 121 | municipalities_ch join | ||
| 122 | municipalities using (rel_id) | ||
| 123 | ", | ||
| 124 | "create view municipalities_ch_v" | ||
| 125 | ); | ||
| 126 | |||
| 127 | |||
| 128 | # my $sth = $self->{dbh}->prepare($stmt); | ||
| 129 | # $sth->execute; | ||
| 130 | # | ||
| 131 | # my @ret; | ||
| 132 | # while (my $r = $sth->fetchrow_hashref) { | ||
| 133 | # push @ret, $r; | ||
| 134 | # } | ||
| 135 | # | ||
| 136 | # return @ret; | ||
| 137 | |||
| 138 | } #_} | ||
| 139 | sub municipalities_ch { #_{ | ||
| 140 | #_{ POD | ||
| 141 | |||
| 142 | =head2 municipalities_ch | ||
| 143 | |||
| 144 | $osm_db_ch->create_table_municipalities_ch(); | ||
| 145 | … | ||
| 146 | my %municipalities = $osm_db_ch->municipalities_ch(); | ||
| 147 | |||
| 148 | |||
| 149 | =cut | ||
| 150 | |||
| 151 | #_} | ||
| 152 | |||
| 153 | my $self = shift; | ||
| 154 | |||
| 155 | my $stmt = " | ||
| 156 | select | ||
| 157 | rel_id, | ||
| 158 | name, | ||
| 159 | lat_min, | ||
| 160 | lat_max, | ||
| 161 | lon_min, | ||
| 162 | lon_max, | ||
| 163 | bfs_no | ||
| 164 | from | ||
| 165 | municipalities_ch_v | ||
| 166 | "; | ||
| 167 | |||
| 168 | |||
| 169 | my $sth = $self->{dbh}->prepare($stmt); | ||
| 170 | $sth->execute; | ||
| 171 | |||
| 172 | my %ret; | ||
| 173 | while (my $r = $sth->fetchrow_hashref) { | ||
| 174 | $ret{$r->{rel_id}} = { | ||
| 175 | name => $r->{name }, | ||
| 176 | lat_min => $r->{lat_min}, | ||
| 177 | lat_max => $r->{lat_max}, | ||
| 178 | lon_min => $r->{lon_min}, | ||
| 179 | lon_max => $r->{lon_max}, | ||
| 180 | bfs_no => $r->{bfs_no }, | ||
| 181 | }; | ||
| 182 | } | ||
| 183 | |||
| 184 | return %ret; | ||
| 185 | |||
| 186 | } #_} | ||
| 187 | sub rel_id_ch { #_{ | ||
| 188 | #_{ POD | ||
| 189 | |||
| 190 | =head2 rel_id_ch | ||
| 191 | |||
| 192 | my $rel_id = $osm_db_ch->rel_id_ch(); | ||
| 193 | |||
| 194 | Return the relation id of Switzerland. | ||
| 195 | |||
| 196 | As of 2017-09-05, it returns C<51701>. | ||
| 197 | |||
| 198 | Note: apparently, a country can have multiple relations with | ||
| 199 | C<< key = 'ISO3166-1' >> (See L<< Geo::OSM::DBI/rel_ids_ISO_3166_1 >>), yet | ||
| 200 | Switzerland has (as it does not have access to the sea) only one. | ||
| 201 | |||
| 202 | =cut | ||
| 203 | |||
| 204 | #_} | ||
| 205 | |||
| 206 | my $self = shift; | ||
| 207 | my ($rel_id_ch) = $self->rel_ids_ISO_3166_1('CH'); | ||
| 208 | return $rel_id_ch; | ||
| 209 | |||
| 210 | } #_} | ||
| 211 | sub rel_ch { #_{ | ||
| 212 | #_{ POD | ||
| 213 | |||
| 214 | =head2 rel_id_ch | ||
| 215 | |||
| 216 | Return the L<relation|Geo::OSM::DBI::Primitive::Relation> of Switzerland. | ||
| 217 | |||
| 218 | See L<Geo::OSM::DBI/rel_id_ch> for more details. | ||
| 219 | |||
| 220 | |||
| 221 | =cut | ||
| 222 | |||
| 223 | #_} | ||
| 224 | |||
| 225 | my $self = shift; | ||
| 226 | |||
| 227 | my $rel_id_ch = $self->rel_id_ch; | ||
| 228 | |||
| 229 | return Geo::OSM::DBI::Primitive::Relation->new($rel_id_ch, $self); | ||
| 230 | |||
| 231 | } #_} | ||
| 232 | #_} | ||
| 233 | #_{ POD: Copyright and license | ||
| 234 | |||
| 235 | =head1 COPYRIGHT and LICENSE | ||
| 236 | |||
| 237 | Copyright © 2017 René Nyffenegger, Switzerland. All rights reserved. | ||
| 238 | |||
| 239 | This program is free software; you can redistribute it and/or modify it | ||
| 240 | under the terms of the the Artistic License (2.0). You may obtain a | ||
| 241 | copy of the full license at: L<http://www.perlfoundation.org/artistic_license_2_0> | ||
| 242 | |||
| 243 | =cut | ||
| 244 | |||
| 245 | #_} | ||
| 246 | #_{ POD: Source Code | ||
| 247 | |||
| 248 | =head1 SOURCE CODE | ||
| 249 | |||
| 250 | The source code is on L<< github|https://github.com/ReneNyffenegger/perl-Geo-OSM-DBI >>. Meaningful pull requests are welcome. | ||
| 251 | |||
| 252 | =cut | ||
| 253 | |||
| 254 | #_} | ||
| 255 | |||
| 256 | 'tq84'; |