source: subversion/applications/utils/import/airport_import/import_apt.pl @ 5763

Last change on this file since 5763 was 2669, checked in by hakan, 13 years ago

Import runways, store unicode place names

  • Property svn:executable set to *
  • Property svn:keywords set to Revision
File size: 9.6 KB
Line 
1#! /usr/bin/perl -w
2
3
4use DBI;
5
6
7$FEET_TO_METER = 0.3048;
8
9$APT_VERSION="AptNav200701XP810";
10$APT_FILE=$ENV{HOME} . "/osm/" . $APT_VERSION . "/apt.dat";
11$MYSELF="airport_import " . qw($Revision: 2669 $);
12
13
14sub save_airport
15{
16    return if (!(defined($icao_code)));
17
18    local $tablename = "airport_place_node";
19
20    $sql = "select id from $tablename where icao = ?";
21    $select_place_id_sth = $dbh->prepare($sql);
22    $select_place_id_sth->bind_param(1, $icao_code);
23    $select_place_id_sth->execute();
24
25    @row = $select_place_id_sth->fetchrow_array;
26    if ($#row == -1)
27    {
28        $sql = "insert into $tablename (icao) values (?)";
29        $insert_sth = $dbh->prepare($sql);
30        $insert_sth->bind_param(1, $icao_code);
31        $insert_sth->execute();
32
33        $insert_sth->finish();
34    }
35
36    $select_place_id_sth->finish();
37
38    $sql = "select id from $tablename where icao = ?";
39    $select_place_id_sth = $dbh->prepare($sql);
40    $select_place_id_sth->bind_param(1, $icao_code);
41    $select_place_id_sth->execute();
42    @row = $select_place_id_sth->fetchrow_array;
43    if ($#row > -1)
44    {
45        $place_node_id = $row[0];
46        print "Place ID: '$place_node_id', '$icao_code', '$airport_name'\n";
47    }
48
49    #
50    #  The Airport node itself
51    #
52
53    $min_lat =  999;
54    $min_lon =  999;
55
56    $max_lat = -999;
57    $max_lon = -999;
58
59    $sum_lat = 0;
60    $sum_lon = 0;
61
62    foreach (@ways)
63    {
64        @foo = split(/\s+/);
65
66        $center_lat = $foo[1];
67        $center_lon = $foo[2];
68
69        $sum_lat = $sum_lat + $center_lat;
70        $sum_lon = $sum_lon + $center_lon;
71
72        $min_lat = $center_lat if ($center_lat < $min_lat);
73        $min_lon = $center_lon if ($center_lon < $min_lon);
74
75        $max_lat = $center_lat if ($max_lat < $center_lat);
76        $max_lon = $center_lon if ($max_lon < $center_lon);
77
78        create_way($place_node_id, $_);
79    }
80
81    $lat = $sum_lat / ( $#ways + 1 );
82    $lon = $sum_lon / ( $#ways + 1 );
83
84    $sql = "update $tablename set lat = ?, lon = ?, min_lat = ?, min_lon = ?, max_lat = ?, max_lon = ?, name = ? where id = ?";
85    $update_place_sth = $dbh->prepare($sql);
86    $update_place_sth->bind_param(1, $lat);
87    $update_place_sth->bind_param(2, $lon);
88    $update_place_sth->bind_param(3, $min_lat);
89    $update_place_sth->bind_param(4, $min_lon);
90    $update_place_sth->bind_param(5, $max_lat);
91    $update_place_sth->bind_param(6, $max_lon);
92    $update_place_sth->bind_param(7, $airport_name);
93    $update_place_sth->bind_param(8, $place_node_id);
94    $update_place_sth->execute();
95    $update_place_sth->finish();
96
97
98    #
99    #   Tags of the airport node
100    #
101    # create_tag($place_node_id, "P", "icao"); # is already in the master table
102    # create_tag($place_node_id, "P", "iata", "unknown"); # Where do I get the IATA from?
103    # create_tag($place_node_id, "P", "name"); # is already in the master table
104    # create_tag($place_node_id, "P", "is_in", "Asia,Europe,Turkey"); # Ask geonames for that data...
105    create_tag($place_node_id, "P", "is_in", $is_in->{$icao_code}) if defined($is_in->{$icao_code});
106    create_tag($place_node_id, "P", "created_by", $MYSELF);
107    create_tag($place_node_id, "P", "source", $APT_VERSION);
108    create_tag($place_node_id, "P", "aeroway", "aerodrome");
109    create_tag($place_node_id, "P", "place", "airport");
110    create_tag($place_node_id, "P", "type", "civil"); # This is not always right. Where do I get correct data from?
111}
112
113
114sub create_tag
115{
116    my ($parent_id, $type, $key, $value) = @_;
117
118    local $tablename = "airport_tags";
119
120    my $select_tag_id_sql = "select id from $tablename where parent_id = ? and type = ? and key = ?";
121    my $select_tag_id_sth = $dbh->prepare($select_tag_id_sql);
122    $select_tag_id_sth->bind_param(1, $parent_id);
123    $select_tag_id_sth->bind_param(2, $type);
124    $select_tag_id_sth->bind_param(3, $key);
125    $select_tag_id_sth->execute();
126    my @row = $select_tag_id_sth->fetchrow_array;
127    if ($#row == -1)
128    {
129        my $insert_sql = "insert into $tablename (parent_id, type, key) values (?, ?, ?)";
130        my $insert_sth = $dbh->prepare($insert_sql);
131        $insert_sth->bind_param(1, $parent_id);
132        $insert_sth->bind_param(2, $type);
133        $insert_sth->bind_param(3, $key);
134        $insert_sth->execute();
135        $insert_sth->finish();
136    }
137    $select_tag_id_sth->finish();
138
139    my $update_sql = "update $tablename set value = ? where parent_id = ? and type = ? and key = ?";
140    my $update_sth = $dbh->prepare($update_sql);
141    $update_sth->bind_param(1, $value);
142    $update_sth->bind_param(2, $parent_id);
143    $update_sth->bind_param(3, $type);
144    $update_sth->bind_param(4, $key);
145    $update_sth->execute();
146    $update_sth->finish();
147}
148
149
150sub create_node
151{
152    my ($parent_id, $type, $lon, $lat) = @_;
153
154    local $tablename = "airport_nodes";
155
156    my $select_tag_id_sql = "select id from $tablename where parent_id = ? and type = ?";
157    my $select_tag_id_sth = $dbh->prepare($select_tag_id_sql);
158    $select_tag_id_sth->bind_param(1, $parent_id);
159    $select_tag_id_sth->bind_param(2, $type);
160    $select_tag_id_sth->execute();
161    my @row = $select_tag_id_sth->fetchrow_array;
162    if ($#row == -1)
163    {
164        my $insert_sql = "insert into $tablename (parent_id, type) values (?, ?)";
165        my $insert_sth = $dbh->prepare($insert_sql);
166        $insert_sth->bind_param(1, $parent_id);
167        $insert_sth->bind_param(2, $type);
168        $insert_sth->execute();
169        $insert_sth->finish();
170    }
171    $select_tag_id_sth->finish();
172
173    my $update_sql = "update $tablename set lon = ?, lat = ? where parent_id = ? and type = ?";
174    my $update_sth = $dbh->prepare($update_sql);
175    $update_sth->bind_param(1, $lon);
176    $update_sth->bind_param(2, $lat);
177    $update_sth->bind_param(3, $parent_id);
178    $update_sth->bind_param(4, $type);
179    $update_sth->execute();
180    $update_sth->finish();
181}
182
183
184sub create_way
185{
186    my ($parent_id, $wayline) = @_;
187
188    local $tablename = "airport_ways";
189
190    local $way_id;
191
192    local ($x, $center_lat, $center_lon, $name, $heading, $length, $x, $width) = split(/\s+/, $wayline);
193
194    my $select_way_id_sql = "select id from $tablename where center_lon = ? and center_lat = ?";
195    my $select_way_id_sth = $dbh->prepare($select_way_id_sql);
196    $select_way_id_sth->bind_param(1, $center_lon);
197    $select_way_id_sth->bind_param(2, $center_lat);
198    $select_way_id_sth->execute();
199    my @row = $select_way_id_sth->fetchrow_array;
200    if ($#row == -1)
201    {
202        my $insert_way_sql = "insert into $tablename (parent_id, center_lon, center_lat) values (?, ?, ?)";
203        my $insert_way_sth = $dbh->prepare($insert_way_sql);
204        $insert_way_sth->bind_param(1, $parent_id);
205        $insert_way_sth->bind_param(2, $center_lon);
206        $insert_way_sth->bind_param(3, $center_lat);
207        $insert_way_sth->execute();
208        $insert_way_sth->finish();
209
210        $select_way_id_sth->execute();
211        @row = $select_way_id_sth->fetchrow_array;
212    }
213    $select_way_id_sth->finish();
214
215    $way_id = $row[0];
216
217    my $update_way_sql = "update $tablename set runway_number = ?, heading = ?, length = ?, width = ? where parent_id = ? and center_lon = ? and center_lat = ?";
218    my $update_way_sth = $dbh->prepare($update_way_sql);
219    $update_way_sth->bind_param(1, $name);
220    $update_way_sth->bind_param(2, $heading);
221    $update_way_sth->bind_param(3, $length * $FEET_TO_METER);
222    $update_way_sth->bind_param(4, $width  * $FEET_TO_METER);
223    $update_way_sth->bind_param(5, $parent_id);
224    $update_way_sth->bind_param(6, $center_lon);
225    $update_way_sth->bind_param(7, $center_lat);
226    $update_way_sth->execute();
227    $update_way_sth->finish();
228
229    create_node($way_id, "C", $center_lon, $center_lat);
230
231    my ($lon_s, $lat_s) = moveTo($center_lon, $center_lat,  $heading, $length / 2);
232    create_node($way_id, "S", $lon_s, $lat_s);
233
234    my ($lon_e, $lat_e) = moveTo($center_lon, $center_lat, -$heading, $length / 2);
235    create_node($way_id, "E", $lon_e, $lat_e);
236}
237
238
239sub moveTo
240{
241    my ($lat_from, $lon_from, $distance, $heading) = @_;
242    local ($lon, $lat, $dlon);
243
244    $lat_from = $lat_from / 100;
245    $lon_from = $lon_from / 100;
246    $heading  = $heading  / 360;
247
248    #
249    # $lat = asin(sin($lat_from) * cos($distance) + cos($lat_from) * sin($distance) * cos($heading))
250    # $dlon = atan2(sin($heading) * sin($distance) * cos($lat_from) , cos($distance) - sin($lat_from) * sin($lat))
251    # $lon = mod($lon_from - $dlon + $PI , 2 *  $PI ) - $PI
252    #
253
254    $lon = -999;
255    $lat = -999;
256
257}
258
259
260sub read_airports_file
261{
262    open (APT, "< $APT_FILE") || die ("Can't open $APT_FILE: $!\n");
263
264    while (<APT>)
265    {
266        chomp;
267
268        if (/^1\s+/)
269        {
270            # New airport starting, dump the (previous) valid airport
271            if ($#ways > -1)
272            {
273                save_airport();
274            }
275            else
276            {
277                print "No ways for airport $icao_code / $airport_name\n";
278            }
279
280            # Airport Header
281            @foo = split(/\s+/);
282
283            # print $#foo . " - '" . join("', '", @foo) . "'\n";
284
285            $icao_code = $foo[4];
286            $airport_name = join(" ", @foo[ 5 .. $#foo ]);
287            $airport_name = $utf8_names->{$icao_code} if (defined($utf8_names->{$icao_code}));
288
289            @ways = ();
290
291            # print "    $icao_code -> '$airport_name'\n";
292        }
293        elsif (/^10\s+/)
294        {
295            # Runways and Taxiways
296            @foo = split(/\s+/);
297
298            # print $#foo . " - '" . join("', '", @foo) . "'\n";
299
300            $center_lat = $foo[1];
301            $center_lon = $foo[2];
302
303            push (@ways, $_);
304        }
305    }
306   
307    close (APT);
308}
309
310
311sub read_utf8_airport_names
312{
313    open (UTF8, "< airportnames.utf8");
314    while (<UTF8>)
315    {
316        chomp();
317        next if (/^\#/);
318
319        @foo = split(/\t+/, $_);
320        if ($#foo == 2)
321        {
322            # print "'" . $foo[0] . "' - '" . $foo[1] . "' - '" . $foo[2] . "'\n";
323            $icao_code = $foo[0];
324
325            $is_in->{$icao_code} = $foo[1] if (!($foo[1] =~ /^\?/));
326            $utf8_names->{$icao_code} = $foo[2];
327        }
328    }
329    close (UTF8);
330}
331
332
333#
334#
335#
336$dbh = DBI->connect('dbi:Pg:dbname=osm') || die ("Can't connect to database: " . $DBI::errstr);
337
338$dbh->do("SET client_encoding to UNICODE");
339
340undef %is_in;
341undef %utf8_names;
342
343read_utf8_airport_names();
344read_airports_file();
345
346$dbh->disconnect();
347
Note: See TracBrowser for help on using the repository browser.