Mysql实现附近的人,距离排序

常用的函数

ST_GEOMFROMTEXT
用于将空间数据从可读的文本类型转换成内部存储的二进制类型

ST_ASTEXT
将空间数据转换成可读的文本类型

mysql> SELECT ST_ASTEXT(ST_GEOMFROMTEXT("POINT(1 2)"));
+------------------------------------------+
| ST_ASTEXT(ST_GEOMFROMTEXT("POINT(1 2)")) |
+------------------------------------------+
| POINT(1 2)                               |
+------------------------------------------+
1 row in set (0.00 sec)

POINT

POINT(arg1, arg2)函数用于代表地理空间上的某个点的位置，arg1为经度，arg2为纬度

• 两个坐标都用角度表示
• 经度值的范围为(-180, 180)，正数表示本初子午线的东边
• 纬度值的范围为[-90, 90]。整数表示赤道的北边。

SELECT `NAME`,POINT(lng,lat)
FROM `table_name`
limit 20000

ST_DISTANCE_SPHERE

可以使用函数st_distance_sphere来计算两个地点的球面距离：

mysql> select st_distance_sphere(point(-78.7698947, 35.890334), point(122.38657, 37.60954)) as distance;
+--------------------+
| distance           |
+--------------------+
| 11556620.032685472 |
+--------------------+
1 row in set (0.00 sec)

ST_WITHIN
一个常见的应用需求是找出你周围的目的地（例如餐馆），可以使用该函数。
ST_WITHIN(g1, g2)： 如果g1在g2的范围内，则返回1，否则返回0 

mysql> set @g1 =  ST_GeomFromText('POINT(1.558064 110.341903)');
Query OK, 0 rows affected (0.00 sec)

mysql> set @g2 = ST_GeomFromText('POLYGON((1.558467 110.341781, 1.558081 110.342317, 1.557764 110.34175, 1.558467 110.341781 ))');
Query OK, 0 rows affected (0.00 sec)

mysql> select st_within(@g1, @g2);
+---------------------+
| st_within(@g1, @g2) |
+---------------------+
|                   1 |
+---------------------+
1 row in set (0.00 sec)

ST_CONTAINS是ST_WITHIN的反向操作，表示某个多边形内是否包含某个点。

mysql> select st_contains(@g2, @g1);
+-----------------------+
| st_contains(@g2, @g1) |
+-----------------------+
|                     1 |
+-----------------------+
1 row in set (0.00 sec)

GeoHash

Geohash用于将代表位置的经纬度编码成一个字符串，支持WGS 84 Coordinate System

mysql> SELECT ST_GeoHash(90,90,10);
+----------------------+
| ST_GeoHash(90,90,10) |
+----------------------+
| ypbpbpbpbp           |
+----------------------+
1 row in set (0.00 sec)

 

3. 求出附近的公司，我们在表里面根据GeoHash生成了 如下的字符串，并在此上建立了索引，

| wmpfefhv8f |
| wmr0qdv0kk |
| wmrj6uxqjm |
| wmrj6uxqjm |
| wmrj6uxqjm |
| wmrj6uxqjm |
| wmrj6uxqjm |
| wmrj6uxqjm |
| wmrj6uxqjm |
| wmrj6uxqjm |
| wmrj6uxqjm |
| wmrj6uxqjm |
| wmrj6uxqjm |
| wmrj6uxqjm |
| wmrj6uxqjm |
| wmrj6uxqjm |
| wmrj6uxqjm |
| wmrj6uxqjm |
| wmrj6uxqjm |
| wmrj6uxqjm |
| wmrj6uxqjm |
| wmrj6uxqjm |
| wmrj6uxqjm |
| wmzm91e6zn |
| wq3xtt0jp0 |
| wq66kw5mjh |
| wqg80u1t5m |
| wqh5qnw9ut |
| wqhkkje9vq |
| wqhkkje9vq |
| wqj3yhmk8r |
| wqj6zvv3tj |
| wqj6zvv3tj |
| wqj6zvv3tj |
| wqj6zvv3tj |
| wqj6zvv3tj |
| wqr4wqn4g1 |
| wqr4wy2sy4 |
| wqrf4muxd3 |
| wqrgny7kdt |
| wqxh6rmzt5 |
| wqxj3qsvvg |
| wrkbjw86gs |
| wrkbjw86gs |
| wrkbjw86gs |
+------------+

geohash like concat(left((select DISTINCT geohash from company_address where EID =  #{eid} ),7),'%') 

这个是存在误差的

geohash长度	可以获得的距离
1	±2500
2	±630
3	±78
4	±20
5	±2.4
6	±0.61
7	±0.076
8	±0.019

 

sql

		SELECT distinct *
		FROM (
		SELECT `NAME`,EID,QYZT,floor(ST_DISTANCE_SPHERE((SELECT DISTINCT location FROM company_address WHERE EID = #{eid}),location)) distance
		FROM
		company_address
		WHERE
		geohash like concat(left((select DISTINCT geohash from company_address where EID =  #{eid} ),7),'%')
		AND ST_DISTANCE_SPHERE((SELECT DISTINCT location FROM company_address WHERE `EID` =  #{eid} ),location)   <= 100
		AND `EID` != #{eid}
		<if test="absEids != null and absEids.size()>0">
			AND `EID` not in
			<foreach item="item" index="index" collection="absEids" open="("  close=")" separator=",">
				#{item}
			</foreach>
		</if>
		) as a
		ORDER BY a.distance