MySQL ODBC Connector - Postcode Search

Author

Postcode Search

Shaun

2006-01-23, 11:24 am

Hi,

We have a dataset of uk postcodes and their relevant X-Coordinates and
Y-Coordinates, a table of properties (houses), a table of users and a table
of offices - users work in an office - table structures below.

Is it possible to run a search of all properties in the properties table
that come within a certain distance of the users postcode, currently we do
this by downloading all properties into an array and stripping out the ones
that don't come within the radius with php.

Any advice would be greatly appreciated.

# ---------- MySQL dump ----------
#
# Table structure for table 'Offices'
#
CREATE TABLE Offices (
Office_ID int(11) DEFAULT '' NOT NULL auto_increment,
Subscriber_ID int(11) ,
Type varchar(10) ,
Address_Line_1 varchar(50) ,
Address_Line_2 varchar(50) ,
City varchar(50) ,
County varchar(50) ,
Postcode varchar(10) ,
Telephone varchar(12) ,
Fax varchar(12) ,
Radius tinyint(4) ,
PRIMARY KEY (Office_ID)
);

#
# Table structure for table 'Postcodes'
#
CREATE TABLE Postcodes (
PCDSECT varchar(6) DEFAULT '' NOT NULL ,
SORTSECT varchar(6) ,
PCDDIST varchar(4) ,
SORTDIST varchar(4) ,
PCDAREA char(2) ,
X_COORD double(7,1) unsigned ,
Y_COORD double(7,1) unsigned ,
PRIMARY KEY (PCDSECT)
);

#
# Table structure for table 'Properties'
#
CREATE TABLE Properties (
CHK varchar(20) ,
Property_ID int(11) DEFAULT '' NOT NULL auto_increment,
Insertion_Date date ,
Status varchar(20) ,
Property_Name_Or_Num
ber varchar(50) ,
Address_Line_1 varchar(50) ,
Address_Line_2 varchar(50) ,
City varchar(50) ,
County varchar(50) ,
Postcode varchar(12) ,
PRIMARY KEY (Property_ID)
);

#
# Table structure for table 'Users'
#
CREATE TABLE Users (
User_ID int(11) DEFAULT '' NOT NULL auto_increment,
Office_ID int(11) ,
Type varchar(20) ,
Title varchar(4) ,
Firstname varchar(20) ,
Lastname varchar(20) ,
Password varchar(20) DEFAULT '' NOT NULL ,
Email varchar(50) ,
PRIMARY KEY (User_ID)
);

# ----------- Dump ends -----------

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql? unsub...sie.nctu.edu.tw

SGreen@unimin.com

2006-01-23, 11:24 am

--=_alternative 005EEBB6852570FF_=
Content-Type: text/plain; charset="US-ASCII"

"Shaun" < shaunthornburgh@hotm
ail.com> wrote on 01/23/2006 11:51:32 AM:

> Hi,
>
> We have a dataset of uk postcodes and their relevant X-Coordinates and
> Y-Coordinates, a table of properties (houses), a table of users and a
table

> of offices - users work in an office - table structures below.
>
> Is it possible to run a search of all properties in the properties table

> that come within a certain distance of the users postcode, currently we
do
> this by downloading all properties into an array and stripping out the
ones
> that don't come within the radius with php.
>
> Any advice would be greatly appreciated.
>
>
> # ---------- MySQL dump ----------
> #
> # Table structure for table 'Offices'
> #
> CREATE TABLE Offices (
> Office_ID int(11) DEFAULT '' NOT NULL auto_increment,
> Subscriber_ID int(11) ,
> Type varchar(10) ,
> Address_Line_1 varchar(50) ,
> Address_Line_2 varchar(50) ,
> City varchar(50) ,
> County varchar(50) ,
> Postcode varchar(10) ,
> Telephone varchar(12) ,
> Fax varchar(12) ,
> Radius tinyint(4) ,
> PRIMARY KEY (Office_ID)
> );
>
> #
> # Table structure for table 'Postcodes'
> #
> CREATE TABLE Postcodes (
> PCDSECT varchar(6) DEFAULT '' NOT NULL ,
> SORTSECT varchar(6) ,
> PCDDIST varchar(4) ,
> SORTDIST varchar(4) ,
> PCDAREA char(2) ,
> X_COORD double(7,1) unsigned ,
> Y_COORD double(7,1) unsigned ,
> PRIMARY KEY (PCDSECT)
> );
>
> #
> # Table structure for table 'Properties'
> #
> CREATE TABLE Properties (
> CHK varchar(20) ,
> Property_ID int(11) DEFAULT '' NOT NULL auto_increment,
> Insertion_Date date ,
> Status varchar(20) ,
> Property_Name_Or_Num
ber varchar(50) ,
> Address_Line_1 varchar(50) ,
> Address_Line_2 varchar(50) ,
> City varchar(50) ,
> County varchar(50) ,
> Postcode varchar(12) ,
> PRIMARY KEY (Property_ID)
> );
>
> #
> # Table structure for table 'Users'
> #
> CREATE TABLE Users (
> User_ID int(11) DEFAULT '' NOT NULL auto_increment,
> Office_ID int(11) ,
> Type varchar(20) ,
> Title varchar(4) ,
> Firstname varchar(20) ,
> Lastname varchar(20) ,
> Password varchar(20) DEFAULT '' NOT NULL ,
> Email varchar(50) ,
> PRIMARY KEY (User_ID)
> );
>
> # ----------- Dump ends -----------
>
>

