SQL Server 有个字段类型为 geography，这个类型存经纬度，就可以利用经纬度排序了。

(注意，还有一个近似类型：geometry，这个是用来计算二维平面的，不适合经纬度)

新建表

新建一张citys表

USE [test]
GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[citys](
	[Id] [int] IDENTITY(1,1) NOT NULL,
	[cityName] [nvarchar](50) NOT NULL,
	[geo] [geography] NOT NULL,
 CONSTRAINT [PK_citys] PRIMARY KEY CLUSTERED 
(
	[Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

asp.netcore跨域设置,asp.net操作sqlserver数据库

表字段

插入数据

insert into citys(cityName, geo) values('北京', geography::STPointFromText('POINT (116.555963 39.711394)', 4326));
insert into citys(cityName, geo) values('上海', geography::STPointFromText('POINT (121.560028 31.175737)', 4326));
insert into citys(cityName, geo) values('广州', geography::STPointFromText('POINT (113.465217 23.087146)', 4326));
insert into citys(cityName, geo) values('深圳', geography::STPointFromText('POINT (114.090725 22.507165)', 4326));
insert into citys(cityName, geo) values('成都', geography::STPointFromText('POINT (104.137788 30.524926)', 4326));
insert into citys(cityName, geo) values('杭州', geography::STPointFromText('POINT (120.382601 30.157892)', 4326));
insert into citys(cityName, geo) values('重庆', geography::STPointFromText('POINT (106.603025 29.532371)', 4326));
insert into citys(cityName, geo) values('武汉', geography::STPointFromText('POINT (114.403479 30.556776)', 4326));

利用的是 geography::STPointFromText()。

第一个参数 POINT() 里面再跟经度、纬度。

第二个参数 4326 代表 GCS-WGS-1984 坐标系。

插入完了。我们看下数据

asp.netcore跨域设置,asp.net操作sqlserver数据库

全部数据

查询数据

declare @currentLocation geography

select @currentLocation = geography::STPointFromText('POINT (119.627438 29.078988)', 4326)

select *, geo.STDistance(@currentLocation) as 距离 from citys

where geo.STDistance(@currentLocation) < 1000000

order by geo.STDistance(@currentLocation)

119.627438 29.078988 是我当前的坐标，金华

查询结果

asp.netcore跨域设置,asp.net操作sqlserver数据库

查询结果

关键词总结

geography::STPointFromText 函数

geo.STDistance(@currentLocation) 函数用作排序，有近到远

返回的距离单位是米，实际使用的话，换算公里的话自己程序中解决

根据上面的例子，我写了个分页存储过程，方便程序中调用

代码如下

USE [test]
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROC [dbo].[GetCitysPager]
(
	@mylon varchar(30), --我的经度
	@mylat varchar(30),  --我的纬度
	@maxDistance int,  --筛选的最大距离,米
	@pageIndex int = 1, --默认1
	@pageSize int = 10, --默认10
	@totalCount int OUTPUT --符合条件总数据量 输出参数　　
)
AS

if(@pageIndex<=0)
	set @pageIndex = 1

if(@pageSize<=0)
	set @pageSize = 10

declare @offset int
set @offset = (@pageIndex-1)*@pageSize

declare @fetch int
set @fetch = @pageSize

declare @currentLocation geography

select @currentLocation = geography::STPointFromText('POINT(' +@mylon+ ' ' + @mylat +')', 4326)

select *, geo.STDistance(@currentLocation) as Distance from citys where geo.STDistance(@currentLocation) < @maxDistance
order by geo.STDistance(@currentLocation)
OFFSET @offset ROWS
FETCH NEXT @fetch ROWS ONLY;

set @totalCount = (select count(*) from citys where geo.STDistance(@currentLocation) < @maxDistance)

执行存储过程代码：我输入的参数是我的经度纬度 119.627438 29.078988，要筛选的最大距离 1000000，由于分页条件pageIndex pageSize 在存储过程中有默认值，这里不用输入

USE [test]
GO

DECLARE	@return_value int,
		@totalCount int

EXEC	@return_value = [dbo].[GetCitysPager]
		@mylon = N'119.627438',
		@mylat = N'29.078988',
		@maxDistance = 1000000,
		@totalCount = @totalCount OUTPUT

SELECT	@totalCount as N'@totalCount'

执行查询结果，符合记录的第一页数据就出来了

asp.netcore跨域设置,asp.net操作sqlserver数据库

查询结果

我们用C#代码来实现上面的过程

这里我用freesql orm 来操作。小伙伴换其他的orm也行，无所谓的

插入地理位置

[HttpPost]
        public IActionResult Insert()
        {
            string Long = "119.458497";
            string Lat = "29.215102";
            //insert into citys(cityName, geo) values('北京', geography::STPointFromText('POINT (116.555963 39.711394)', 4326));
            //模仿上面的语句来写添加sql
            string cmdText = "insert into citys(cityName, geo) values(@cityName, geography::STPointFromText(@Location, 4326));";
            SqlParameter[] parameters = new SqlParameter[] {
                new SqlParameter("@cityName","兰溪市实验小学"),
                new SqlParameter("@Location","POINT("+ Long + " " + Lat + ")"),
            };

            using Object<DbConnection> conn = fsql.Ado.MasterPool.Get();
            using DbTransaction transaction = conn.Value.BeginTransaction();
            try
            {
                int num = fsql.Ado.ExecuteNonQuery(transaction, CommandType.Text, cmdText, parameters);
                transaction.Commit();
            }
            catch (Exception)
            {
                transaction.Rollback();  //记录日志，抛出异常（使用全局异常）或者返回自定义数据 
                throw;
            }

            return Ok();
        }

修改地理位置

[HttpPost]
        public IActionResult Update()
        {
            string Long = "119.757128";
            string Lat = "29.097946";
            //update citys set cityName = 'test', geo = geography::STPointFromText('POINT (116.555963 39.711394)', 4326) where id = 1;
            //模仿上面的语句来写修改sql
            string cmdText = "update citys set cityName = @cityName, geo = geography::STPointFromText(@Location, 4326) where id = @Id;";
            SqlParameter[] parameters = new SqlParameter[] {
                new SqlParameter("@Id",11),
                new SqlParameter("@cityName","东湖小学"),
                new SqlParameter("@Location","POINT("+ Long + " " + Lat + ")"),
            };

            using Object<DbConnection> conn = fsql.Ado.MasterPool.Get();
            using DbTransaction transaction = conn.Value.BeginTransaction();
            try
            {
                int num = fsql.Ado.ExecuteNonQuery(transaction, CommandType.Text, cmdText, parameters);
                transaction.Commit();
            }
            catch (Exception)
            {
                transaction.Rollback();
                throw;
            }

            return Ok();
        }

上面代码我没有去使用City实体，因为是用ADO.Net实现的缺点硬编码

查询存储过程

dto

public class CitysDto
    {
        public int id { get; set; }
        public string cityName { get; set; }
        public double Distance { get; set; }
    }

[HttpGet]
        public IActionResult GetCitysPager(int pageIndex, int pageSize)
        {
            //执行存储过程
            DbParameter? p2 = null;//输出参数
            var dtos = fsql.Ado.CommandFluent("GetCitysPager")
                 .CommandType(CommandType.StoredProcedure)
                 .CommandTimeout(60)
                 .WithParameter("mylon", "119.627438")
                 .WithParameter("mylat", "29.078988")
                 .WithParameter("maxDistance", 1000000)
                 .WithParameter("pageIndex", pageIndex)
                 .WithParameter("pageSize", pageSize)
                 .WithParameter("totalCount", null, p =>
                 {
                     p2 = p;
                     p.DbType = DbType.Int32;
                     p.Direction = ParameterDirection.Output;
                 }).Query<CitysDto>();
            return Ok(new { totalCount = p2!.Value, rows = dtos });
        }

查询结果

asp.netcore跨域设置,asp.net操作sqlserver数据库

存储过程查询结果

另外再来写个例子，更好的理解，其实也可以不用 geography 类型

先创业一张建筑的表 build

USE [test]
GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[build](
	[id] [int] IDENTITY(1,1) NOT NULL,
	[Name] [nvarchar](50) NOT NULL,
	[lng] [decimal](18, 6) NOT NULL,
	[lat] [decimal](18, 6) NOT NULL,
 CONSTRAINT [PK_distance_lat_lng] PRIMARY KEY CLUSTERED 
(
	[id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[build] ADD  CONSTRAINT [DF_distance_lat_lng_lng]  DEFAULT ((0)) FOR [lng]
GO

ALTER TABLE [dbo].[build] ADD  CONSTRAINT [DF_distance_lat_lng_lat]  DEFAULT ((0)) FOR [lat]
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'经度' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'build', @level2type=N'COLUMN',@level2name=N'lng'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'纬度' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'build', @level2type=N'COLUMN',@level2name=N'lat'
GO

上面用经度和维度 decimal(18, 6) 来代替geography 类型

准备数据

我用高德拾取了一些坐标数据，并且仓储在sqlserver中

高德坐标拾取器网站 https://lbs.amap.com/tools/picker

asp.netcore跨域设置,asp.net操作sqlserver数据库

数据库数据

看个简单的例子

查询2个坐标之间的距离，代码如下

DECLARE @g geography;
DECLARE @h geography;
SET @g = geography::STGeomFromText('POINT(119.458497 29.215102)', 4326); --兰溪市实验小学
SET @h = geography::STGeomFromText('POINT(119.461072 29.216169)', 4326); --兰溪市实验中学
SELECT @g.STDistance(@h);

asp.netcore跨域设置,asp.net操作sqlserver数据库

查询结果

查询结果276.908 注意单位是米

再从地图上看，也确实是查不多

asp.netcore跨域设置,asp.net操作sqlserver数据库

地图上2个学校的位置

新建一个“标量函数”

这个函数的作用就是计算2个点之间的距离

代码如下

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER FUNCTION [dbo].[fun_get_distance_pointA_pointB]
(
	@destination_lng varchar(30), -- @destination_lng 目标经度
	@destination_lat varchar(30), -- @destination_lat 目标纬度
	@source_lng varchar(30),-- @source_lng  源的经度
	@source_lat varchar(30) -- @source_lat  源的纬度
)
RETURNS FLOAT AS 

BEGIN
  declare @distance varchar(30);--@distance 距离 返回值
  declare @destinationLocation geography   --@destinationLocation 目标位置
  declare @sourceLocation geography;  --@sourceLocation 源的位置
  SET @destinationLocation = geography::STGeomFromText('POINT(' +@destination_lng+ ' ' + @destination_lat +')', 4326);
  SET @sourceLocation = geography::STGeomFromText('POINT(' + @source_lng+ ' ' + @source_lat+')', 4326);
  SET @distance = @destinationLocation.STDistance(@sourceLocation);
  return @distance
END

标量函数可以返回一个值

asp.netcore跨域设置,asp.net操作sqlserver数据库

标量函数位置

执行下标量函数，函数中传入2个坐标，

兰溪市实验小学

119.458497

29.215102

兰溪市实验中学

119.461072

29.216169

select dbo.fun_get_distance_pointA_pointB('119.458497','29.215102','119.461072','29.216169')

执行结果返回276.908米

DECLARE @g geography;

DECLARE @h geography;

SET @g = geography::STGeomFromText('POINT(119.458497 29.215102)', 4326); --兰溪市实验小学

SET @h = geography::STGeomFromText('POINT(119.461072 29.216169)', 4326); --兰溪市实验中学

SELECT @g.STDistance(@h);

和上面的代码执行是一致的

asp.netcore跨域设置,asp.net操作sqlserver数据库

标量函数执行结果

根据上面的执行原理，用标量函数来批量算距离

写一条sql，传入我的当前坐标城北安居苑21幢 119.481730 29.227700

SELECT *
FROM 
    (SELECT a.id,
        a.Name,
        a.lng,
        a.lat,
        dbo.fun_get_distance_pointA_pointB(cast(a.lng AS varchar(30)),
        cast(a.lat AS varchar(30)),
        '119.481730','29.227700') AS distance
    FROM build AS a) AS t
ORDER BY distance asc

查询结果

asp.netcore跨域设置,asp.net操作sqlserver数据库

查询结果

查询3000米内的数据

SELECT *
FROM 
    (SELECT a.id,
        a.Name,
        a.lng,
        a.lat,
        dbo.fun_get_distance_pointA_pointB(cast(a.lng AS varchar(30)),
        cast(a.lat AS varchar(30)),
        '119.481730','29.227700') AS distance
    FROM build AS a) AS t
WHERE distance<3000
ORDER BY distance asc

asp.netcore跨域设置,asp.net操作sqlserver数据库

查询结果

用C#代码查询下

DTO

public class BuildDto
    {
        public int id { get; set; }
        public string Name { get; set; }
        public decimal lng { get; set; }
        public decimal lat { get; set; }
        public double distance { get; set; }
    }

控制器方法

[HttpPost]
        public IActionResult GetList()
        {
            string Long = "119.481730";
            string Lat = "29.227700";
            float maxDistance = 3000f;
            string sql = @"SELECT *
                    FROM 
                        (SELECT a.id,
                            a.Name,
                            a.lng,
                            a.lat,
                            dbo.fun_get_distance_pointA_pointB(cast(a.lng AS varchar(30)),
                            cast(a.lat AS varchar(30)),@Long,@Lat) AS distance
                        FROM build AS a) AS t
                    WHERE distance<@maxDistance
                    ORDER BY distance asc";

            var parms = new { Long = Long, Lat = Lat, maxDistance = maxDistance };

            //Ado 下面所有参数 object parms 都可以接受匿名对象，或者字典
            /*
                new { id = 1, name = "xx" }
                new Dictionary<string, object> { ["id"] = 1, ["name"] = "xx" }
             */
            List<BuildDto> list = fsql.Ado.Query<BuildDto>(sql, parms);
            return Ok(list);
        }

asp.netcore跨域设置,asp.net操作sqlserver数据库

代码执行结果

分页

 [HttpGet]
        public IActionResult GetPagerList(int pageIndex, int pageSize)
        {
            if (pageIndex <= 0)
            {
                pageIndex = 1;
            }

            if (pageSize <= 0)
            {
                pageSize = 10;
            }

            string Long = "119.481730";
            string Lat = "29.227700";
            float maxDistance = 3000f;
            string sql = @"SELECT *
                    FROM 
                        (SELECT a.id,
                            a.Name,
                            a.lng,
                            a.lat,
                            dbo.fun_get_distance_pointA_pointB(cast(a.lng AS varchar(30)),
                            cast(a.lat AS varchar(30)),@Long,@Lat) AS distance
                        FROM build AS a) AS t
                    WHERE distance<@maxDistance
                    ORDER BY distance asc
                    OFFSET @offset ROWS
                    FETCH NEXT @fetch ROWS ONLY;"
            ;

            var parms = new { Long = Long, Lat = Lat, maxDistance = maxDistance, offset = (pageIndex - 1) * pageSize, fetch = pageSize };

            List<BuildDto> list = fsql.Ado.Query<BuildDto>(sql, parms);
            return Ok(list);
        }

pageIndx=1,pageSize=2

asp.netcore跨域设置,asp.net操作sqlserver数据库

第一页数据

pageIndx=2,pageSize=2

asp.netcore跨域设置,asp.net操作sqlserver数据库

第二页数据

以上就关于sqlserver 来实现地理位置的案例

mongodb 如何实现可以看我另外一篇文章

Asp.Net Core MongoDB 空间定位（点）与距离检索

asp.net操作sql server数据库 (asp.net core数据库)

新建表

插入数据

查询数据

我们用C#代码来实现上面的过程

先创业一张建筑的表 build

准备数据

新建一个“标量函数”

根据上面的执行原理，用标量函数来批量算距离

用C#代码查询下