I’m trying to select documents with multiple lat/lon pairs using geodist but whilst the documentation mentions: If the document has multiple locations, they can be embedded in a JSON object as float pairs, it doesn’t specify how this should be done.
I’ve tried both lat / lon pairs in a single json attribute [[52.673, -1.18767], [52.7208, -1.36021]
SELECT *, GEODIST(50.407505, -5.264365, latlon_json, {in=degrees, out=miles}) AS distance FROM notices ORDER BY DISTANCE ASC
and lat and lon in seperate attributes [50.407505, 50.1682] [-5.264365, -5.10308]
SELECT *, GEODIST(50.407505, -5.264365, lat_json, lon_json, {in=degrees, out=miles}) AS distance FROM notices ORDER BY DISTANCE ASC
But I’m afraid what is meant is that if a document includes multiple locations it may be convenient to store them in a json attribute, but it doesn’t change the way you can access the coordinates, i.e. it doesn’t change the behaviour of GEODIST(), nor it does any other magic. E.g. you can just do:
mysql> drop table if exists t; create table t(f text, j json); insert into t(j) values('{"coord1":[54.833941, 83.096050],"coord2":[54.785440, 83.196717]}'); select *, geodist(54.808514, 83.134377,j.coord1[0],j.coord1[1],{in=degrees, out=km}) dist from t; select *, geodist(54.808514, 83.134377,j.coord2[0],j.coord2[1],{in=degrees, out=km}) dist from t;
--------------
drop table if exists t
--------------
Query OK, 0 rows affected (0.01 sec)
--------------
create table t(f text, j json)
--------------
Query OK, 0 rows affected (0.00 sec)
--------------
insert into t(j) values('{"coord1":[54.833941, 83.096050],"coord2":[54.785440, 83.196717]}')
--------------
Query OK, 1 row affected (0.00 sec)
--------------
select *, geodist(54.808514, 83.134377,j.coord1[0],j.coord1[1],{in=degrees, out=km}) dist from t
--------------
+---------------------+-----------------------------------------------------------------+------+------------+
| id | j | f | dist |
+---------------------+-----------------------------------------------------------------+------+------------+
| 1514665016379310097 | {"coord1":[54.833941,83.096050],"coord2":[54.785440,83.196717]} | | 3.75286126 |
+---------------------+-----------------------------------------------------------------+------+------------+
1 row in set (0.01 sec)
--------------
select *, geodist(54.808514, 83.134377,j.coord2[0],j.coord2[1],{in=degrees, out=km}) dist from t
--------------
+---------------------+-----------------------------------------------------------------+------+------------+
| id | j | f | dist |
+---------------------+-----------------------------------------------------------------+------+------------+
| 1514665016379310097 | {"coord1":[54.833941,83.096050],"coord2":[54.785440,83.196717]} | | 4.76190233 |
+---------------------+-----------------------------------------------------------------+------+------------+
1 row in set (0.00 sec)
Perfect, huge thanks for such a quick response, I have around 5 million documents with several geo coordinates each and search results are returned in around a second which is very impressive
Just wondered if you could think of a better way to select the minimum distance in the following query?
MySql has a least function that takes multiple values but Manticore’s least funtion requires a json array and using multiple mins doubles the query time.
SELECT *,
GEODIST(53.14208, -2.48365, latlon_json.1[0], latlon_json.1[1], {in=degrees, out=miles}) AS d1,
GEODIST(53.14208, -2.48365, latlon_json.2[0], latlon_json.2[1], {in=degrees, out=miles}) AS d2,
GEODIST(53.14208, -2.48365, latlon_json.3[0], latlon_json.3[1], {in=degrees, out=miles}) AS d3,
GEODIST(53.14208, -2.48365, latlon_json.4[0], latlon_json.4[1], {in=degrees, out=miles}) AS d4,
MIN(d1, d2) as d_r2,
MIN(d_r2, d3) as d_r3,
MIN(d_r3, d4) as d
FROM notices WHERE d<20
If the document has multiple locations, they can be embedded in a JSON object as float pairs
I’ve discussed this with the team. It means you can use just a single json attribute instead N of float attributes.
MIN/LEAST don’t support more than 2 arguments, but you can do MIN(MIN(... etc.
It shouldn’t make the query faster, but on the other hand this:
MIN(d1, d2) as d_r2,
MIN(d_r2, d3) as d_r3,
MIN(d_r3, d4) as d
shouldn’t have made it slower. At least not 2 times slower compared to ordering by just single MIN() result. Please provide full query/result examples with SHOW META after the queries.
Everything else is pretty much the same and to be honest, 1 second is more than acceptable anyway
I’d be interesed to know if SHOW META is connection aware, for example, if I open a connection and perform a query then show meta before closing the connection, will the meta data be for the connections previous query or for the previous query globally?
I’d also be interested in sponsoring an enhancement based on the GEODIST function to take lat, lon and single json field which contains a varying number of lat / lon pairs and returns the minimum distance found i.e.
meta is valid only in current MySQL connection, ie MYSQL_COM_QUIT or sphinxql_timeout closes connection
And every next query replace previous meta information.
So I can use show meta to retrieve the maximum number or records considered in a limit query rather than having to do select count(*) first to get total records.
