Efcore: Support Spatial Data Types on SQL Server and SQLite

[vijayantkatyal]

how to use Spatial Data types (e.g. DbGeography) in model (code-first method) to save points, shapes etc.

e.g.

public DbGeography gps_points {get; set;}

i'm unable to use "DbGeography".

[rowanmiller]

@vijayantkatyal are you asking about EF Core?

[simonseyock]

I have the same Question. I am trying to use the DbGeometry datatype, but it is unavailable with EF Core, neither System.Data.Entity.Spatial nor System.Data.Spatial are referable.

[rowanmiller]

Partially covered by https://github.com/aspnet/EntityFramework/issues/242 but we probably also want to look at built-in support for spatial types.

[vijayantkatyal]

@rowanmiller sorry for late response. yes i'm asking about EF Core.

[neil-119]

+100000. We make extensive use of spatial data in our applications.

[roji]

I know you guys aren't working on this at the moment, but what are the chances that you'll be reusing types and concepts from EF6's System.Data.Entity.Spatial namespace?

I'm asking because there's some interest in working on this namespace and I'd like to have an idea (if possible) as to whether that will be useful going forward...

cc @rossini-t

[bricelam]

@roji At the moment, I don't think we intend to put a common abstraction over it. Instead we would light-up provider-specific types like SqlGeography leveraging the work of #242.

[roji]

OK thanks.

Note that this mean it will no longer be possible to write provider-independent code that manipulates spatial DB values (as was possible in EF6). I have no idea to what extent spatial support across databases (SqlServer, PostGIS, etc.) overlaps, so this may or may not be a big loss.

Then again with the EF Core approach a common spatial abstraction seems to be a problem outside of EF's domain - as long as #242 allows for reading/writing the provider-specific spatial types a common abstraction may or may not be used afterwards.

[balivo]

Any news about Spatial types in EF Core?

[dfaivre]

I'd also love an update -- it's a blocker for EF Core, ASP.NET Core and .Net Core for us. Can't wait to try out the new toys though!

[rowanmiller]

Spatial data type support will happen post initial RTM of EF Core (i.e. after the 7.0.0 release). This is purely due to how much work we can achieve between now and RTM and not because we think this scenario is unimportant.

[balivo]

Tks for answer @rowanmiller...

[BehnamAbdy]

it seems we have no way but waiting for next release of EF Core to include and support the Spatial types

[RobertDM]

Just a vote for basic DbGeography support.

Our application (like many others I am sure) relies on the DbGeography datatype mapping to the geography type in SQL Server.

Very disappointing that an actual datatype, fully supported by EF6, would be de-prioritized for EF Core. There isn't even a mention of it in the roadmap.

Please don't let this one slip through the cracks.

[xperiandri]

So what can we do now to use spatial types from EF Core?
Will creating a type mapper to Well-Known text work?

[Ronan-Farrell]

Also a vote here for some DbGeography support!

[roji]

Speaking from a PostgreSQL perspective, if and when you do tackle the problem of spatial data it's extremely important to do so with a cross-provider vision. EF6's spatial types, just like most other things in EF6, were designed to work with SqlServer's spatial support and don't necessarily capture what PostgreSQL's PostGIS allows - so a provider-independent abstraction such as DbGeography may not work well here.

Am far from being an expert here but it's important not to rush into this without comparing spatial support in different databases etc.

[spass2000]

Any Timeline to support DbGeography or DbGeometry?

[ErikEJ]

It is in the roadmap: https://github.com/aspnet/EntityFramework/wiki/Roadmap

[ischas]

"High priority features" means "lowest priority" of the list?

[RobertDM]

No way should an actual datatype, used by many existing EF6 applications, be lumped in with a bunch of wish-list items that impact nobody.

[rowanmiller]

@ischas @RobertDM - The items on the Roadmap page are the highest priority items, they are a small subset of the 100's of items we have on our backlog (the Backlog milestone in this issue tracker). You could argue that spatial should be part of the Critical Features list - probably true of all things listed in the High Priority Features list. The idea of the roadmap is give folks an idea of the top priority items our team will be looking at adding once we have spare cycles to spend on adding new features.

[unt1tled]

+1

[jumpinjackie]

@roji

Speaking as someone who's extensively used other spatially-capable data access technologies for Map/GIS applications (such as GDAL and FDO), if you're going to bring in spatial data type support into EF core and want to retain the theme of provider agnosticism, the spatial data support should be something that closely matches the OGC simple features model.

• The spatial data model covers the Point/LineString/Polygon/MultiPoint/MultiLineString/MultiPolygon/GeometryCollection geometry types
• The spatial data model supports basic spatial operatiors like Intersect/Contains/etc, and EF providers should be able to translate LINQ expressions containing such operators to their respective SQL
• The spatial data model is serializable to and from WKT/WKB formats.

This is the "lowest common denominator" set of features that any RDBMS that supports spatial data will support.

From what I've seen, DbGeometry/DbGeography should fit the mold (in the lowest common denominator sense). It just needs sufficient extension/integration points so that providers like PostgreSQL aren't constrained by its LCD-ness and can add all of its PostGIS goodies on top of.

[roji]

@jumpinjackie that makes sense to me and corresponds to the little I know about spatial. It does make sense for Microsoft to provide a set of lowest common denominator classes which represent the OGC spec, keeping extensibility in mind. IMHO this should probably even be a package that's completely separate from EF (e.g. a nuget with a collection of pure data types), which specific EFCore providers supporting spatial would reference, optionally extend, and use.

As an example of provider specificity, PostgreSQL's PostGIS uses EWKT/EWKB (supersets of the standard WKT/WKB), allowing the embedding of SRIDs and some other features. Since they are supersets Npgsql could simply extend the general OGC Point, adding the extra features. See http://postgis.refractions.net/docs/using_postgis_dbmanagement.html#EWKB_EWKT for more details.

[RobertDM]

As a first measure I would be glad to at least see mapping/conversion/migration of POCO DbGeography to a database geography type, even with no linq query support whatsoever. Then at least EF Core wouldn't break existing code first definitions/migrations, and application workarounds would be possible.

The way things are looking it will be back to latitude/longitude for us... really disappointing.

[ikourfaln]

!!! Still waiting for milestone... Wish if this feature included in 1.1.0 milestone.

[robertmclaws]

I've opened an issue in .NET Core to create Framework-native Spatial types, so that _some_ spatial work can be done without taking a dependency on any database library. For example, it should be entirely possible to have a type that can store and understand WKT data, without worrying about how that data gets serialized to the database.

I've opened the issue here. If you think it is an important feature, please vote it up. I'd love to put a group of people together that can help make an implantation happen.

[pauloanalista]

Does anyone know if Entity to support the DbGeometry type properties in MySql?
I'm building a vehicle tracking software and am using the MySql database.
When I try to save a PONT the framework entity works, but when I try to save a LineString'm taking error.

Alguém sabe se o Entity da suporte a propriedades do tipo DbGeometry no MySql?
Estou construindo um software de rastreamento de veículos e estou usando o banco de dados MySql.
Quando tento salvar um PONT pelo entity framework funciona, mas quando tento salvar um LineString estou tomando erro.

StackTrace:
System.Data.Entity.Infrastructure.DbUpdateException was unhandled
HResult=-2146233087
Message=An error occurred while updating the entries. See the inner exception for details.
Source=EntityFramework
StackTrace:
em System.Data.Entity.Internal.InternalContext.SaveChanges()
em System.Data.Entity.Internal.LazyInternalContext.SaveChanges()
em System.Data.Entity.DbContext.SaveChanges()
em ConsoleApplication.Program.Main(String[] args) na C:UserspauloDesktopEFWithMySqlEFWithMySqlConsoleApplicationProgram.cs:linha 47
em System.AppDomain._nExecuteAssembly(RuntimeAssembly assembly, String[] args)
em System.AppDomain.ExecuteAssembly(String assemblyFile, Evidence assemblySecurity, String[] args)
em Microsoft.VisualStudio.HostingProcess.HostProc.RunUsersAssembly()
em System.Threading.ThreadHelper.ThreadStart_Context(Object state)
em System.Threading.ExecutionContext.RunInternal(ExecutionContext executionContext, ContextCallback callback, Object state, Boolean preserveSyncCtx)
em System.Threading.ExecutionContext.Run(ExecutionContext executionContext, ContextCallback callback, Object state, Boolean preserveSyncCtx)
em System.Threading.ExecutionContext.Run(ExecutionContext executionContext, ContextCallback callback, Object state)
em System.Threading.ThreadHelper.ThreadStart()
InnerException:
HResult=-2146233087
Message=An error occurred while updating the entries. See the inner exception for details.
Source=EntityFramework
StackTrace:
em System.Data.Entity.Core.Mapping.Update.Internal.UpdateTranslator.Update()
em System.Data.Entity.Core.EntityClient.Internal.EntityAdapter.b__2(UpdateTranslator ut)
em System.Data.Entity.Core.EntityClient.Internal.EntityAdapter.UpdateT
em System.Data.Entity.Core.Objects.ObjectContext.SaveChangesToStore(SaveOptions options, IDbExecutionStrategy executionStrategy, Boolean startLocalTransaction)
em System.Data.Entity.Core.Objects.ObjectContext.<>c__DisplayClass2a.b__27()
em System.Data.Entity.Infrastructure.DefaultExecutionStrategy.ExecuteTResult
em System.Data.Entity.Core.Mapping.Update.Internal.DynamicUpdateCommand.Execute(Dictionary2 identifierValues, List1 generatedValues)
em System.Data.Entity.Core.Mapping.Update.Internal.UpdateTranslator.Update()
InnerException:
HResult=-2146233080
Message=O índice estava fora dos limites da matriz.
Source=MySql.Data.Entity.EF6
StackTrace:
em MySql.Data.Entity.Metadata.NormalizeValue(TypeUsage type, Object value)
em MySql.Data.Entity.SqlGenerator.Visit(DbConstantExpression expression)
em System.Data.Entity.Core.Common.CommandTrees.DbConstantExpression.AcceptTResultType
em MySql.Data.Entity.InsertGenerator.GenerateSQL(DbCommandTree tree)
em MySql.Data.MySqlClient.MySqlProviderServices.CreateDbCommandDefinition(DbProviderManifest providerManifest, DbCommandTree commandTree)
em System.Data.Entity.Core.Common.DbProviderServices.CreateDbCommandDefinition(DbProviderManifest providerManifest, DbCommandTree commandTree, DbInterceptionContext interceptionContext)
em System.Data.Entity.Core.Common.DbProviderServices.CreateCommandDefinition(DbCommandTree commandTree, DbInterceptionContext interceptionContext)
em System.Data.Entity.Core.Common.DbProviderServices.CreateCommand(DbCommandTree commandTree, DbInterceptionContext interceptionContext)
em System.Data.Entity.Core.Mapping.Update.Internal.UpdateTranslator.CreateCommand(DbModificationCommandTree commandTree)
InnerException:

[xperiandri]

The only way I know is to save data as primitive types and then query them by FromSql method from stored procedure call or SQL with all the transformations.

[pauloanalista]

I want to persist the data through the entity completed for my context and save with SaveChanges

If I do Sql in hand and have performed at ctx.Database.ExecuteSqlCommand (sql); it works perfectly.

But the idea of using the ORM is not write sql in hand.

I'm two days looking for a solution.

I'm almost migrating to NHibernate

---

Quero persistir os dados passando a entidade preenchida para meu contexto e salvar com SaveChanges

Se eu fizer o Sql na mão e mandar executar pelo ctx.Database.ExecuteSqlCommand(sql); funciona perfeitamente.

Mas a ideia de usar o ORM é não escrever sql na mão.

Estou a 2 dias procurando por uma solução.

Estou quase migrando para o NHibernate

[ikourfaln]

really need this feature :disappointed:

[pauloanalista]

I solved the problem this way.

But in fact I would like to just add the object to the entity context and save it for me

C# string sql = @" INSERT INTO `_trackbus.empresas`.`trecho` ( `Nome`, `GeoCodeLineString`, `ECorredor`, `VelocidadeMaxima`, `ToleranciaEmMetros`) VALUES ( 'Teste Paulo', ST_LineFromText('LINESTRING (-43.169719999999984 -22.50821, -43.170210380287131 -22.507401944497914, -43.17208784523001 -22.508305750311617, -43.173587322235107 -22.509068149329881, -43.175263644180234 -22.509818760345773, -43.175808191299438 -22.509286202204262, -43.176362915344271 -22.508249823122942, -43.177122100524912 -22.506921169153081, -43.177750110626221 -22.505827050155133, -43.1783401966095 -22.504885432178252, -43.179539474868761 -22.505150264654358, -43.180142641067505 -22.505876608818408, -43.180818017196657 -22.505309823544408, -43.181687695770279 -22.504621851919538, -43.182127475738525 -22.503656363278225, -43.181739474868721 -22.502569207740123, -43.181408643722534 -22.501911859633882, -43.181344270706177 -22.501297313336377, -43.181140422821045 -22.500613379569185, -43.180539608001709 -22.500008739654255, -43.180430728836086 -22.499192721834696, -43.180174827575684 -22.498601206992014, -43.180539608001709 -22.497629802858334, -43.179864373016358 -22.49648956202677, -43.179627656936646 -22.495756361338124, -43.179584741592407 -22.495062487670484, -43.17926416931158 -22.492934367973508, -43.178308010101318 -22.492445561386042, -43.178082704544067 -22.491850798509919, -43.178211865081721 -22.491019215188004, -43.176891803741455 -22.490175536001722, -43.177200728836056 -22.489004255573377, -43.177205101852451 -22.487547304709945, -43.176780085983239 -22.486867045568079, -43.175829648971558 -22.486259902528296, -43.175357580184937 -22.4851000607153, -43.174617290496826 -22.484574659088949, -43.174304898147625 -22.483264246658855, -43.173598051071167 -22.482443292416768, -43.173499474868777 -22.48121844306775, -43.173040151596069 -22.480420942073575, -43.172986507415771 -22.479707164311463, -43.172882186508104 -22.478871726834022, -43.17277193069458 -22.478220115489169, -43.17278265953064 -22.477615377733777, -43.1724338478852 -22.476948965687729, -43.171752691268921 -22.476623998650922, -43.170111179351807 -22.476386066614975, -43.169403076171875 -22.476098564859441, -43.168823719024658 -22.475573129074444, -43.167121050262381 -22.475440688734523, -43.166409730911255 -22.475751579187246, -43.165712356567383 -22.475533473462583, -43.165186643600464 -22.474849412370862, -43.164037931213386 -22.474352236527466, -43.162922859191895 -22.474809756551782, -43.162343502044678 -22.474175261902353, -43.161408338623062 -22.474249827993923, -43.160648345947266 -22.473620076699664, -43.159639835357666 -22.473768787240033, -43.15865623809816 -22.473580343828939, -43.157708644866943 -22.473818357384665, -43.157972186508118 -22.473101117039576, -43.158448934555054 -22.472460129005061, -43.158824443817139 -22.471875194432013, -43.158732186508189 -22.470880514588195, -43.15954327583313 -22.469228086753766, -43.159555423278789 -22.468414951654037, -43.159940242767334 -22.467592208406238, -43.159961700439453 -22.46640246656662, -43.15953254699707 -22.465361434076446, -43.159076269836419 -22.464849903169952, -43.159569153442362 -22.462694063821665, -43.159942186508147 -22.462249063448784, -43.15968093254088 -22.4609287243229, -43.159103393554688 -22.460413944193949, -43.156120777130127 -22.458688725828754, -43.155176639556885 -22.458282205731496, -43.154329061508179 -22.458311951144893, -43.153620958328247 -22.458044242194511, -43.15304160118103 -22.457697211304112, -43.153706789016724 -22.458113648268334, -43.154425621032715 -22.458321866281285, -43.1552517414093 -22.458311951144893, -43.1562066078186 -22.458728386262219, -43.159167766571045 -22.460433774165264, -43.159682750701904 -22.460929522526037, -43.160079717636108 -22.460126409291046, -43.1609796468353 -22.459975695404879, -43.162293644180295 -22.459319490400738, -43.162740468978882 -22.458421017606081, -43.163416385650635 -22.457905429942613, -43.164008017196693 -22.456631101719342, -43.164746761322021 -22.455843060111036, -43.164810000000045 -22.455040592422272, -43.165100812911987 -22.453701335887317, -43.165326118469238 -22.452749447834258, -43.165034169311525 -22.4522512665265, -43.164902329444885 -22.451797553245957, -43.165095448493958 -22.451460422345473, -43.165100812911987 -22.45102413408101, -43.164875507354736 -22.450657254250608, -43.164767084655807 -22.450272613277541, -43.164244780425975 -22.450257218909016, -43.163947463035583 -22.449973070352215, -43.1638240814209 -22.449655766528856, -43.163411021232605 -22.449293840968956, -43.163003325462341 -22.449003804050793, -43.162670731544495 -22.448954225029702, -43.162367641925812 -22.448912082847851, -43.16215842962265 -22.448666666357891, -43.161621987819672 -22.448979014542473, -43.16129744052887 -22.448951746078183, -43.160986304283142 -22.44881788263033, -43.160653710365295 -22.448882335417654, -43.160326480865479 -22.449085609396818, -43.160094317536277 -22.448790416826792, -43.159740265946311 -22.448671426963337, -43.159654434930417 -22.44910276472865, -43.159579333078 -22.449524185391418, -43.159466680299374 -22.450039804224922, -43.159128722127207 -22.449965436696179, -43.159248232841492 -22.449269051512481) '), 0, 1, 1); "; ctx.Database.ExecuteSqlCommand(sql);

