Can't find polygons in database where point is within

Question

I am trying to use a method to determine if a Lat,Long coordinate is within any of the stored polygons in the database and return a list of the ones it falls in (if any).

I have a lat/long of:

50.120578, -103.535156

I created a geofence and stored it in the database,

CREATE TABLE [dbo].[GeoFences] (
    [Id]           INT               IDENTITY (1, 1) NOT NULL,
    [GeoPoints]    [sys].[geography] NULL,
    CONSTRAINT [PK_dbo.GeoFences] PRIMARY KEY CLUSTERED ([Id] ASC)
);

These are the values for the GeoPoints.

POLYGON ((-129.63729858398437 57.279042764977774, -92.899017333984375 56.8970039212726, -93.865814208984375 48.922499263758255, -122.86972045898437 48.806863461085172, -129.37362670898437 57.088515327886505, -129.63729858398437 57.279042764977774))

Using this method, I expect it to return that row as the polygon surrounds the point.

public IEnumerable<GeoFence> SearchGeoPoint(DbGeography geoPoint)
        {
            try
            {
                return _geoLocationRepository.Get.Where(obj => obj.GeoPoints.Intersects(geoPoint));
            }
            catch (Exception)
            {
                return null;
            }
        }

However I keep getting no rows returned. I don't get any exceptions or errors, simply 0 rows.

Answer 1

Found out this was actually a few issues here and hopefully someone who experiences this issue will find this helpful.

First issue was that I was using a .Get.Where together in my LINQ query. When I removed the .Get and used the database context directly (I was using a generic repository) it returned the row(s) I expected.

I replaced

return _geoLocationRepository.Get.Where(obj => obj.GeoPoints.Intersects(geoPoint));

With

using(var context as DatabaseContext())
{
  var results = new List<GeoFence>();
  var query = context.GeoFences.Where(obj => obj.GeoPoints.Intersects(geoPoint));

  //Have to iterate through the results (Only way I've found to access the data)
  foreach(var fence in query)
{
   results.Add(fence);
}

return results;
}

The other thing to note is that you need to use Long/Lat to create your POINT not Lat/Long (You will get an exception if you do Lat/Long).