关于已知经纬度查找周围两公里的用户

jovien 2017-08-14 09:40:19
已知user表，有uid,name,lon,lat,online(bit,0离线，1在线)移动端心跳包发过来的经纬度，一分钟更新一次。

现在需要用一个存储过程，用当前发过来的@lon,@lat 作为参数找出周围2公里在线的用户，返回一个TABLE。

要求效率高一点，有可能在线用户有几万。


跪求各路大神
...全文
341 10 打赏 收藏 转发到动态 举报
写回复
用AI写文章
10 条回复
切换为时间正序
请发表友善的回复…
发表回复
jovien 2017-08-15
  • 打赏
  • 举报
 回复
引用 9 楼 zengertao 的回复:
[quote=引用 6 楼 jovien 的回复:] [quote=引用 3 楼 zengertao 的回复:] [quote=引用 2 楼 jovien 的回复:] [quote=引用 1 楼 sinat_28984567 的回复:] 试试这个： 
CREATE PROC dbo.getDistance
    @lon decimal(10,6),
    @lat  decimal(10,6)
AS
    BEGIN
        SELECT  *
        FROM    [User]
        WHERE   (Geography::Point(@lat, @lon, 4326) .STDistance(( Geography::Point(lat,
                                                              lon, 4326) ))
                / 1000) < 2
                AND [online] = 1;
    END;
GO


SELECT  uid,name,lon,lat,(Geography::Point(30.606311,114.253424,4326) .STDistance(( Geography::Point(lat,lon, 4326) ))/ 1000) as jl
        FROM    [users]
        WHERE   [online] = 1 and (Geography::Point(30.606311,114.253424,4326) .STDistance(( Geography::Point(lat,lon, 4326) ))/ 1000)<2 order by jl desc;
如果我同时把距离拉出来，像我这样计算好像不行，距离计算了两次，有什么办法可以避免吗？ 我测试了一下，从10W行里检索到7W行，你的方法只需要1.4秒，而我这样增加一列需要2秒，消耗增加不少啊。 多谢大神指导。 [/quote] 
CREATE PROC dbo.getDistance
    @lon decimal(10,6),
    @lat  decimal(10,6)
AS
    BEGIN
    	SELECT uid,name,lon,lat,c1 FROM (
			SELECT  uid,name,lon,lat,(Geography::Point(@lat, @lon, 4326) .STDistance(( Geography::Point(lat,lon, 4326) ))/ 1000) AS c1
			FROM    [User]
			WHERE   [online] = 1
    	) tt
    	WHERE tt.c1<2
    END;
GO
你可以尝试再套一层，当然如果原始数据量比过滤后大很多的话，肯定还是你2#的计算2次更效率。这样做只是纯粹少计算一次罢了。[/quote] 套一层，我试了下，实际差距不大，都还是稳定在2秒左右[/quote] 这样我建议如果需要查询出来这个字段的话，采用你自己2#的计算2次语句。[/quote] 是的，我也是准备这么处理，实际生产数据WHERE过滤之后已经所剩无几了
繁花尽流年 2017-08-15
  • 打赏
  • 举报
 回复
引用 6 楼 jovien 的回复:
[quote=引用 3 楼 zengertao 的回复:] [quote=引用 2 楼 jovien 的回复:] [quote=引用 1 楼 sinat_28984567 的回复:] 试试这个： 
CREATE PROC dbo.getDistance
    @lon decimal(10,6),
    @lat  decimal(10,6)
AS
    BEGIN
        SELECT  *
        FROM    [User]
        WHERE   (Geography::Point(@lat, @lon, 4326) .STDistance(( Geography::Point(lat,
                                                              lon, 4326) ))
                / 1000) < 2
                AND [online] = 1;
    END;
GO


SELECT  uid,name,lon,lat,(Geography::Point(30.606311,114.253424,4326) .STDistance(( Geography::Point(lat,lon, 4326) ))/ 1000) as jl
        FROM    [users]
        WHERE   [online] = 1 and (Geography::Point(30.606311,114.253424,4326) .STDistance(( Geography::Point(lat,lon, 4326) ))/ 1000)<2 order by jl desc;
