mysql

作者/来源：yixinu.com

栏目：运维/编程

日期：2015-10-15 20:37:34

SELECT `villagename`,`loglat`,substring_index(`loglat`, ',' ,1 ) as log , substring_index( `loglat` , ',' ,-1 ) as lat FROM `clo_village`

villagename小区名称	loglat经纬度	log	lat
华盛外滩999z	112.991345,28.208682	112.991345	28.208682
天马小区	113.04161,28.05843	113.04161	28.05843
广福园1-10栋	113.02235,28.148159	113.02235	28.148159
广福园11-20栋	113.027812,28.065826	113.027812	28.065826
金牛小区	113.02695,28.056645	113.02695	28.056645
滨江美寓	112.950198,28.125734	112.950198	28.125734

SELECT `villagename`,`loglat`,substring_index(`loglat`, ',' ,1 ) as lng , substring_index( `loglat` , ',' ,-1 ) as lat , (POWER(MOD(ABS(substring_index(`loglat`, ',' ,1 ) - '112.991345'),360),2) + POWER(ABS(substring_index( `loglat` , ',' ,-1 ) - '28.208682'),2)) AS distance FROM `clo_village` ORDER BY distance