Postcode Search

[Shaun]

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_Number 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/[EMAIL PROTECTED]