CODEWITHSUNDEEP
sql-serverentity-frameworkgoogle-mapsspatial
Iravanchi
Iravanchi

Reputation: 5159

Query for a rectangular area when using geography data type (DbGeography)

I'm querying a table in SQL Server to show results on Google Maps, and I'm using Entity Framework and DbGeography data types on the server application.

Simply, I get the map bounds from the Google Maps instance, construct a DbGeography instance from it using something like this:

var wkt = string.Format("POLYGON(({0} {1}, {0} {2}, {3} {2}, {3} {1}, {0} {1}))", EastLng, NorthLat, SouthLat, WestLng);
var bounds = DbGeography.FromText(wkt);

And then query the database using this (as a part of a larger query):

dbset.Where(i => bounds.Intersects(l.GeographicLocation));

This works fine, and I actually have it working in production for a while. Today I noticed that sometimes some results don't show up correctly on the map.

After some investigation, I realized that it is because the earth's curvature; Google Maps uses a rectangular projection of the earth, but what when I create a rectangular polygon, it means a shape like this (copied from SQL Server Management Studio, from the query that was running):

Boundary projection

The effect is increased when looking at maps of very large areas (zooming out). But when I zoom in, the effect of earth's curvature decreases and query seems to work fine.

What I need to do, is to basically query the DB for all points that lie within a Lat/Lng boundary. Is there a way to do this?

EDIT:

This is the complete code that I'm using to create a DbGeography instance:

public class LatLngBounds
{
    // ...

    public DbGeography ToDbGeography()
    {
        return DbGeographyUtil.CreatePolygon(string.Format("POLYGON(({0} {1}, {0} {2}, {3} {2}, {3} {1}, {0} {1}))", EastLng, NorthLat, SouthLat, WestLng));
    }
}


public static class DbGeographyUtil
{
    public static DbGeography CreatePolygon(string wktString)
    {
        if (string.IsNullOrWhiteSpace(wktString))
            return null;

        var sqlGeography = SqlGeography.STGeomFromText(new SqlChars(wktString), DbGeography.DefaultCoordinateSystemId).MakeValid();

        var invertedSqlGeography = sqlGeography.ReorientObject();
        if (sqlGeography.STArea() > invertedSqlGeography.STArea())
        {
            sqlGeography = invertedSqlGeography;
        }

        return DbSpatialServices.Default.GeographyFromProviderValue(sqlGeography);
    }

}

EDIT 2:

The coordinates coming out of Google Maps in the above example is:

This is a part of the query that was running, that I captured using SQL Server Profiler:

declare @p4 sys.geography
set @p4=convert(sys.geography,0xE6100000010405000000463E933FAFD64A40070000006E3F354061E412079A894140070000006E3F354061E412079A894140020000808B245740463E933FAFD64A40020000808B245740463E933FAFD64A40070000006E3F354001000000020000000001000000FFFFFFFF0000000003)

SELECT @p4;

(I added the SELECT to see how it looks like, and resulted in the image above)

Upvotes: 4

Views: 1420

Answers (1)

ingljo
ingljo

Reputation: 51

I manage to solve this by using DbGeometry instead of DbGeography

var boundsAsGeometry = DbGeometry.FromText(bounds.AsText(), bounds.CoordinateSystemId);

dbset.Where(x => boundsAsGeometry.Intersects(DbGeometry.FromText(x.GeographicLocation.AsText(), x.GeographicLocation.CoordinateSystemId)));

Be aware that this will not use the existing db index for the GeographicLocation field if you have that.

Upvotes: 1

Related Questions