# Radius of Latitude and Longitude Logic & Maths Scope - Postcodes

Go to solution Solved by Barand,

## Recommended Posts

Hi all and thanks for reading;

I need to get a scope together before I start building and having a little trouble with logic and the maths and could do with some input from you all the best way to attack this.

Basically I need to use Latitude and Longitude and a radius of (X) km and get all the postcodes (ZIP, Area code) within that (X) km radius.

There is 8000+ postcodes and some Lat & Long share the same postcode area.

Tools:

• Html/CSS
• Php
• SQL/MySqli

Example:

• Data: Postcode's | Name of area | Lat | Long | (X) Area Radius km
• User Input: '4001'
• Output: 4002, 4005, 4010 3090, 3097 etc

Maths:

• Circumference ( C )
• Diameter ( D )
• Area ( A )
• Pi ( P )
A = P R Square
50 km = A≈7853.98

##### Share on other sites

Hi All,

Having trouble getting this to work, in scope of the project stated above and could do with some assistance to get it to work please.

SQL STATEMENT:

Does not seem to enjoy a double:

```*180/pi())*60*­ 1.1515*1.609344) as `distance`
```

FULL SQL STATEMENT:

```SELECT *,(((acos(sin(( tbl_aupc.Latitude *pi()/180)) * sin((`lat`*pi()/180))+cos(( tbl_aupc.Longitude *pi()/180)) * cos((`lat`*pi()/180)) * cos(((tbl_aupc.Longitude - `long`)*pi()/180))))*180/pi())*60*­ 1.1515*1.609344) as `distance`
FROM `tbl_aupc`
HAVING `distance` <= 50
ORDER BY `distance` ASC;
```

ERROR:

```02:24:12 SELECT *,(((acos(sin(( tbl_aupc.Latitude *pi()/180)) * sin((`lat`*pi()/180))+cos(( tbl_aupc.Longitude *pi()/180)) * cos((`lat`*pi()/180)) * cos(((tbl_aupc.Longitude - `long`)*pi()/180))))*180/pi())*60*­ 1.1515*1.609344) as `distance`  FROM `tbl_aupc`  HAVING `distance` <= 50  ORDER BY `distance` ASC	Error Code: 1064. You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '1.1515*1.609344) as `distance`  FROM `tbl_aupc`  HAVING `distance` <= 50  ORDER ' at line 1	0.000 sec
```

Table Columns:

```pcode
Latitude
Longitude
```

Kind regards;

##### Share on other sites

HAVING should be WHERE

##### Share on other sites

Changed Having -> Where still have this error of the double.

```03:12:29 SELECT *,(((acos(sin(( tbl_aupc.Latitude *pi()/180)) * sin((`lat`*pi()/180))+cos(( tbl_aupc.Longitude *pi()/180)) * cos((`lat`*pi()/180)) * cos(((tbl_aupc.Longitude - `long`)*pi()/180))))*180/pi())*60*­ 1.1515*1.609344) as `distance`  FROM `tbl_aupc`  WHERE  `distance` <= 50  ORDER BY `distance` ASC	Error Code: 1064. You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '1.1515*1.609344) as `distance`  FROM `tbl_aupc`  WHERE  `distance` <= 50  ORDER ' at line 1	0.000 sec

```

...bump

##### Share on other sites

Guess no one wanted a challenge, oh well... I accidentally had a minus where I should have before the double. Thanks @ignace for at least giving it a try.

##### Share on other sites

• Solution

Ignace - the HAVING is ok and necessary.You cannot use an alias in a WHERE clause

Ansego - this was my version to find postcodes within 200km of 5558

```SELECT a.postcode as centre
, a.latitude as ctr_lat
, a.longitude as ctr_long
, b.postcode
, b.areaname
, b.latitude
, b.longitude
* COS(RADIANS(b.longitude - a.longitude)))) * 6372.795
as distance
FROM test1 a
INNER JOIN test1 b
WHERE a.postcode = 5558
AND b.postcode <> a.postcode
HAVING distance <= 200;

+--------+-------------+--------------+----------+------------+-------------+--------------+------------------+
| centre | ctr_lat     | ctr_long     | postcode | areaname   | latitude    | longitude    | distance         |
+--------+-------------+--------------+----------+------------+-------------+--------------+------------------+
|   5558 | 45.57349396 | -73.65723419 |    15948 | Area 15948 | 46.59859085 | -72.83525085 | 130.462675168181 |
|   5558 | 45.57349396 | -73.65723419 |    19254 | Area 19254 | 45.49897766 | -73.71706390 | 9.50901533415637 |
|   5558 | 45.57349396 | -73.65723419 |    19827 | Area 19827 | 45.63068771 | -72.94931793 | 55.4545466390431 |
|   5558 | 45.57349396 | -73.65723419 |    29110 | Area 29110 | 45.53290176 | -73.74340057 | 8.08850181505773 |
|   5558 | 45.57349396 | -73.65723419 |    34732 | Area 34732 | 45.30852127 | -73.26404572 | 42.5458257195249 |
|   5558 | 45.57349396 | -73.65723419 |    37368 | Area 37368 | 45.46603394 | -73.46455383 | 19.1921588559054 |
|   5558 | 45.57349396 | -73.65723419 |    59852 | Area 59852 | 45.48648834 | -73.46222687 | 18.0145815911446 |
+--------+-------------+--------------+----------+------------+-------------+--------------+------------------+
```
##### Share on other sites

Perfect mate!!! Thanks heaps!!! Works a treat!!! So happy ;-)

```SELECT a.PCode as centre
, a.latitude as ctr_lat
, a.longitude as ctr_long
, b.PCode
, b.Locality
, b.latitude
, b.longitude
* COS(RADIANS(b.longitude - a.longitude)))) * 6372.795
as distance
FROM tbl_aupc a
INNER JOIN tbl_aupc b
WHERE a.PCode = 5558
AND b.PCode <> a.PCode
HAVING distance <= 200;
```
Edited by Ansego
##### Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

## Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

×   Pasted as rich text.   Restore formatting

Only 75 emoji are allowed.