MySQL - Query based on another query

Question

I have two tables,

National latitude, longitude (italy_db) => id | cap | comune | latitudine | longitudine
Shop list DB (center) => id_geo | address | lat | lng

    CREATE TABLE `italy_db` (
      `id` int(11) NOT NULL,
      `cap` varchar(5) DEFAULT NULL,
      `comune` varchar(48) DEFAULT NULL,
      `latitudine` decimal(13,9) DEFAULT NULL,
      `longitudine` decimal(13,9) DEFAULT NULL
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8;

    CREATE TABLE `center` (
      `id_geo` int(11) NOT NULL,
      `address` varchar(50) NOT NULL,
      `lat` varchar(255) NOT NULL,
      `lng` varchar(255) NOT NULL
    ) ENGINE=MyISAM DEFAULT CHARSET=utf8;


    INSERT INTO `italy_db` (`id`, `cap`, `comune`, `latitudine`, `longitudine`) VALUES
    (145, '00137', 'Roma',  '41.952211560', '12.565102860'),
    (146, '00138', 'Roma',  '42.005710820', '12.522652930'),
    (147, '00139', 'Roma',  '41.990002960', '12.557905280');

    INSERT INTO `center` (`id_geo`, `address`, `lat`, `lng`) VALUES
    (1, 'Via Luigi Corsi', '44.3060039', '8.4779466'),
    (2, 'Via Fieschi', '44.4062547', '8.9348863'),
    (3, 'Corso Garibaldi', '44.3153768', '9.3244363');

A query like this, but without UNION SELECT + lat and longitude from italy_db:

SELECT (TRUNCATE(6371 * acos(cos(radians(41.952211560)) 
                      * cos(radians(lat)) 
                      * cos(radians(lng) - radians(12.565102860))  
                      + sin(radians(41.952211560))  
                      * sin(radians(lat))), 2)) AS distance, address
                      FROM center
UNION                       
SELECT (TRUNCATE(6371 * acos(cos(radians(42.005710820)) 
                      * cos(radians(lat)) 
                      * cos(radians(lng) - radians(12.522652930))  
                      + sin(radians(42.005710820))  
                      * sin(radians(lat))), 2)) AS distance, address
                      FROM center       
UNION                       
SELECT (TRUNCATE(6371 * acos(cos(radians(41.990002960)) 
                      * cos(radians(lat)) 
                      * cos(radians(lng) - radians(12.557905280))  
                      + sin(radians(41.990002960))  
                      * sin(radians(lat))), 2)) AS distance, address
                      FROM center  

 UNION...

Desired result it is something like this:

+-----+----------+-----------------+
| id  | distance |     address     |
+-----+----------+-----------------+
| 145 |   422.41 | Via Luigi Corsi |
| 145 |    401.3 | Via Fieschi     |
| 145 |   371.69 | Corso Garibaldi |
| 146 |   415.92 | Via Luigi Corsi |
| 146 |   394.65 | Via Fieschi     |
| 146 |   364.97 | Corso Garibaldi |
| 147 |   419.28 | Via Luigi Corsi |
| 147 |   397.96 | Via Fieschi     |
| 147 |   368.26 | Corso Garibaldi |
+-----+----------+-----------------+

http://sqlfiddle.com/#!9/811e4b/1

thanks!

Strawberry · Accepted Answer · 2020-01-14 16:39:25Z

I have an (easily googlable) function on my database called geo_distance_km() that calculates distances between points on Earth:

Edited to suit revised question:

SELECT i.id
     , c.address
     , geo_distance_km(i.latitudine,i.longitudine,c.lat,c.lng) delta 
  FROM italy_db i 
  JOIN center c;
+-----+-----------------+-----------------+
| id  | address         | delta           |
+-----+-----------------+-----------------+
| 145 | Via Luigi Corsi | 422.54940773235 |
| 145 | Via Fieschi     | 401.43142363755 |
| 145 | Corso Garibaldi | 371.80978209984 |
| 146 | Via Luigi Corsi | 416.05639591524 |
| 146 | Via Fieschi     | 394.77550635234 |
| 146 | Corso Garibaldi | 365.09263623054 |
| 147 | Via Luigi Corsi | 419.41813848248 |
| 147 | Via Fieschi     | 398.08904062967 |
| 147 | Corso Garibaldi | 368.37766651696 |
| 148 | Via Luigi Corsi | 422.30379996483 |
| 148 | Via Fieschi     | 401.81952331197 |
| 148 | Corso Garibaldi | 372.49204793302 |
| 149 | Via Luigi Corsi | 420.34605188137 |
| 149 | Via Fieschi     | 399.41130410404 |
| 149 | Corso Garibaldi | 369.87810059498 |
| 150 | Via Luigi Corsi | 427.59291358736 |
| 150 | Via Fieschi     | 407.62059536037 |
| 150 | Corso Garibaldi | 378.52524882203 |
| 151 | Via Luigi Corsi | 433.78943788163 |
| 151 | Via Fieschi     | 414.24806777672 |
| 151 | Corso Garibaldi | 385.34799821521 |
| 152 | Via Luigi Corsi | 424.48039315087 |
| 152 | Via Fieschi     |  404.6886672107 |
| 152 | Corso Garibaldi | 375.68725240841 |
| 153 | Via Luigi Corsi | 424.69438280039 |
| 153 | Via Fieschi     | 404.53229563659 |
| 153 | Corso Garibaldi | 375.35235082512 |
| 154 | Via Luigi Corsi | 423.30285536186 |
| 154 | Via Fieschi     | 403.21887435326 |
| 154 | Corso Garibaldi | 374.07925613256 |
| 155 | Via Luigi Corsi | 425.20481909222 |
| 155 | Via Fieschi     | 404.96007335298 |
| 155 | Corso Garibaldi | 375.73970068109 |
| 156 | Via Luigi Corsi | 419.82757084333 |
| 156 | Via Fieschi     | 400.31659533471 |
| 156 | Corso Garibaldi | 371.46280026526 |
| 157 | Via Luigi Corsi | 422.16040253477 |
| 157 | Via Fieschi     | 402.13158556705 |
| 157 | Corso Garibaldi | 373.02091846066 |
+-----+-----------------+-----------------+

I cannot see what part of the problem, this fails to solve. But perhaps that's down to your explanation.

That function in full...

delimiter //
create DEFINER = CURRENT_USER function geo_distance_km (lat1 double, lon1 double, lat2 double, lon2 double) returns double
 begin
   declare R int DEFAULT 6372.8;
   declare phi1 double;
   declare phi2 double;
   declare d_phi double;
   declare d_lambda double;
   declare a double;
   declare c double;
   declare d double;
   set phi1 = radians(lat1);
   set phi2 = radians(lat2);
   set d_phi = radians(lat2-lat1);
   set d_lambda = radians(lon2-lon1);
   set a = sin(d_phi/2) * sin(d_phi/2) +
         cos(phi1) * cos(phi2) *
         sin(d_lambda/2) * sin(d_lambda/2);
   set c = 2 * atan2(sqrt(a), sqrt(1-a));
   set d = R * c;
   return d;
   end;
//
delimiter ;

Raul Luna · Accepted Answer · 2020-01-15 07:52:26Z

1

Other version of the same:

select id, 
(TRUNCATE(6371 * acos(cos(radians(ita.latitudine)) 
                      * cos(radians(cent.lat)) 
                      * cos(radians(cent.lng) 
                            - radians(ita.longitudine))  
                      + sin(radians(ita.latitudine))  
                      * sin(radians(cent.lat))), 2)) AS distance, address
from center cent, italy_db ita;

answered Jan 15, 2020 at 7:52

Raul Luna

2,0551 gold badge20 silver badges30 bronze badges

1 Comment

cloude Over a year ago

Thank you! this is what I was looking for, I am really grateful to you!

Collectives™ on Stack Overflow

MySQL - Query based on another query

2 Answers 2

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related