MySQL Spatial – Changing Output Unit of ST_Distance Function

MySQLselectspatial

I'm trying to get a result in nautical miles between two lon/lat saved in a point data type field in mySQL. However, the result seems to be not in any unit I'm familiar with (maybe degrees). How can I convert to my preferred unit?. My query as follows…

select t1.id, t2.id, ST_Distance(t1.test,t2.test) as DistanceApart from table1
t1 join table2 t2 on (ST_Distance(t1.test,t2.test) <= 10)

Best Answer

ST_Distance assumes a flat surface and returns a distance in the same units as the "Points" that are fed to it.

You want ST_Distance_Sphere . It takes an optional argument, which you should give as the radius of the earth in nautical miles. Suggest 3440.

But note: That function was not implemented until Version 5.7.6. (Not yet in MariaDB)

Related Solutions

Mysql – Is index on foreign key used? (No difference in EXPLAIN.)

Very quick answer: MyISAM does not support FOREIGN KEYs at all... Any FOREIGN KEY you define on a MYISAM table is silenty thrown away.

When you later switch to InnoDB (i.e. ALTER TABLE my_table ENGINE=InnoDB) the FOREIGN KEY is not there either; it was thrown away when the table used to be MyISAM.

Mysql – Query to compare two subsets of data from the same table

I think you have to hack it a bit with a derived table, AKA an implicit temporary table, AKA a "subquery in the from clause."

We derive a table we'll call `t` containing each distinct (server,software) from gocore, then left join to gocore twice, once on tag = 'old' and once on tag = 'new'.

SELECT t.server, t.software, o.revision AS old_rev, n.revision AS new_rev
  FROM (SELECT DISTINCT server, software FROM gocore) t
  LEFT JOIN gocore o ON o.server = t.server AND o.software = t.software AND o.tag = 'old'
  LEFT JOIN gocore n ON n.server = t.server AND n.software = t.software AND n.tag = 'new';

Best Answer

Related Solutions

Mysql – Is index on foreign key used? (No difference in EXPLAIN.)

Mysql – Query to compare two subsets of data from the same table

Related Question