Get all Longitude and Latitude within a radius without spatial type Entity Framework

Question

We have a postal code table with longitude & latitude as column, can't be changed to spatial data type due to production requirement.

Was using STDistance() but I'm getting an error:

LINQ to Entities does not recognize the method

Therefore I have to call toList then only use STDistance(), which is pulling every postal code into the system & causing slow performance.

private static List<string> GetPostalCodesWithinRange( DbContext db, double latitude, double longitude)
{
    var yourLocation = SqlGeography.Point(latitude, longitude, 4326);

    var query = from postal in db.POSTALS
                                 .Where(x => x.LATITUDE != null || 
                                             x.LONGITUDE != null)
                                 .ToList()
    let distance = SqlGeography.Point((double)postal.LATITUDE.Value, (double)postal.LONGITUDE.Value, 4326).STDistance(yourLocation)
                    .Value
                where postal.LATITUDE != null && postal.LONGITUDE != null &&  distance < 3000
                orderby distance
                select postal.POSTAL_CODE;

    return query.Distinct().ToList();
}

Currently the Postals toList() method only filters out the null Latitude & Longitude

db.POSTALS.Where(x => x.LATITUDE != null || x.LONGITUDE != null).ToList()

Since we are only checking the longlat within 3km, now we would like to optimize the query by pulling only the longitude & latitude that reside within 3km square border, then only filter by radius using the method above.

May I ask what's way to get the corner longlat points that's 1.5km away from the center?

Out of curiosity, do you have an appetite for adding a calculated column that is a spatial point based on the existing lat and long columns? — Ben Thul, Mar 06 '19 at 21:57
@BenThul that could be a great solution, but for now, I was planning to do it without changing the production db. — AppeShopper, Mar 07 '19 at 01:03
You could create a T-SQL Stored Procedure which uses the STDistance function, and call the Stored Procedure using LINQ to Entities. If your production system will not allow you to create a Stored Procedure, alternatively you could write the SQL Query using the STDistance function and execute the SQL directly from LINQ to Entities using SQLQuery(). Either of these would remove the need to calculate the corner points! — Edward, Mar 07 '19 at 13:54

Edward · Answer 1 · 2019-03-06T23:35:13.233

SQL Server geographic function "STBuffer" adds a buffer distance to the input Geography. You can input a single point to STBuffer you will get back a polygon approximating a circle centred at the point. You can then find the minimum and maximum LAT/LONG of this polygon.

(This is not accurate over large distances, because the Earth is a sphere, but for a radius of 1.5km it is close enough; it also breaks down close to 180 degrees longitude, since close to this you go to -180 degrees.)

Ignoring these caveats, here is a function to do this:

public static void GetBorderBounds(SqlGeography point, double distanceMeters, out double minLat, out double minLong, out double maxLat, out double maxLong)
{
    var buffer = point.STBuffer(distanceMeters);

    minLat = (double)point.Lat;
    maxLat = (double)point.Lat;
    minLong = (double)point.Long;
    maxLong = (double)point.Long;
    for (int i = 1; i <= buffer.STNumPoints(); i++)
    {
        var p = buffer.STPointN(i);
        if (p.Lat < minLat) { minLat = (double)p.Lat; }
        if (p.Long < minLong) { minLong = (double)p.Long; }
        if (p.Lat > minLat) { maxLat = (double)p.Lat; }
        if (p.Long > maxLong) { maxLong = (double)p.Long; }
    }
}

Here's how you could add this function to your original query:

