Facilitating the Spread of Knowledge and Innovation in Professional Software Development

Write for InfoQ


Choose your language

InfoQ Homepage News Partial Support for Spatial Queries in EF Core 2.2

Partial Support for Spatial Queries in EF Core 2.2

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.

Rate this Article