18

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?

Eric
  • 2,273
  • 2
  • 29
  • 44

6 Answers6

29

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!

Jon Bellamy
  • 3,333
  • 20
  • 23
  • I'm actually on another project where this came back up again, so thanks, just what I was looking for. – Eric Sep 09 '13 at 20:34
  • 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? – joelmdev Oct 11 '13 at 13:10
  • @jm2 if you've referenced Microsoft.SqlServer.Types it should be available to you. – Jon Bellamy Oct 11 '13 at 16:21
  • @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. – joelmdev Oct 11 '13 at 16:31
  • @jm2, perhaps you need Assembly v11.0.0.0? That's what I use. – Jon Bellamy Oct 14 '13 at 14:32
  • If you don't want to hard-code the SRID, you can use `DBGeography.FromText(theGeography.ToString(), theGeography.STSrid.Value)`. – Anthony Mills Nov 03 '13 at 23:51
  • 2
    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. – joelmdev Feb 12 '14 at 16:13
  • @joelmdev 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. – Jon Bellamy Feb 12 '14 at 16:25
  • Serializing to WKT or WKB is not the best/fast way to do it – Sergei Zinovyev Apr 09 '17 at 02:55
10

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
Serge Belov
  • 5,633
  • 1
  • 31
  • 40
  • 1
    For the reverse operation: `SqlGeography.STGeomFromWKB(new SqlBytes(value.AsBinary()), value.CoordinateSystemId)` – Marc Gravell Mar 23 '15 at 09:22
1

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.

1

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

phipex
  • 721
  • 11
  • 14
1

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.

Mark Glasgow
  • 529
  • 2
  • 9
0

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

Sergei Zinovyev
  • 1,238
  • 14
  • 14