CODEWITHSUNDEEP
phpmysqlsql
Karthik Malla
Karthik Malla

Reputation: 5820

MySQL Geo Location showing wrong distance

I am using the below SQL query to fetch the distance of the each record form its longitude and latitude but can't understand why the distance for all records is showing same? 

SELECT
 max(if(`field_name`='store-name', `field_value`, null )) AS `store-name`,
 max(if(`field_name`='store-description', `field_value`, null )) AS `store-description`,
 max(if(`field_name`='store-longitude', `field_value`, null )) AS `store-longitude`,
 max(if(`field_name`='store-latitude', `field_value`, null )) AS `store-latitude`,
 ((ACOS( SIN( -27.486204 * PI( ) /180 ) * SIN( 'store-latitude' * PI( ) /180 ) + COS( -27.486204 * PI( ) /180 ) * COS( 'store-latitude' * PI( ) /180 ) * COS( (152.994962 - 'store-longitude') * PI( ) /180 ) ) *180 / PI( )) *60 * 1.1515) AS `distance`
FROM `wp_cf7dbplugin_submits` 
WHERE `form_name` = 'Add Store'  
GROUP BY `submit_time` 
ORDER BY `submit_time` DESC
LIMIT 0,100

Output:

enter image description here

Upvotes: 0

Views: 129

Answers (1)

Mark Baker
Mark Baker

Reputation: 212522

Use backticks (`), not single quotes (') around column names.... they're column names, not strings

SELECT *,
       ((ACOS( SIN( -27.486204 * PI( ) /180 ) * SIN( `store-latitude` * PI( ) /180 ) + COS( -27.486204 * PI( ) /180 ) * COS( `store-latitude` * PI( ) /180 ) * COS( (152.994962 - `store-longitude`) * PI( ) /180 ) ) *180 / PI( )) *60 * 1.1515) AS `distance`
   FROM ( SELECT
             max(if(`field_name`='store-name', `field_value`, null )) AS `store-name`,
             max(if(`field_name`='store-description', `field_value`, null )) AS `store-description`,
             max(if(`field_name`='store-longitude', `field_value`, null )) AS `store-longitude`,
             max(if(`field_name`='store-latitude', `field_value`, null )) AS `store-latitude`
        FROM `wp_cf7dbplugin_submits` 
       WHERE `form_name` = 'Add Store'  
       GROUP BY `submit_time` 
       ORDER BY `submit_time` DESC
       ) A
LIMIT 0,100

Otherwise, MySQL will try to cast that quoted string to a numeric to do the multiplication, and treat it as 0

Upvotes: 1

Related Questions