<[EMAIL PROTECTED]> wrote in message news:[EMAIL PROTECTED] > "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 > > > >
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/[EMAIL PROTECTED]
