MYSQL 经纬度地理位置

2024-07-09 05:49| 来源: 网络整理| 查看: 265

mysql支持经纬度计算需要使用的函数：ST_Distance_Sphere

需要至少5.7的版本！

在这里插入图片描述

关于经纬的设计：

1）使用两列，一列经度，一列纬度

如：

-- 建表 CREATE TABLE `store` ( `id` bigint(20) NOT NULL AUTO_INCREMENT, `name` varchar(32) NOT NULL, `item_id` bigint(20) NOT NULL DEFAULT '0', `longitude` double(50,6) NOT NULL COMMENT '经度', `latitude` double(50,6) NOT NULL COMMENT '纬度', PRIMARY KEY (`id`) )

2）使用内置geometry类型

如：

create table t_geo_test ( ID int auto_increment primary key, NAME varchar(64) not null, SHAPE geometry not null ) engine = InnoDB charset = utf8;

测试：

分别插入数据：

1）

INSERT INTO `store` (`id`, `name`, `item_id`, `longitude`, `latitude`) VALUES (3, '欧美金融城_星爸爸', 1, 120.011496, 30.287637), (4, '永乐城_星爸爸', 1, 120.011802, 30.280433), (5, '小镇_一鸣', 2, 120.011209, 30.298552), (6, '赛银国际', 2, 120.027181, 30.280808), (7, '合景天峻_星爸爸', 1, 120.004597, 30.291660), (8, '仓溢东苑_星爸爸', 1, 120.008622, 30.292783);

2）

INSERT INTO t_geo_test VALUES (1, '测试', POINT(121.590347, 31.388094)); INSERT INTO t_geo_test VALUES (2, 'somewhere', ST_GeomFromText('POINT(121.366961 31.190049)'));

执行SQL:

1）查询5公里内门店权益id

select shop.* from ( select * ,round(st_distance_sphere(point(120.012484,30.298926),point(`longitude`,`latitude`))) dis from store having dis < 5000 order by dis limit 100000 ) shop group by shop.item_id order by dis limit 10

2）计算距离

SELECT st_distance_sphere(POINT(121.590347, 31.388094),SHAPE) AS distant FROM t_geo_test;

查询到某点的距离小于一个值的所有数据：

SELECT *, ST_Distance_Sphere(POINT(121.590347, 31.388094),SHAPE) AS distant FROM t_geo_test WHERE ST_Distance_Sphere(POINT(121.590347, 31.388094),SHAPE) < 1000 ORDER BY distant;

注意：坐标不能随意写测试数据，注意官网中的说的条件：

几何参数应由指定（经度，纬度）坐标值的点组成：经度和纬度分别是该点的第一和第二坐标。两个坐标均以度为单位。经度值必须在（-180，180]范围内。正值位于本初子午线以东。纬度值必须在[-90，90]范围内。正值位于赤道以北。否则则会报错：

[HY000][1210] Incorrect arguments to st_distance_sphere

关于Mybatis对于geometry类型的使用

数据库字段类型：geometry

引入geo工具包 com.vividsolutions 版本1.13

方式1 ：使用mysql函数 ST_AsText: 可将mysql的geometry类型转换成 string类型， ST_GeometryFromText : 将wkt描述的字符串转换为 mysql中geometry类型，这种方法简单直接，但需编写原生sql，不支持mybatis insert update之类的方法。

参考：https://blog.csdn.net/ununie/article/details/98651568

方式2 ：使用mybatis自定义类型转换器主要代码

@TableField(typeHandler = GeometryTypeHandler.class) private String coordinate; @MappedTypes({String.class}) @MappedJdbcTypes({JdbcType.OTHER}) public class GeometryTypeHandler extends BaseTypeHandler { @Override public void setNonNullParameter(PreparedStatement preparedStatement, int i, String s, JdbcType jdbcType) throws SQLException { Geometry geo = null; try{ //String转Geometry geo = new WKTReader(new GeometryFactory(new PrecisionModel())).read(s); // Geometry转WKB byte[] geometryBytes = new WKBWriter(2, ByteOrderValues.LITTLE_ENDIAN, false).write(geo); // 设置SRID为mysql默认的 0 byte[] wkb = new byte[geometryBytes.length+4]; wkb[0] = wkb[1] = wkb[2] = wkb[3] = 0; System.arraycopy(geometryBytes, 0, wkb, 4, geometryBytes.length); preparedStatement.setBytes(i,wkb); }catch (ParseException e){ } } } @Override public String getNullableResult(ResultSet resultSet, String s){ try(InputStream inputStream = resultSet.getBinaryStream(s)){ Geometry geo = getGeometryFromInputStream(inputStream); if(geo != null){ return geo.toString(); } }catch(Exception e){ } return null; } @Override public String getNullableResult(ResultSet resultSet, int i){ try(InputStream inputStream = resultSet.getBinaryStream(i)){ Geometry geo = getGeometryFromInputStream(inputStream); if(geo != null){ return geo.toString(); } }catch(Exception e){ } return null; } @Override public String getNullableResult(CallableStatement callableStatement, int i) throws SQLException { return ""; } /** * 流转 geometry * */ private Geometry getGeometryFromInputStream(InputStream inputStream) throws Exception { Geometry dbGeometry = null; if (inputStream != null) { // 二进制流转成字节数组 byte[] buffer = new byte[255]; int bytesRead = 0; ByteArrayOutputStream baos = new ByteArrayOutputStream(); while ((bytesRead = inputStream.read(buffer)) != -1) { baos.write(buffer, 0, bytesRead); } // 得到字节数组 byte[] geometryAsBytes = baos.toByteArray(); // 字节数组小于5 异常 if (geometryAsBytes.length < 5) { throw new PlatformAlertException("坐标异常"); } //字节数组前4个字节表示srid 去掉 byte[] sridBytes = new byte[4]; System.arraycopy(geometryAsBytes, 0, sridBytes, 0, 4); boolean bigEndian = (geometryAsBytes[4] == 0x00); // 解析srid int srid = 0; if (bigEndian) { for (int i = 0; i < sridBytes.length; i++) { srid = (srid

【本文地址】

公司简介

联系我们

MYSQL 经纬度 地理位置

MYSQL 经纬度地理位置