Ansego Posted March 21, 2014 Share Posted March 21, 2014 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 Quote Link to comment Share on other sites More sharing options...

Ansego Posted March 24, 2014 Author Share Posted March 24, 2014 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; Quote Link to comment Share on other sites More sharing options...

ignace Posted March 24, 2014 Share Posted March 24, 2014 HAVING should be WHERE Quote Link to comment Share on other sites More sharing options...

Ansego Posted March 24, 2014 Author Share Posted March 24, 2014 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 Quote Link to comment Share on other sites More sharing options...

Ansego Posted March 25, 2014 Author Share Posted March 25, 2014 ...bump Quote Link to comment Share on other sites More sharing options...

Ansego Posted March 25, 2014 Author Share Posted March 25, 2014 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. Quote Link to comment Share on other sites More sharing options...

Solution Barand Posted March 25, 2014 Solution Share Posted March 25, 2014 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 | +--------+-------------+--------------+----------+------------+-------------+--------------+------------------+ Quote Link to comment Share on other sites More sharing options...

Ansego Posted March 25, 2014 Author Share Posted March 25, 2014 (edited) 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 Quote Link to comment Share on other sites More sharing options...

## Recommended Posts

## Join the conversation

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