maths function to draw circle around central point

TomGoodacre

I have a database full of OS grid references related to postcodes. I need to be able to extract all postcodes that appear a certain distance away from a postcode that a user enters.

so...

user enters postcode into form
easting (horizontal) and northing (vertical) grid references for entered postcode are retrieved from the database.
PHP used to locate all postcodes (by querying other easting and nothings) that are within a certain distance from postcode entered in stage 1.

The problem. This is fairly easy to do if you take the entered postcode and then draw a square around it of a certain size but I would like to be able to retrieve postcodes by drawing a circle around the entered postcode. Is this possible with PHP and if so how???

Complicated to explain I know - if there are any further questions just post back.

barand

You are going to need Pythagoras' theorem. If $e and $n are easting and northing od search centre and $radius is the search radius:-

SELECT .... WHERE ( SQRT(POW((easting - $e),2) + POW((northing - $n),2) ) <= $radius )

As this is going to computationally intensive you might want to consider putting those in the 'square area' in a temp table then selecting from that subset, or just settle for the square.

hth

Weedpacket

A bit of a saving can be made by not using SQRT() and comparing the bit on the left with $radius*$radius (I don't know if that would be better than POW($radius,2), but then that bit could be evaluated in PHP instead of SQL anyway.)

jamal_dakak

Hi,

I need to display all properties that exist within x milles of the entered postcode.
This is my query:

SELECT p.ID AS PropertyID, p.PriceSale, p.PriceRent, p.Status, p.Name AS PropertyName, p.Type, p.Image1, p.Image2, p.Image3, p.Image4, p.Image5, p.Image6, p.Description, p.NumBedrooms, p.Address1, p.Address2, p.TownCity, p.County, p.Postcode
FROM Properties AS p
WHERE p.Type = "Sale"
AND (
SQRT( POWER( (
x_cord -353800
), 2 ) + POWER( (
y_cord -429300
), 2 ) ) <5
)
AND p.NumBedrooms +0 >=2
AND p.PriceSale +0 >=2
ORDER BY p.PriceSale +0 ASC
LIMIT 0 , 30

It give me the same results even if I change the distance to whatever
SQRT( POWER( (
x_cord -353800
), 2 ) + POWER( (
y_cord -429300
), 2 ) ) < distance(miles)

can you help me with this

Thanks 😕

Weedpacket

What units are you using for your easting and northing (usually they're given in metres)? The distance has to be given in the same units. Oh, and of course the eastings and northings for both sites have to be measured from the same origin point.