SELECT id,
CONCAT('point(',
conv(
CONCAT(mid(cast(hex(data) as char),7, 2),
mid(cast(hex(data) as char),5, 2),
mid(cast(hex(data) as char),3, 2),
mid(cast(hex(data) as char),1, 2)),
16,10)/1000000,
',',
conv(
CONCAT(mid(cast(hex(data) as char),15, 2),
mid(cast(hex(data) as char),13, 2),
mid(cast(hex(data) as char),11, 2),
mid(cast(hex(data) as char),9, 2)),16,10)/1000000,
')') AS point from m_position WHERE id = 123
结果示例:
123, point(115.149722,5.005278)
有负数经纬度的处理
if(conv( mid(cast(hex(data) as char),7, 1),16,10) < 8,
conv(CONCAT(mid(cast(hex(data) as char),7, 2), mid(cast(hex(data) as char),5, 2), mid(cast(hex(data) as char),3, 2), mid(cast(hex(data) as char),1, 2)),16,10) ,
-(-conv(CONCAT(mid(cast(hex(data) as char),7, 2), mid(cast(hex(data) as char),5, 2), mid(cast(hex(data) as char),3, 2), mid(cast(hex(data) as char),1, 2)), 16,10)&0x7fffffff)
) as lon,
if(conv( mid(cast(hex(data) as char),15, 1),16,10) < 8,
conv(CONCAT(mid(cast(hex(data) as char),15, 2), mid(cast(hex(data) as char),13, 2), mid(cast(hex(data) as char),11, 2), mid(cast(hex(data) as char),9, 2)),16,10),
-(-conv(CONCAT(mid(cast(hex(data) as char),15, 2), mid(cast(hex(data) as char),13, 2), mid(cast(hex(data) as char),11, 2), mid(cast(hex(data) as char),9, 2)), 16,10)&0x7fffffff)
)as lat