Multi column in nested query in select

Question

I've this query in mysql:

    set @dist=20;
    set @earthRadius=6371;
    SELECT *,SPLIT_STR(posizione_GPS,';', 1) as mylat,SPLIT_STR(posizione_GPS,';', 2) as mylon,(SELECT CALC_DISTANCE(mylat,mylon,cit.lat,cit.lng,@earthRadius) as distance FROM city as cit HAVING distance<@dist ORDER BY distance asc LIMIT 1 ) as distance FROM statistiche as stat

with this, I get the statistics, and given that each statistic is associated with a coordinated GPS, I want to get the name of the city associated with it using a table (the city) that I already in the database. To do this calculation the distance between the coordinates and take the city which is less distant.

CALC_DISTANCE is a custom function for calculate distance between two gps points.

The query works but I need the name of city and if I put in subquery a second column, name:

    set @dist=20;
    set @earthRadius=6371;
    SELECT *,SPLIT_STR(posizione_GPS,';', 1) as mylat,SPLIT_STR(posizione_GPS,';', 2) as mylon,(SELECT nome, CALC_DISTANCE(mylat,mylon,cit.lat,cit.lng,@earthRadius) as distance FROM city as cit HAVING distance<@dist ORDER BY distance asc LIMIT 1 ) as distance FROM statistiche as stat

I get this error

    Error Code: 1241. Operand should contain 1 column(s)

How do I get the name of the city? Thanks

The structure of statistiche table is:

    `id` int(11) NOT NULL AUTO_INCREMENT,
    `utenti_id` int(11) NOT NULL,
    `spots_id` int(11) NOT NULL,
    `posizione_GPS` varchar(45) DEFAULT NULL,
    `data` date DEFAULT NULL,
    `ora` time DEFAULT NULL,
     PRIMARY KEY (`id`)

The structure of city table is:

   `id` varchar(10) NOT NULL,
   `nome` varchar(100) DEFAULT NULL,
   `prov` varchar(45) DEFAULT NULL,
   `lat` float(10,6) DEFAULT NULL,
   `lng` float(10,6) DEFAULT NULL,
   PRIMARY KEY (`id`)

Yes work. this is the definition earth_radius * 2 * ASIN(SQRT( POWER(SIN((mylat - destlat) * pi()/180 / 2), 2) +COS(mylat * pi()/180) * COS(destlat * pi()/180) * POWER(SIN((mylng -destlng) * pi()/180 / 2), 2) )) — majinb_igor, Dec 09 '15 at 14:58

Richard St-Cyr · Answer 1 · 2015-12-15T15:44:38.930

0

I see the problem. You sub select is built as follow:

(SELECT nome, CALC_DISTANCE(mylat,mylon,cit.lat,cit.lng,@earthRadius) as distance 
        FROM city as cit 
        HAVING distance<@dist 
        ORDER BY distance asc LIMIT 1 ) 
      as distance

In this sub select, you are selecting two columns (nome and the result of CALC_DISTANCE), which can't be done. I guess you already knew this but I did not catch it.

I would change your sub query to return only the city. Once the city is available, you can recompute the distance, for this single row.

That's probably not the most efficient way, but it is the only way I can think of.

EDIT

I would rework the query to use a JOIN, instead of a sub-select.

set @dist=20;
set @earthRadius=6371;

SELECT * FROM (
SELECT 
  stat.id as s_id,
  posizione_GPS,
  SPLIT_STR(posizione_GPS,';', 1) as mylat,
  SPLIT_STR(posizione_GPS,';', 2) as mylon,
  city.id as c_id,
  lat,
  lng,
  CALC_DISTANCE(SPLIT_STR(posizione_GPS,';', 1), SPLIT_STR(posizione_GPS,';', 2), lat, lng, 6371) as distance
 FROM statistiche stat JOIN city 
ON CALC_DISTANCE(SPLIT_STR(posizione_GPS,';', 1), SPLIT_STR(posizione_GPS,';', 2), lat, lng, 6371) < @dist
ORDER BY s_id, distance ASC
) A group by s_id;

This query seems to work properly on a test dataset that I created and performance is not much worst than using the sub-query.

edited Dec 15 '15 at 15:44

answered Dec 09 '15 at 15:39

Richard St-Cyr

970
1
8
14

Also I would like to do this, but I have not found a way to do it – majinb_igor Dec 10 '15 at 09:33
What do you mean? You did not find a way to compute the distance based on the city? – Richard St-Cyr Dec 10 '15 at 12:54
No, I did not find a way to solve the problem of the second query of my original post – majinb_igor Dec 14 '15 at 10:34
I'd like to play with this a little. Can you share the structure of the city table, and a few entries relevant to it, so that I can see how this could be solved? – Richard St-Cyr Dec 14 '15 at 12:36
hello, I have modified the initial post adding table structure – majinb_igor Dec 15 '15 at 15:18
Hello, I tried it but it does not work. I get this error: `Error Code: 2013. Lost connection to MySQL server during query` is possible the query is too complex and the server kill it? – majinb_igor Dec 16 '15 at 09:35
How many rows do you have in statistiche and city? The query is complex and might take some time running. It's more likely that your connection times out if it takes too long than MySQL killing it. What do you use to test the query? – Richard St-Cyr Dec 16 '15 at 12:21
`city` has 8000 records, while `statistiche` has 68000 records. Use this query on a semi-dedicated hosting, and I read that I have a limit of 5 seconds for queries – majinb_igor Dec 18 '15 at 09:29
What is the result of EXPLAIN on the query? – Richard St-Cyr Dec 18 '15 at 12:23

Multi column in nested query in select

1 Answers1