Changeset 149 for trunk/npemap.org.uk/scripts/exporter/export.pl

Timestamp:

Oct 24, 2006, 4:59:04 PM (15 years ago)

Author:

Nick Burch

Message:

Remaining modes

File:

• trunk/npemap.org.uk/scripts/exporter/export.pl (modified) (3 diffs)

trunk/npemap.org.uk/scripts/exporter/export.pl

@@ -90 +90 @@
 ############################################################################
 
+# Get a query returning 
+#               outer, inner, easting, northing
+# for the given mode
 sub getQuery($$) {
         my ($mode,$conn) = @_;
 
-        if($mode == "-gpsonly") {
-                my $source = getSource("FreeThePostcode.org Importer", $conn);
+        if($mode eq "-gpsonly" || $mode eq "-npeonly") {
+                my $source;
+                if($mode eq "-gpsonly") {
+                        $source = getSource("FreeThePostcode.org Importer", $conn);
+                } else {
+                        $source = getSource("NPE Postcode web submission", $conn);
+                }
 
                 my $sql = 
@@ -106 +114 @@
                 $sth->bind_param(1, $source);
                 return $sth;
-        } elsif($mode == "-npeonly") {
-                my $source = getSource("NPE Postcode web submission", $conn);
-        } elsif($mode == "-outer") {
-        } elsif($mode == "-outer1") {
+        } elsif($mode eq "-outer") {
+                my $sql = 
+                        "SELECT outward, '###' AS inward, AVG(easting) AS avg_easting, AVG(northing) AS avg_northing ".
+                        "FROM postcodes   ".
+                        "GROUP BY outward ".
+                        "ORDER BY outward ";
+
+                my $sth = $conn->prepare($sql);
+                return $sth;
+        } elsif($mode eq "-outer1") {
+                my $sql = 
+                        "SELECT outward, substr(inward,1,1) || '##' AS inward, AVG(easting) AS avg_easting, AVG(northing) AS avg_northing ".
+                        "FROM postcodes   ".
+                        "GROUP BY outward, substr(inward,1,1) ".
+                        "ORDER BY outward, inward";
+
+                my $sth = $conn->prepare($sql);
+                return $sth;
         } else {
                 die("Invalid mode '$mode'");
@@ -123 +145 @@
 
         my $s = undef;
-        while(my @row = $sth->fetchrow_array) {
-                if($s) {
-                        die("Should only find one source with name '$source', got more than one!");
-                }
-                $s = $row[0];
+        my @row = $sth->fetchrow_array;
+        if($sth->fetchrow_array) {
+                die("Should only find one source with name '$source', got more than one!");
         }
-        unless($s) {
+
+        unless(@row) {
                 die("Can't find id for source with name '$source'");
         }
 
-        return $s;      
+        return $row[0]; 
 }