Assuming a general distance formula of R=SQR((x1-x2)^2 + (y1-y2)^2)

Here is a parameterized example for a single user:

SET @userID = ?? /* your pick */
SET @TargetR = ?? /* again, your pick */

SELECT @sourceX:= pc.X_COORD, @sourceY := pc.Y_COORD
FROM postcodes pc
INNER JOIN office o
ON o.postcode = PC.pcdsect
INNER JOIN users u
ON u.office_id = o.office_id
AND u.user_id = @userID;

CREATE TEMPORARY TABLE tmpShortList (KEY(pcdsect)) SELECT
pc.pcdsect,
(@sourceX - pc.X_COORD) X_DIFF,
(@sourceY - pc.Y_COORD) Y_DIFF,
FROM Postcodes pc
WHERE pc.X_COORD BETWEEN (@SourceX - @TargetR) and (@SourceX + @TargetR)
AND pc.Y_COORD BETWEEN (@SourceY - @TargetR) and (@SourceY + @TargetR)
HAVING SQR(X_DIFF*X_DIFF + Y_DIFF*Y_DIFF) <= @TargetR;

Now you have a table of all Postal codes (tmpShortList) that fall within a
certain radius (@TargetR) of a certain user (@UserID). Do with it what you
will. In your example, you wanted to see all properties were within that
search radius.

SELECT p.*
FROM properties p
INNER JOIN tmpShortList sl
ON sl.pcdsect = p.postcode;

HTH!

Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine

--=_alternative 005EEBB6852570FF_=--

Michael Stearne

2006-01-23, 1:23 pm

We use the inradius function as described here:

http://www.phparch.com/discuss/inde...=3Dmsg&th=3D878

It's not the fast way I'm sure but seems to be pretty reliable.=20
Instead of the zipdata table you would point it at the Postcodes table
and the Properties table. You'll also need to add an X,Y (lat,lon)
value for each of your Properties.

Will you allow address lookup as a service for public use (which would
be great) or can you let us know where you got the coordinates for the
postal codes (Royal Mail?).

Thanks!
Michael

P.S. Yahoo provides good free geocoding for the U.S.
http://developer.yahoo.net/maps/rest/V1/geocode.html

