查询DBGeometry具体经纬度值

问题描述

背景

我有我从一个形状文件中得到了这里的国家数据库并上传到利用 http://www.sharpgis.net Shape2Sql工具，我的数据库。

I have a database of countries which I got from a shape file from here and uploaded to my database using Shape2Sql tool from http://www.sharpgis.net.

在使用该工具时，我选择了选项使用SRID 4326，因为我相信这种重新presents地球表面的几何形状（请纠正我如果当我是错的）。

While using the tool, I selected the option to use SRID 4326 as I believe this represents the geometry of the earths surface (please correct me if and when I am wrong).

当我运行选择在SQL Management Studio中，在结果窗格中有一个空间选项卡，基本上显示了世界地图，即在查询所有的几何数据，我可以看到英国在那里。

When I run Select in Sql management studio, in the results pane there is a spatial tab which basically shows a map of the world i.e. all the geometric data in the query and I can see the United kingdom in there.

问题是：

我有一组经纬度坐标，我想找出坐标的国家。我使用实体框架和我的code看起来像这样

I have a set of LatLng coordinates and I want to find out the country where the coordinates are. I am using Entity Framework and my code looks something like this

var coords = location.Split(',');
var myLocation = DbGeometry.FromText(string.Format("POINT({0} {1})", coords[1], coords[0]));
var country = db.CountryRepository.Where(x => x.Geom.Contains(myLocation));

我的查询总是返回国为空，即使我有英国在数据库中，我用我的当前位置的坐标。

My query always returns country as null even though I have the United kingdom in the database and I am using my current location as coordinates.

当我换2号线到 VAR myLocation = DbGeometry.FromText（的String.Format（POINT（{0} {1}），COORDS [1]，COORDS [0]），4326 ）;

我得到一个异常：

A .NET Framework error occurred during execution of user-defined routine or aggregate "geometry":
System.ArgumentException: 24144: This operation cannot be completed because the instance is not valid. Use MakeValid to convert the instance to a valid instance. Note that MakeValid may cause the points of a geometry instance to shift slightly.
System.ArgumentException:
   at Microsoft.SqlServer.Types.SqlGeometry.ThrowIfInvalid()
   at Microsoft.SqlServer.Types.SqlGeometry.STContains(SqlGeometry other)
.

坐标：

{"latitude":"53.15366296446161","longitude":"-1.098928023733833"}

没有人有任何想法如何做到这一点正确？

Does anyone have any idea how to do this properly?

更新

我使用下面的查询SQL服务器上直接解决这个

I have solve this directly on SQL server using the following query

SELECT [Id]
      ,[NAME]
      ,[ISO]
      ,[Geom]
      ,[Geom].MakeValid().STIntersects(geometry::STGeomFromText('POINT(-1.098928023733833 53.15366296446161)', 4326)) as Inters
  FROM [Countries]

该半成品列是1曼联KINDOM，否则为0，没有人知道我可以做的EntityFramework MakeValid？

The Inters column is 1 for United Kindom and 0 otherwise, does anyone know how I can do MakeValid from EntityFramework?

推荐答案

确定，所以除了上述的TSQL查询，这里是如何与实体框架做到这一点

Ok so in addition to the tsql query above, here is how to do this with entity framework

 var coords = location.Split(',');
 var myLocation = DbGeometry.FromText(string.Format("POINT({0} {1})", coords[1], coords[0]), 4326);
 var country = db.CountryRepository.Where(x => SqlSpatialFunctions.MakeValid(x.Geom).Intersects(myLocation));

TSQL

TSQL

SELECT [Id]
      ,[NAME]
      ,[ISO]
      ,[Geom]
  FROM [Countries]
  WHERE [Geom].MakeValid().STIntersects(geometry::STGeomFromText('POINT(-1.098928023733833 53.15366296446161)', 4326)) = 1

这篇关于查询DBGeometry具体经纬度值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！