source: freethepostcode.org/html/ruby/stats.rbx @ 726

Last change on this file since 726 was 726, checked in by Dominic Hargreaves, 10 years ago

initial import

  • Property svn:executable set to *
File size: 1.8 KB
Line 
1#!/usr/bin/ruby
2
3require 'mysql'
4
5MYSQL_SERVER = '127.0.0.1'
6MYSQL_USER = 'postcode'
7MYSQL_PASS = 'kc8dFusmw'
8MYSQL_DATABASE = 'postcode'
9
10def mysql_error(e)
11  print "Error code: ", e.errno, "\n"
12  print "Error message: ", e.error, "\n"
13end
14
15def get_connection
16  begin
17    return Mysql.real_connect(MYSQL_SERVER, MYSQL_USER, MYSQL_PASS, MYSQL_DATABASE)
18     
19  rescue MysqlError => e   
20    mysql_error(e)
21  end
22     
23end
24
25connection = get_connection
26
27puts '<html>'
28puts '<script type="text/javascript" src="sorttable.js"></script>'
29
30if connection
31  sql = 'select count(*) as c from (select * from codes where confirmed = 1 group by part1, part2) as a;'
32  res = connection.query(sql)
33  res.each_hash do |row|
34    puts 'total postcodes in system: ' +  row['c']
35  end
36
37  puts '<br><br>'
38
39
40if connection
41  sql = 'select part1, 10-count(*) as c from (select part1, left(part2, 1) as b  from codes where confirmed = 1 group by part1, b) as a group by part1 order by c, part1;'
42  res = connection.query(sql)
43  puts "To map postcodes exhaustively, we should first get a point for each of the first char of the second part of the postcode (eg '0' for NW1 0AA). So something at NW1 0something, NW1 1something, NW1 2something and so on. This gets the data reasonably accurate for many applications. The list below tells you how many we are missing for each postcode first part.<br><br>"
44  puts "Click the headings to change the sort order (but be patient, it may take a few seconds!<br><br>"
45  puts '<table class="sortable" border="1"><tr><th>First part of<br />postcode</th><th>number of<br />missing codes</th></tr>'
46  res.each_hash do |row|
47    puts '<tr><td>' +  row['part1'] + '</td><td align="right">' + row['c'] + "</td><td><img src='/redpixel.gif' width='#{row['c'].to_i * 10}' height='10' /></td></tr>"
48  end
49
50  puts '</table><br><br></html>'
51
52end
53
54
55end
56
Note: See TracBrowser for help on using the repository browser.