"Shaun" <[EMAIL PROTECTED]> 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_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 -----------
>
>
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