On 1/23/06, Shaun < shaunthornburgh@hotm
ail.com> wrote:
> Hi,
>
> We have a dataset of uk postcodes and their relevant X-Coordinates and
> Y-Coordinates, a table of properties (houses), a table of users and a tab=
le
> of offices - users work in an office - table structures below.
>
> Is it possible to run a search of all properties in the properties table
> that come within a certain distance of the users postcode, currently we d=
o
> this by downloading all properties into an array and stripping out the on=
es
> that don't come within the radius with php.
>
> Any advice would be greatly appreciated.
>
>
> # ---------- MySQL dump ----------
> #
> # Table structure for table 'Offices'
> #
> CREATE TABLE Offices (
> Office_ID int(11) DEFAULT '' NOT NULL auto_increment,
> Subscriber_ID int(11) ,
> Type varchar(10) ,
> Address_Line_1 varchar(50) ,
> Address_Line_2 varchar(50) ,
> City varchar(50) ,
> County varchar(50) ,
> Postcode varchar(10) ,
> Telephone varchar(12) ,
> Fax varchar(12) ,
> Radius tinyint(4) ,
> PRIMARY KEY (Office_ID)
> );
>
> #
> # Table structure for table 'Postcodes'
> #
> CREATE TABLE Postcodes (
> PCDSECT varchar(6) DEFAULT '' NOT NULL ,
> SORTSECT varchar(6) ,
> PCDDIST varchar(4) ,
> SORTDIST varchar(4) ,
> PCDAREA char(2) ,
> X_COORD double(7,1) unsigned ,
> Y_COORD double(7,1) unsigned ,
> PRIMARY KEY (PCDSECT)
> );
>
> #
> # Table structure for table 'Properties'
> #
> CREATE TABLE Properties (
> CHK varchar(20) ,
> Property_ID int(11) DEFAULT '' NOT NULL auto_increment,
> Insertion_Date date ,
> Status varchar(20) ,
> Property_Name_Or_Num
ber varchar(50) ,
> Address_Line_1 varchar(50) ,
> Address_Line_2 varchar(50) ,
> City varchar(50) ,
> County varchar(50) ,
> Postcode varchar(12) ,
> PRIMARY KEY (Property_ID)
> );
>
> #
> # Table structure for table 'Users'
> #
> CREATE TABLE Users (
> User_ID int(11) DEFAULT '' NOT NULL auto_increment,
> Office_ID int(11) ,
> Type varchar(20) ,
> Title varchar(4) ,
> Firstname varchar(20) ,
> Lastname varchar(20) ,
> Password varchar(20) DEFAULT '' NOT NULL ,
> Email varchar(50) ,
> PRIMARY KEY (User_ID)
> );
>
> # ----------- Dump ends -----------
>
>
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe: http://lists.mysql.com/mysql? unsub...nt
ermix=
..com
>
>

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql? unsub...sie.nctu.edu.tw

mysql@karsites.net

2006-01-23, 1:23 pm

It might be more productive to add a foreign key to the
Offices and Properties tables that points to the
Primary key ID of the respective PostCode in the Postcodes table.

Combined with the other suggestions, this would give you a
key from the Offices and Properties tables directly into the
Postcodes table, and the associated coordinates you want to
match on.

HTH

Keith

In theory, theory and practice are the same;
In practice they are not.

On Mon, 23 Jan 2006, Shaun wrote:

> To: mysql@lists.mysql.com
> From: Shaun < shaunthornburgh@hotm
ail.com>
> Subject: Postcode Search
>
> Hi,
>
> We have a dataset of uk postcodes and their relevant
> X-Coordinates and Y-Coordinates, a table of properties
> (houses), a table of users and a table of offices - users
> work in an office - table structures below.
>
> Is it possible to run a search of all properties in the
> properties table that come within a certain distance of
> the users postcode, currently we do this by downloading
> all properties into an array and stripping out the ones
> that don't come within the radius with php.
>
> Any advice would be greatly appreciated.
>
>
> # ---------- MySQL dump ----------
> #
> # Table structure for table 'Offices'
> #
> CREATE TABLE Offices (
> Office_ID int(11) DEFAULT '' NOT NULL auto_increment,
> Subscriber_ID int(11) ,
> Type varchar(10) ,
> Address_Line_1 varchar(50) ,
> Address_Line_2 varchar(50) ,
> City varchar(50) ,
> County varchar(50) ,
> Postcode varchar(10) ,
> Telephone varchar(12) ,
> Fax varchar(12) ,
> Radius tinyint(4) ,
> PRIMARY KEY (Office_ID)
> );
>
> #
> # Table structure for table 'Postcodes'
> #
> CREATE TABLE Postcodes (
> PCDSECT varchar(6) DEFAULT '' NOT NULL ,
> SORTSECT varchar(6) ,
> PCDDIST varchar(4) ,
> SORTDIST varchar(4) ,
> PCDAREA char(2) ,
> X_COORD double(7,1) unsigned ,
> Y_COORD double(7,1) unsigned ,
> PRIMARY KEY (PCDSECT)
> );
>
> #
> # Table structure for table 'Properties'
> #
> CREATE TABLE Properties (
> CHK varchar(20) ,
> Property_ID int(11) DEFAULT '' NOT NULL auto_increment,
> Insertion_Date date ,
> Status varchar(20) ,
> Property_Name_Or_Num
ber varchar(50) ,
> Address_Line_1 varchar(50) ,
> Address_Line_2 varchar(50) ,
> City varchar(50) ,
> County varchar(50) ,
> Postcode varchar(12) ,
> PRIMARY KEY (Property_ID)
> );
>
> #
> # Table structure for table 'Users'
> #
> CREATE TABLE Users (
> User_ID int(11) DEFAULT '' NOT NULL auto_increment,
> Office_ID int(11) ,
> Type varchar(20) ,
> Title varchar(4) ,
> Firstname varchar(20) ,
> Lastname varchar(20) ,
> Password varchar(20) DEFAULT '' NOT NULL ,
> Email varchar(50) ,
> PRIMARY KEY (User_ID)
> );
>
> # ----------- Dump ends -----------
>
>
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe: http://lists.mysql.com/mysql? unsub...ites
.net
>
>

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql? unsub...sie.nctu.edu.tw

