Radius of Latitude and Longitude Logic & Maths Scope - Postcodes

[Ansego]

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 )
• Radius ( R )
• Area ( A )
• Pi ( P ) 

A = P R Square

50 km = A≈7853.98

 

 

 

[Ansego]

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;

 

[ignace]

HAVING should be WHERE

[Ansego]

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

[Ansego]

...bump

[Ansego]

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.

[Barand]

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
, ATAN2(SQRT(POW(COS(RADIANS(a.latitude)) * SIN(RADIANS(b.longitude - a.longitude)), 2) 
    + POW(COS(RADIANS(b.latitude)) * SIN(RADIANS(a.latitude)) 
    - SIN(RADIANS(b.latitude)) * COS(RADIANS(a.latitude)) 
    * COS(RADIANS(b.longitude - a.longitude)), 2)), (SIN(RADIANS(b.latitude)) 
    * SIN(RADIANS(a.latitude)) + COS(RADIANS(b.latitude)) * COS(RADIANS(a.latitude)) 
    * 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 |
+--------+-------------+--------------+----------+------------+-------------+--------------+------------------+

[Ansego]

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
, ATAN2(SQRT(POW(COS(RADIANS(a.latitude)) * SIN(RADIANS(b.longitude - a.longitude)), 2) 
    + POW(COS(RADIANS(b.latitude)) * SIN(RADIANS(a.latitude)) 
    - SIN(RADIANS(b.latitude)) * COS(RADIANS(a.latitude)) 
    * COS(RADIANS(b.longitude - a.longitude)), 2)), (SIN(RADIANS(b.latitude)) 
    * SIN(RADIANS(a.latitude)) + COS(RADIANS(b.latitude)) * COS(RADIANS(a.latitude)) 
    * 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 March 25, 2014 by Ansego