CODEWITHSUNDEEP
sql-serverc#-4.0gisgeospatialspatial
Eric
Eric

Reputation: 2313

sql geography to dbgeography?

Maybe I'm missing something. I have a sql server column of the "Geography" datatype.

I want to use the DbGeography type in my c# code. Any way to cast or convert from sql's geography to dbgeography?

Upvotes: 18

Views: 10508

Answers (6)

Mark Glasgow
Mark Glasgow

Reputation: 541

Based on my reading of EntityFramework.SqlServer.dll in ILSpy, I believe the quickest way to convert from SqlGeography to DbGeography is:

var dbGeo = System.Data.Entity.SqlServer.SqlSpatialServices.Default.GeographyFromProviderValue(sqlGeo);

This method has the advantage that no binary conversion and parsing is required. It simply returns a DbGeography using the SqlGeography as the internal provider value.

Upvotes: 1

Sergei Zinovyev
Sergei Zinovyev

Reputation: 1286

I found this as a working solution:

int coordSys = DbGeography.DefaultCoordinateSystemId; // 4326; 
SqlGeography g = SqlGeography.Point(lat, lon, coordSys);
return DbSpatialServices.Default.GeographyFromProviderValue(g);

No serializing/converting to string (WKT) or binary (WKB) that kill performance.

It is working for me on EF 6.1.3, SqlServer 2016 SP1 and Microsoft.SqlServer.Types.14.0.314.76

Upvotes: 0

Serge Belov
Serge Belov

Reputation: 5803

When the performance is of any importance, the well-known binary should be used instead of the well-known text:

var newGeography = DbGeography.FromBinary(theGeography.STAsBinary().Value);

There is an overload using a SRID, if that is important. In my simple test with a 1,000 reasonably complicated polygons the binary-based approach is 4 times faster than the text-based one:

* Binary-based conversion
Run 1: 1948
Run 2: 1944
Run 3: 1959
Run 4: 1979
Run 5: 1988
Average: 1963.6

* Text-based conversion
Run 1: 8527
Run 2: 8553
Run 3: 8596
Run 4: 8535
Run 5: 8496
Average: 8541.4

Upvotes: 10

phipex
phipex

Reputation: 741

You must add a reference to the assemblies as mentioned above. The following post may help you link,link2

Upvotes: 1

André Sobreiro
André Sobreiro

Reputation: 51

The provided solution seems to be ok if you are not running EF6. With early versionsit´s ok, but with EF6, we shouldnt make references to this assembly. It turns EF litle crazy.

Upvotes: 1

Jon Bellamy
Jon Bellamy

Reputation: 3373

Sorry for the late response - but saw this whilst searching for something else.

Simply do the following:

SqlGeography theGeography;
int srid = 4326; // or alternative

DbGeography newGeography = DbGeography.FromText(theGeography.ToString(), srid);

To reverse it:

DbGeography theGeography;
SqlGeography newGeography = SqlGeography.Parse(theGeography.AsText()).MakeValid();

Hope that helps!

Upvotes: 29

Related Questions