sql geography to dbgeography?

Question

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?

Montsaintmichel

Answer 1

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!

Troop

I'm actually on another project where this came back up again, so thanks, just what I was looking for. – Montsaintmichel

I've seen this recommended a few times but the MakeValid method does not seem to be available on SqlGeography type when used in .NET. Am I missing something? – Poky

@jm2 if you've referenced Microsoft.SqlServer.Types it should be available to you. – Troop

@JonBellamy I have. Assembly v10.0.0.0 with runtime v2.0.50727. SqlGeometry and SqlGeography types are available, SqlGeometry has MakeValid, SqlGeography does not. Referencing wrong version perhaps? This is probably best suited for another question. – Poky

@jm2, perhaps you need Assembly v11.0.0.0? That's what I use. – Troop

If you don't want to hard-code the SRID, you can use DBGeography.FromText(theGeography.ToString(), theGeography.STSrid.Value). – Gorge

For posterity, the "MakeValid" issue is a matter of SQL Server version. From what I can gather, MakeValid was not a function of the SqlGeography type until SQL Server version 2012. Therefore if an earlier version of SQL Server is installed, the MakeValid function will not be available in .NET applications even when referencing Microsoft.SqlServer.Types. I'm not aware of a way to update the assembly version without updating the SqlServer version. Maybe you could snatch the dll from somewhere and add it to the GAC, but I would imagine that would cause side-effects. – Poky

@Poky Thanks for clearing that up. MakeValid() actually shouldn't be required if the Geography instance is already well formed, but I've tended to find that more often than not, spatial sources break the rules due to poor quality data. – Troop

Serializing to WKT or WKB is not the best/fast way to do it – Lundeen

Answer 2

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

Fakieh

For the reverse operation: SqlGeography.STGeomFromWKB(new SqlBytes(value.AsBinary()), value.CoordinateSystemId) – Rauwolfia

Answer 3

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.

Retarder

Could you be more specific? Exactly what happens and what is the correct solution? – Latashialatch

For anyone else reading this, I still use my provided solution with EF6 with zero problems. – Troop

Answer 4

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

Seaddon

Answer 5

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.

Shaunna

Answer 6

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

Lundeen

I will add that if you're using SqlGeography greater than v10 (SQL 2008), then this method will throw an error, meaning if you need any of the features of SQL 2012 and above, you're screwed. Performance is good though. – Troop

I'm going to assume there's no reverse method also? – Troop

There should be a reverse method. Cannot tell right now. – Lundeen