[brunoracosta]

They have put in the roadmap like High priority features and still without launch data.

@pauloanalista, I have the same problem by the same motive.

https://github.com/aspnet/EntityFramework/wiki/Roadmap

[skendrot]

Is this still not available with the recent release of NET Core 1.1? @rowanmiller?

[pauloanalista]

Not yet.

But you have some parallel solutions.

Give it a field of the type varchar in the bank and then you put a trigger that populates the field of type Geometry.

Entity 6 and entity core does not have this functionality yet

[rowanmiller]

It's not natively supported yet. But cc @divega as he was experimenting with this and may have found a way to get things working with the current release.

[divega]

Unfortunately I switched in my prototype from replacing the type mapper service to actually modifying the existing type mapper in SQL Server provider very early, so not sure I can help with making things work in the current release. Anyway, clearing up the milestone so we can discuss assigning this issue to a release.

[robertmclaws]

It would be really nice if we could replace the SQL Server types with a clean, simple .NET Core one that can be used by central class libraries without taking a dependency on SQL Server. There has to be a way that SQL Server can use a .NET library for these types, instead of us having to use theirs. It should be possible to move System.Spatial to .NET Core, and SQL Server should use that library instead. It would make everyone's lives orders of magnitude easier in the end.

[divega]

@robertmclaws Just to be clear, my prototyping exercise centered on enabling existing SQL Server types to be mapped through the type mapper in the EF Core provider for SQL Server and I was planning to enable LINQ method translation for certain calls on those types. This can be done similarly for other providers that support spatial functionality and have their own set of CLR types that are supported by their ADO.NET provider. I am not trying to come up with a provider agnostic solution like one we took in EF6, nor a new library of universal spatial types for .NET.

Completely removing the dependency on the SQL Server types is something that cannot be done in EF Core alone. I think the issue you created at https://github.com/dotnet/corefx/issues/12034 is a good representation of what would need to happen to make that reality. Besides that, System.Spatial already exists, but I am not sure where the source code lives now.

[jumpinjackie]

@robertmclaws It's called GeoAPI and NetTopologySuite

https://github.com/NetTopologySuite/GeoAPI
https://github.com/NetTopologySuite/NetTopologySuite

Get EFCore to work with these 2 libraries and things would be golden

[rowanmiller]

Note to self: leaving unassigned as we will prioritize as part of 1.2 planning next week

[ikourfaln]

@rowanmiller thanx for informing us.
Waiting for roadmap details.

[robertmclaws]

@divega FYI, System.Spatial is here: https://github.com/OData/odata.net/tree/ODataV4-7.x/src/Microsoft.Spatial

@jumpinjackie Those libraries have no docs, and few samples. No thanks. This is something that Microsoft needs to take responsibility for shipping, so that the core types used by SQL Server are the same ones used by .NET. Each provider should be responsible for internally translating the provider type to the .NET type without core layers having to take a dependency on provider types.

[xperiandri]

The questions if and when will it be updated to support .NET Core

[gdoron]

@rowanmiller :

Note to self: leaving unassigned as we will prioritize as part of 1.2 planning next week

Any update? We are waiting for it too, even a rough estimation will be good enough.
Should we expect getting the feature in the next release/Q/year?

Thanks.

[figuerres]

@rowanmiller : lack of Geo types is keeping the project i am working on stuck on .net 4.xx and EF 6.xx.
just my vote to get this solved please !!! I work for a logistics company so locations are very important for our core business.

[xperiandri]

Agree

However you can workaround it with Geodesy, mapping and stored procedures while

model

``` C#
[Column]
private float Latitude { get; set; }

[Column]
private float Longitude { get; set; }

[NotMapped]
public GlobalCoordinates Location
{
get
{
return new GlobalCoordinates
{
Latitude = this.Latitude,
Longitude = this.Longitude
};
}
set
{
this.Latitude = Convert.ToSingle(value.Latitude.Degrees);
this.Longitude = Convert.ToSingle(value.Longitude.Degrees);
}
}

#### insert

await context.Database.ExecuteSqlCommandAsync("EXEC InsertCarRequest {0}, {1}, {2}, {3}, {4}, {5}", System.Threading.CancellationToken.None,
carRequest.Id,
carRequest.PassengerId,
carRequest.Location.Latitude .Degrees,
carRequest.Location.Longitude.Degrees,
carRequest.PassengersCount,
carRequest.Type);
```

[robertmclaws]

Which is fine if you want to work with stored procedures. If you don't, you're out of luck.

[rowanmiller]

We're doing https://github.com/aspnet/EntityFramework/issues/7434 in 2.0 as a stepping stone for this feature.

[gdoron]

@rowanmiller but Spatial won't be ready for 2.0, am I right?
Can you give (even if not accurate) estimation of when it will be ready? Q2/Q3/Q4 2017/8?

Thanks Rowan.

[rowanmiller]

The work we do in #7434 will likely make it possible, just full native support won't be included in the box. One option we are considering is for spatial support to be a separate library that builds on the feature.

[roji]

I think the important question here is whether there's a goal of providing portable, cross-database spatial or not.

If portable spatial is not a goal, then there's nothing really stopping providers from exposing their spatial support right now. In fact, Npgsql already allows you to define Npgsql-specific spatial (PostGIS) types on your entities and have those mapped to database columns. #7434 seems like it would make such the provider maintainer's work easier, but nothing beyond that - unless I'm mistaken it won't allow anything that isn't already possible today (just with more work). So if portable spatial is not a goal this issue can probably be closed.

If, however, portable spatial is a goal, then this issue is more about providing a database-independent spatial object model to which the different providers would map their specific types. Unlike the EF6 object model, it would have to be extensible to at least some degree to allow for provider-specific features, etc.

In addition, it seems that spatial isn't just about mapping objects, but also allowing spatial queries - a standard (LINQ-based) way to express spatial queries. It seems like this would depend on a common portable spatial model.

[robertmclaws]

It sounds to me like they have the right plan and are on the right track. You have to have a solid, extensible underlying type mapping system before you can have support for a specific type. Once they have that in place, they should be able to support spatial types, then queries that leverage those types. Hopefully then during that time frame we can get the SQL Server team to adopt System.Spatial as their underlying type, and we can be good to go 😀 🤞.

[figuerres]

just let me know when it works for ms sql server .... all the details are nice but i just need to make it work.
nothing against all the good ideas of a universal data type etc....
but for ME we just need to work with sql server today.
so for now i stick with the "classic" untill this is solved.

[RobertDM]

The existing EF spatial users who are left in a lurch by this oversight are using SQL Server. At the very least, a solution for mapping/migrations should be made a priority - linq/query support can always be worked around.

[nateoelrich]

+1 for spatial support. What is the rough timeline for the release of EF Core 2.0?

[dagope]

+1 too!

[ipleh]

+1 for spatial data.

[bragma]

Hi,
considering I don't need spatial operations right now (but I may need them in future), what's the suggested way for storing spatial data right now, considering a migration to proper spatial types will be needed later?
Thanks!

[skendrot]

SQL Server has built in ways to convert from Well Known Text and Well Known Binary to Geometry. You could store either of those and convert to a different spatial type after querying.

[ikourfaln]

@skendrot, can you give us an example? Thanx for advance.

[skendrot]

https://msdn.microsoft.com/en-us/library/bb933823.aspx

[ikourfaln]

@skendrot,
I see, Thank you.

DECLARE @g geography;
SET @g = geography::STGeomFromText('POINT(51.425934 35.701737)', 4326);

[hatefghani]

Hi,
I found a solution guys! you can use this until _EntityframeworkCore_ supports spatial

follow the instruction :

First of all you need to add a field with geography data type, then you need to run this commend in one of migrations up class :

protected override void Up(MigrationBuilder migrationBuilder)
{
    migrationBuilder.Sql("ALTER TABLE [dbo].[Cities] ADD [Location] geography");
}

if you are using UnitOfWork you can update Location field after you inserted a record like this :

    {
        City city = new City
        {
            Title = creator.Title
        };

        _cities.Add(city);

        _uow.ExecuteSqlCommand("UPDATE Cities SET Location = geography::STPointFromText('POINT(' + CAST({0} AS VARCHAR(20)) + ' ' + CAST({1} AS VARCHAR(20)) + ')', 4326) WHERE(ID = {2})", city.Longitude, city.Latitude, city.ID);

        return RedirectToAction("Index");
    }
    catch
    {
        return View(creator);
    }

And now if you want to find nearby cities in you can use this commend :

        var cities = _uow.Set<City>()
            .FromSql(@"DECLARE @g geography = geography::STPointFromText('POINT(' + CAST({0} AS VARCHAR(20)) + ' ' + CAST({1} AS VARCHAR(20)) + ')', 4326);
                       Select ID, Address, CreationDate, CreationDateInPersian, CreationDateStandard, CreatorRealName, CreatorUserID, ExLanguageID, IsActive, IsDeleted, Latitude, Longitude, ModifierRealName, ModifierUserID, ModifyDate, ModifyDateInPersian, ModifyDateStandard, PhoneNumbers, Summary, TimeStamp, Title, Image from Cities
                       ORDER BY Location.STDistance(@g) DESC;",
                       35.738083, 51.591263)
                       .Select(x => new AllRecordsViewModel
                       {
                           ID = x.ID,
                           Title = x.Title
                       })
            .ToList();

    return View(cities);

// result for nearest cities :

1.Tehran
2.Ankara
3.Paris
4.Washington DC

Remember! you should select all of records except that record with geography data type!

[ikourfaln]

@per3ian thank you. But what about Coupling !! it works but it's a bad design. If someone changed the structure of the City entity we need to change the SQL query or it will breaks.
Anyway, temporary, it is a good solution. Thanx again.

[hatefghani]

@ikourfaln absolutely! I know this is bad design & structure but it is very good for now. we can not design in other way until EFCore 2 release. i waiting for spatial like you and other guys but we have not spatial features in this version. All should know this is temporary way.

[Choleriker]

+1

[plamen-i]

I'm second behind using GeoAPI and NetTopologySuite:

https://github.com/NetTopologySuite/GeoAPI
https://github.com/NetTopologySuite/NetTopologySuite

They are common denominator behind many GIS libraries.

[robertmclaws]

The point is to create a minimal set of core types that can be used on portable platforms, not re-create a huge spatial querying library. I do not want to take a dependency on NetTopologySuite the project where the core types for my system reside, because that will add unnecessary bloat to my apps.

In an ideal world, those core types would have minimal implementation, then @FObermaier and @xivk would adjust their implementation to leverage THOSE core types for their spatial logic. That would let me use GeoAPI in my back-end code (where the actual spatial queries will happen), but still have front-end code that supports the proper types without needing additional spatial logic.

[xperiandri]

NetTopology suite is too complicated.
https://github.com/juergenpf/Geodesy is much simpler

[plamen-i]

As I see, https://github.com/juergenpf/Geodesy is oriented towards calculations. It contains no geometry types in form they need to be stored in GIS-enabled database (point, line, polygon, etc.) and participate in GIS-enabled database level operations (SQL-based intersect, contains, touches and others). As I see, main goal of EF in GIS support should be to leverage these operations at database level.

[plamen-i]

@robertmclaws I agree with you. And I see an easy answer in this situation - that's the way GeoAPI is planned. AFAIK NetTopologySuite uses in its code GeoAPI geometry interfaces only. So a GeoAPI geometry interfaces implementation in minimal EF Core geometry types would allow us to read/write geometries in database and use them without converting with NetTopologySuite where appropriate (back-end) and without carrying dependency to NetTopologySuite in front-end.

[FObermaier]

Thanks @robertmclaws for drawing my attention to this topic.
We'd be more than pleased to adjust/refactor GeoAPI that it'd be suitable as the spatial type for EF7.

[stijnherreman]

Can someone confirm that I understand this correctly: there currently is no way to port a library to .NET Standard, when it uses types from Microsoft.SqlServer.Types (e.g. SqlGeometry), and the situation will remain like this for the foreseeable future?

(This may not be entirely related to EF, the library I'm trying to port uses DbDataReader, but I assume there's some overlap with EF here.)

[robertmclaws]

So, because .NET Standard 2.0 targets 4.6.1, you CAN actually use it, IF you're installing the library in a .NET 4.6.1 app. If it's in another platform, it will not function, since Microsoft.SqlServer.Types is a native DLL.

I'm using this approach in my .NET Standard 2.0 implementation. One way I got around the problem was that I factored the Spatial-specific stuff out of my core types, and just started storing the WKT string in the DB.

I have a class I call DbAddress that inherits from my core Address, and has a DbGeography property called GeoCode. That inherited class is ONLY in my Data assembly that has my EF model, and is ONLY used on the back-end. GeoCode's getters and setters translate the WKT into something usable to query against. More cumbersome than I would like, but it works OK. Definitely not ideal, by any means.

HTH!

[King-G]

Here is my workaround:
(check the answer by Eli)
DbGeography Workaround in EF Core

Edit:
We won't see DBGeography for a while, it's not even planned for 2.0 release.
Hah, dislike this if you want, I'm just trying to make the best out of the situation.

[Jonatthu]

@robertmclaws So in dot net core 2 Do we can use using System.Data.Entity.Spatial;?
But this without loosing the portability that offers dot net core?

[yang-zhang-syd]

+1

[robertmclaws]

@Jonatthu You can use System.Data.Entity.Spatial on .NET Core 2.0, but only on .NET Framework 4.x apps. Because it has a dependency on Microsoft.SqlServer.Types, you can't use it in mobile apps.

[Giancarlos]

@robertmclaws So to confirm I'm not misunderstanding, when .NET Core 2.0 is released the System.Data.Entity.Spatial package / lib will be availabe to use in a full .NET Core application? Or did you somehow mean .NET Standard 2.0? Just want to be sure I'm on the same page as you.

[figuerres]

BY the way folks ....
I learned something last week that i think should be a topic of what should be discussed:

the current EF version of sql geography is limited and lacks some core functions.
for me i had to load the sql clr geography types so that i could enumerate the polygons in a multipolygon.
this to me a much better than trying to sting parse a complex structure.

so if the call is to do a clean portable library make sure it can enumerate the elements and not be dumbed down.

[King-G]

@Jonatthu
If you're targeting .net core. No.
If you're targeting .net frameworks, then yes, but in a lot of situations it's futile to use .net core if you target .net framework.

[KhalloufHassan]

wasn't this supposed to be released with EF Core 2.0?
is there any news about it? I working on a new project that depends heavily on locations and areas

[figuerres]

Just a reminder to Microsoft staff, this issue is blocking projects from using .net core, so if Microsoft wants to shift to supporting .net core and away from maintaining. Net 4 x then this needs to be addressed.

[McGittyHub]

This is currently the only reason why we won't be using ef core for our next project. Please solve this asap or add a workaround

[figuerres]

yeah , portable and modular .net is a good thing but do not stop supporting the rest of the Microsoft family to get there.... Geo support is important to several kinds of enterprise apps.

[ikourfaln]

Geo support please

[elciopa]

Currently we cannot move to .Net Core due to this missing feature. Please support it!

[nkovacic]

We have a lot of location based WebApi projects and we are waiting for this missing feature to be implemented before we can migrate to .NET Core.

[Jonatthu]

I want to begin a project but I need this feature

[figuerres]

@Jonatthu
for now i would start with the 4.6 or 4.7 full framework and ef 6.x .... you can switch later when they get this in the new bits....

[mhosman]

