CODEWITHSUNDEEP
.netentity-frameworkspatial-query
Obi
Obi

Reputation: 3091

Query DBGeometry for specific LatLng value

Background

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.

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).

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.

Question is:

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.

When I change line 2 to var myLocation = DbGeometry.FromText(string.Format("POINT({0} {1})", coords[1], coords[0]), 4326);.

I am getting an exception : 

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)
.

Coordinates: 

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

Does anyone have any idea how to do this properly?

UPDATE

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]

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

Upvotes: 1

Views: 2296

Answers (1)

Obi
Obi

Reputation: 3091

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

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

Upvotes: 1

Related Questions