未加星标

Partial Support for Spatial Queries in EF Core 2.2

字体大小 | |
[数据库(mssql) 所属分类 数据库(mssql) | 发布者 店小二05 | 时间 2019 | 作者 红领巾 ] 0人收藏点击收藏

While there are many “nice-to-haves” in Entity Framework that have not been ported to EF Core, they aren’t talked about much because developers can do without. But one feature that has been repeatedly cited as a reason to not adopt EF Core, or even .NET Core itself, was the lack of support for spatial data types.

Spatial data is used for a wide variety of functionality, but the most common is geo-bounding. For example, a website may wish to display a list of their stores within a 5-mile radius of the user’s zip/postal code. In a single query, the database could calculate the center of a zip/postal code, then filter the list of all stores based on the distance from that center point. Spatial indexes , using a technique known as tessellation, make this a fast operation.

Up until now, EF Core has been unable to implement support for spatial data types because of SQL Server. With SQL Server, the types themselves are implemented as a COM library. This means the code is non-portable and a cross-platform .NET Core wrapper isn’t possible. In theory EF Core could have still supported other spatial databases, but saying “everything but SQL Server” in a Microsoft-funded library would not be acceptable.

To solve this problem, the EF Core team decided to build upon the NetTopologySuite library. This is a geometrical library that can read and write to spatial data types such as SQL Server’s geometry and geography types. When combined with EF Core 2.2, LINQ style queries using these types are enabled.

Geometry vs Geography

At this point it is important to discuss a very important detail; spatial data types come in two flavors, geometry and geography.

The geometry type represents a 2-dimensional or 3-dimensional point or shape using a Cartesian coordinate system. This is for anything that can be represented in terms squares or cubes. It is usually accurate enough for a city or region.

The geography type represents 2-dimensional or 3-dimensional point or shape on an imperfect sphere. Since the distance between two points on a sphere is a lot different than the distance between two points on a flat square, the calculations when using the geography type are very different.

Furthermore, the Earth isn’t perfectly round. The diameter of the Earth varies depending on where you measure it, as does the length of the north-south and east-west axis. In order to tell others what values you used when creating your map, all geography data is supposed to include an SRID or Spatial Reference System Identifier . An SRID is usually a 4-digit number and is mapped to a description file that includes approximated size/shape of the Earth, the units used (e.g. degrees or meters), who created the SRID, and other pertinent information. Most geographical databases and libraries understand SRIDs and their description files.

Unfortunately, the NetTopologySuite library is not a geographical library; it is only geometrical. While it can read and write SRID values, any operation performed client-side will ignore them. From the EF Core documentation ,

NTS ignores SRID values during operations. It assumes a planar coordinate system. This means that if you specify coordinates in terms of longitude and latitude, some client-evaluated values like distance, length, and area will be in degrees, not meters. For more meaningful values, you first need to project the coordinates to another coordinate system using a library like ProjNet4GeoAPI before calculating these values.

This can be quite problematic when using EF Core and LINQ because EF Core may choose to execute part of your query client-side. When using spatial data, verify any SRID sensitive operations are being executed in the database where the SRID is honored by examining the generated SQL.

Microsoft also warns that NetTopologySuite doesn’t support CircularString, CompoundCurve, and CurePolygon shapes.

Supported Databases

Currently EF Core has spatial data support for SQL Server (geometry and geography), SQLite , and PostgreSQL via extension libraries.

本文数据库(mssql)相关术语:熊片数据库 mssql数据库 oracle数据库 pubmed数据库 access数据库 万方数据库

分页:12
转载请注明
本文标题:Partial Support for Spatial Queries in EF Core 2.2
本站链接:https://www.codesec.net/view/628508.html


1.凡CodeSecTeam转载的文章,均出自其它媒体或其他官网介绍,目的在于传递更多的信息,并不代表本站赞同其观点和其真实性负责;
2.转载的文章仅代表原创作者观点,与本站无关。其原创性以及文中陈述文字和内容未经本站证实,本站对该文以及其中全部或者部分内容、文字的真实性、完整性、及时性,不作出任何保证或承若;
3.如本站转载稿涉及版权等问题,请作者及时联系本站,我们会及时处理。
登录后可拥有收藏文章、关注作者等权限...
技术大类 技术大类 | 数据库(mssql) | 评论(0) | 阅读(54)