1 | #!/usr/bin/perl |
---|
2 | # Postcode Data Exporter |
---|
3 | # ---------------------- |
---|
4 | # |
---|
5 | # Exports various parts of the data, in various formats |
---|
6 | # Normally these are of the form: |
---|
7 | # <postcode|part postcode> <easting> <northing> <lat> <long> |
---|
8 | # |
---|
9 | # Supported outputs are: |
---|
10 | # -gpsonly |
---|
11 | # Only export GPS sourced postcodes, averaged if several for a postcode |
---|
12 | # -outer |
---|
13 | # Only export the outer parts of the postcodes, averaged over all |
---|
14 | # -outer1 |
---|
15 | # Only export the outer part + 1st inner digit, averaged over all |
---|
16 | # -npeonly |
---|
17 | # Only export NPE sourced postcodes, averaged if several for a postcode |
---|
18 | |
---|
19 | use strict; |
---|
20 | use DBI; |
---|
21 | use Geo::HelmertTransform; |
---|
22 | use Geography::NationalGrid; |
---|
23 | use Geography::NationalGrid::GB; |
---|
24 | |
---|
25 | my $mode = shift; |
---|
26 | unless($mode) { |
---|
27 | die("Use:\n\texport.pl <-gpsonly|-outer|-outer1|-npeonly>\n"); |
---|
28 | } |
---|
29 | |
---|
30 | # What to connect with |
---|
31 | my $dbname = $ENV{DBNAME} || "npemap"; |
---|
32 | my $dbhost = $ENV{DBHOST} || ""; |
---|
33 | my $dbuser = $ENV{DBUSER} || "npemaps"; |
---|
34 | my $dbpass = $ENV{DBPASS} || "npemaps"; |
---|
35 | |
---|
36 | # Connect to the database |
---|
37 | my $dsn = "dbi:Pg:dbname=$dbname"; |
---|
38 | $dsn .= ";host=$dbhost" if $dbhost; |
---|
39 | |
---|
40 | my $conn = DBI->connect($dsn, $dbuser, $dbpass, |
---|
41 | { PrintError => 0, RaiseError => 1, AutoCommit => 1 } ); |
---|
42 | |
---|
43 | # Print out our header |
---|
44 | print "# Generated on ".gmtime()."\n"; |
---|
45 | |
---|
46 | # Get our data fetch query |
---|
47 | my $query = getQuery($mode,$conn); |
---|
48 | |
---|
49 | # Process the query |
---|
50 | $query->execute; |
---|
51 | while(my ($outer,$inner,$e,$n) = $query->fetchrow_array) { |
---|
52 | # Generate lat+long |
---|
53 | my ($lat,$long) = makeLatLong($e,$n); |
---|
54 | |
---|
55 | # Make e+n look nicer |
---|
56 | $e =~ s/^(\d+)\.\d+$/$1/; |
---|
57 | $n =~ s/^(\d+)\.\d+$/$1/; |
---|
58 | |
---|
59 | # Make lat+long look nicer |
---|
60 | $lat = sprintf("%0.6f", $lat); |
---|
61 | $long = sprintf("%0.6f", $long); |
---|
62 | |
---|
63 | print "$outer $inner $e $n $lat $long\n"; |
---|
64 | } |
---|
65 | |
---|
66 | # Close down |
---|
67 | $conn->disconnect; |
---|
68 | |
---|
69 | |
---|
70 | ############################################################################ |
---|
71 | |
---|
72 | sub makeLatLong { |
---|
73 | my ($e,$n) = @_; |
---|
74 | |
---|
75 | # Turn e+n into OSGB lat+long |
---|
76 | my $point = Geography::NationalGrid::GB->new( Easting=>$e, Northing=>$n ); |
---|
77 | my $oslat = $point->latitude; |
---|
78 | my $oslong = $point->longitude; |
---|
79 | |
---|
80 | # Turn OSGB lat+long into WGS84 lat+long |
---|
81 | my $osgb_helper = Geo::HelmertTransform::datum("Airy1830"); |
---|
82 | my $wgs84_helper = Geo::HelmertTransform::datum('WGS84'); |
---|
83 | |
---|
84 | my ($lat,$long,$h) = |
---|
85 | Geo::HelmertTransform::convert_datum($osgb_helper, $wgs84_helper, $oslat, $oslong, 0); |
---|
86 | |
---|
87 | return ($lat,$long); |
---|
88 | } |
---|
89 | |
---|
90 | ############################################################################ |
---|
91 | |
---|
92 | sub getQuery($$) { |
---|
93 | my ($mode,$conn) = @_; |
---|
94 | |
---|
95 | if($mode == "-gpsonly") { |
---|
96 | my $source = getSource("FreeThePostcode.org Importer", $conn); |
---|
97 | |
---|
98 | my $sql = |
---|
99 | "SELECT outward, inward, AVG(easting) AS avg_easting, AVG(northing) AS avg_northing ". |
---|
100 | "FROM postcodes ". |
---|
101 | "WHERE source = ? ". |
---|
102 | "GROUP BY outward, inward ". |
---|
103 | "ORDER BY outward, inward "; |
---|
104 | |
---|
105 | my $sth = $conn->prepare($sql); |
---|
106 | $sth->bind_param(1, $source); |
---|
107 | return $sth; |
---|
108 | } elsif($mode == "-npeonly") { |
---|
109 | my $source = getSource("NPE Postcode web submission", $conn); |
---|
110 | } elsif($mode == "-outer") { |
---|
111 | } elsif($mode == "-outer1") { |
---|
112 | } else { |
---|
113 | die("Invalid mode '$mode'"); |
---|
114 | } |
---|
115 | } |
---|
116 | |
---|
117 | sub getSource($$) { |
---|
118 | my ($source,$conn) = @_; |
---|
119 | |
---|
120 | my $sth = $conn->prepare("SELECT id FROM sources WHERE name = ?"); |
---|
121 | $sth->bind_param(1, $source); |
---|
122 | $sth->execute; |
---|
123 | |
---|
124 | my $s = undef; |
---|
125 | while(my @row = $sth->fetchrow_array) { |
---|
126 | if($s) { |
---|
127 | die("Should only find one source with name '$source', got more than one!"); |
---|
128 | } |
---|
129 | $s = $row[0]; |
---|
130 | } |
---|
131 | unless($s) { |
---|
132 | die("Can't find id for source with name '$source'"); |
---|
133 | } |
---|
134 | |
---|
135 | return $s; |
---|
136 | } |
---|