| Index: trunk/tools/osm-tools/osm2pgsql-style/create-language-views.pl |
| — | — | @@ -0,0 +1,207 @@ |
| | 2 | +#!/usr/bin/env perl |
| | 3 | + |
| | 4 | +=head1 NAME |
| | 5 | + |
| | 6 | +create-language-views.pl - Create the views needed to render l18n-enabled maps with mapnik |
| | 7 | + |
| | 8 | +=head1 SYNOPSIS |
| | 9 | + |
| | 10 | + perl wikipedia-language-codes.pl > wikipedia-languages.yml |
| | 11 | + perl create-language-views.pl --languages wikipedia-languages.yml --psql-user gis > create_views.sql |
| | 12 | + psql -U gis gis < create_views.sql |
| | 13 | + |
| | 14 | +=head1 OPTIONS |
| | 15 | + |
| | 16 | +=over |
| | 17 | + |
| | 18 | +=item -h, --help |
| | 19 | + |
| | 20 | +Print a usage message listing all available options |
| | 21 | + |
| | 22 | +=item --languages |
| | 23 | + |
| | 24 | +A YAML file to read languages from, e.g. F<wikipedia-languages.yml> |
| | 25 | + |
| | 26 | +=item --psql-user |
| | 27 | + |
| | 28 | +The PostgreSQL user to use. |
| | 29 | + |
| | 30 | +=head1 LINKS |
| | 31 | + |
| | 32 | +L<http://wiki.openstreetmap.org/wiki/Regionalisedmap> |
| | 33 | + |
| | 34 | +=head1 AUTHOR |
| | 35 | + |
| | 36 | +E<AElig>var ArnfjE<ouml>rE<eth> Bjarmason <avarab@gmail.com> |
| | 37 | + |
| | 38 | +=cut |
| | 39 | + |
| | 40 | +use feature ':5.10'; |
| | 41 | +use strict; |
| | 42 | +use warnings; |
| | 43 | + |
| | 44 | +use YAML::Syck qw(LoadFile); |
| | 45 | + |
| | 46 | +use Getopt::Long; |
| | 47 | +use Pod::Usage (); |
| | 48 | + |
| | 49 | +# |
| | 50 | +# Get command line options |
| | 51 | +# |
| | 52 | + |
| | 53 | +Getopt::Long::Parser->new( |
| | 54 | + config => [ qw< bundling no_ignore_case no_require_order > ], |
| | 55 | +)->getoptions( |
| | 56 | + 'h|help' => \my $help, |
| | 57 | + 'languages=s' => \my $languages, |
| | 58 | + 'psql-user=s' => \my $psql_user, |
| | 59 | + 'style=s' => \my $style, |
| | 60 | +) or help(); |
| | 61 | + |
| | 62 | +help() if $help; |
| | 63 | + |
| | 64 | +unless (-r $languages) |
| | 65 | +{ |
| | 66 | + warn "Can't read the file `$languages'"; |
| | 67 | + help(); |
| | 68 | +} |
| | 69 | + |
| | 70 | +# |
| | 71 | +# Config |
| | 72 | +# |
| | 73 | + |
| | 74 | +# The tables we're creating views for |
| | 75 | +my @tables = split_query( "select table_name from information_schema.tables where table_name ~ '^planet_osm';" ); |
| | 76 | + |
| | 77 | +# Columns in those tables |
| | 78 | +my %columns; |
| | 79 | + |
| | 80 | +for my $table (@tables) { |
| | 81 | + my @columns = split_query( "select column_name from information_schema.columns where table_name = '$table';" ); |
| | 82 | + |
| | 83 | + $columns{$table} = [ @columns ]; |
| | 84 | +} |
| | 85 | + |
| | 86 | +# Our languages |
| | 87 | +my %languages = %{ LoadFile($languages) }; |
| | 88 | +my @languages = sorted_languages(%languages); |
| | 89 | + |
| | 90 | +# |
| | 91 | +# main |
| | 92 | +# |
| | 93 | + |
| | 94 | +my %created_views; |
| | 95 | +for my $language (@languages) |
| | 96 | +{ |
| | 97 | + my $code = $language->[0]; |
| | 98 | + my $name = $language->[1]; |
| | 99 | + |
| | 100 | + say "--"; |
| | 101 | + say "-- Begin views for $code ($name)"; |
| | 102 | + say "--"; |
| | 103 | + |
| | 104 | + for my $table (@tables) { |
| | 105 | + my @munged_columns = munged_columns($code, @{ $columns{$table} }); |
| | 106 | + |
| | 107 | + my $view_name = "view_${table}_lang_${code}"; |
| | 108 | + say "CREATE VIEW \"$view_name\" as"; |
| | 109 | + say " SELECT"; |
| | 110 | + say join ",\n", map { " $_" } @munged_columns; |
| | 111 | + say "FROM $table;"; |
| | 112 | + say ""; |
| | 113 | + |
| | 114 | + push @{ $created_views{ $table} } => $view_name; |
| | 115 | + } |
| | 116 | + |
| | 117 | + say "--"; |
| | 118 | + say "-- End views for $code ($name)"; |
| | 119 | + say "--"; |
| | 120 | + say ""; |
| | 121 | + say ""; |
| | 122 | +} |
| | 123 | + |
| | 124 | +while (my ($table, $views) = each %created_views) { |
| | 125 | + my @views = @$views; |
| | 126 | + |
| | 127 | + my $type; |
| | 128 | + given ($table) { |
| | 129 | + when ("planet_osm_point") { $type = "POINT" } |
| | 130 | + when ("planet_osm_line") { $type = "LINESTRING" } |
| | 131 | + when ("planet_osm_polygon") { $type = "POLYGON" } |
| | 132 | + when ("planet_osm_roads") { $type = "LINESTRING" } |
| | 133 | + } |
| | 134 | + |
| | 135 | + say ""; |
| | 136 | + say "--"; |
| | 137 | + say "-- Views of $table ($type)"; |
| | 138 | + say "--"; |
| | 139 | + say ""; |
| | 140 | + |
| | 141 | + for my $view (@views) { |
| | 142 | + say qq[INSERT INTO geometry_columns VALUES ('', 'public', '$view', 'way', 2, 900913, '$type');]; |
| | 143 | + } |
| | 144 | +} |
| | 145 | + |
| | 146 | +exit 0; |
| | 147 | + |
| | 148 | +sub munged_columns |
| | 149 | +{ |
| | 150 | + my ($code, @columns) = @_; |
| | 151 | + my @ret; |
| | 152 | + |
| | 153 | + for my $column (@columns) { |
| | 154 | + if ($column !~ /^name/) { |
| | 155 | + push @ret => qq["$column"]; |
| | 156 | + } else { |
| | 157 | + if ($column =~ /^name$/) { |
| | 158 | + # We only want one name column |
| | 159 | + push @ret => qq[case when "name:$code" is not null then "name:$code" else name end as name]; |
| | 160 | + } else { |
| | 161 | + # Drop all other name:$whatever columns |
| | 162 | + } |
| | 163 | + } |
| | 164 | + } |
| | 165 | + |
| | 166 | + return @ret; |
| | 167 | + |
| | 168 | +} |
| | 169 | + |
| | 170 | +sub split_query |
| | 171 | +{ |
| | 172 | + my $query = shift; |
| | 173 | + |
| | 174 | + map { /(\S+)/; $1 } split /^/, do_query($query); |
| | 175 | +} |
| | 176 | + |
| | 177 | +sub do_query |
| | 178 | +{ |
| | 179 | + my $query = shift; |
| | 180 | + |
| | 181 | + my $out = qx[ echo "$query" | psql -t -A -U $psql_user ]; |
| | 182 | + return $out; |
| | 183 | +} |
| | 184 | + |
| | 185 | +sub sorted_languages |
| | 186 | +{ |
| | 187 | + my %lang = @_; |
| | 188 | + my @ret; |
| | 189 | + |
| | 190 | + for my $key (sort keys %lang) |
| | 191 | + { |
| | 192 | + push @ret => [ $key, $lang{$key} ]; |
| | 193 | + } |
| | 194 | + |
| | 195 | + @ret; |
| | 196 | +} |
| | 197 | + |
| | 198 | + |
| | 199 | +sub help |
| | 200 | +{ |
| | 201 | + require Pod::Usage; |
| | 202 | + my %arg = @_; |
| | 203 | + |
| | 204 | + Pod::Usage::pod2usage( |
| | 205 | + -verbose => $arg{ verbose }, |
| | 206 | + -exitval => $arg{ exitval } || 0, |
| | 207 | + ); |
| | 208 | +} |