#sql-server #entity-framework #sqlgeography
#sql-сервер #entity-framework #sqlgeography
Вопрос:
У нас есть таблица почтовых индексов с долготой и широтой в качестве столбца, не может быть изменен на пространственный тип данных из-за производственных требований.
Использовал STDistance() , но я получаю сообщение об ошибке:
LINQ to Entities не распознает метод
Поэтому мне приходится вызывать, toList затем только use STDistance() , который загружает каждый почтовый индекс в систему и приводит к снижению производительности.
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 amp;amp; postal.LONGITUDE != null amp;amp; distance < 3000
orderby distance
select postal.POSTAL_CODE;
return query.Distinct().ToList();
}
В настоящее время Postals toList() метод отфильтровывает только нулевые широту и долготу
db.POSTALS.Where(x => x.LATITUDE != null || x.LONGITUDE != null).ToList()
Поскольку мы проверяем только длину в пределах 3 км, теперь мы хотели бы оптимизировать запрос, извлекая только долготу и широту, которые находятся в пределах квадратной границы 3 км, а затем фильтровать только по радиусу, используя описанный выше метод.
Могу ли я спросить, каким способом получить угловые точки longlat, расположенные в 1,5 км от центра?
Комментарии:
1. Из любопытства, есть ли у вас желание добавить вычисляемый столбец, который является пространственной точкой, на основе существующих столбцов lat и long?
2. @BenThul это могло бы быть отличным решением, но пока я планировал сделать это без изменения рабочей базы данных.
3. Вы могли бы создать хранимую процедуру T-SQL, которая использует функцию STDistance, и вызвать хранимую процедуру, используя LINQ to Entities. Если ваша производственная система не позволит вам создать хранимую процедуру, альтернативно вы могли бы написать SQL-запрос с помощью функции STDistance и выполнить SQL непосредственно из LINQ к сущностям с помощью SqlQuery(). Любой из этих способов избавил бы от необходимости вычислять угловые точки!
Ответ №1:
Географическая функция SQL Server «STBuffer» добавляет расстояние буфера к вводимой географии. Вы можете ввести одну точку в STBuffer, и вы получите многоугольник, аппроксимирующий окружность с центром в точке. Затем вы можете найти минимальную и максимальную широту / ДЛИНУ этого полигона.
(Это неточно на больших расстояниях, потому что Земля является сферой, но для радиуса 1,5 км это достаточно близко; он также разбивается на долготу около 180 градусов, поскольку вблизи этого значения вы переходите к -180 градусам.)
Игнорируя эти предостережения, вот функция для этого:
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; }
}
}
Вот как вы могли бы добавить эту функцию в свой исходный запрос:
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 amp;amp; postal.LONGITUDE != null amp;amp; distance < 3000
amp;amp; postal.LATITUDE > minLat1 amp;amp; postal.LATITUDE < maxLat1
amp;amp; postal.LONGITUDE > minLong1 amp;amp; postal.LONGITUDE < maxLong1
orderby distance
select postal.POSTAL_CODE;
return query.Distinct().ToList();
}
Ответ №2:
После того, как я опубликовал свой первый ответ, используя STBuffer, я понял, что есть более простой способ получить угловые точки на определенном расстоянии от точки.
Я сохраняю исходный ответ STBuffer, поскольку он не является неправильным, и он может быть полезен в качестве примера использования STBuffer и повторения точек в объекте Geography.
Вот более простая реализация 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);
}
Эта реализация выходит из строя вблизи Северного и Южного полюсов. Также, как и в первом решении, оно дает неправильный ответ, если граница пересекает 180-й меридиан.
Если все ваши точки удалены от полюсов или 180-го меридиана, это не имеет значения.
Если ваши точки могут находиться вблизи полюсов или 180-го меридиана, вот пуленепробиваемая реализация:
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;
}
}