Hey, still without Spatial Data Types? This is an issue that was opened in 2014, I have a project in .NET Core and I can't use Spatial Data Types from my code first. Come on Microsoft!!! This is a priority 1 feature. This is not serious.

[Giancarlos]

The only work around is to use the underlying ADO .NET functionality or the FromSql method that EF Core is built on top of and do raw database queries. It gets worse because you're not allowed to return FromSql anything that isn't defined as a DbSet, so if you have views (ASP .NET Core) that require part of your table (excluding your spatial fields) you still have to return all of the fields which forces you to use nasty FromSql statements where you could of just returned the needed columns. If they at least allowed mapping data results to any object this issue wouldn't be as awful as some of the solutions.

What's sadder is that we're not asking them to support some random feature from some random database solution, but their own SQL Server. The "workarounds" are not satisfactory either way.

This issue outlines other issues with EF like not being able to selectively map a table to any object or partial objects in other words allow us to ignore specific fields in a data model. I know these sort of things are not impossible considering Dapper lets you map to any object you throw at it (especially when you do a raw query), not just a "DbSet" object.

[figuerres]

Not a part of this issue but something to know is that right now there is not a working OData for core also.
so if you need / want Odata support that also will point your project back to the full .net for now.
it looks like OData is in the works and will be coming but not i think till next year from what i read.

[tinchoel10]

Hey Microsoft!! This still remains in backlog and is not available for next release 2.1?? This is not serious and is a pain in the ass for all of us.

[ErikEJ]

@tinchoel10 Swearing and generalizations will not get the feature made 😞

[McGittyHub]

@tinchoel10 I agree 100% at this point, it almost feels like Micro$oft has abandoned EF Core.

[ajcvickers]

EF Team Triage: We really appreciate the recent feedback on this issue. Feedback from developers is extremely important in determining how resources across the team are assigned. However, please keep in mind that that feedback is only one of several inputs into the planning process and that--like in all software projects--resources are limited and every decision to implement a specific feature usually involves postponing others.

To shed a little more light onto our planning process we have created some documentation on the common considerations we take when deciding what to work on next.

We read and consider all feedback (and try to make the right choices based on it) no matter how it is delivered; that is, whether it is provided politely and with respect or not. Yelling at us at best makes you feel good and us feel bad. It doesn’t achieve anything concrete. We are doing our best to prioritize features and implement them in the best way we can. We really appreciate your feedback and it makes a big difference on shaping the product. We personally appreciate it more when feedback is delivered in a respectful way, but please don’t stop providing the feedback.

[figuerres]

@ajcvickers thank you for the post, it helps if we do gte at least feedback from the "team" that they are reading the posts.

as you may gather from my posts i have a project that i cannot move until i have both the geography support and OData 4 , i know that OData is not in the EF run time, just wanting to give a good picture of what my application needs.

i can stay on .net 4.6 / 4.7 for now but several projects that my project depends on are moving the new development to core and that makes me ask when this will happen.

web api security - Identity Server
web api documentation - Swagger / Swashbuckle
are two main parts that we use in my companies larger work.

Angular client app and a Xamarin client app are both connecting to a common restful set of Web API interfaces.

[mhosman]

@ajcvickers thanks for your answer. We started to migrate to EF Core and now we are stucked because Spatial Data Types are not available. We think that this is a day 1 must-have feature. At least, meanwhile, it would be nice if you post some (not so tricky) workaround. I just need to create and get polygons from SQL Server and check if a coordinate is inside/outside a polygon. I know that I can do this with a raw query but my database is created from code-first with a defined model. Maybe a string attribute and a trigger in SQL Server that converts that string, temporary in a Geography type and it makes all calculations?

[figuerres]

@mhosman
possibly have a second api that uses the full .net and do some of the geo things with it ?
there is a nuget i used for some geo stuff that is really good and the native sql clr dlls can be used with full .net to give access that EF 6 does not have.
if you want more info send me a pm. i do not want to get to off topic here...

[stijnherreman]

@ajcvickers that's useful to know, but what I gather from your comment is that there are no short-term plans to work on this. You'd have told us if there were any plans, I assume.

[tinchoel10]

@ajcvickers This issue started in 2014. This is not serious. 3 YEARS!!!! You are Microsoft, not just two guys working and trying to do some effort. You announce .NET core with so much fanfare a lot of time ago (this is not new), and this day 1 issue is still in "Backlog". That's serious for you? I think that as a team you are making things just wrong and you are losing a wave of developers that wanted to move to .NET Core but they can't. I'm really disappointed.

[figuerres]

@tinchoel10
Hey while i also find this an issue the way you are posting is not going to help much.
@ajcvickers is not "Microsoft" and may not even work for "Microsoft"
as this is a project that WE can submit code to.
even if he is working for "Microsoft" we do not know what level he works at or what power he has.

all you are doing is creating an impression here that you are hot tempered and angry.

[Jonatthu]

@tinchoel10 I don't think this is the right way to talk to a team that is working hard to bring us all EF Core to the community.

I agree that this is a priority feature that in modern applications is so necessary, since almost every app this days provide a location feature.

If somebody feels with the right to insult or yelling this team go ahead and make pull requests, use that energy to help since this is open source.

I'm disappointed of the devs that are not being respectful.

But coming back the topic, yes I really need this feature and I hopes we can see this in a backlog very soon.

@ajcvickers please ignore the bad responses, we really appreciate the work has done until now !

[DGuidi]

@Jonatthu sad state of almost OS projects out there: rage for fixing things and add features and lot of people using projects but no one actually want to help same projects, even simply opening issues correctly filled

[Giancarlos]

@DGuidi the issue was filed in 2014 just fine and clear as day in the first post, nobody has wondered what the original post was talking about, that's as clear as it gets. Everything after has been the experience of people doing workarounds. The only easy workaround is to do FromSql statements, but even those get a bit tricky at times. Some of us have code in production that requires these features to work, and we don't have time to work on Open Source projects because our jobs and lives get in the way. If anyone at Microsoft would like to follow up with me about my issues I'd happily give them my time and energy if it meant getting at least started on this feature.

[ajcvickers]

Thanks again for all the feedback. We wanted to provide a bit more information on the current situation for spatial types in .NET/EF. Some of this is likely not what you want to hear or very palatable, but it still seems better to be open about it.

In .NET Framework, spatial support looks something like this:

• SqlClient has .NET Framework types for SQL Server spatial support
  
  
  
  • These types use a lot of native code
  
  
  • There are specific to SQL Server—i.e. they won’t work with Postgres, etc.
  
  
• EF6 has specialized spatial types
  
  
  
  • For SQL Server, these types build on the SqlClient types
  
  
  • These types are somewhat less coupled to SQL Server, but not much
  
  
  • The design of these types is not very good (coupled to SQL Server/static initialization issues, etc.)
  
  
• There is a Microsoft.Spatial library that was created primarily for OData
  
  
  
  • It was designed to be not tightly coupled to SQL Server and we may be able to bring it to .NET Core and use it more generally
  
  
  • There are open source spatial libraries out there as well, which may or may not be a better option--we haven't done detailed investigation yet
  
  

Ideally, what we would like for .NET Core is:

• A database and platform independent spatial library
• The ability to query spatial columns in ADO.NET and have database-independent spatial types materialized
• Integration of these types into EF Core so that LINQ translation, etc. will work correctly

It’s going to be hard to get to this ideal, so we may end up with some hybrid approach. For example, it may be that the ADO.NET types are still database-specific, but that EF Core provides the abstraction to database-independent types.

Some other observations:

• Moving the existing SqlClient types to .NET Core is not trivial, primarily because of the native code
• Whatever we end up doing in EF Core will likely be different from what is in EF6

In the short term, we are investigating making it easier to use the SQLClient spatial types in EF Core, but this will likely be limited to:

• EF Core running on .NET Framework (since the types don’t currently work for .NET Core)
• SQL Server specific
• Not deeply integrated with LINQ queries, etc.

Beyond this, putting everything together into a cohesive plan is a lot of work that obviously spans more than EF. We’re working on this, but realistically it’s not something that is going to happen quickly.

[RobertDM]

@ajcvickers This short term proposal would be extremely welcome for existing SqlServer projects. Deep query integration can always be worked around with direct SQL queries, as long as there is basic spatial type mapping in result sets and code first.

[mhosman]

@RobertDM I think that @ajcvickers says that this short term propsal could be available only for .NET Framework, not for .NET Core.

[figuerres]

I think the "short term" is use the full .net framework and ef 6 ... we can get things done and not wait for this.
but also keep telling them how many projects are not able to move.

honestly i personally do not care about the lofty goals of having this work with all the different servers.
not that the idea is bad at all, just that for me i only need one to work.
make that work and then i can use the new code.

the rest is just not a priority for my work.

just my side of this, others may need PostgreSQL or other servers.

[mrlund]

Like many others here we've been waiting for spatial support for a LONG time too, and had even started a project that depends on it, assured in the belief that support would most certainly be in 2.0 release, which would arrive before we needed to launch.

It's very disappointing that this extremely common feature continues to elude us, but in an attempt to try and be constructive here, I think it would be great to have:

1. An "official" workaround published by the EF team, using excluded properties and FromSql()
2. An MVP approach, with the minimum viable solution first, providing just the bare bones for SQL Server (which may help most cases move forward), and then adding the more advanced stuff in subsequent releases. As is often the case, I believe that 20% of the features/effort could cover 80% of the use cases.

[ErikEJ]

@mrlund The framework is extensible and open for Pull requests 😄

[mhosman]

@ErikEJ The framework is extensible and open for pull requests, but not all of us have the knowledge to make changes of this kind. Most of us are developers using a platform. We can contribute with ideas, comments, maybe with some code, and using the platform itself. We just think that this is a must-have feature for such platform (very limited by the lack of these characteristics). It would be wonderful if you give a higher priority to this feature.

[ErikEJ]

@mhosman It only has to be one or two of you!

[McGittyHub]

@ErikEJ this is not trivial to implement at all in EF core. As "customers" just posting in here is probably the best we can do. This is a feature that was promised for 2.0 afaik

[roji]

@ajcvickers just to say a big +1 on your general approach to the problem, from a PostgreSQL/cross-database perspective. Simply providing EF6-level spatial may help some people out in the short time but is definitely not the right long-term story for EF Core. I hope we end up with a powerful, extensible database-independent spatial abstraction that would allow exposing database-specific features while still allowing general database-independent querying etc.

FYI in the meantime, in PostgreSQL/Npgsql it's possible to use the PostgreSQL-specific PostGIS spatial types with the EF Core provider, although that's definitely a sub-optimal solution.

[dpedrelli]

@ajcvickers Thanks for the explanation. Is the plan to define the spatial data types in EF or in .NET? I ask because I just want the data types for my entity classes. I don't want to add EF to a class library that just defines my entities.

[ajcvickers]

@dpedrelli Ideally not in EF, but that isn't a concrete plan yet.

[figuerres]

when it comes to a library for the types: please base it on the sql clr library not the ef 6 library.
why:
ef 6 only has a limited set of classes, methods and properties that do not fully expose the sql geography data. in my projects i have the clr types and an added library to help me deal with that, i have to convert a ef 6 geography wekk known binary to a full one then do work then convert back to what ef 6 can store back.

this happens when the geography might be a complex shape and i need to select part of it or to union 2 or more shapes to make one larger outline.
also i great new feature that could be added is the option to convert a sql geography binary to a GeoJson object and to take one and make the WKB to store.
for example i work with Google Maps and we read zipcode outlines from sql and convert them to GeoJson for use with google maps DataLayer objects.

if we had a .ToGeoJson() for example that would remove an extra library from my project.

[ajcvickers]

Just an FYI to this thread that the initial work for the short-term plan described a few comments above is now merged and will be in the 2.1 release. This change allows properties of type SqlGeography and SqlGeometry (and HierarchyId) to be included in entity classes and mapped by convention to corresponding database columns.

WARNING: This is by no-means full spatial support for EF Core. These are the important limitations:

• It only works when running on .NET Framework. It will not work on .NET Core, since SqlClient for .NET Core does not support these types.
• Query support is limited:
  
  
  
  • LINQ queries that don't use the semantics of the SQL types should work.
  
  
  • Queries that need to use the type semantics (e.g. using STDistance) can be done with FromSql, but not LINQ
  
  
  • See issue #10108 for some test code and issue #10109 for some ideas to make LINQ work better
  
  
• The SQL Server spatial types do not provide a good client-side programming experience. A proper .NET spatial library is needed for a good, cross-platform experience.

[Giancarlos]

It's good news to know support is being added, it's not the greatest to know it's mostly towards .NET Framework, but at least it is a start. What would it take for the community to be able to add support for Spatial types for .NET Core? The way .NET Framework implements it afaik is a sort of black box since Microsoft never open sourced their Spatial libraries it would require information on the best approach to implement an open source version of it.

Thanks for the update @ajcvickers I will soon be making a post about how I've gotten SqlGeometry working with .NET Core and what does / and what doesn't work.

[ajcvickers]

Question for all the people following this. Long term plan here is for EF Core to work with a variety of client-side spatial libraries. Which spatial libraries should we focus on? Which is the "best" and why?

[ajcvickers]

@Giancarlos et al. We have been doing more investigations and thinking in this area. The plan is currently this:

• Interact with the database using well-known binary (WKB) or well-known text (WKT).
• Define an adapter or similar that can be implemented against various spatial libraries that would:
  
  
  
  • Know how to convert to and from the libraries types and WKT/WKB
  
  
  • Add LINQ translations for methods/operations in the spatial library to appropriate SQL for the database provider
  
  

From what we can tell so far, the approach should work for SQL Server and other databases.

For SQL Server on .NET Core the most promising approach seems to be store-side conversions. That is, EF generates SQL that translates raw database values into WKT/WKB before they are returned from a query. Likewise, parameters would be translated in SQL back to the native types. This means that the .NET Core code does not need any support for provider-specific client types, so the lack of SqlGeography and SqlGeometry does not matter.

A second possible approach is to read the raw bytes from SQL Server and use a client-side conversion to create WTK/WKB. We would have to write a parser for the raw bytes, but that seems doable. When passing parameters back, we would convert back to the raw bytes before sending to the server. This all seems to be possible on .NET Core, but it requires more work and introduces a coupling to the format of the raw bytes, so if the server-side conversions in the first approach pans out we're likely do that instead.

Any feedback on these approaches is appreciated. None of this is set in stone--it's just prototyping and brainstorming at this point.

[dpedrelli]

@ajcvickers What is necessary to be able to use a Geography::Point data type with Code First Migrations?

I have a computed column, based on latitude and longitude. I am creating the database with a Migration and then adding a computed column of type Geography::Point(), with a script. I am doing calculations with a stored procedure. I, really, only need to add the Geography type to my model for the Migration.

[ajcvickers]

@dpedrelli What version of EF Core are you using?

[dpedrelli]

@ajcvickers EF Core 2.0.

[ajcvickers]

@dpedrelli Spatial data types are not supported in EF Core 2.0. There is some post 2.0 work that hasn't been released yet, which is referenced in my message 5 days ago. It should be available on the nightly builds, although I haven't checked the status of these following our infrastructure issues, so I'm not sure how up-to-date they are.

[robertmclaws]

I don't care what approach gets used, I'm down with whatever. My only requirement is that I can use my modified EF code generation T4 templates to generate the entities into a stand-alone project that has ZERO dependencies on the Entity Framework or the SQL Spatial native libraries.

Anything less than that is not good enough.

[dpedrelli]

@ajcvickers my post was in response to your questions about which 3rd party libraries should be supported. My only need, for the foreseeable future, is for Migrations. Whether it is native support (which is preferable) or through a 3rd party library that is my only concern. I am perfectly fine using stored procedures for any spatial calculations.

[figuerres]

for me make MS SQL database work at least as well as the EF 6.xx code works with them.
but if possible make the exposed type the same as the SQL CLR type not the reduced one that EF 6 exposes. even if V1 of this has some "Not Implemented" exceptions thrown....

[DGuidi]

+1 for wkt + wkb as a starting point at least, then maybe take a look at GeoAPI

[Giancarlos]

My suggestion for the EF Core team is to make it so we can get WKT back or similar. GeoJSON support in the future would be fantastic too, but not as crucial.

As for anyone trying to get EF to build a geometry type when EF migrations runs this is what I did using Data Annotations:

    [Column(TypeName = "sys.geometry")]
    public string Boundary { get; set; }

[Giancarlos]

I also noticed if you send a WKT through EF it works for update / insert, it's getting back data that's the problem so far. I guess MSSQL casts the WKT back to a geometry type internally.

[vitalbsk]

Any update at the moment?

[edwardyehuang]