private static List<string> GetPostalCodesWithinRange(DbContext db, double latitude, double longitude)
{
    var yourLocation = SqlGeography.Point(latitude, longitude, 4326);

    double minLat1, minLong1, maxLat1, maxLong1;
    GetBorderBounds(yourLocation, 1500, out minLat1, out minLong1, out maxLat1, out maxLong1);

    var query = from postal in db.POSTALS
                                 .Where(x => x.LATITUDE != null ||
                                             x.LONGITUDE != null)
                                 .ToList()
                let distance = SqlGeography.Point((double)postal.LATITUDE.Value, (double)postal.LONGITUDE.Value, 4326).STDistance(yourLocation)
                                .Value
                where postal.LATITUDE != null && postal.LONGITUDE != null && distance < 3000
                && postal.LATITUDE > minLat1 && postal.LATITUDE < maxLat1
                && postal.LONGITUDE > minLong1 && postal.LONGITUDE < maxLong1
                orderby distance
                select postal.POSTAL_CODE;

    return query.Distinct().ToList();
}

score 0 · Answer 2 · answered Mar 07 '19 at 13:44

After I posted my first answer using STBuffer, I realised there is a simpler way to get the corner points a certain distance away from the point.

I'm keeping the original STBuffer answer as it's not wrong, and it could be useful as an example of using STBuffer and iterating the points in a Geography object.

Here is the simpler implementation of GetBorderBounds:

public static void GetBorderBounds2(SqlGeography point, double distanceMeters, out double minLat, out double minLong, out double maxLat, out double maxLong)
{
    var metresPerDegreeLat = point.STDistance(SqlGeography.Point((double)point.Lat + 1.0, (double)point.Long, 4326));
    var metresPerDegreeLong = point.STDistance(SqlGeography.Point((double)point.Lat, (double)point.Long + 1.0, 4326));
    minLat = (double)(point.Lat - distanceMeters / metresPerDegreeLat);
    maxLat = (double)(point.Lat + distanceMeters / metresPerDegreeLat);

    minLong = (double)(point.Long - distanceMeters / metresPerDegreeLong);
    maxLong = (double)(point.Long + distanceMeters / metresPerDegreeLong);
}

This implementation breaks down near the North and South Poles. Also, as with the first solution, it gives the wrong answer if the boundary crosses the 180th meridian.

If all your points are away from the poles or the 180th meridian, this doesn't matter.

If your points could be near the poles or the 180th meridian, here is a bullet-proof implementation:

public static void GetBorderBounds3(SqlGeography point, double distanceMeters, out double minLat, out double minLong, out double maxLat, out double maxLong)
{
    // Near the North pole: 
    // Select whole circle of longitude from North pole to latitude south of point by distance
    if (point.Lat >= 89)
    {
        minLat = (double)(point.Lat - distanceMeters / point.STDistance(SqlGeography.Point((double)point.Lat - 1.0, (double)point.Long, 4326)));
        maxLat = 90;
        minLong = -180;
        maxLong = 180;
        return;
    }

    // Near the South pole: 
    // Select whole circle of longitude from South pole to latitude north of point by distance metres
    if (point.Lat <= -89)
    {
        minLat = -90;
        maxLat = (double)(point.Lat + distanceMeters / point.STDistance(SqlGeography.Point((double)point.Lat + 1.0, (double)point.Long, 4326)));
        minLong = -180;
        maxLong = 180;
        return;
    }

    var metresPerDegreeLat = point.STDistance(SqlGeography.Point((double)point.Lat + 1.0, (double)point.Long, 4326));
    var metresPerDegreeLong = point.STDistance(SqlGeography.Point((double)point.Lat, (double)point.Long + 1.0, 4326));
    minLat = (double)(point.Lat - distanceMeters / metresPerDegreeLat);
    maxLat = (double)(point.Lat + distanceMeters / metresPerDegreeLat);
    minLong = (double)(point.Long - distanceMeters / metresPerDegreeLong);
    maxLong = (double)(point.Long + distanceMeters / metresPerDegreeLong);

    // If we cross the 180th meridian, select the whole circle of longitude:
    if (minLong < -180 || maxLong > 180.0)
    {
        minLong = -180;
        maxLong = 180;
    }
}

Get all Longitude and Latitude within a radius without spatial type Entity Framework

2 Answers2