The road to using Mysql

Sorry, I've been away for a while. Hope to write a useful post fairly soon. In the meantime, here are a couple of things I've found out today. To look for useful twitter posts regarding Mysql, search on #Mysql (I know, fairly basic, but I'm not yet a Twitter person).
Second I found mysql commands you may not know
It's in Spanish, and if I get some time I'll translate some of the entries. Very interesting!

The normal way to produce a website that accesses data from a Mysql database is via the scripting language PHP. I wondered if it were possible to use Perl instead, and found that with the use of a couple of modules, it's not so hard to get started. (Here's a brief introduction to using modules with web programming.
Here's the Perl I wrote (calling the file empnew.cgi). I made sure the program was executable, and placed the file in a directory on my web server (make sure your path points to this directory):


#!/usr/bin/perl
use CGI;
use HTML::Template;
use DBI;
use strict;

my $dsn = 'dbi:mysql:one:localhost:3306:mysql_socket=/opt/lampp/var/mysql/mysqld.sock';
# the database here is called 'one'

my $CGI = CGI->new();
my $user = 'root';
my $pass = 'passwordx';


my $dbh = DBI->connect($dsn, $user, $pass)
or die "Can’t connect to the DB: $DBI::errstr\n";

my $sth = $dbh->prepare("select empno, ename, job, sal from emp order by ename");
$sth->execute;



# Prepare a data structure for HTML::Template
my $rows;
push @{$rows}, $_ while $_ = $sth->fetchrow_hashref();

#
# initiate the template and substitute the values:

my $template = HTML::Template->new(filename => 'empnew.tmpl',
die_on_bad_params => 0, );
# call param to fill in the loop with the loop data by reference.
$template->param(ROWS => $rows);


print $CGI->header();
# print the template
print $template->output();

$dbh->disconnect();


The module CGI deals quickly and efficiently with http headers.

The module HTML::Template that is called, allows you to state the Html layout in a separate file. In our example this calls empnew.tmpl

Code:

There are basically 3 new kinds of tags to be aware of when making a .tmpl file for access by HTML::Template. These are

Code:

The other tags can be standard html formatting stuff. Here's a good introduction to using the HTML::Template

Finally, in order to access data from the Mysql database we need to use the DBI module. You can see from the Perl script that we connect to the database using parameters given (not a good idea from the security pov to have the password in this script in real life - better to bring in as a parameter from a file that can't be seen by public eyes), prepare the sql script, and then execute. For more information on starting with DBI see this tutorial

Once this is setup, it's fairly simple to change the layout by editing the tmpl file directly without needing to change the program code.

I hope this has been of interest! Good luck with your web programming!

After the recent posting of data in Wikileaks, it's claimed that here in the UK we can now freely access our longitude and latitude co-ordinates for each post code. However, some of the data in their files may well be deliberately corrupted - you may be infringing copyright law by using that data, and the mistakes will prove that you've used illegal data. Free the Postcode is perhaps a better place to get free data - http://www.freethepostcode.org/
Now, it's possible to calculate distances between each point using the following calculation (originally from the fine book Pro Mysql):

The distance d between two points (x1,y1) and (x2,y2) can be calculated from the following equation (so long as x values are latitude and y values are longitude in radians, r is the radius of the sphere which is 3956 in miles):

Code:

Initially I downloaded relevant postcode data file onto my Linux system. I then (by using regex in egrep) selected those post codes beginning NG and a single digit (Nottingham - I'm not interested in loading every single postcode at this stage) and use the shell command cut to extract only those fields I'm after. In this case it's a comma-delimited file so we use the -d switch to let the function cut know that commas are the delimiter.

Code:

I did the same for OX postcodes so I had some data in two different towns to play around with.

Prior to inserting the postcode data I used the following script to create the table (I'll explain the last three columns a little later).

Code:

To insert the data into my table I used :

LOAD DATA LOCAL INFILE '/home/usname/gis/ng.txt' INTO TABLE postcodes FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' LINES TERMINATED BY '\n' (postcode, longitude, latitude);

and again for the ox file (Oxford postcodes)
LOAD DATA LOCAL INFILE '/home/usname/gis/ox.txt' INTO TABLE postcodes FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
LINES TERMINATED BY '\n' (postcode, longitude, latitude);

After putting our data into a table we can then use the trig functions available in Mysql to calculate distances between two points. However we have to be aware of time-consuming functions, that make the selection of records from the table where the distance is below a certain figure (ie after the calculation of all relevant records) can be very cumbersome. It's better to precompute and store much of this in the database table where we're recording the postcodes. To start with we need to change the latitude and longitude figures given from degrees into radians. This is done using the following formula:

radians= degrees*(PI/180)

Luckily we can obtain PI in Mysql by using PI()
What I will do here is precompute and populate 3 extra fields for each record (in radians) and store them as rad_xaxis, rad_yaxis and rad_zaxis, as per the following definition:

xaxis = cos(radians(Lat)) * cos(radians(Lon))
yaxis = cos(radians(Lat)) * sin(radians(Lon))
zaxis = sin(radians(Lat))

So we update our calculated fields as per the following:

Code:

The distance between points can then be calculated using SQL loosely like (acos( xaxis * $xaxis + yaxis * $yaxis + zaxis * $zaxis ) * 3956) (where those starting with a $ are precomputed for the start point in question in the same manner as above)
So to get my distance between say the post code OX1 1AB (central Oxford) and those postcodes starting 'OX4 3Y%'

Code:

Note I don't want to calculate where the file concerned didn't have data, so add to the where clause the restriction that the latitude figure is greater than 0.
I have done some calculations using this formula (for example where the fixed post code is NG7 4ER in Nottingham), and it seems to be more accurate over greater distances - I'm not sure that you could trust it for under 5 miles. Even so it's very responsive and you can add different parts of the UK and have a distance calculator for different areas easily available. It should be remembered that as the Earth is not a perfect sphere, our calculations here will not be totally accurate, but we seem to be within 200 yards for the distance between Oxford and Nottingham (as the crow flies).

Now it's often the case that you won't want all the distances between lots of random postcodes (even within your home city). However, suppose you want to bring back all supermarkets (or takeaway shops or pubs etc) within 6 miles of your designated postcode? So long as you have the supermarkets recorded in a table along with the correct post code (make sure the format is exactly the same as the postcode table) then you can use the following statement:

Code:

This brought back the following records (those records I've recorded in my shops database table - in real life obviously there are others within the specified distance):

******* 1. row *******
postcode: OX2 7BY
distance: 2.66911298653386
name: Midcounties Co-Op
address: 228-240 Banbury Road, Oxford
******* 2. row *******
postcode: OX4 3XQ
distance: 3.50779225250805
name: Midcounties Co-Op
address: 21 Templars Square, Cowley
******* 3. row *******
postcode: OX3 8RA
distance: 3.88607301210902
name: Midcounties Co-Op
address: Atkyns Road, Headington
******* 4. row *******
postcode: OX4 6XJ
distance: 4.67029242514653
name: Tesco
address: Oxford Retail Park, Cowley
4 rows in set (0.01 sec)

How good is that? I'll have to round the figures though - it doesn't make any sense to calculate to 15 decimal points! Hope you get as much enjoyment from using this technique as I do. Until next time - Enjoy your Mysql coding!