如果我同时把距离拉出来，像我这样计算好像不行，距离计算了两次，有什么办法可以避免吗？ 我测试了一下，从10W行里检索到7W行，你的方法只需要1.4秒，而我这样增加一列需要2秒，消耗增加不少啊。 多谢大神指导。 [/quote] 
CREATE PROC dbo.getDistance
    @lon decimal(10,6),
    @lat  decimal(10,6)
AS
    BEGIN
    	SELECT uid,name,lon,lat,c1 FROM (
			SELECT  uid,name,lon,lat,(Geography::Point(@lat, @lon, 4326) .STDistance(( Geography::Point(lat,lon, 4326) ))/ 1000) AS c1
			FROM    [User]
			WHERE   [online] = 1
    	) tt
    	WHERE tt.c1<2
    END;
GO
你可以尝试再套一层，当然如果原始数据量比过滤后大很多的话，肯定还是你2#的计算2次更效率。这样做只是纯粹少计算一次罢了。[/quote] 套一层，我试了下，实际差距不大，都还是稳定在2秒左右[/quote] 这样我建议如果需要查询出来这个字段的话，采用你自己2#的计算2次语句。
jovien 2017-08-15
  • 打赏
  • 举报
 回复
引用 7 楼 sinat_28984567 的回复:
[quote=引用 5 楼 jovien 的回复:] @sinat_28984567 你的意思是，其实这个计算本身是没有多大消耗的是吧，多计算一次比多筛选一次来得更快。
不是，where筛选可以过滤掉很多数据的，能先通过where 、on 等过滤的要先这样过滤……[/quote] 明白了，WHERE 里先过滤掉大部分数据之后，别名列再计算其实量就已经相当少了，正常的生产数据，可能WHERE过滤完之后就只剩下几十或几百条而已
二月十六 2017-08-15
  • 打赏
  • 举报
 回复
引用 5 楼 jovien 的回复:
@sinat_28984567 你的意思是，其实这个计算本身是没有多大消耗的是吧，多计算一次比多筛选一次来得更快。
不是，where筛选可以过滤掉很多数据的，能先通过where 、on 等过滤的要先这样过滤……
jovien 2017-08-15
  • 打赏
  • 举报
 回复
引用 3 楼 zengertao 的回复:
[quote=引用 2 楼 jovien 的回复:] [quote=引用 1 楼 sinat_28984567 的回复:] 试试这个： 
CREATE PROC dbo.getDistance
    @lon decimal(10,6),
    @lat  decimal(10,6)
AS
    BEGIN
        SELECT  *
        FROM    [User]
        WHERE   (Geography::Point(@lat, @lon, 4326) .STDistance(( Geography::Point(lat,
                                                              lon, 4326) ))
                / 1000) < 2
                AND [online] = 1;
    END;
GO


SELECT  uid,name,lon,lat,(Geography::Point(30.606311,114.253424,4326) .STDistance(( Geography::Point(lat,lon, 4326) ))/ 1000) as jl
        FROM    [users]
        WHERE   [online] = 1 and (Geography::Point(30.606311,114.253424,4326) .STDistance(( Geography::Point(lat,lon, 4326) ))/ 1000)<2 order by jl desc;
如果我同时把距离拉出来，像我这样计算好像不行，距离计算了两次，有什么办法可以避免吗？ 我测试了一下，从10W行里检索到7W行，你的方法只需要1.4秒，而我这样增加一列需要2秒，消耗增加不少啊。 多谢大神指导。 [/quote] 
CREATE PROC dbo.getDistance
    @lon decimal(10,6),
    @lat  decimal(10,6)
AS
    BEGIN
    	SELECT uid,name,lon,lat,c1 FROM (
			SELECT  uid,name,lon,lat,(Geography::Point(@lat, @lon, 4326) .STDistance(( Geography::Point(lat,lon, 4326) ))/ 1000) AS c1
			FROM    [User]
			WHERE   [online] = 1
    	) tt
    	WHERE tt.c1<2
    END;
GO
你可以尝试再套一层，当然如果原始数据量比过滤后大很多的话，肯定还是你2#的计算2次更效率。这样做只是纯粹少计算一次罢了。[/quote] 套一层，我试了下，实际差距不大，都还是稳定在2秒左右
jovien 2017-08-15
  • 打赏
  • 举报
 回复
@sinat_28984567 你的意思是，其实这个计算本身是没有多大消耗的是吧，多计算一次比多筛选一次来得更快。
二月十六 2017-08-15
  • 打赏
  • 举报
 回复
