New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Support Spatial Data Types on SQL Server and SQLite #1100

Closed
vijayantkatyal opened this Issue Nov 19, 2014 · 294 comments

Comments

Projects
None yet
@vijayantkatyal

vijayantkatyal commented Nov 19, 2014

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

This comment has been minimized.

Member

rowanmiller commented Nov 24, 2014

@vijayantkatyal are you asking about EF Core?

@simonseyock

This comment has been minimized.

simonseyock commented Nov 25, 2014

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 rowanmiller changed the title from How to use Spatial Data Types ? to Support Spatial Data Types Nov 26, 2014

@rowanmiller

This comment has been minimized.

Member

rowanmiller commented Nov 26, 2014

Partially covered by #242 but we probably also want to look at built-in support for spatial types.

@rowanmiller rowanmiller added this to the Backlog milestone Nov 26, 2014

@vijayantkatyal

This comment has been minimized.

vijayantkatyal commented Nov 29, 2014

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

@neil-119

This comment has been minimized.

neil-119 commented Sep 7, 2015

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

@roji

This comment has been minimized.

Contributor

roji commented Sep 30, 2015

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

This comment has been minimized.

Member

bricelam commented Sep 30, 2015

@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

This comment has been minimized.

Contributor

roji commented Oct 1, 2015

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

This comment has been minimized.

balivo commented Dec 18, 2015

Any news about Spatial types in EF Core?

@dfaivre

This comment has been minimized.

dfaivre commented Dec 18, 2015

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

This comment has been minimized.

Member

rowanmiller commented Dec 22, 2015

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

This comment has been minimized.

balivo commented Dec 29, 2015

Tks for answer @rowanmiller...

@BehnamAbdy

This comment has been minimized.

BehnamAbdy commented Feb 25, 2016

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

@RobertDM

This comment has been minimized.

RobertDM commented Apr 2, 2016

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

This comment has been minimized.

xperiandri commented Apr 17, 2016

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

This comment has been minimized.

Ronan-Farrell commented May 3, 2016

Also a vote here for some DbGeography support!

@roji

This comment has been minimized.

Contributor

roji commented May 3, 2016

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

This comment has been minimized.

spass2000 commented Jun 11, 2016

Any Timeline to support DbGeography or DbGeometry?

@ErikEJ

This comment has been minimized.

Contributor

ErikEJ commented Jun 11, 2016

@ischas

This comment has been minimized.

ischas commented Jun 11, 2016

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

@RobertDM

This comment has been minimized.

RobertDM commented Jun 11, 2016

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.

@btecu

This comment has been minimized.

btecu commented Oct 2, 2018

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

This comment has been minimized.

Member

bricelam commented Oct 2, 2018

@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

This comment has been minimized.

Member

bricelam commented Oct 2, 2018

@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

This comment has been minimized.

Member

ajcvickers commented Oct 2, 2018

@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

This comment has been minimized.

FransBouma commented Oct 3, 2018

@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

This comment has been minimized.

Contributor

roji commented Oct 4, 2018

@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

This comment has been minimized.

Member

ajcvickers commented Oct 5, 2018

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

@bricelam

This comment has been minimized.

Member

bricelam commented Oct 10, 2018

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.

@bricelam bricelam closed this Oct 10, 2018

@roji

This comment has been minimized.

Contributor

roji commented Oct 21, 2018

@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 (npgsql/Npgsql.EntityFrameworkCore.PostgreSQL#658), 2.2.0-preview3 should be out in a few days.

@Olbrasoft

This comment has been minimized.

Olbrasoft commented Oct 28, 2018

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
var entity1 new Entity
{
  CenterCoordinates = NetTopologySuite.Geometries.Point.Empty
}
Context.Add(entity1)
Context.SaveChanges() ... throw Exception SRID
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

This comment has been minimized.

Member

ajcvickers commented Oct 28, 2018

@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

This comment has been minimized.

jioruu commented Oct 31, 2018

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

This comment has been minimized.

DGuidi commented Oct 31, 2018

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

This comment has been minimized.

Member

ajcvickers commented Oct 31, 2018

@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

This comment has been minimized.

jioruu commented Oct 31, 2018

@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:
image

Can you help in this case?

Thank you

@jioruu

This comment has been minimized.

jioruu commented Oct 31, 2018

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

@aherrick

This comment has been minimized.

aherrick commented Nov 2, 2018

@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

This comment has been minimized.

Member

bricelam commented Nov 2, 2018

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

This comment has been minimized.

aherrick commented Nov 3, 2018

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

@DGuidi

This comment has been minimized.

DGuidi commented Nov 3, 2018

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

@aherrick

This comment has been minimized.

aherrick commented Nov 3, 2018

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

This comment has been minimized.

Contributor

weitzhandler commented Nov 5, 2018

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

This comment has been minimized.

Member

bricelam commented Nov 5, 2018

@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

This comment has been minimized.

Member

bricelam commented Nov 6, 2018

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));
@weitzhandler

This comment has been minimized.

Contributor

weitzhandler commented Nov 6, 2018

  • 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

This comment has been minimized.

Member

bricelam commented Nov 6, 2018

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

This comment has been minimized.

flensrocker commented Nov 12, 2018

@bricelam I have seen your issue about missing curves support in NTS: NetTopologySuite/NetTopologySuite#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!

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment