Commons:Wiki Loves Monuments 2011/Tools/specific to WLM Estonia

From Wikimedia Commons, the free media repository
Jump to: navigation, search

This page lists WLM tools specific to WLM Estonia.

Stage 1[edit]

The registrant (http://register.muinas.ee/?menuID=monument) lets to download its monument list as Excel files. The coordinates file is given separetly as CSV file.

These Excel files and CSV file are then converted to intermediatory MySql database on toolserver.

excel2sql.pl[edit]

    #!/usr/bin/perl -w
    # Usage: perl excel2sql.pl -a "Saare maakond" -f saare1.xls
 
    use strict;
#    use POSIX qw(locale_h); 
 
#setlocale(LC_CTYPE, "et_EE.utf8");
#use utf8; 
    binmode STDIN, ":utf8"; 
    binmode STDOUT, ":utf8"; 
 
    use Getopt::Std;
    use vars qw( $opt_a $opt_f );
    use Spreadsheet::ParseExcel;
 
    if( ! getopts('a:f:') or ! $opt_a or ! $opt_f )
    {
    die 'Usage: perl excel2sql.pl -a "Saare maakond" -f saare1.xls' . "\n";
    }    
 
 
    my $parser   = Spreadsheet::ParseExcel->new();
    my $workbook = $parser->parse($opt_f);
 
    if ( !defined $workbook ) {
        die $parser->error(), ".\n";
    }
 
 
    my $adm_division = $opt_a;
 
    for my $worksheet ( $workbook->worksheets() ) {
 
        my ( $row_min, $row_max ) = $worksheet->row_range();
        my ( $col_min, $col_max ) = $worksheet->col_range();
 
        print "INSERT INTO `monument_list` (`reg_nr`, `adm_division`, `name`, `type_obj_vk`, `municipality`, `address`, `old_nr`, `type_text`) VALUES " . "\n";
 
 
        my $row_count = 0;
 
        for my $row ( $row_min .. $row_max ) {
            my $row_text = '';
            my @row_data = ();   # FIXME: init array?
 
            for my $col ( $col_min .. $col_max ) {
 
                my $cell = $worksheet->get_cell( $row, $col );
                next unless $cell;
                my $cell_data = $cell->value();
                push( @row_data,  $cell_data);
 
            }
 
            my $reg_nr = '';
            my $name = '';
            my $type_obj_vk = '';
            my $municipality = '';
            my $address = '';
            my $old_nr = '';
            my $type_text = '';
 
            if ($row_data[0] =~ /^\d+$/) {   # first cell must be integer number: reg_nr
                $reg_nr = $row_data[0];
                $name = $row_data[1];
                $type_obj_vk = $row_data[2];
                $municipality = $row_data[3];
                $address = $row_data[4];
                $old_nr = $row_data[5];
                $type_text = $row_data[6];
                $type_text =~ s|\r||;     # have to strip carrige return
                $type_text =~ s|\n||;     # have to strip newline
            }
 
            if ($reg_nr) {
                if ($row_count) {
                    $row_text .= ", \n";
                }
                # FIXME should escape ' in text, replace ' with \'
                # xxxxxxxxxx =~ s/\'/\\'/;
                $row_text .= "($reg_nr, '". $adm_division . "', '" . $name . "', '" . $type_obj_vk . "', '" . $municipality . "', '" . $address . "', '" . $old_nr . "', '" . $type_text . "')";
                $row_count++;
            }
 
            if ($row_text) {
                print $row_text;
            }
        } #for each row
        print ";\n";  ## end sql statement
    }

Stage 2[edit]

Then the intermediatory MySql database is used by script to generete heritage lists on Wikipedia.