Shaun

2006-01-26, 4:56 pm

<SGreen@unimin.com> wrote in message
news:OF940D16D5.DB6DC8B3-ON852570FF.005D5BA1-852570FF.005EEBB8@unimin.com...
> "Shaun" < shaunthornburgh@hotm
ail.com> wrote on 01/23/2006 11:51:32 AM:
>
> table
>
> do
> ones
>
> Assuming a general distance formula of R=SQR((x1-x2)^2 + (y1-y2)^2)
>
>
> Here is a parameterized example for a single user:
>
> SET @userID = ?? /* your pick */
> SET @TargetR = ?? /* again, your pick */
>
> SELECT @sourceX:= pc.X_COORD, @sourceY := pc.Y_COORD
> FROM postcodes pc
> INNER JOIN office o
> ON o.postcode = PC.pcdsect
> INNER JOIN users u
> ON u.office_id = o.office_id
> AND u.user_id = @userID;
>
> CREATE TEMPORARY TABLE tmpShortList (KEY(pcdsect)) SELECT
> pc.pcdsect,
> (@sourceX - pc.X_COORD) X_DIFF,
> (@sourceY - pc.Y_COORD) Y_DIFF,
> FROM Postcodes pc
> WHERE pc.X_COORD BETWEEN (@SourceX - @TargetR) and (@SourceX + @TargetR)
> AND pc.Y_COORD BETWEEN (@SourceY - @TargetR) and (@SourceY + @TargetR)
> HAVING SQR(X_DIFF*X_DIFF + Y_DIFF*Y_DIFF) <= @TargetR;
>
> Now you have a table of all Postal codes (tmpShortList) that fall within a
> certain radius (@TargetR) of a certain user (@UserID). Do with it what you
> will. In your example, you wanted to see all properties were within that
> search radius.
>
> SELECT p.*
> FROM properties p
> INNER JOIN tmpShortList sl
> ON sl.pcdsect = p.postcode;
>
>
>
> HTH!
>
> Shawn Green
> Database Administrator
> Unimin Corporation - Spruce Pine
>
>
>
>

Hi Shawn,

Sorry for not replying sooner, had to get to get create table permissions
added to the server. In our table of postcodes we only have the 1st part of
the postcode plust the next one after the space i.e.

mysql> SELECT * FROM Postcodes LIMIT 10;
+---------+----------+---------+----------+---------+----------+----------+
| PCDSECT | SORTSECT | PCDDIST | SORTDIST | PCDAREA | X_COORD | Y_COORD |
+---------+----------+---------+----------+---------+----------+----------+
| AB10 1 | AB10 1 | AB10 | AB10 | AB | 393439.0 | 806083.6 |
| AB10 6 | AB10 6 | AB10 | AB10 | AB | 392722.5 | 804986.1 |
| AB10 7 | AB10 7 | AB10 | AB10 | AB | 392137.3 | 803705.5 |
| AB11 5 | AB11 5 | AB11 | AB11 | AB | 394945.3 | 805929.4 |
| AB11 6 | AB11 6 | AB11 | AB11 | AB | 393777.1 | 805585.0 |
| AB11 7 | AB11 7 | AB11 | AB11 | AB | 393878.7 | 804655.6 |
| AB11 8 | AB11 8 | AB11 | AB11 | AB | 395810.7 | 804873.8 |
| AB11 9 | AB11 9 | AB11 | AB11 | AB | 395330.2 | 805247.4 |
| AB12 3 | AB12 3 | AB12 | AB12 | AB | 394715.1 | 801526.0 |
| AB12 4 | AB12 4 | AB12 | AB12 | AB | 391968.1 | 797593.4 |
+---------+----------+---------+----------+---------+----------+----------+
10 rows in set (0.00 sec)

mysql>

So I need to compare an office postcode i.e. SN1 1QS to the Postcode table
in the 1st query, is this possible?

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql? unsub...sie.nctu.edu.tw

Sponsored Links