Changeset 666


Ignore:
Timestamp:
Sep 21, 2008, 10:13:36 PM (11 years ago)
Author:
Nick Burch
Message:

Update to include source too

File:
1 edited

Legend:

Unmodified
Added
Removed
  • trunk/npemap.org.uk/scripts/exporter/export.pl

    r635 r666  
    3737#   -npeonly
    3838#       Only export NPE sourced postcodes, averaged if several for a postcode
     39#   -all
     40#       Outputs all postcodes, averaged if several for a postcode, along
     41#       with their source
    3942
    4043use strict;
     
    5154my $mode = shift;
    5255unless($mode) {
    53         die("Use:\n\texport.pl <-gpsonly|-outer|-outer1|-npeonly>\n");
     56        die("Use:\n\texport.pl <-gpsonly|-outer|-outer1|-npeonly|-all>\n");
    5457}
    5558
     
    7275print "# This information is public domain, and comes from http://www.npemap.org.uk/\n";
    7376print "#\n";
    74 print "# <outward>,<inward>,<easting>,<northing>,<WGS84 lat>,<WGS84 long>,<2+6 NGR>,<grid>\n";
     77print "# <outward>,<inward>,<easting>,<northing>,<WGS84 lat>,<WGS84 long>,<2+6 NGR>,<grid>,<sources>\n";
    7578
    7679# Get our data fetch query
     
    7982# Process the query
    8083$query->execute;
    81 while(my ($outer,$inner,$e,$n,$ie_e,$ie_n) = $query->fetchrow_array) {
     84while(my ($outer,$inner,$e,$n,$ie_e,$ie_n,$source) = $query->fetchrow_array) {
    8285        # What grid are we on?
    8386        my $grid = "osgb";
     
    101104        $long = sprintf("%0.6f", $long);
    102105
    103         print "$outer,$inner,$e,$n,$lat,$long,$ngr,$grid\n";
     106        # Make Source Nicer
     107        $source =~ s/^\{//;
     108        $source =~ s/\}$//;
     109        $source =~ s/\"//g;
     110        $source =~ s/,/;/g;
     111
     112        print "$outer,$inner,$e,$n,$lat,$long,$ngr,$grid,$source\n";
    104113}
    105114
     
    116125        my ($mode,$conn) = @_;
    117126
    118         if($mode eq "-gpsonly" || $mode eq "-npeonly") {
     127        if($mode eq "-gpsonly" || $mode eq "-npeonly" || $mode eq "-postboxonly" || $mode eq "-all") {
    119128                my $source;
    120129                if($mode eq "-gpsonly") {
    121130                        $source = getSource("FreeThePostcode.org Importer", $conn);
     131                } elsif($mode eq "-postboxonly") {
     132                        $source = getSource("Dracos.co.uk Postbox Importer", $conn);
    122133                } else {
    123134                        $source = getSource("NPE Postcode web submission", $conn);
    124135                }
    125136
     137                my $source_sql = "";
     138                if($source) {
     139                        $source_sql = " AND source = ? ";
     140                }
     141
    126142                my $sql =
    127                         "SELECT outward, inward, AVG(easting) AS avg_easting, AVG(northing) AS avg_northing, AVG(ie_easting) AS avg_easting_ie, AVG(ie_northing) AS avg_northing_ie ".
     143                        "SELECT outward, inward, AVG(easting) AS avg_easting, AVG(northing) AS avg_northing, AVG(ie_easting) AS avg_easting_ie, AVG(ie_northing) AS avg_northing_ie, to_array(DISTINCT sources.name) AS sources ".
    128144                        "FROM postcodes   ".
    129                         "WHERE source = ? AND NOT deleted ".
     145                        "INNER JOIN sources ON (postcodes.source = sources.id) ".
     146                        "WHERE NOT deleted ".$source_sql.
    130147                        "GROUP BY outward, inward ".
    131148                        "ORDER BY outward, inward ";
    132149
    133150                my $sth = $conn->prepare($sql);
    134                 $sth->bind_param(1, $source);
     151                if($source) {
     152                        $sth->bind_param(1, $source);
     153                }
    135154                return $sth;
    136155        } elsif($mode eq "-outer") {
    137156                my $sql =
    138                         "SELECT outward, '###' AS inward, AVG(easting) AS avg_easting, AVG(northing) AS avg_northing, AVG(ie_easting) AS avg_easting_ie, AVG(ie_northing) AS avg_northing_ie ".
    139                         "FROM postcodes WHERE NOT deleted ".
     157                        "SELECT outward, '###' AS inward, AVG(easting) AS avg_easting, AVG(northing) AS avg_northing, AVG(ie_easting) AS avg_easting_ie, AVG(ie_northing) AS avg_northing_ie, to_array(DISTINCT sources.name) AS sources ".
     158                        "FROM postcodes ".
     159                        "INNER JOIN sources ON (postcodes.source = sources.id) ".
     160                        "WHERE NOT deleted ".
    140161                        "GROUP BY outward ".
    141162                        "ORDER BY outward ";
     
    146167                # Not - will exclude data where we only have an outward, and no inward
    147168                my $sql =
    148                         "SELECT outward, substr(inward,1,1) || '##' AS inward, AVG(easting) AS avg_easting, AVG(northing) AS avg_northing, AVG(ie_easting) AS avg_easting_ie, AVG(ie_northing) AS avg_northing_ie ".
     169                        "SELECT outward, substr(inward,1,1) || '##' AS inward, AVG(easting) AS avg_easting, AVG(northing) AS avg_northing, AVG(ie_easting) AS avg_easting_ie, AVG(ie_northing) AS avg_northing_ie, to_array(DISTINCT sources.name) AS sources ".
    149170                        "FROM postcodes ".
     171                        "INNER JOIN sources ON (postcodes.source = sources.id) ".
    150172                        "WHERE NOT deleted AND length(inward) > 0 ".
    151173                        "GROUP BY outward, substr(inward,1,1) ".
Note: See TracChangeset for help on using the changeset viewer.