1

Total SQL newb here. I've created two SQL tables, one containing information about hotels and the other containing information about attractions. 

SELECT hotels.*, attractions.*,  
    ((ACOS(SIN(hotels.Hotel_Lat * PI() / 180) * SIN(attractions.Attraction_Lat * PI() / 180) +  
        COS(hotels.Hotel_Long * PI() / 180) * COS(attractions.Attraction_Long * PI() / 180) *  
        COS((hotels.Hotel_Long - attractions.Attraction_Long) * PI() / 180)) *   
        180 / PI()) * 60 * 1.1515) as distance  
FROM hotels join attractions

This query returns 'Null' for distances. Any ideas why please? I have negative value longitudes if that makes a difference?

Serpiton
  • 3,676
  • 3
  • 24
  • 35
user3694204
  • 11
  • 3
  • 1
    Yes, but which version of MySQL? The workbench is just an application that connects to MySQL. – Joachim Isaksson May 31 '14 at 12:45
  • Oh sorry, mysql community server version 5.6.17. Sound right? – user3694204 May 31 '14 at 12:50
  • Please edit your question and show the query you are currently using. – Gordon Linoff May 31 '14 at 12:53
  • Please post the SQL as plain text, not an image link. Even better would be to make a sqlfiddle with sample data. – Barmar May 31 '14 at 12:55
  • Your distance formula doesn't look right. You're calculating normal cartesian distance, not distance on a sphere. See http://en.wikipedia.org/wiki/Geographical_distance – Barmar May 31 '14 at 12:57
  • See the query in this question: http://stackoverflow.com/questions/4741384/distance-between-two-coordinates-how-can-i-simplify-this-and-or-use-a-different?rq=1 – Barmar May 31 '14 at 12:58
  • Because you're using the wrong formula, your distances aren't in any useful units. Your formula treats degrees as units on the plane, and it's calculating distance in those units. – Barmar May 31 '14 at 13:00
  • I see, would it be simpler for me to change the formula, or to change the units? This doesn't need to be huge, my data is very small. – user3694204 May 31 '14 at 13:05
  • If at least one of the value of the formula is `NULL` it'll spread to the results, and even if the formulata return a value it will be the direct route, not the road distance – Serpiton May 31 '14 at 14:35

2 Answers2

0

You can't calculate the distance like this, because it doesn't consider the curvature of the earth.

After a little bit of googling you would have got the following link: http://zcentric.com/2010/03/11/calculate-distance-in-mysql-with-latitude-and-longitude/ and there is the correct SQL Query:

SELECT ((ACOS(SIN($lat * PI() / 180) * SIN(`lat` * PI() / 180) + COS($lat * PI() / 180) * COS(`lat` * PI() / 180) * COS(($lon – `lon`) * PI() / 180)) * 180 / PI()) * 60 * 1.1515) AS distance 
FROM `members` 
HAVING distance<=’10′ 
ORDER BY distance ASC

So for you it would look something like this:

SELECT h.*, a.*,
    ((ACOS(SIN(h.Hotel_Lat * PI() / 180) * SIN(a.Attractions_Lat * PI() / 180) +
        COS(h.Hotel_Long * PI() / 180) * COS(a.Attractions_Long * PI() / 180) *
        COS((h.Hotel_Long - a.Attractions_Long) * PI() / 180)) * 
        180 / PI()) * 60 * 1.1515) as distance
FROM hotel h join attractions a
peter
  • 14,348
  • 9
  • 62
  • 96
  • Really appreciate the help, thankyou. Do I need to change anything in the formula? I'm getting a syntax error. – user3694204 May 31 '14 at 13:13
  • I might have a typo in it - what's the exact error? – peter May 31 '14 at 13:13
  • 14:14:59 SELECT h.*, a.*, ((COS(SIN(h.Hotel_Lat * PI() / 180) * SIN(a.Attractions_Lat * PI() / 180) + COS(h.Hotel_Long * PI() / 180) * COS(a.Attractions_Long * PI() / 180) * 180 / PI()) * 60 * 1.1515 as distance from hotel h join attractions a 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 'as distance from hotel h join attractions a' at line 4 0.000 sec – user3694204 May 31 '14 at 13:15
  • There was a bracket missing at the end of the formula - it's there now – peter May 31 '14 at 13:18
  • SELECT hotels.*, attractions.*, ((ACOS(SIN(hotels.Hotel_Lat * PI() / 180) * SIN(attractions.Attraction_Lat * PI() / 180) + COS(hotels.Hotel_Long * PI() / 180) * COS(attractions.Attraction_Long * PI() / 180) * COS((hotels.Hotel_Long - attractions.Attraction_Long) * PI() / 180)) * 180 / PI()) * 60 * 1.1515) as distance FROM hotels join attractions – user3694204 May 31 '14 at 13:25
  • But it's bringing back Null in distance, any idea? – user3694204 May 31 '14 at 13:26
  • no, what you could do is - splitting the long formula into different columns to see which part evaluates to NULL - because that purt will make everything else NULL as well. However this should only happen if one of the columns used is NULL in your tables already - this doesn’t seem to be the case according to your screenshot. – peter Jun 01 '14 at 12:29
0

Why are you making this incredibly complicated for yourself? Just enable the MySQL Geospatial extensions and use ST_Distance to solve your problem:

SELECT hotels.*, a.*,  ST_Distance(ST_MakePoint(hotels.Hotel_Lon, hotels.Hotel_Lat),  ST_MakePoint(a.Attractions_Long, a.Attractions_Lat) as distance FROM hotels join attractions a;

Do be aware, that mysql's St_Distance function does not take advantage of indices and only handles polar coordinates.

Hope it helps and if you have further problems, do leave a comment.

hd1
  • 33,938
  • 5
  • 80
  • 91
  • Because I don't have a clue what I am doing unfortunately. I can't even see how to enable extensions. – user3694204 May 31 '14 at 14:59
  • Please try the query I posted and report back? – hd1 May 31 '14 at 15:20
  • 16:20:12 SELECT hotel.*, attraction.*, ST_Distance(ST_MakePoint(hotel.long, hotel.lat), ST_MakePoint(attraction.long, attraction.lat) as distance FROM hotel join attraction a 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 'FROM hotel join attraction a' at line 3 0.000 sec I changed it a bit because I made some new tables, specifying the lats and longs as a geometry datatype. – user3694204 May 31 '14 at 15:21
  • I literally started using this program this morning, I've had a look at fiddle but have no idea what to do with it. I can post screenshots if that helps? – user3694204 May 31 '14 at 15:25
  • On the left pane, you put your DDL statements to create your data, on the right, put SELECT 1 and "Run SQL". Put the resulting URL in a comment here and I'll look at it. – hd1 May 31 '14 at 15:27
  • My apologies, I don't know how to write DDL statements, I just make the tables in workbench. I've only been given an assignment and no direction. This is the best I can do... http://imgur.com/APsVEJX – user3694204 May 31 '14 at 15:41
  • `mysqldump -d -u -p -h localhost ` will show you the DDL statements from the command line – hd1 May 31 '14 at 15:47
  • Drop me a line on google talk -- hd1@jsc.d8u.us and we'll get this sorted – hd1 May 31 '14 at 16:01