引用 2 楼 jovien 的回复:
[quote=引用 1 楼 sinat_28984567 的回复:] 试试这个： 
CREATE PROC dbo.getDistance
    @lon decimal(10,6),
    @lat  decimal(10,6)
AS
    BEGIN
        SELECT  *
        FROM    [User]
        WHERE   (Geography::Point(@lat, @lon, 4326) .STDistance(( Geography::Point(lat,
                                                              lon, 4326) ))
                / 1000) < 2
                AND [online] = 1;
    END;
GO


SELECT  uid,name,lon,lat,(Geography::Point(30.606311,114.253424,4326) .STDistance(( Geography::Point(lat,lon, 4326) ))/ 1000) as jl
        FROM    [users]
        WHERE   [online] = 1 and (Geography::Point(30.606311,114.253424,4326) .STDistance(( Geography::Point(lat,lon, 4326) ))/ 1000)<2 order by jl desc;
如果我同时把距离拉出来，像我这样计算好像不行，距离计算了两次，有什么办法可以避免吗？ 我测试了一下，从10W行里检索到7W行，你的方法只需要1.4秒，而我这样增加一列需要2秒，消耗增加不少啊。 多谢大神指导。 [/quote] 没办法吧……sql会先执行where，再执行select，所以select的里边的别名计算列，在where中无法使用
繁花尽流年 2017-08-15
  • 打赏
  • 举报
 回复
引用 2 楼 jovien 的回复:
[quote=引用 1 楼 sinat_28984567 的回复:] 试试这个： 
CREATE PROC dbo.getDistance
    @lon decimal(10,6),
    @lat  decimal(10,6)
AS
    BEGIN
        SELECT  *
        FROM    [User]
        WHERE   (Geography::Point(@lat, @lon, 4326) .STDistance(( Geography::Point(lat,
                                                              lon, 4326) ))
                / 1000) < 2
                AND [online] = 1;
    END;
GO


SELECT  uid,name,lon,lat,(Geography::Point(30.606311,114.253424,4326) .STDistance(( Geography::Point(lat,lon, 4326) ))/ 1000) as jl
        FROM    [users]
        WHERE   [online] = 1 and (Geography::Point(30.606311,114.253424,4326) .STDistance(( Geography::Point(lat,lon, 4326) ))/ 1000)<2 order by jl desc;
如果我同时把距离拉出来，像我这样计算好像不行，距离计算了两次，有什么办法可以避免吗？ 我测试了一下，从10W行里检索到7W行，你的方法只需要1.4秒，而我这样增加一列需要2秒，消耗增加不少啊。 多谢大神指导。 [/quote] 
CREATE PROC dbo.getDistance
    @lon decimal(10,6),
    @lat  decimal(10,6)
AS
    BEGIN
    	SELECT uid,name,lon,lat,c1 FROM (
			SELECT  uid,name,lon,lat,(Geography::Point(@lat, @lon, 4326) .STDistance(( Geography::Point(lat,lon, 4326) ))/ 1000) AS c1
			FROM    [User]
			WHERE   [online] = 1
    	) tt
    	WHERE tt.c1<2
    END;
GO
你可以尝试再套一层，当然如果原始数据量比过滤后大很多的话，肯定还是你2#的计算2次更效率。这样做只是纯粹少计算一次罢了。
jovien 2017-08-15
  • 打赏
  • 举报
 回复
引用 1 楼 sinat_28984567 的回复:
试试这个： 
CREATE PROC dbo.getDistance
    @lon decimal(10,6),
    @lat  decimal(10,6)
AS
    BEGIN
        SELECT  *
        FROM    [User]
        WHERE   (Geography::Point(@lat, @lon, 4326) .STDistance(( Geography::Point(lat,
                                                              lon, 4326) ))
                / 1000) < 2
                AND [online] = 1;
    END;
GO


SELECT  uid,name,lon,lat,(Geography::Point(30.606311,114.253424,4326) .STDistance(( Geography::Point(lat,lon, 4326) ))/ 1000) as jl
        FROM    [users]
        WHERE   [online] = 1 and (Geography::Point(30.606311,114.253424,4326) .STDistance(( Geography::Point(lat,lon, 4326) ))/ 1000)<2 order by jl desc;
