Jump to content

Radius of Latitude and Longitude Logic & Maths Scope - Postcodes


Ansego
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 )
  • Radius ( R )
  • Area ( A )
  • Pi ( P ) 
A = P R Square
50 km = A≈7853.98
 
 
 
Link to comment
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;

 

Link to comment
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

Link to comment
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
, 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 |
+--------+-------------+--------------+----------+------------+-------------+--------------+------------------+
Link to comment
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
, 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 by Ansego
Link to comment
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.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.