MYSQL 经纬度地理位置

男娘i 2022-11-04 11:16 340阅读 0赞

mysql支持经纬度计算需要使用的函数：ST\_Distance\_Sphere

### 需要至少**5.7**的版本！ ###

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzE5Njc0MjYz_size_16_color_FFFFFF_t_70]

关于经纬的设计：

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

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzIxNDU0OTcz_size_16_color_FFFFFF_t_70][]

2）计算距离

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

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzIxNDU0OTcz_size_16_color_FFFFFF_t_70 1][]

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

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;

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzIxNDU0OTcz_size_16_color_FFFFFF_t_70 2][]

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

几何参数应由指定（经度，纬度）坐标值的点组成：  
经度和纬度分别是该点的第一和第二坐标。  
两个坐标均以度为单位。  
经度值必须在（-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][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<String> {
     
     
    	@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 << 8) + (sridBytes[i] & 0xff);
    				}
    			} else {
    				for (int i = 0; i < sridBytes.length; i++) {
    					srid += (sridBytes[i] & 0xff) << (8 * i);
    				}
    			}
     
    			WKBReader wkbReader = new WKBReader();
    			// WKBReader 把字节数组转成geometry对象。
    			byte[] wkb = new byte[geometryAsBytes.length - 4];
    			System.arraycopy(geometryAsBytes, 4, wkb, 0, wkb.length);
    			dbGeometry = wkbReader.read(wkb);
    			dbGeometry.setSRID(srid);
    		}
    		return dbGeometry;
    	}
    }

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzE5Njc0MjYz_size_16_color_FFFFFF_t_70]: /images/20221023/1ee7d57da8034bd9ae857a302f2ec7e1.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzIxNDU0OTcz_size_16_color_FFFFFF_t_70]: /images/20221023/1313cad4a5964befadf4b9edd90c4749.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzIxNDU0OTcz_size_16_color_FFFFFF_t_70 1]: /images/20221023/0d8ec5434b3f494f87b9df22c1a8efe3.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzIxNDU0OTcz_size_16_color_FFFFFF_t_70 2]: /images/20221023/38a4e817c5944dc0801f7079c13c6812.png
[https_blog.csdn.net_ununie_article_details_98651568]: https://blog.csdn.net/ununie/article/details/98651568