如果我同时把距离拉出来，像我这样计算好像不行，距离计算了两次，有什么办法可以避免吗？ 我测试了一下，从10W行里检索到7W行，你的方法只需要1.4秒，而我这样增加一列需要2秒，消耗增加不少啊。 多谢大神指导。
二月十六 2017-08-14
  • 打赏
  • 举报
 回复
试试这个：
CREATE PROC dbo.getDistance

    @lon decimal(10,6),

    @lat  decimal(10,6)

AS

    BEGIN

        SELECT  *

        FROM    [User]

        WHERE   (Geography::Point(@lat, @lon, 4326) .STDistance(( Geography::Point(lat,

                                                              lon, 4326) ))

                / 1000) < 2

                AND [online] = 1;

    END;

GO
根据当前坐标(经纬度)计算附近位置-java版本
根据已知点坐标计算附近位置，代码详细解释了三种计算策略。主要涉及日常需要解决问题，例如类似于滴滴打车实现查找附近车辆业务逻辑和贝壳找房根据指定位置筛选出附近房源，上述两场场景应用可借鉴代码中两种方法予以解决，当然如果技术允许可以参照使用mongoDb数据库，内置了geohash算法。
给定经纬度，计算附近多少公里范围内的地点
场景：当时我们项目用的是spring-data-jpa, 用hql实现的话，实在是不知道怎么搞，所以想出这么个方法。这样做的就是避免直接在数据查询语句中去计算该点附近多少公里范围内的点，将sql语句的实现转成了代码实现。   第一步：根据给定某点经纬度，给定距离数，计算出该点附近相应距离范围的所有的点 /** * 查找附近的门店 * @param longi...
安装和配置 Windows Server 2022 更新服务
微软对于自己开发的产品支持方式，其中一种就是开发补丁程序，用于修复微软产品的已知问题，比如功能缺陷、安全漏洞、逻辑错误等，这些缺陷一般来自于客户反馈、安全研究人员和团队并且微软会维护着一个在线服务，Microsoft 更新服务平台，用于分发补丁程序，方便用户和 IT 管理人员理解每一个补丁程序的作用，并且允许用户和 IT 管理人员轻松地获取到这些补丁程序，下载并且安装到相关的设备当中每一台 Windows 设备，包括 Windows 客户端和服务器系统，都集成了一个 Windows Update 服务，用于联机到 Microsoft 在线更新服务平台，定期查询适用于当前设备的补丁更新程序，并且下载和安装这些更新程序，以确保 Windows 设备能够安全、稳定和可靠的运行而 Windows 更新服务角色，是 Windows Server 2022 系统当中的一个可选角色，一方面它可以从 Microsoft 更新服务平台联机地同步最新的补丁描述和安装文件，另外的一方面将这些补丁分发给企业当中的设备，管理员可以借助于 Windows 更新服务，集中地审批和分发补丁，了解网络当中设备更新的状态，并且在需要的时候，也可以实现补丁的自动分发和状态报告在我们的这一个课程当中，我们将详细地为大家去 Windows 更新服务的安装和配置过程
根据自身所在经纬度，查询周围一定范围内的地理位置(经纬度)
(注：知识浩瀚，本人能力有限，定有不足，往各位多多指点，不足之处定要指出，俺好随时改进，小的无以为报，在此鞠躬致谢…) 根据自身经纬度，查询周围一定范围内的地理位置(经纬度)，这在很多需要定位的项目中经常遇到。下面是我在项目中遇到后，各种上网搜索后，然后自己整理的： /** * 获取附近dis千米内的站点 * @param lon [经度] *
用一句 Mysql 语句，根据经纬度，计算5公里范围内的数据
经常会有这样的需求，已知用户具体的经纬度，要查询出距离用户最近 X 公里内的商家下面给出一条SQL语句来解决这个问题ROUND(6378.138*2*ASIN(SQRT(POW(SIN(($latitude*PI()/180-latitude*PI()/180)/2),2)+COS($latitude*PI()/180)*COS(latitude*PI()/180)...
应用实例

27,580

社区成员

68,556

社区内容

发帖
与我相关
我的任务
社区描述
MS-SQL Server 应用实例
社区管理员
  • 应用实例社区
加入社区
  • 近7日
  • 近30日
  • 至今

加载中

查看更多榜单
社区公告
暂无公告

试试用AI创作助手写篇文章吧

+ 用AI写文章