Any update atm?

[ikourfaln]

Any Update ? any Milestone ?

[mhosman]

No response since november... who is in charge of this project? how much lack of seriousness!

[ajcvickers]

This issue is in the Backlog milestone. This means that it is not going to happen for the 2.1 release. We will re-assess the backlog following the 2.1 release and consider this item at that time. However, keep in mind that there are many other high priority features with which it will be competing for resources.

[RobertDM]

The EF Team seems to think that spatial support is some sort of esoteric feature.

I would respectfully remind them that the spatial types people are now clamoring for were fully supported in EF 6, and that removal of a datatype is about as severe of a breaking change as you could ask for.

[mrlund]

Seriously?! Spatial is a BASIC feature that many apps depend on, and - I'm willing to bet - are trying their best to work around until EF adds proper support.

VERY disappointing to hear it's been cut from 2.1 as well! I'm starting to think we'll never see spatial support in core.

[figuerres]

I think the problem is more like this: what the we need is to know how many projects today need the feature and are staying with EF 6 until EF Core has it ?
not many folks are posting here so it's all a guess.
if a lot of projects were built before .net Core and are not being updated then we do not know about them. they may never get updated.
so if they hear only 5 or 6 users asking then it's not a huge priority if there are other things they are doing that have dozens or hundreds of users asking form them.

vocal minority problem , a classic one in development ....

[elciopa]

I'm pretty sure we are not just 5 or 6 users...most of us are silently watching the evolution of this request

[mhosman]

And... this is not an issue that was opened some months ago... more than 3 years!!!

[pauldendulk]

Oh, if I understand @figuerres correctly we should all be posting here :) Well, here it is then!

[nimbusparis]

Guys, I hope none of you deseperatly wait for this feature to start his project! If the feature is not existing, work around and move on!
By waiting the perfect conjunction of features, you would never start to code...
Btw, it's not only that EF Core not support DBGeometry, some DB engine neighter, such as SQLite...

[figuerres]

Well on my work i am sticking with .net 4.xx and EF 6.xx - that works and we are not waiting.
from time to time i chime in here as i would like to switch someday.
maby by the time we see .net 4.xx go to .net 7 or 8 and EF6 goes to EF10 then EF Core will catch up ?

thing is weather the number is 6 or 60 is not the exact issue, it's how many requests they get compared to other parts. while we are real developers using EF i do bet that fro each developer using geography features that are 10,000 developers who do not and will not.
it's just the way it is.
lots of apps have no need for locations or shapes.
the ones that do really really do use it and need it but what % of all databases use it ?

[ikourfaln]

I'm disappointed, not included in 2.1 !! 😢
And this feature was requested more than 3 years ago !!!
We don't want to refactor or migrate our data model to a new one, this feature is required not BaSiC!

[slavanap]

I was quite, but what's wrong with you, people?! .NET Core is open-sourced project, and if you desperately want some feature, implement one and do a pull request!

[ikourfaln]

@slavanap
It is not easy, ASP.NET Core team follow patterns and guide styles internally for best practices.
Must of the external contributor (like me 😉) are helping them by reporting bugs and request useful features, so they can grow up their business, especially with Azure, that's how I think and everyone is happy.

[figuerres]

also @slavanap the talk on this has been that they want a clean path that does not use the old microsoft code .... so to make this work means a lot of code to be written, and do we do it for one database or for multiple (Microsoft, Oracle, MySQL,Postgress, etc) so this is not a simple "add a method" thing.

[robertmclaws]

@slavanp You can't just magically open a pull request and write the code for this feature. It's dependent on native database implementations, see https://github.com/dotnet/corefx/issues/12034. Part of my proposal and plan was to have the team build it in a way that the SQL Server team could actually use THAT implementation in SQL, instead of forcing apps to have a dependency on SqlServer.Types (which is not a .NET Standard 2.0 library).

That is the only way this problem gets solved without creating 5 new problems with the solution.

[roji]

Just posting in case newcomers have missed this: EF Core 2.1 does add spatial support, please read this comment above. Long story short, the SqlGeometry/SqlGeography types will be supported on SQL Server on .NET Framework.

For proper cross-database and cross-platform support much more work and thinking is needed. At least as the maintainer of the PostgreSQL driver I'm happy the team didn't rush into this and do a half-baked solution.

[mhosman]

@roji, in 2.1 this will not available for .NET Core, just for .NET Framwork.

[Jonatthu]

What about if the EntityFrameworkCore Team add a new repository called EntityFrameworkCoreSQLExtensions, EntityFrameworkCoreMongoExtensions, etc
and there it can be added the SqlGeography types and what ever other custom implementations

[ajcvickers]

Hi all. There is a lot of passion here, which we appreciate. Can I tap into that to ask some questions about the specifics of what people are looking for? If you're following this and want spatial support in EF Core, then please answer the following questions:

• What framework are you targeting? (e.g. .NET Framework, .NET Core)
• What type of application are you targeting? (e.g. web app, WinForms app)
• What operating system are you targeting? (e.g. Linux, Windows)
• If you are using spatial types right now, then what are they? (e.g. DbGeography/DbGeometry from EF6, SqlGeography/SqlGeometry from SQLClient)
• Ideally, which spatial types would you use with EF Core and why?

Feedback is greatly appreciated!

[mhosman]

Hi @ajcvickers

Here my answers to that questions. Thanks!

• .NET Core / EF Core
• Web app (API)
• Windows
• DbGeography (in other of my old projects)
• I need to calculate distances, check if a point is inside a polygon, etc. For example, now I have a workaround to check if a point is inside a polygon. To do that I just create an attrubute "Polygon" (string) inside my model (in order to store a polygon as string). Later, in the Controller I do this:

await _context.Zones.FromSql($"SELECT * FROM dbo.Zones WHERE geography::STGeomFromText(CONCAT('POLYGON((', Polygon, '))'), 4326).STIntersects(geography::Point(" + longitude + ", " + latitude + ", 4326)) = 1").Skip((page - 1) * limit).Take(limit).AsNoTracking().ToListAsync();

[Jonatthu]

@ajcvickers
I am doing what @mhosman is doing, those would be my answers.
I recommend to open a pull or something to have the answers to those questions.

[figuerres]

1) Net: currently 4.6 untill i can use EF on core
2) our app is an angualr 4/5 spa that calls web api 2 and Odata 4 api
3) Windows Server 2016 with IIS 10 / possibly azure also
4) DbGeography/DbGeometry from EF6, SqlGeography/SqlGeometry from SQLClient) and GeoJSON
5) we might go to GeoJSON more over time as that works well with Google Maps Data layers, our app is a full end to end system for a parcel shipping / delivery company so we are plotting driver routes, zip code areas, route areas, market areas, states, single locations and combination of them. currently we store a "location" column and a "shape" column in tables in SQL 2016, we use them in SSRS and Web pages with google maps. as we have many tables that have geo data we do not want to split our web services up to move the non geog stuff to core, two api servers with different calls would be a pain to manage.

[nkovacic]

Hi @ajcvickers

• .NET Core / EF Core
• Web app (API)
• Windows
• DbGeography

We are using the Spatial types for proximity searching, so order by closest is critical for us.
Our migration to .NET Core is blocked because proximity is a core functionality of our product.

[andriysavin]

@ajcvickers

• .NET Core (well, actually I have to stick to .NET Framework runtime since I have to use EF6, but other stuff is ASP.NET Core and netstandard libs)
• Web App (API), Console App (for running some processing)
• Windows (would also target Linux to host in Linux containers, but again - have to use .NET Framework)
• DbGeography
• Ideally, I want to be able to use any custom type for storing geography information and map it to some common (provider-independent) EF geography type. I prefer DDD, so I don't want my domain library to depend on some infrastructure libraries like EF just to use something like DbGeography. With EF6 I use a trick to accomplish that: in repository implementation my model is augmented with a linked entity (kind of shadow entity) containing DbGeography, and on saving changes I take geography from my custom type and make it into DbGeography on the linked entity. Ugly, but with EF Core even that isn't possible.

Recently I made an experimental port to EF Core to be able to use Core runtime and at last target Linux Containers, but this forced me to use raw SQL (via FromSql) - which is very inflexible and hard to maintain.

[ikourfaln]

@ajcvickers

• What framework are you targeting? (e.g. .NET Framework, .NET Core)
• [x] .NET Framework 4.6.1
• What type of application are you targeting? (e.g. web app, WinForms app)
• [x] Web app (ASP.NET Core 2.0)
• What operating system are you targeting? (e.g. Linux, Windows)
• [x] Windows (!! we are targeting .NET 4.6.1)
• If you are using spatial types right now, then what are they? (e.g. DbGeography/DbGeometry from EF6, SqlGeography/SqlGeometry from SQLClient)
• [x] DbGeography but I'm confused, as I know, it is required to use DbGeography to work with EF !!
• Ideally, which spatial types would you use with EF Core and why?
• [x] DbGeography but we need a type that supports all SqlGeography functionalities

Thank you.

[mrlund]

@ajcvickers :

• .NET Core 2.0 / .NET Standard
• ASP .NET Core (MVC/WebAPI)
• Windows / Azure
• DbGeography in old project. Stalling/workarounds in new one(s).
• Anything that allows me to store a geo point on a record and query distance from another point (i.e. distance to each point, and all records with x distance)

[Giancarlos]

What framework are you targeting? (e.g. .NET Framework, .NET Core)

.NET Core, as far as I know .NET Framework supports it fine, but the library is proprietary to Microsoft (code is not open source at all).

What type of application are you targeting? (e.g. web app, WinForms app)

ASP .NET Core MVC Application using EF Core.

What operating system are you targeting? (e.g. Linux, Windows)

I'm guessing our Azure Apps instance is running on Windows, but it would make more sense to me if it supported all platforms that .NET Core can run on, including Mac and Linux.

If you are using spatial types right now, then what are they? (e.g. DbGeography/DbGeometry from EF6, SqlGeography/SqlGeometry from SQLClient)

SqlGeometry from EF6 sort of... It's a hack what I'm doing to force EF Core to use it.

Ideally, which spatial types would you use with EF Core and why?

Any that SQL Server is using, it is what I'm using already. It is an older project, was coded in Silverlight and I ported it to .NET Core only to realize by the time it was too late that Entity Framework lacked support for standard SQL Server database features. Everything I've done to make it work currently is a hack on EF Core itself which is not okay at all. I also would inherit many speed improvements if it were natively supported. I do hope every spatial type in SQL Server at the minimum, but those supported by MySQL and PostgreSQL would be good too. I'm mostly wanting SQL Server Spatial types more than anything.

[figuerres]

to all: by the way i think all of us should be looking at GeoJson an interesting format if you do web stuff and also if you use google maps.

[tomap]

@ajcvickers (currently > future)

• .Net 4.7 (windows) > .Net Core (linux)
• Azure App Service (Windows) > Either App Service Linux or Docker elsewhere
• NancyFx 1.4 > NancyFx 2.0 (or Raw Web API)
• Dapper & DbGeograph (relying on EF for geography support)
• Usage: Order by proximity
• Workaround: switch to two fields (Lat/Lng) and convert those on the fly in SQL to order records

[mdmoura]

@figuerres Yes, I also need this and for a bunch of projects.

[abydal]

Migrating an ASPNET MVC/WebAPI (with EF6) to .NET Core.

• .NET Core / EF Core
• Web app (API)
• Windows
• DbGeography - use it for registering a location and displying it in a map.

[ikourfaln]

@figuerres @mdmoura
For me, it is a bad idea to use GeoJSON as a datatype, the good design is to use SqlGeography as datatype so you can have more functionalities and power on querying your data.
And keep using GeoJSON in your Web API Layer by serializing geography datatype because your Front-end component needs GeoJSON.

[hades200082]

Web app, .net core, want to measure distance between points and filter by / order by distance

[figuerres]

@mdmoura , @ikourfaln

yeah possibly making it a built in might not be ideal. but it is a needed format for things that use GeoJSON.

@mdmoura if you use .net 4.xxx there is a GeoJson package that can be used and works well for me.
GeoJSON.Net

i took the contrib project as source and made a few changes for my use. i'd say check it out.
i have a set of api's that do the JSOn for a google maps data layer and works great so far.

[figuerres]

PS: I also have some code to get a google driving path string and convert that to a SQL Geo , comes in really handy to be able to store the directions in a format that sql can read and also plot on SSRS map reports.

[xperiandri]

https://github.com/aspnet/EntityFrameworkCore/issues/1100#issuecomment-361671850

This change allows properties of type SqlGeography and SqlGeometry (and HierarchyId) to be included in entity classes and mapped by convention to corresponding database columns.

This is exactly what I need!
Just be able to map DB types to Geodesy NuGet and use stored procedures for calculations.

[SVoyt]

Is it possible to create extension for EFCore with Geometry type mapping? Is it possible to create custom mappings without rebuilding EFCore?

[ajcvickers]

@SVoyt Depends what platform you are on and what database you are using. But, for example, on .NET Core using SQL Server the answer is essentially no since SqlGeometry is not supported by SqlClient on .NET Core.

[SVoyt]

@ajcvickers I'm using Postgres, and there is a PostgisGeometry type in Npgsql.

[thepenguinmaster]

+1 on this

• .NET Core / EF Core
• Web app (API)
• Windows
• DbGeography

[Echarnus]

Still a bummer we cannot use EF Core yet as our applications are GIS based.

[pauldendulk]

@ajcvickers

...what people are looking for?

My primary interest is in a generic geometry library.

There are many geometry related open source projects (NTS, GeoAPI, SharpMap, GeoJSON.NET, Mapsui, vectortile-cs, Itinero). Often they use some geometry type in their interface. Since there is not one standard geometry library, there are many different ones, and there are many conversions from one library to another. I am interested in ways this incompatibility could alleviated. The geometry library used most often now is NTS. Perhaps it is also possible to find a solution where there is better compatibility but no choice for one single library.

What framework are you targeting? (e.g. .NET Framework, .NET Core)
A library with high compatibility. So a low .NET Standard version would be optimal.

What type of application are you targeting? (e.g. web app, WinForms app)
Any

What operating system are you targeting? (e.g. Linux, Windows)
Right now mostly Windows and Mac for Xamarin, but Linux becomes more important for us as well

If you are using spatial types right now, then what are they? (e.g. DbGeography/DbGeometry from EF6, SqlGeography/SqlGeometry from SQLClient)
For some projects I used this package: Microsoft.SqlServer.Types ("Please Microsoft - don't kill me"). Which worked great but not cross platform.

Ideally, which spatial types would you use with EF Core and why?
One which is compatible with many of the libraries out there.

[roji]

@pauldendulk thanks for this information, have you also considered Microsoft.Spatial? It supports .NET Standard 1.1 and appears to be quite complete in terms of type support, what's you're opinion of it?

Several of the libraries you mention haven't been updated in quite some time: SharpMap hasn't release a version since 2014, and NTS hasn't released since 2015 - although there seems to be some ongoing work at the moment.

[pauldendulk]

@roji Thanks for the suggestion. I did not look at Microsoft.Spatial before. It is .NET Standard 1.1, that's great. It seems to be missing things like GeometryFactory.MultiPolygon() at this point (or I am missing something? Following documentation here). With all geometry types supported and spatial operations added this would be a good candidate. A library like this should be outside of the odata or sqlserver domain.

NTS is under active development. Current work is going on on a .NET Standard version. There is a pre release available. Read here.

SharpMap may be going slower but it is stable and maintained. It's providers would be suitable for reuse by other libraries if they would share their interface. This is a reason for mentioning it here.

[roji]

