1 | #!/usr/bin/perl |
---|
2 | # Raw Postcode Data Exporter |
---|
3 | # -------------------------- |
---|
4 | # |
---|
5 | # Exports the raw postcode data, including IDs. |
---|
6 | # Doesn't export deleted postcodes, but does export ones with open |
---|
7 | # problem reports. |
---|
8 | # Normally used by people wishing to check our data for errors |
---|
9 | |
---|
10 | use strict; |
---|
11 | use DBI; |
---|
12 | use Geography::NationalGrid; |
---|
13 | use Geography::NationalGrid::GB; |
---|
14 | use Geography::NationalGrid::IE; |
---|
15 | |
---|
16 | # Find our private perl libraries |
---|
17 | use FindBin; |
---|
18 | use lib "$FindBin::Bin/../../perllib"; |
---|
19 | use NPEMap::GeoConverter; |
---|
20 | |
---|
21 | # What to connect with |
---|
22 | my $dbname = $ENV{DBNAME} || "npemap"; |
---|
23 | my $dbhost = $ENV{DBHOST} || ""; |
---|
24 | my $dbuser = $ENV{DBUSER} || "npemap"; |
---|
25 | my $dbpass = $ENV{DBPASS} || ""; |
---|
26 | |
---|
27 | # Connect to the database |
---|
28 | my $dsn = "dbi:Pg:dbname=$dbname"; |
---|
29 | $dsn .= ";host=$dbhost" if $dbhost; |
---|
30 | |
---|
31 | my $conn = DBI->connect($dsn, $dbuser, $dbpass, |
---|
32 | { PrintError => 0, RaiseError => 1, AutoCommit => 1 } ); |
---|
33 | |
---|
34 | # Print out our header |
---|
35 | print "# Generated on ".gmtime()."\n"; |
---|
36 | print "#\n"; |
---|
37 | print "# This information is public domain, and comes from http://www.npemap.org.uk/\n"; |
---|
38 | print "#\n"; |
---|
39 | print "# This is raw postcode data, and not intended for end users. See \n"; |
---|
40 | print "# http://www.npemap.org.uk/data/ for formatted data\n"; |
---|
41 | print "#\n"; |
---|
42 | print "# <internal id>,<outward>,<inward>,<easting>,<northing>,<grid>,<WGS84 lat>,<WGS84 long>,<problem reported>\n"; |
---|
43 | |
---|
44 | # Get our data fetch query |
---|
45 | my $source = getSource("NPE Postcode web submission", $conn); |
---|
46 | my $query = getQuery($conn); |
---|
47 | |
---|
48 | # Process the query |
---|
49 | $query->execute; |
---|
50 | while(my ($id,$outer,$inner,$e,$n,$ie_e,$ie_n,$problem) = $query->fetchrow_array) { |
---|
51 | # What grid are we on? |
---|
52 | my $grid = "osgb"; |
---|
53 | if(!$e && $ie_e) { |
---|
54 | $grid = "osie"; |
---|
55 | $e = $ie_e; |
---|
56 | $n = $ie_n; |
---|
57 | } |
---|
58 | |
---|
59 | # Generate lat+long |
---|
60 | my ($lat,$long) = eastingNorthingToLatLong($e,$n,$grid); |
---|
61 | |
---|
62 | # Make e+n look nicer |
---|
63 | $e =~ s/^(\d+)\.\d+$/$1/; |
---|
64 | $n =~ s/^(\-?\d+)\.\d+$/$1/; |
---|
65 | |
---|
66 | # Make lat+long look nicer |
---|
67 | $lat = sprintf("%0.6f", $lat); |
---|
68 | $long = sprintf("%0.6f", $long); |
---|
69 | |
---|
70 | print "$id,$outer,$inner,$e,$n,$grid,$lat,$long,$problem\n"; |
---|
71 | } |
---|
72 | |
---|
73 | # Close down |
---|
74 | $conn->disconnect; |
---|
75 | |
---|
76 | |
---|
77 | ############################################################################ |
---|
78 | |
---|
79 | # Get a query returning |
---|
80 | # id, outer, inner, easting, northing |
---|
81 | sub getQuery($) { |
---|
82 | my $sql = |
---|
83 | "SELECT id, outward, inward, easting, northing, ie_easting, ie_northing, reason IS NOT NULL AS problem ". |
---|
84 | "FROM postcodes ". |
---|
85 | "LEFT OUTER JOIN bad_postcodes ON (id = postcode AND NOT actioned) ". |
---|
86 | "WHERE source = ? AND NOT deleted ". |
---|
87 | "ORDER BY outward, inward "; |
---|
88 | |
---|
89 | my $sth = $conn->prepare($sql); |
---|
90 | $sth->bind_param(1, $source); |
---|
91 | return $sth; |
---|
92 | } |
---|
93 | |
---|
94 | sub getSource($$) { |
---|
95 | my ($source,$conn) = @_; |
---|
96 | |
---|
97 | my $sth = $conn->prepare("SELECT id FROM sources WHERE name = ?"); |
---|
98 | $sth->bind_param(1, $source); |
---|
99 | $sth->execute; |
---|
100 | |
---|
101 | my $s = undef; |
---|
102 | my @row = $sth->fetchrow_array; |
---|
103 | if($sth->fetchrow_array) { |
---|
104 | die("Should only find one source with name '$source', got more than one!"); |
---|
105 | } |
---|
106 | |
---|
107 | unless(@row) { |
---|
108 | die("Can't find id for source with name '$source'"); |
---|
109 | } |
---|
110 | |
---|
111 | return $row[0]; |
---|
112 | } |
---|