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 #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.

[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 <[email protected]> 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 GitHub<#1100 (comment)>, or mute the thread<https://github.com/notifications/unsubscribe-auth/AKIqTbnatHVcWMtNroa3Gm3jG6M47cckks5vMt7agaJpZM4C9f_8>.

[KarimGabsi]

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 ***@***.*** 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 <#1100 (comment)>, 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: dotnet/EntityFramework.Docs#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 dotnet/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.