@pauldendulk thanks for the information. Microsoft.Spatial does seem to have support for multipolygon - see this. I don't think there's any connection between SQL Server and Microsoft.Spatial - the former still seems to support on the old DbGeometry/DbGeography - but I agree this package should be taken out of odata (opened https://github.com/OData/odata.net/issues/1089).

I do agree that at this point NTS looks more actively-maintained and may be a more appropriate choice as it seems to have more of an ecosystem of libraries around it. However, if you or someone qualified could provide a detailed comparison between them that would be very helpful.

[DSilence]

What framework are you targeting? (e.g. .NET Framework, .NET Core)

For now, we're targeting .NET 4.6.2, we plan to move to .NET Core in future.

What type of application are you targeting? (e.g. web app, WinForms app)

Web app, running under Azure App Service.

What operating system are you targeting? (e.g. Linux, Windows)

Windows now, possibly Linux later

If you are using spatial types right now, then what are they? (e.g. DbGeography/DbGeometry from EF6, SqlGeography/SqlGeometry from SQLClient)

We're using NTS in the system.

Ideally, which spatial types would you use with EF Core and why?

For us, NTS would be ideal.

[ranouf]

Hi,

I would like to order my items by distance, each items has a latitude and longitude.
Is there a way to do it faster?
Currently, I created a Linq functions which has no equivalent to SQL, so the function is executed by the .netcore framework which is really consuming and make the api too slow.
Any Idea?

[Grauenwolf]

@ranouf Sounds like you are looking for the STDistance function, which is available in SQL Server and C#.

https://docs.microsoft.com/en-us/sql/t-sql/spatial-geography/stdistance-geography-data-type

https://msdn.microsoft.com/en-us/library/microsoft.sqlserver.types.sqlgeometry.stdistance.aspx

[pauldendulk]

@roji I don't fully understand the situation at Microsoft. It seems they have separate geometry implementations in EF and odata. These can not easily be ported to .NET Standard because of native dependencies. I have worked with Microsoft.SqlServer.Types in the past. As far as I can judge, this was a quite complete geometry implementation, like NTS. Both are based on the OGC simple feature specification.

A concern for Microsoft is to help developers currently using geometry types in EF for .NET Framework to port their applications to .NET Core.

I am more generally interested in cross compatibility for spatial libraries. This could be done by choosing one spatial library to rule them all, but there might also be other options to improve compatibility. For instance easy exchange through WKB or WKT, or adding functionality to map you spatial queries to any spatial library. Also see this thread.

[ranouf]

@Grauenwolf yes, this one, I tested it with scalar function in entity framework 2.1, all the time I throw an exception instead of calling the sql function.
It could be interesting to implement the distance in EF.
Any plan for this?

[Grauenwolf]

Honestly, even with the full version of EF I avoided the geospatial functions and instead did all my work using raw SQL. I wish EF supported this, but we barely have support for views so I don't see it happening any time soon.

[roji]

@pauldendulk here's the state of things to the best of my understanding (keep in mind that I don't work at Microsoft, I'm just the Npgsql guy)...

1. There's the classical System.Spatial, with types DbGeometry/DbGeography, distributed in nuget Microsoft.SqlServer.Types. These are the type that work with SqlServer, and are supported on EF 6.x and will also be supported on EF Core 2.1 (on Windows/.NET Framework only). As you say they have native dependencies, and AFAIK there's no plan to ever port them to netstandard. From a quick look it also seems like the API is problematic and would be a poor choice for a general-purpose .NET spatial library - but I admit I didn't look very deep (nor am I an expert).
2. There's the newer Microsoft.Spatial. This is part of OData, and unlike what you wrote already supports .NET Standard 1.1. The issues I have with this are:
   
   
   
   1. The API has some issues (e.g. https://github.com/OData/odata.net/issues/1073)
   
   
   2. While it supports WKT it doesn't support WKB (https://github.com/OData/odata.net/issues/1059)
   
   
   3. It doesn't seem to be very maintained or prioritized (see https://github.com/OData/odata.net/issues/1089). In other words, it may have been built solely as an OData component, but isn't necessarily meant to be a standard, general-purpose spatial library for .NET, addressing database needs etc.
   
   
3. There's NTS, which in general looks good and seems to have a small ecosystem of libraries around it. It hasn't been updated for a long time and currently lacks netstandard support, but that seems to be in progress.

So in general I'd put my money on NTS at this moment, but we can also see how Microsoft responds in https://github.com/OData/odata.net/issues/1089. At least from a PostgreSQL perspective, nothing is preventing me from implementing either NTS (https://github.com/npgsql/npgsql/issues/1816) or MS.Spatial (https://github.com/npgsql/npgsql/issues/1808) support for PostGIS or even both. Then, an EF Core plugin could add support for mapping and translating spatial operations to PostGIS.

[figuerres]

@ranouf
Hey I am not sure what you mean about throwing an exception all the time ?
In normal .net i can call sql and get a list of locations ordered by distance from a start location.
the results can be done with ado.net no EF required.
that should work for you also.
in TSQL i get the STDistance() result returned to my client as a float / double data type in miles and tenths of miles.
like 27.8 (27 and 8/10ths)

just do a plain call to run a sql proc no EF and get the data.

[pauldendulk]

@roji Well NTS has been around for a long time and activity has increased over de last years so definitely not a bad choice.

Perhaps just picking one and keeping alternative implementations open is the best option at this point. Limit te dependency to just one of the subproject, perhaps in it's own nuget package, allowing alternative implementations in their own nugets.

[bertt]

The Microsoft.SqlServer.Types (https://www.nuget.org/packages/Microsoft.SqlServer.Types) library is one of the best and fast spatial library I've ever worked with. If this library could be ported to cross platform .NET Standard it will be a great win.

[ranouf]

@figuerres Hi,
I use Scalar function (https://docs.microsoft.com/en-us/ef/core/what-is-new/#database-scalar-function-mapping).

public class BloggingContext : DbContext
{
    [DbFunction]
    public static int PostReadCount(int blogId)
    {
        throw new Exception();
    }
}

From what I understood, by adding this static function in C# in the DbContext Class, you can call the SQL function "PostReadCount", the binding is automatic.

But each time i call the function by the code, it throw a exception. I guess, I did something wrong somewhere ..

Finally I found a solution, but not my favorite on, I call the FromSQL to generate my list of Entity. I dont like this solution because the Include doesnt work as it was, then if i add a new field to my class, I will need to remember to modify my SQL Query.

I read that EF Core team asked to post here to explain our need to use GeoLocation. My need is to get the distance directly by C#. I found a workaround, that s ok. But i hope in the future, EF Core Team will add this functionality :)

[pmiddleton]

@ranouf - The query you wrote with the scalar function must have caused client side evaluation on the part which contained the function call. That can happen in some situations.

You can turn on logging and see if you are getting a client eval warning. If so you would need to track down what is causing the eval and try to work around it. A big one that causes client eval in 2.0 is group by.

If you have a small repo of the issue you can open a new bug and I can take a look into it.

[pauloanalista]

It seems that SqlServer and Entity Framework 6 uses a dll to perform queries that use spatial data.

The big problem is that this dll is done in a language that ends up needing the windows kernel to give load in the dll.

By my tests, this dll is not possible to take to linux, due to not having how to give a load in dll

With this EntityFramework Core, could not run inside a container Docker for example in Linux

---

Parece que o SqlServer e Entity Framework 6, utiliza uma dll para realizar consultas que utilizam dados espaciais.

O grande problema é que esta dll é feita em uma linguaguem que acaba necessitando do kernel do windows para dar load na dll.

Pelos meus testes, essa dll não é possível levar para o linux, devido não ter como dar um load na dll

Com isso o EntityFramework Core, não poderia rodar dentro de um container Docker por exemplo no Linux

[rahicks26]

A good workaround for this if you are just waiting on geography support is to use NetTopologySuite for all the server side lifting and EF for moving the data to the db. Instead of using a geography type directly in the db use a byte[] or text which EF can handle like a boss. Once you spin up you db add a computed column that translates you byte[] or text to a geography type here is a pretty solid example. Then use the NetTopologySuite to store either wkb or wkt in your prop and you are ready to go for CRUDS. For spatial searches you will have to decide do I want to solve it on the Db side or Server side. If you want Db then the best you can do right now is stored procedures.

Hope this helps to fill the gap a bit while the EF team knocks this issue out.

[JeanCollas]

Meanwhile would there be an option to implement it in a DbFunction, similar to EF.Functions.XXXX family?

I used to do something like the following to get the closest elements, where Location is a pre-computed spacial column not present in the Place object, but this implies a lot of SQL in the code:

  var query = "DECLARE @g geography = "
                  + "geography::STPointFromText("
                     + "'POINT(' + CAST({0} AS VARCHAR(20)) + ' ' + CAST({1} AS VARCHAR(20)) + ')', 4326);"
                   + $" SELECT {String.Join(",", fields)} FROM {table} "
                   + " ORDER BY Location.STDistance(@g) DESC;"

  return dbContext.Set<Place>().FromSql(query, lat, lng);

It works well, maybe it can help some people here :)

[plamen-i]

Dear EntityFrameworkCore team:

So there's first movement towards supporting Spatial data and operations in EntityFramework Core - on top on PostgreSQL/PostGIS:
NetTopologySuite plugin

Some of community members (me including) argue to implement spatial support in EFCore using NTS.
With above mentioned commit in mind, do you think the same approach could be implemented for SQL Server? After all it is plugin/options-based which means implementing/using it will not hurt later implementation/use of another plug-in based on, lets say, Microsoft.Spatial library?

[SpiegelSoft]

@plamen-i This is a very compact solution. Congratulations.

I'd be very interested in contributing to such a plugin. DbGeography is a blocker for my project, and a plugin would be far superior to any workaround.

[roji]

Guys, please note that the plugin you're seeing in the Npgsql provider isn't anything standard - EF Core doesn't currently have any sort of standardized plugin API AFAIK, it's just something I hacked up to support NetTopologySuite and other scenarios.

On the other hand, thanks to the dependency injection architecture of EF Core it's really easy to do this kind of thing.

[plamen-i]

I proposed similar architecture for SQL Server, nothing more.

As I see missing spatial support problem - it is mainly in choosing one spatial library over another. Because behind NTS is GeoAPI, I just think GeoAPI (+ NTS) is the right way to go. And because I think it will take more time EFCore team to choose interface specification + implementation, I just see plug-in architecture (i.e. hack you mention) as the way to speed-up this process towards one (maybe best) solution and in the same way to leave open door for other solutions.

Finally - whatever will be The implementation library, I want to point out to GeoAPI - as maybe best GIS/OGC specification.

[ajcvickers]

@plamen-i We have discussed this with @roji and NTS does look promising. There are additional challenges with SQL Server as mentioned previously in this thread, but it certainly seems like getting something to work with NTS is doable.

[bricelam]

Quick question: Would you expect operations (distance, intersects, length, etc.) to work outside of query?

• 👍 Yes
• 👎 No

In other words, after you read the spatial data from the database, will you perform additional calculations on it, or will you just consume the point data (e.g. plot it on a map)?

[bricelam]

@figuerres you mentioned selecting part of a complex shape. Could you provide an example query that does this?

[bricelam]

Good news everyone, NTS supports reading and writing to GeoJSON. 😉

[figuerres]

@bricelam
i think what you are refffering to is that we have a set of tables that have different geography shapes.
in our app a zipcode outline is stored for each zipcode, a delivery route is created by doing (in tsql) a union of the zipcodes and that result is stored in the route table. a market is a collection of routes and the same union process makes a market shape.
the user can select to view the zipcodes, routes and markets when they do we convert the sql stored shape to a collection of GeoJson objects that are then sent to google maps as data layers to display.

on the subject of operations if we need to get distance between objects we generally do it in tsql and just return the value that was computed.

the #1 issue we have with EF 6 is that it does not directly use sql clr types, for several operations in C# we need the sql clr types not the watered down EF 6 versions.
this has to do with beeing able to fully examine and break down what is in the geo data, the polygons, lines areas etc.... so that we can build the geo json or make valid sql geography
that happens when we ask google for a driving path from a to b.
that data comes back as an encoded string that we decode to shape data that we then make into a format that we can store in the sql table.

[bricelam]

Another question: Do you use an SRID beside 4326?

[bricelam]

...and one more: Do you use...

• 👍 Z
• 😕 M
• 👎 Just X/Long and Y/Lat

[Kukkimonsuta]

Quick question: Would you expect operations (distance, intersects, length, etc.) to work outside of query?

That actually depends:

// No, I do not expect this to work outside of query.
.Where(x => EF.Functions.GeoDistance(x.Location, center) < radius)

// Yes, I do expect this to work outside of query.
.Where(x => x.Location.GetDistance(center) < radius)

[roji]

Good news everyone, NTS supports reading and writing to GeoJSON. wink

Note that there's also the GeoJSON.NET project, which can do GeoJSON directly. Not sure what (if any) are the advantages or disadvantages of passing through NTS to do this...

[vitaliy-goncharuk]

Hey guys,

1) When will ms sql geography be properly supported?

2) What is the best approach for working with ms sql geography from .net core now?
I can't find any useful sample.

I just need an example of getting places names from the table if their points are in some polygon from another table via .net core app.

[xperiandri]

@vitaliy-goncharuk use raw SQL queries and column type conversions

[J0nKn1ght]

@vitaliy-goncharuk @xperiandri I'm using a workaround to add sys.Geograpy SQL datatypes, but I'm having problems with subsequent 'add-migration' operations.

I have a Retailer entity which I've added a 'Location' property to, defined as 'string':

public string Location { get; set; }

In the context, the column is set to sys.Geography, and set to ignore:

builder.Entity<Retailer>().Property(nameof(Retailer.Location)).HasColumnType("sys.Geography");
builder.Entity<Retailer>().Ignore(r => r.Location);

I then have a function to update the Location property whenever the Lat/Lng is updated. (Note that we have multiple entities with a Location property, hence passing in the entity name). I could have used a trigger for this, but preferred to keep it in the code:

public static string UpdateLocation(string entityName, Guid id, double lat, double lng)
{
    return System.FormattableString.Invariant($"update [{entityName}] set Location = geography::Point({lat}, {lng}, 4326) WHERE Id = '{id}';");
}

When I need to query based on the location, I have the following function:

public async Task<RetailerResultsViewModel> GetRetailerResultsByLocationAsync(double lat, double lng, int radiusKm)
{
    RetailerResultsViewModel model = new RetailerResultsViewModel();
    // Get the column list, excluding the Location column, as EF will throw a "Type Udt is not supported on this platform" exception if this is included.
    // N.B. this is done in code, so that if properties are added to Retailer, we don't have to remember to update this to include the new column names
    var entities = _context.Model.GetEntityTypes();
    var entity = entities.Where(et => et.ClrType.Name == nameof(Retailer)).SingleOrDefault();
    var props = entity.GetProperties().Where(p => p.Name != nameof(Retailer.Location)).ToList();
    var columns = string.Join(",", props.Select(p => $"[{p.Name}]"));

    string query = System.FormattableString.Invariant($@"declare @g geography = geography::Point({lat}, {lng}, 4326);
            Select top {_maximumResults} 
            {columns}
            from Retailer 
            where Location is not null 
            and Location.STDistance(@g) < {radiusKm * 1000}
            order by Location.STDistance(@g) asc;");
    model.Retailers = await _context.Retailers.AsNoTracking().FromSql(query).ToListAsync();

    return model;
}

This all works fine. The problem I have is that whenever I do a subsequent migration on the database, it generates the following in the migration file 'Up' method:

    migrationBuilder.DropColumn(
        name: "Location",
          table: "Retailer");

and this in the 'Down' method:

    migrationBuilder.AddColumn<string>(
        name: "Location",
        table: "Retailer",
        type: "sys.Geography",
         nullable: true);

Does anyone else have this problem, and does anyone have any proposed workarounds for this? I'm not sure if it's just the way that I've defined the property that's causing the problem with the migration?

[smitpatel]

builder.Entity().Ignore(r => r.Location);

This line removes the property from EF model hence DropColumn operation. Remove it.

[J0nKn1ght]

@smitpatel Thanks for the suggestion, but that doesn't work.

If I remove the 'Ignore', I get an exception thrown when the following query is run:

string query = System.FormattableString.Invariant($@"declare @g geography = geography::Point({location.Lat}, {location.Lng}, 4326);
            Select top {_maximumResults} 
            *
            from Retailer 
            where CountryTwoLetterISORegionName = '{countryIso}'
            and Location is not null
            and Location.STDistance(@g) < {radiusKm * 1000}
            order by Location.STDistance(@g) asc;");

model.Retailers = await _context.Retailers.AsNoTracking().FromSql(query).ToListAsync();

System.IO.FileNotFoundException: 'Could not load file or assembly 'Microsoft.SqlServer.Types, Version=10.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91'. The system cannot find the file specified.'

(Note that in my original comment, I am getting the list of properties and excluding the 'Location' column, rather than selecting '*', because of this problem.)

That's why the 'Ignore' is in there, because EFCore doesn't understand what a 'sys.Geography' column type is.

Do you know how I can stop the exception? I tried adding a reference to a copy of 'Microsoft.SqlServer.Types' from the SQL Server 10 SDK folder on my PC, but I still get the same exception.

[JeanCollas]

@J0nKn1ght For your initial use, the one including columns, have you tried [NotMapped] attribute to the entity property?

[J0nKn1ght]

@JeanCollas I've just tried using [NotMapped], and I get the same result.

I'm not sure NotMapped is correct anyway, as we want EF to include a Location column in the database, but tell it to set the column type to 'sys.Geography', hence the line following line in the context:

builder.Entity<Retailer>().Property(nameof(Retailer.Location)).HasColumnType("sys.Geography");

My understanding of NotMapped is that you are indicating to EF that you don't want the property persisted to the database, whereas we do want the property persisted, just not with the data type that we've specified (i.e. string).

[JeanCollas]

@J0nKn1ght I was seeing it as "you create it, then remove it from your model".
On my models, I always have a Latitude and Longitude decimal(9,6), and a computed column for Location on SQL side geography::Point(Latitude, Longitude, 4326), and also computed on C# side as something like cached version of new LatLng(Latitude, Longitude).
So it will be ready when EF will have the feature, meanwhile I can use it in stored procedures if I need to.

[J0nKn1ght]

@JeanCollas OK. Is there no performance hit to having the Location sys.Geography column defined as a computed column?

[JeanCollas]

@J0nKn1ght Just set it as "persisted", so it is recomputed only when latitude or longitude is changed. However I am not sure about indexes if it is possible to use spacial indexes or not, it needs to be checked. (it depends also on the number of lines you have to handle, few thousands will not be an issue at all).

[J0nKn1ght]

Thanks @JeanCollas.

Can anyone comment on whether using computed columns outside of the normal EF model is the best approach (until spatial types are supported), and whether there are any other knock-on effects in subsequent migrations.

Presumably, if I use the same technique as I mentioned above, of populating a list of entities using 'FromSQL', I'll still need to generate the list of properties (which will exclude the manually added computed 'Location' column), and inject this column list into the Select query?

[ajcvickers]

Both Ignore() and [NotMapped] are intended to _completely remove the property from the model_. That is, no database column will be created and any existing column cannot be queried. It appears like this is not happening here, which is a bug. Anything that still uses the property/column is utilizing this bug, which is obviously dangerous and not a good idea.

If you're on .NET Framework and using native SQL Server types, then you should be able to include them in your model directly. You will need to make sure that the Microsoft.SqlServer.Types is available to your application, which I always find very frustrating to achieve--I generally copy the DLL around manually.

If you're on .NET Core, you won't be able to use these types.

[J0nKn1ght]

@ajcvickers Thanks. We are on .Net Core.

We know that it is potentially dangerous, and not a good idea, but we switched to .Net Core development for this project before we knew that later phases would require spatial types, and we also didn't realise that EFCore v2.x didn't include support for them (as we had been using them successfully in EF 6 projects).

We therefore only have the options of switching everything back to use the full .Net framework (which we don't particularly want to do), or using dangerous workarounds until Spatial is supported in EFCore 3(?).

Would there be any code changes required or impact on performance if we switched the project to use .Net Framework? We're running a standard MVC Core site as an Azure Web App, which is available globally 24/7, and hosted on the East US Azure servers.

[figuerres]

@J0nKn1ght

my view is that until the geo types are working i am staying with EF 6 on full .net.
ef core to me (my view only) this is a beta not a ready library until then.

others may see this differently, that's ok. just my view.

[JeanCollas]

@J0nKn1ght
I think to get a good advice, you should share the approximate volume of

• # of requests per second/minute/hour
• # of rows in the concerned table(s)
• if you query coordinates as other fields or if you filter on coordinates
• if you have some optimizations for working with coordinates, and what do you use them for

On my side, I have 2 approaches that work in parallel, both use distances:

• I have an extract of items that are queried often (actually I have 3 for optimization), these are all loaded in a memory cache which is reloaded every 10 minutes, and queries that match this subset do not need DB calls. If you optimize a bit distances functions (I have different distance calculations for sorting and then calculate the exact distance).
• I have a stored procedure (which could also be inline SQL query), which returns the right extract of items in case the query do not match the in-memory subset. The stored procedure uses the Location field with distance calculation.

To make things much faster on .Net, I use different filters:

• if there are some numeric/date filters, apply them first (very fast)
• apply an absolute distance filter to latitude and absolute distance to longitude (including modulo adjustment): this allows to extract points too far away, and reduces really significantly the work to be done, 1 degree is more or less 111km

&& Math.Abs(e.Latitude - origin.Latitude) < 1
&& Math.Abs(((e.Longitude - origin.Longitude + 180) % 360) - 180) < 1

or probably faster:

public bool Contains(decimal lat, decimal lng)
{
     //bool lngSameModulo = MaxLng >= MinLng;
     return (MinLat <= lat && lat <= MaxLat)
              && (/* lngSameModulo */ MaxLng >= MinLng ? MinLng <= lng && lng <= MaxLng :
              (((MinLng <= lng && lng <= MaxLng + 360)
              || (MinLng - 360 <= lng && lng <= MaxLng))));
}

• apply all filters I need (text filters)
• then at the end I apply distance

(from e in items
        where // numeric or date filters here
        where bounds.Contains(e.Coordinates) // I use a 222km square filter (-1 lat/lng +1 lat/lng), very fast filter, removes 95% of bad results. You can of course adjust the size depending on the type radius you use
        where // string filters here
        let dist = target.EquirectangularKmTo(e.Coordinates)
        where dist < 50
        order by dist
        select new { Item = e, Dist = dist }
        ).Take(XXX)

With EquirectangularKmTo defined as

    public const double EarthRadiusKm = 6371;
    const double ToRadiansFactorD = 1.0d / 180d * (double)Math.PI;
    public static double EquirectangularKmTo(this ILatLng p1, ILatLng p2)
        => EquirectangularKmTo_Degrees(p1.Latitude, p1.Longitude, p2.Latitude, p2.Longitude);
    public static double EquirectangularKmTo_Degrees(decimal lat1Deg, decimal lng1Deg, decimal lat2Deg, decimal lng2Deg)
    {
        // more details: http://www.movable-type.co.uk/scripts/latlong.html
        var x = (double)(lng2Deg - lng1Deg) * ToRadiansFactorD * Math.Cos((double)(lat1Deg + lat2Deg) * ToRadiansFactorD / 2);
        var y = (double)(lat2Deg - lat1Deg) * ToRadiansFactorD;
        return Math.Sqrt(x * x + y * y) * EarthRadiusKm;
    }

This was working quite well for 200k items in memory, but of course it consumes memory :)
Advices:

• don't use real distance functions, the gain in precision is usually not useful, except for very long distances. The Equirectangular function is precise enough for most cases.
• if you just need to take the closest results around a point, don't use EquiRectangular distance which has useless additional calculations, on my side, I use this one:

    public static double DistToOrder(this ILatLng gp, ILatLng gp2)
    {
        var lat1 = ((float)gp.Latitude).ToRadians();
        var lat2 = ((float)gp2.Latitude).ToRadians();
        var x = (((float)gp2.Longitude).ToRadians() - ((float)gp.Longitude).ToRadians()) * Math.Cos((lat1 + lat2) / 2);
        var y = (lat2 - lat1);
        return x * x + y * y;
    }

[J0nKn1ght]

@JeanCollas Thanks for taking the time to add some more detail.

With regards to your questions, we don't really know how many requests we're likely to get that involve spatial queries. The site currently serves around 100k requests a week, so the likely number of requests which will involve a spatial query is likely to be pretty low.

Again, the number of rows isn't yet known, as we don't have all of the data that needs to be included yet. We have UK & US data which is around 4000 rows, so it's not likely that the eventual dataset is going to be bigger than 10s of thousands.

All of the queries will be the shortest distance to a fixed location (so we're just using the SQL Server Geography STDistance function). Additional filters may be added later, but initially it is purely on the location. They are basically of the 'find all retailers near me' type.

I think that we will probably stick with what we have until the Spatial types are properly supported in EFCore, as it is basically working ok. The main problem seemed to be with the migration trying to remove the 'Location' property from the database. However, after manually editing this out of the migration, the subsequent migrations _seem_ to be ok.

Thanks for your input, though, and hopefully the code that you've posted may help others trying to use Spatial data in EFCore.

[bricelam]

Design Notes

Ecosystem

We did a survey of a few different databases and .NET libraries:

• NTS—NetTopologySuite
• MSSQL—SQL Server
• SQLite—SQLite + SpatiaLite
• PG—PostgreSQL + PostGIS
• EF6—System.Data.Entity.Spatial (was System.Data.Spatial)
• OData—Microsoft.Spatial (was System.Spatial)

The shows support for varios OGC operations.

Operation | NTS | MSSQL | SQLite | PG | EF6 | OData
--- |:---:|:---:|:---:|:---:|:---:|:---:
Area | ✔ | ✔ | ✔ | ✔ | ✔ |
AsBinary | ✔ | ✔ | ✔ | ✔ | ✔ |
AsGML | ✔ | ✔ | ✔ | ✔ | ✔ | ✔
AsText | ✔ | ✔ | ✔ | ✔ | ✔ | ✔
Boundary | ✔ | ✔ | ✔ | ✔ | ✔ |
Buffer | ✔ | ✔ | ✔ | ✔ | ✔ |
Centroid | ✔ | ✔ | ✔ | ✔ | ✔ |
Collect | ✔ | ✔ | ✔ | ✔ | |
Contains | ✔ | ✔ | ✔ | ✔ | ✔ |
ConvexHull | ✔ | ✔ | ✔ | ✔ | ✔ | ✔
CoveredBy | ✔ | | ✔ | ✔ | |
Covers | ✔ | | ✔ | ✔ | |
Crosses | ✔ | ✔ | ✔ | ✔ | ✔ |
CurveN | | ✔ | | | |
CurveToLine | | ✔ | | ✔ | |
Dimension | ✔ | ✔ | ✔ | ✔ | ✔ |
Disjoint | ✔ | ✔ | ✔ | ✔ | ✔ |
Distance | ✔ | ✔ | ✔ | ✔ | ✔ | ✔
EndPoint | ✔ | ✔ | ✔ | ✔ | ✔ | ✔
Envelope | ✔ | ✔ | ✔ | ✔ | ✔ |
Equals | ✔ | ✔ | ✔ | ✔ | ✔ |
ExteriorRing | ✔ | ✔ | ✔ | ✔ | ✔ | ✔
GeometryN | ✔ | ✔ | ✔ | ✔ | ✔ | ✔
GeometryType | ✔ | ✔ | ✔ | ✔ | ✔ | ✔
GeomFromText | ✔ | ✔ | ✔ | ✔ | ✔ |
GeomFromWKB | ✔ | ✔ | ✔ | ✔ | ✔ |
InteriorRingN | ✔ | ✔ | ✔ | ✔ | ✔ | ✔
Intersection | ✔ | ✔ | ✔ | ✔ | ✔ |
Intersects | ✔ | ✔ | ✔ | ✔ | ✔ | ✔
IsCCW | ✔ | | | ✔ | |
IsClosed | ✔ | ✔ | ✔ | ✔ | ✔ |
IsEmpty | ✔ | ✔ | ✔ | ✔ | ✔ | ✔
IsRing | ✔ | ✔ | ✔ | ✔ | ✔ |
IsSimple | ✔ | ✔ | ✔ | ✔ | ✔ |
IsValid | ✔ | ✔ | ✔ | ✔ | ✔ |
Length | ✔ | ✔ | ✔ | ✔ | ✔ | ✔
M | ✔ | ✔ | ✔ | ✔ | ✔ | ✔
MakeValid | | ✔ | ✔ | ✔ | ✔ |
Normailze | ✔ | | | ✔ | |
NumCurves | | ✔ | | | |
NumGeometries | ✔ | ✔ | ✔ | ✔ | ✔ | ✔
NumInteriorRing | ✔ | ✔ | ✔ | ✔ | ✔ | ✔
NumPoints | ✔ | ✔ | ✔ | ✔ | ✔ | ✔
Overlaps | ✔ | ✔ | ✔ | ✔ | ✔ |
PointN | ✔ | ✔ | ✔ | ✔ | ✔ | ✔
PointOnSurface | ✔ | ✔ | ✔ | ✔ | ✔ |
Relate | ✔ | ✔ | ✔ | ✔ | ✔ |
Reverse | ✔ | | ✔ | ✔ | |
SRID | ✔ | ✔ | ✔ | ✔ | ✔ | ✔
StartPoint | ✔ | ✔ | ✔ | ✔ | ✔ | ✔
SumDifference | ✔ | ✔ | ✔ | ✔ | ✔ |
Touches | ✔ | ✔ | ✔ | ✔ | ✔ |
Union | ✔ | ✔ | ✔ | ✔ | ✔ |
Union (Aggregate) | ✔ | ✔ | ✔ | ✔ | |
Within | ✔ | ✔ | ✔ | ✔ | ✔ |
X | ✔ | ✔ | ✔ | ✔ | ✔ | ✔
Y | ✔ | ✔ | ✔ | ✔ | ✔ | ✔
Z | ✔ | ✔ | ✔ | ✔ | ✔ | ✔

SQL Server also provides some extended operations that don't often translate directly to the others.

Operation | NTS | MSSQL | SQLite | PG | EF6 | OData
--- | --- | --- | --- | --- | --- | ---
BufferWithCurves | | ✔ | | | |
BufferWithTolerance | | ✔ | | | ✔ |
ConvexHull | | ✔ | | | |
CurveToLineWithTolerance | | ✔ | | | | |
Envelope (Aggregate) | | ✔ | | | | |
InstanceOf | | ✔ | | | ✔ |
Point | ✔ | ✔ | | | ✔ | ✔
Reduce | | ✔ | | | ✔ |
ShortestLineTo | | ✔ | ✔ | | |

During this reaserch we came to a few conclusions:

• Microsoft.Spatial in its current state isn't a good fit for EF Core
  
  
  
  • Excluding the APIs to access shape data, it currently only supports three operations: Distance, Intersects, and Length
  
  
  • It was designed specifically for OData (e.g. streaming shape data to the server) and isn't a general-purpose spatial library
  
  
• NTS looks like a good fit
  
  
  
  • It covers most of the functionality of EF6, but works on .NET Core
  
  
  • It's APIs are more idiomatic to .NET than EF6 was. Like Microsoft.Spatial, it provides collection properties. For example, use polygon.InteriorRings[n] and InteriorRings.Length instead of polygon.GetInteriorRingN(n) and NumInteriorRings.
  
  
  • Like EF6, it decouples the implementation from the types. You could program against the interfaces defined GeoAPI and provide mocks in your tests or lightweight implementations to just hold the shape data on a client.
  
  

Currently, NTS support for .NET Standard is only in preview. We'll need to work with the NTS team to when they plan to make a stable release.

PostgreSQL notes

The Npgsql.EntityFrameworkCore.PostgreSQL.NetTopologySuite package was released in version 2.1.

It passes the NTS instances directly into the ADO.NET provider.

As part of working on this feature, we'll establish a common pattern that providers can use to implement spatial support. For example, the 2.1 implementation doesn't support programming against the GeoAPI interfaces. We also want to improve EF Core's extensibility so each provider doesn't have to re-invent things like the plugin model Npgsql introduced in 2.1. And of course, any additional code that can be shared between providers should be moved into a common assembly owned by the EF Core team.

SQLite notes

SpatiaLite is easy to install globally on Linux and macOS via a package manager like apt-get or brew. We (or someone) may need to create a NuGet package to make acquiring it easier on Windows.

NTS provides APIs to convert between their instances and the BLOB format used by SpatiaLite. Creating a type mapping that uses a value converter to call these should be trivial.

SQL Server notes

As we all know, getting this to work on .NET Core is going to be the bulk of the work for spatial.

Some of the challenges:

• SqlGeometry doesn't exist on .NET Core
• Serializing and deserializing between the SqlGeometry binary format and NTS instances would be expensive for the EF Core team to implement and maintain
• The well-known text and binary formats on SQL Server don't include the SRID

To compensate for the lack of SqlGeometry support, we can add hooks into Query and the Update Pipeline to alter the SQL for instances of this type. For example, would call geometry.AsText() in SQL for any geometry returned as part of a result, and the Update pipeline would use geometry::GeomFromText(text) in the SQL to create instances from the well-known text.

To preserve the SRID, we're considering a few options:

• Don't allow per-instance SRID values, but instead declare them statically either per DbContext instance or per entity type property.
• Enable value converters that aren't one-to-one. This would allow us to convert between one Geometry value to two values in the provider (well-know text and SRID)
• Ask the team that owns SqlGeometry to create a library for serializing and deserializing on .NET Core

Geography and NTS

One of the biggest differences between NTS and EF6/OData is that NTS doesn't provide a separate type for geography. We are working with them to find the best solution for this. There are essentially two options:

One option is to add types for geometry. Easy enough. This keeps the mapping straightforward between NTS and technologies like SQL Server and PostGIS which have both geometry and geography types. Creating fully functional types would be a lot of work for them, and it would likely throw for client-side operations at first.

The other option is to re-use NTS's geometry type, and allow it to be mapped to either geometry or geography on the server. The obvious downside to this is that it may be confusing especially where the APIs don't quite align like exterior and interior rings of polygons. This also runs the risk of getting geographic results on the server, but geometric results when evaluating on the client.

Feedback

As always, let us know if we're going in the right direction and if you have a strong preference or objection to one approach over another.

[Giancarlos]

I will note that with EF Core and MSSQL I can:

Send WKT to MSSQL using EF ORM Update and Insert, and MSSQL will convert WKT back to a Geometry type (I put [Type="Geometry"] in my model if I remember correctly but I define it as a string. When I'm selecting it will not map it back to a string so I've got to do a .FromSQL to get it back STAsString.

So weirdly enough what works with the ORM with Geometry types:

Insert and Update with a WKT string because MSSQL will just cast it...

What doesn't work:

Selecting, and if I got a model with a Geometry types I cannot do an arbitrary query because EF Core does not provide support for this last I checked, so I have to do weird things to return it back to a model, I can't remember exactly since it's been a while since I've done this.

I think if I could at least select Geometry types that are mapped to a STRING (but the type is specified as geometry) I should get back a WKT string, strangely enough this is not the care, but if it were this would allow me to fully use EF Core with MSSQL in regards to geometry types, as for geography, I don't have that type in the project I worked on.

If anyone at Microsoft would like to get more feedback from me I'll happily talk to them. I have been meaning to document my approach to how I've gotten the Geometry type to work but I have not yet found the time. I may do so soon because I think it's relevant to EF Core and how it works.

[divega]

@J0nKn1ght I looked at your workaround in more detail to try to understand if Ignore() is not working as expected and if you are in any danger of depending on that behavior. My conclusion is that Ignore() is working correctly and what you are doing is fine. Here is my understanding:

You first create an entity with a string property, and tell EF Core that the actual column type is sys.Gepgraphy. With this property in place, you can get EF Core migrations to create the table with the right column for you.

In a later step, you tell EF Core to ignore that property. At this point the property gets removed completely from the model, and when migrations computes the diff it detects (as expected) that the columns should be removed from the table. Then you manually update the resulting migration to remove the drop operation.

From there on, you only mention the Location column in raw-SQL queries, and never project it out in query results, which is a known workaround.

The neat part of what you are doing is that you got the initial migration to create the spatial column for you, even if EF Core does not have knowledge of the spatial column types.

The drop operation generated in the next migration after you introduce Ignore() is unavoidable. But just removing the operation from the migration manually as you did should work just fine.

FWIW, there shouldn't be much of a difference between calling Ignore() and removing the Location property from the Retailer type altogether.

Re @JeanCollas' additional suggestion to add latitude and longitude columns that EF Core can use and make the spatial column computed persisted, that should work too. I am not really sure either option makes you more _ready_ for when EF Core adds spatial support: With your approach, you will be able to add a property to the entity and map it to the existing column. With @JeanCollas' approach, he will do that, and then he will have to decide if he wants to get rid of the Latitude and Longitude columns. I cannot answer the question on overhead.

@JeanCollas this is very clever!

apply an absolute distance filter to latitude and absolute distance to longitude (including modulo adjustment): this allows to extract points too far away, and reduces really significantly the work to be done, 1 degree is more or less 111km

@Giancarlos Thanks for sharing the information about the implicit conversion of WKT to spatial. For being able to materialize spatial instances we are thinking we will add explicit conversions or function calls in the SQL. This is what https://github.com/aspnet/EntityFrameworkCore/issues/10861 is about. Anyway, I remember @bricelam mentioning that there is some data loss in the WKT conversions in SQL Server, so it will likely be more complicated.

All in all, thank you everyone for sharing your workarounds.

[KthProg]

This issue has been open for 4 years? For realsies? It hasn't been too big a deal since I can just create views that return Lat/Lng separately and write sprocs to do the heavy lifting query-wise, but it would be really cool to have this supported.

[ikourfaln]

@KthProg
This feature is just added to 2.2.0 milestone, so we are waiting for EF Core v2.2.x

[bricelam]

Anyone using CircularString, CompoundCurve, or CurvePolygon? (upvote NetTopologySuite/NetTopologySuite#247)

[mrcoymeeks]

Where can I find the 2.1 workaround doc for sqlgeometry? Thanks!

[plamen-i]

Soooo... FINALLY!
First preview version of EntityFrameworkCore Spatial support for SQL Server using NTS is available:
Microsoft.EntityFrameworkCore.SqlServer.NetTopologySuite

Congrats Microsoft & all community members involved in pushing/coding for this to become reality!

[DGuidi]

thanks to all involved developers for this :)

[figuerres]

Looking forward to the day i can port my web api project from .net classic to .net core !!!

[bricelam]

To get started:

Install the Microsoft.EntityFrameworkCore.SqlServer.NetTopologySuite package.

Add this to your UseSqlServer() call:

.UseSqlServer(connectionString, x => x.UseNetTopologySuite())

Add some spatial properties to your entities:

class SpatialThing
{
    public int Id { get; set; }

    // GeoAPI interfaces...
    public IGeometry Gemoetry { get; set; }
    public IPoint Point { get; set; }
    public ILineString LineString { get; set; }
    public IPolygon Polygon { get; set; }
    public IMultiPoint MultiPoint { get; set; }
    public IMultiLineString MultiLineString { get; set; }
    public IMultiPolygon MultiPolygon { get; set; }
    public IGeometryCollection GemoetryCollection { get; set; }

    // ...or NTS implementations
    public Geometry Gemoetry { get; set; }
    public Point Point { get; set; }
    public LineString LineString { get; set; }
    public Polygon Polygon { get; set; }
    public MultiPoint MultiPoint { get; set; }
    public MultiLineString MultiLineString { get; set; }
    public MultiPolygon MultiPolygon { get; set; }
    public GeometryCollection GemoetryCollection { get; set; }
}

Query away.

var currentLocation = new Point(0, 0);
var nearestThings = from t in db.SpatialThings
                    where t.Point.Distance(currentLocation) < 100
                    select t;

We still need to implement mapping to geography columns, but feel free to experiment and provide feedback.

The SQLite implementation (using SpatiaLite) is coming soon.

[dpedrelli]

@bricelam
Thanks. Is there a time frame on mapping the columns? I don't really need Geography support as much for program-side functionality as I do code-first migrations. Right now, I'm using a script to modify the tables after I create them and add spatial indices.

Thanks

[bricelam]

For now, you can just specify the column type. Be warned though, the rest of the stack doesn't yet handle geography so you will likely hit issues.

[Column(TypeName = "geography")]
public IGeometry Geography { get; set; }

or

modelBuilder.Entity<SpatialThing>()
    .Property(t => t.Geography)
        .HasColumnType("geography");

[JeanCollas]

@bricelam I haven't checked yet, but I see where t.Point.Distance(currentLocation) < 100 with no indication of the unit there, I guess it is in the comment of "Distance" function, is it meters? km?

[markusschaber]

@JeanCollas I thought the unit of the distance depends on the coordinate system of the geodata - whether it's WGS84, or some local projection, or some other coordinate system...

[figuerres]

@markusschaber

i think sql and the .net code for geo have a default type and it uses meters , you can set the geo type id but i thnk that default is the most used option. i know i have code that gets data and converts meters to miles for my stuff.

[faryu]

How are different coordinate reference systems handled? (Support for ST_Transform, etc?)
Can the SRID for the geometry column be defined in the model configuration?

[SteffenMangold]

Will it be puplished EF Core 2.2.0 or 3.0.0? I'm confused.

[btecu]

@SteffenMangold 2.2.0

[SteffenMangold]

@btecu but why is the alpha depending on entity core 3.0.0-alpha?

[gpanagakis]

@bricelam Iam unable to install Microsoft.EntityFrameworkCore.SqlServer.NetTopologySuite along with the last
packages for Microsoft.EntityFrameworkCore and Microsoft.EntityFrameworkCore.SqlServer.
I tried :
Install-Package Microsoft.EntityFrameworkCore -Version 2.2.0-preview1-35029
Install-Package Microsoft.EntityFrameworkCore.SqlServer -Version 2.2.0-preview1-35029

An afterward there is no
Microsoft.EntityFrameworkCore.SqlServer.NetTopologySuite version 2.2.0-preview1-35029 package
available. The oldest is version is 2.2.0-preview2-35129

[bricelam]

@faryu You set SRID values on the Geometry instances (use a geometry factory to specify a default). Some providers (like SQLite + SpatiaLite) require the SRID to be defined on the column. For these, we'll add Fluent API like modelBuilder.Entity<SpatialThing>().Property(t => t.Point).ForSqliteHasSrid(4326).

[bricelam]

This work is going into the 2.2 branch.

Here is the nightly feed for 2.2.
Here is the nightly feed for 3.0.

Previews of 2.2 will be released to NuGet.org monthly. The 2.2 Preview 2 release will be the first to include the Microsoft.EntityFrameworkCore.SqlServer.NetTopologySuite package.

[aherrick]

Here's how I'm creating a Point from a Lat/Lng:

            var coord = new GeoAPI.Geometries.Coordinate(lat, lng);

            var geomFactory = new GeometryFactory(new PrecisionModel(), 4326);
            return geomFactory.CreatePoint(coord);

[aherrick]

I believe I'm seeing odd results with Distance.

Below the DB query itself seems OK, and is pulling back the correct data.

When I use Distance in memory it's not returning a correct value. Below shows the basic code. Is there something I'm not doing correctly?

            var point = GeoHelper.CreatePoint(lat, lng);

            var distanceMax = GeoHelper.MILES_TO_METERS * radiusMiles;

            var venues = (from v in DB.Venue
                          where v.Location.Distance(point) <= distanceMax
                          select v).ToList();

            foreach (var venue in venues)
            {
                // what is distance? its not in meters
                var distance = venue.Location.Distance(point);
            }

[DGuidi]

as said earlier in this thread, distance is not in "meters", but unit of measure depends on srid. if you use 4326, you're measuring in degrees so you need to convert to meters by yourself.

[aherrick]

@DGuidi I am using 4326 to create my initial Point. What does that conversion look like? Degrees to Meters you are suggesting?

[figuerres]

well if you have the locations in a ms sql database you can do it in tsql, no need to use c# to do the math. tsql has a distance method and a convert

[bricelam]

@aherrick This will get better when we add support for SQL Server geography columns. For now, NTS and SQL Server's geometry columns ignore the SRID, so yes, you'll need to convert from degrees. Note the number of meters in a degree gets less the further you are from the equator, so the math isn't exactly trivial, but you can get in the ballpark using a constant like 111,034.

[figuerres]

here is a link to what the full math is for distance: Great Circle formula
also: wikipedia

[aherrick]

Thanks! I'm already using the formula to get distances between 2 lat/lng, so that is getting me by for now!

[smittix6]

What would be the code-first way of adding a lat/lng to a model? The below does not seem to work yet - not sure if it's ready?

public Point Coords { get; set; }

[corstian]

@smittix6 be sure to manually change the generated migrations. However, should've been fixed a few hours ago. See https://github.com/aspnet/EntityFrameworkCore/issues/13267 for more context.

[smittix6]

@corstian - thanks

[bricelam]

Design note

We're changing the by-convention mapping on SQL Server to geography since this seems to be what most people are using. As always, you can override the convention by explicitly setting the column type to geometry.

[roji]

@bricelam that's interesting, it seems to be the opposite of what I'm getting on the PostgreSQL side (although i haven't done a poll or anything)... Isn't geography significantly more limited and expensive when it comes to calculations?

[bricelam]

Isn't geography significantly more limited and expensive.

Yes, but that's never stopped a de-facto standard before. 😉

I expect PostGIS developers do use geometry more. Their documentation does a good job of nudging users in this direction.

I would map to geometry by convention on Npgsql.

[ajcvickers]

@roji @bricelam With geometry mapping by default on Npgsql, when I ask for the distance between two points, what units will be used for the result?

[FransBouma]

Is there any documentation yet how this is going to work if you e.g. reverse engineer the AdventureWorks database (which contains geography types), as those types are of types SqlGeography/SqlGeometry from the SqlServer types package? Are the poco field types then suppose to be of type SqlGeography/SqlGeometry or of a type from the NTS package and you do the conversion internally?

(I ask this because for the EF Core support in llblgen pro we can already map to whatever type we want, but I want to make this more seamless so people don't have to pull the sqlserver types package from nuget to make the designer work with these types (or if a conversion is needed to NTS, this has to be done under the hood). So if everything is by default on the .NET side of things a type from the NTS package, we should ship that, and if the types are EFCore types we should default to those.)

TIA.

[plamen-i]

Design note
We're changing the by-convention mapping on SQL Server to geography since this seems to be what most people are using. As always, you can override the convention by explicitly setting the column type to geometry.

I'm second to leave default mapping behavior to geometry.

[btecu]

Design note
We're changing the by-convention mapping on SQL Server to geography since this seems to be what most people are using. As always, you can override the convention by explicitly setting the column type to geometry.

I'm second to leave default mapping behavior to geometry.

Because your use case requires geometry?

Saner defaults are a very important part of any library / framework and as it was mentioned, geography is used significantly more, so it makes sense to have it as default.

[bricelam]

@FransBouma Our goal is that if you have the Microsoft.EntityFrameworkCore.SqlServer.NetTopologySuite package installed before you reverse engineer, your model will contain NTS Geography properties for those columns and the column type in the EF Core model will be configured as either geometry or geography. Llblgen pro can mimic this behavior by generating Geography properties in the model and ensuring the package is installed. The conversion to SqlGeometry or SqlGeography is done by EF Core (we actually go straight to the serialization format and bypass these types on the client).

[bricelam]

@ajcvickers From the docs, it looks like it'll project the result to meters (or whatever unit your SRID uses).

ST_Distance—For geometry type Returns the 2D Cartesian distance between two geometries in projected units (based on spatial ref). For geography type defaults to return minimum geodesic distance between two geographies in meters.

[ajcvickers]

@bricelam @roji So then geometry with an SRID is more useful in Npgsql than it is in SQL Server, so I can see why Npqsql people would be more likely to choose geometry than SQL Server people. Still think, on balance, the default should probably be geography for a more consistent experience while allowing advanced people to switch it, but I can see it both ways.

[FransBouma]

@bricelam

@FransBouma Our goal is that if you have the Microsoft.EntityFrameworkCore.SqlServer.NetTopologySuite package installed before you reverse engineer, your model will contain NTS Geography properties for those columns and the column type in the EF Core model will be configured as either geometry or geography. Llblgen pro can mimic this behavior by generating Geography properties in the model and ensuring the package is installed. The conversion to SqlGeometry or SqlGeography is done by EF Core (we actually go straight to the serialization format and bypass these types on the client).

Ah thanks for the info :) Sounds good :)

[roji]

@ajcvickers @bricelam OK... For reference here are the PostGIS docs with user advice on when to use which type (I'm not big spatial expert and by now you probably know more about PostGIS than I do...).

I guess it's legitimate for different providers to map to different types by default, as geometry and geography have somewhat different support across databases. On the other hand as @ajcvickers wrote there's value in a consistent experience - I can imagine an MSSQL application being ported to PostgreSQL and having subtle accuracy errors as geography was used in MSSQL but geometry is used in Npgsql... This seems quite dangerous. This is another issue that would go away if NTS had different types, but I guess we're beyond that.

[ajcvickers]

@roji I think we are happy for you to make the call on what the default should be for Postgres.

[bricelam]

The work for this feature is more-or-less done and shipping in the upcoming EF Core 2.2 Preview 3 release. Thanks everyone for helping shape this feature! 🤣 I'm going to go ahead and close this issue now.

There are currently two known issues: #13181 & #13580. Please continue to provide feedback and let us know about any bugs by submitting new issues.

[roji]

@ajcvickers @bricelam just to confirm that at least for now, geometry will be the default on Npgsql (rather than geography). It seems to be what most people want, and I made that decision back in 2.1 when I first introduced the plugin - so changing now would be a breaking change. We'll see what people say.

FYI I'm now porting my EF Core plugins to use the new EF Core plugin model (https://github.com/npgsql/Npgsql.EntityFrameworkCore.PostgreSQL/issues/658), 2.2.0-preview3 should be out in a few days.

[Olbrasoft]

```C#
public class Entity {
public int Id { get; sets; }
public IPoint CenterCoordinates { get; set; }
}
var entity = new Entity()
Context.Add(entity) .... Throw Object reference is Null

```C#
var entity1 new Entity
{
  CenterCoordinates = NetTopologySuite.Geometries.Point.Empty
}
Context.Add(entity1)
Context.SaveChanges() ... throw Exception SRID

```C#
var entity2 new Entity
{
CenterCoordinates = NetTopologySuite.Geometries.Point.Empty
}
entity2.CenterCoordinates.SRID = 4326

Context.Add(entity2)
Context.SaveChanges() Is OK, but in Database Is Allowed Null is True And must be inserted Empty point .
```

[ajcvickers]

@Olbrasoft For the first snippet see #13457. For the Point.Empty cases can you create a new issue and explain in a bit more detail how and why you want to use Point.Empty.

[jioruu]

Can someone explain why this code returns distance12 = 0.11415834336062121?

Full code:

var geometryFactory = NetTopologySuite.NtsGeometryServices.Instance.CreateGeometryFactory(srid: 4326);

IPoint Location1 = geometryFactory.CreatePoint(new Coordinate(44.85009777, 41.72050718));
IPoint Location2 = geometryFactory.CreatePoint(new Coordinate(44.73594295, 41.71961028));

double distance12 = Location1.Distance(Location2);

I've used .Net Core 2.2 Preview 3.

Same problem is with Polygon.Area.

I need to get in meters (or in case area square meters).

Thank you

[DGuidi]

short story: SRID defines units of measurem degrees in your case; so you need to convert in meters by yourself
long story here and subsequent

[ajcvickers]

@jioruu It's interesting that the code you posted doesn't use EF at all. There are limitations in the kind of calculations that NTS does on the client. When using these functions in a query these limitations are not that important because they are translated and run on the database server anyway. So, from the perspective of feedback, is this the kind of code you wish to use in your application? If so, are you doing it now and with what spatial library/types?

[jioruu]

@DGuidi Thank you, now it is clear. As I've red using SRID 4326 means that unit of measurement is meter. Should I use different SRID for this? What about Area of polygon? Is this in degree too?

I have 1 more problem. I deployed .Net Core 2.2 project to IIS 7.5. All api request works, except methods, where I'm using spatial types.

I'm getting error:

Can you help in this case?

Thank you

[jioruu]

@ajcvickers This is just sample to show problem. Type is GeoAPI.Geometries.IPoint.

[aherrick]

@ajcvickers I would agree the distance method should behave the same on both the client and when hitting SQL Server. Return meters. This is the functionality of Entity Framework 6.

So when porting my App over from EF 6 to EF Core this is what I'm running into.

[bricelam]

As far as I understand it, NTS currently ignores the SRID and assumes a planar coordinate system. (In other words if your x and y coordinates are in degrees, operation results will also be in degrees.) To get distance, length, area, etc. in the right units, you need to project lat-long coordinates to an appropriate planar coordinate system using ProjNet4GeoAPI before computing these values on the client.

[aherrick]

So essentially Lat + Lng is not enough data to do an accurate distance measurement in meters?

[DGuidi]

@aherrick yep, because lat/long in 4326 are NOT meters

[aherrick]

Decided to just use this: https://www.nuget.org/packages/GeoCoordinate.NetCore/

var sCoord = new GeoCoordinate(sLatitude, sLongitude);
var eCoord = new GeoCoordinate(eLatitude, eLongitude);

return sCoord.GetDistanceTo(eCoord);

[weitzhandler]

So currently (as of 2.1), without wanting to leverage the actual SQL functionality of spatial data, is there at least a way to use these types in .NET? Are they anywhere in .NET Core or at least in an official NuGet package?

[bricelam]

@weitzhandler Can you clarify the question?

In EF Core 2.2, we're leveraging the NetTopologySuite spatial library to enable mapping to spatial data types in the database. SQL Server, SQLite (+SpatialLite), and PostgreSQL (+PostGIS) are all supported.

[bricelam]

I finally had some time to experiment with ProjNet4GeoAPI. Here's some code to project the coordinates before calculating the distance.

static class GeometryExtensions
{
    static readonly IGeometryServices _geometryServices = NtsGeometryServices.Instance;
    static readonly ICoordinateSystemServices _coordinateSystemServices
        = new CoordinateSystemServices(
            new CoordinateSystemFactory(),
            new CoordinateTransformationFactory(),
            new Dictionary<int, string>
            {
                // Coordinate systems: (3857 and 4326 included automatically)
                [2855] =
                @"
                    PROJCS[""NAD83(HARN) / Washington North"",
                        GEOGCS[""NAD83(HARN)"",
                            DATUM[""NAD83_High_Accuracy_Regional_Network"",
                                SPHEROID[""GRS 1980"",6378137,298.257222101,
                                    AUTHORITY[""EPSG"",""7019""]],
                                AUTHORITY[""EPSG"",""6152""]],
                            PRIMEM[""Greenwich"",0,
                                AUTHORITY[""EPSG"",""8901""]],
                            UNIT[""degree"",0.01745329251994328,
                                AUTHORITY[""EPSG"",""9122""]],
                            AUTHORITY[""EPSG"",""4152""]],
                        PROJECTION[""Lambert_Conformal_Conic_2SP""],
                        PARAMETER[""standard_parallel_1"",48.73333333333333],
                        PARAMETER[""standard_parallel_2"",47.5],
                        PARAMETER[""latitude_of_origin"",47],
                        PARAMETER[""central_meridian"",-120.8333333333333],
                        PARAMETER[""false_easting"",500000],
                        PARAMETER[""false_northing"",0],
                        UNIT[""metre"",1,
                            AUTHORITY[""EPSG"",""9001""]],
                        AUTHORITY[""EPSG"",""2855""]]
                "
            });

     public static IGeometry ProjectTo(this IGeometry geometry, int srid)
        => GeometryTransform.TransformGeometry(
            _geometryServices.CreateGeometryFactory(srid),
            geometry,
            _coordinateSystemServices.CreateTransformation(geometry.SRID, srid).MathTransform);
}

csharp var seattle = new Point(-122.333056, 47.609722) { SRID = 4326 }; var redmond = new Point(-122.123889, 47.669444) { SRID = 4326 }; var distance = seattle.ProjectTo(2855).Distance(redmond.ProjectTo(2855));

[weitzhandler]

• Is NetTopologySuite going to be the officially supported language EF is going to rely on?
• Can it be used with EF Core 2.1 meanwhile? Should I mark it NotMapped for now or there are better solutions? I don't mind waiting for the querying features until they're implemented, but I need the data to be in the DB.

[bricelam]

Is NetTopologySuite going to be the officially supported language EF is going to rely on?

It’s one spatial library supported by EF Core. If another popular library emerges, we’ll consider adding support for it too.

2.2 will release by the end of the year. At this point, I’d recommend trying the prereleases and waiting rather than investing in workarounds on 2.1.

[flensrocker]

@bricelam I have seen your issue about missing curves support in NTS: https://github.com/NetTopologySuite/NetTopologySuite/issues/247

We are starting to port our app to .Net Standard and EF Core. At this point the main missing feature is SqlGeometryBuilder.AddCircularArc. I hope, curves will be supported in some kind in the not so far future.

Great to see, that spatial types are coming!

[bricelam]

Hello everyone, we just published new docs on using spatial data in EF Core.

[mrcoymeeks]

@bricelam Extremely helpful, thank you!

[aherrick]

I still don't like that Location/Distance is ignored on the client. It doesn't make sense to me how to handle calculations across the US with these big string projections (2855 in your example)

[bricelam]

We too are eager to see this experience improved. We’ll continue working with the NTS team to try and improve the end-to-end spatial experience in EF Core.

[lawrencephillips]

Something weird is going on here
I'm loading WKT from my client application into a WKTReader and then passing the resulting geometry to SQLServer (after first correcting the shell)

var geometryFactory = new OgcCompliantGeometryFactory(new PrecisionModel(PrecisionModels.Floating), 27700);
var extentGeometry = new WKTReader(geometryFactory).Read(extent);

polygon in extent - "POLYGON((
371162.5815447777 102934.05565338745,
371162.5815447777 242741.18455963745,
671089.3393572777 242741.18455963745,
671089.3393572777 102934.05565338745,
371162.5815447777 102934.05565338745))"

The polygon in memory - POLYGON((
371162.58154477773 102934.05565338745,
671089.33935727773 102934.05565338745,
671089.33935727773 242741.18455963745,
371162.58154477773 242741.18455963745,
371162.58154477773 102934.05565338745
))

The polygon actually passed to SQL Server (as varbinary) - POLYGON ((
102934.05565338745 371162.58154477773,
102934.05565338745 671089.33935727773,
242741.18455963745 671089.33935727773,
242741.18455963745 371162.58154477773,
102934.05565338745 371162.58154477773))

Notice all the Easting/Northing values are backwards - what's going on here?

[lawrencephillips]

Is this because I've used geometry rather than geography? If so, how do you get NTS to send geometry compatible data?

[bricelam]

@lawrencephillips We'll need a full repro to investigate. Can you submit a new issue with more details?

[johnkwaters]

So in SQL server my coords are stored as Latitude and Longitude doubles.
What do I do to select records based on distance to a point in a ef core linq query? Is there some cast I can do in the DB query, or do I have to do a migration that converts my schema to use a spatial data type?

[Grauenwolf]

Why not store your coordinates as a geography point?

If you do then you can use spacial indexes.

Jonathan Allen
619-933-8527

---

From: John Waters [e-mail]
Sent: Tuesday, February 12, 2019 7:33:46 AM
To: aspnet/EntityFrameworkCore
Cc: Jonathan Allen; Mention
Subject: Re: [aspnet/EntityFrameworkCore] Support Spatial Data Types on SQL Server and SQLite (#1100)

So in SQL server my coords are stored as Latitude and Longitude doubles.
What do I do to select records based on distance to a point in a ef core linq query? Is there some cast I can do in the DB query, or do I have to do a migration that converts my schema to use a spatial data type?

—
You are receiving this because you were mentioned.
Reply to this email directly, view it on GitHubhttps://github.com/aspnet/EntityFrameworkCore/issues/1100#issuecomment-462805804, or mute the threadhttps://github.com/notifications/unsubscribe-auth/AKIqTbnatHVcWMtNroa3Gm3jG6M47cckks5vMt7agaJpZM4C9f_8.

[King-G]

I believe that the latest entity framework core has spatial data support.
This is an old issue. Try using Geography.

On Tue, 12 Feb 2019, 16:33 John Waters <[email protected] wrote:

So in SQL server my coords are stored as Latitude and Longitude doubles.
What do I do to select records based on distance to a point in a ef core
linq query? Is there some cast I can do in the DB query, or do I have to do
a migration that converts my schema to use a spatial data type?

—
You are receiving this because you are subscribed to this thread.
Reply to this email directly, view it on GitHub
https://github.com/aspnet/EntityFrameworkCore/issues/1100#issuecomment-462805804,
or mute the thread
https://github.com/notifications/unsubscribe-auth/AGsfMyF-UrC_qe4LTOuJLapigH1Ax0fUks5vMt7ZgaJpZM4C9f_8
.

[murbanowicz]

@bricelam is this workaround with projecting still required in 2.2 or not anymore?

[bricelam]

@murbanowicz Yes. Documented here. We're working with the NTS folks to try and improve this in future versions.

[andre-ss6]

Which package should I be using now if I want to use ADO.NET with SqlGeography? Microsoft.SqlServer.Types is still supported only on .Net Framework. And all I see about spatial data on .Net Core is about EF.

[ajcvickers]

@andre-ss6 There is currently no official way to use SqlGeography on .NET Core. (You can hack it in various ways, or read the binary protocol like EF does, but you can't use the actual released types.) This code is owned by the SQL Server team, but from a .NET Core side the issue is being tracked by https://github.com/dotnet/corefx/issues/31775

[MaceWindu]

@andre-ss6 you can try this re-implementation https://github.com/dotMorten/Microsoft.SqlServer.Types

[zp978]

Cant find much documentation on how to use NetTopologySuite with Entity Core. Looking for stuff like
Inserting a Poligon suing Entity Core Classes and NetTopologySuite, Inserting a Location Point using Enity Core with NetTopologySuite datatypes/property.

Links to any documentation on CRUD operations using Enitity Core/NetTopologySuite will be great.

[ajcvickers]

@zp978 Does https://docs.microsoft.com/en-us/ef/core/modeling/spatial cover what you are looking for?

[zp978]

Thanks for the reply. https://docs.microsoft.com/en-us/ef/core/modeling/spatial does not cover much,

Was looking for examples of CRUD operations to geography type columns .

Inserting a new row using (entity core/nettopologysuite) in DB with a Geography column type that will store a Polygon ? or Even inserting a new row in the DB with column (geography) storing location pount?

Have also checked http://www.npgsql.org/efcore/mapping/nts.html not much info there either.

[ajcvickers]

@zp978 Thanks for your feedback. I moved your issue to the docs repo here: https://github.com/aspnet/EntityFramework.Docs/issues/1471

[superdopey]

I finally had some time to experiment with ProjNet4GeoAPI. Here's some code to project the coordinates before calculating the distance.

static class GeometryExtensions
{
    static readonly IGeometryServices _geometryServices = NtsGeometryServices.Instance;
    static readonly ICoordinateSystemServices _coordinateSystemServices
        = new CoordinateSystemServices(
            new CoordinateSystemFactory(),
            new CoordinateTransformationFactory(),
            new Dictionary<int, string>
            {
                // Coordinate systems: (3857 and 4326 included automatically)
                [2855] =
                @"
                    PROJCS[""NAD83(HARN) / Washington North"",
                        GEOGCS[""NAD83(HARN)"",
                            DATUM[""NAD83_High_Accuracy_Regional_Network"",
                                SPHEROID[""GRS 1980"",6378137,298.257222101,
                                    AUTHORITY[""EPSG"",""7019""]],
                                AUTHORITY[""EPSG"",""6152""]],
                            PRIMEM[""Greenwich"",0,
                                AUTHORITY[""EPSG"",""8901""]],
                            UNIT[""degree"",0.01745329251994328,
                                AUTHORITY[""EPSG"",""9122""]],
                            AUTHORITY[""EPSG"",""4152""]],
                        PROJECTION[""Lambert_Conformal_Conic_2SP""],
                        PARAMETER[""standard_parallel_1"",48.73333333333333],
                        PARAMETER[""standard_parallel_2"",47.5],
                        PARAMETER[""latitude_of_origin"",47],
                        PARAMETER[""central_meridian"",-120.8333333333333],
                        PARAMETER[""false_easting"",500000],
                        PARAMETER[""false_northing"",0],
                        UNIT[""metre"",1,
                            AUTHORITY[""EPSG"",""9001""]],
                        AUTHORITY[""EPSG"",""2855""]]
                "
            });

     public static IGeometry ProjectTo(this IGeometry geometry, int srid)
        => GeometryTransform.TransformGeometry(
            _geometryServices.CreateGeometryFactory(srid),
            geometry,
            _coordinateSystemServices.CreateTransformation(geometry.SRID, srid).MathTransform);
}

var seattle = new Point(-122.333056, 47.609722) { SRID = 4326 };
var redmond = new Point(-122.123889, 47.669444) { SRID = 4326 };
var distance = seattle.ProjectTo(2855).Distance(redmond.ProjectTo(2855));

Hi @bricelam I am trying to implement your example, but I get "The name 'GeometryTransform' does not exist in the current context".

I have added the ProjNET4GeoAPI and Microsoft.EntityFrameworkCore.SqlServer.NetTopologySuite nuget package. Where does this 'GeometryTransform' come from?

[superdopey]

Nevermind I was missing the NetTopologySuite nuget package.

[bricelam]

@superdopey Re-opened aspnet/EntityFramework.Docs#1260

[mouaddjedidi]

@bricelam I tried to use your code with postgres for 2 points around paris and I get a distance with about 8 Km difference with respect to what I get in sql. Please refer to the example I put in my question

[bricelam]

@mouaddjedidi The coordinate system 2855 is designed to give accurate results for the area around Redmond. See https://epsg.io/2855 (you can use that site to find a suitable coordinate system for Paris)

[piatkiewicz]

Nevermind I was missing the NetTopologySuite nuget package.

I'm not missing it, but still getting this error

[roji]

@piatkiewicz can you please open a new issue with the full error and a runnable code sample?

[piatkiewicz]

@roji sorry i'm not familiar with this topic and decided to other solution to my problem so removed code already, but all I did was copy-paste code above and install all what VS proposed.