XPO, SQL Server and spatial data revisited

Oliver's Blog
16 January 2017

All the way back in 2008, I wrote a blog post showing how spatial data can be persisted using XPO in conjunction with the built-in data types SqlGeography and SqlGeometry in SQL Server. You can find my original post by following this link.

Recently our support guys brought a request to my attention, where it was pointed out that the mechanism I demonstrated in my old blog post did not correctly take the SRID value into account that is associated with the SQL Server types. I'm not an expert in GIS, but I understand that this ID specifies the type or scope of the information. This obviously seems important, but curiously the standard text or XML formats supported for (readable) serialization of SqlGeography or SqlGeometry data don't contain or even support this field. Because my implemented mechanism relied on the WKT (well-known text - creative naming!) format, the SRID was lost.

I looked around a bit for a mechanism I could use instead. There is a SQL API to fetch the SRID associated with a column, where the syntax makes the SRID appear like a sub-column:

select MYCOLUMN.STSrid from MYTABLE

However, this  is not an approach easily usable from XPO because it would require running an extra query (or alternatively extend the query run by XPO to include the separate field). When data is selected from the column itself, it does not contain the SRID value, but only the string representations I had previously used.

In the end it turned out that I had already summarized the solution in my previous article. Towards the end, I was elaborating on a performance issue I found back then, because it seemed sub-optimal to be converting the values into and out of string format several times. I also said I wouldn't recommend that approach because it neglected certain contracts assumed by XPO. I stand by these comments, but at the same time this approach seems to be the only way of getting hold of the SRID without making changes to the existing query. Compared to my old code, I made the following changes.

First, to persist the SRID together with the spatial data, I changed the ConvertToStorageType method to this:

public override object ConvertToStorageType(object value) {
  if (value == null) return null;

  var sqlGeography = value as SqlGeography;
  if (sqlGeography != null)
    return sqlGeography.Serialize();
  else {
    var sqlGeometry = value as SqlGeometry;
    if (sqlGeometry != null)
      return sqlGeometry.Serialize();
    else
      return value;
  }
}


Instead of using a string representation, I'm simply using the standard serialization format supported by the types. The Serialize helper produces a SqlBytes object, and the data includes the SRID. 

Second, I changed the ConvertFromStorageType method to not do any actual conversion. Technically this is unnecessary because the SqlClient that loads the data on some low level, automatically constructs instances of SqlGeography or SqlGeometry. This is the topic that was already described in my previous post, and I'm now ignoring the conversion contract by skipping this step.

public override object ConvertFromStorageType(object value) {
  return value;
}


Finally, I changed the ReformatReadValue method implementation to also skip any conversion, simply returning the original value when I find the type to be one of the supported ones. This has the effect of deactivating the base class behavior, which would otherwise result in runtime exceptions because there is no automatic conversion method.

protected override object ReformatReadValue(object value, ReformatReadValueArgs args) {
  if (value != null) {
    Type valueType = value.GetType();
    if (valueType == typeof(SqlGeography) || valueType == typeof(SqlGeometry))
      return value;
  }
  return base.ReformatReadValue(value, args);
}


While I still subscribe to the idealistic view that contracts shouldn't be ignored (in other words, methods should not be overridden or implemented to do something different than they are supposed to), I see these changes as a reasonable approach under the circumstances. Any other technique would be much more complicated to implement, and the past nine years have shown that the APIs have remained stable and unchanged. I hope this mechanism will do the job for those of you who work with this combination of XPO and SQL Server.

You can find the complete sample in a github repository here: xpo-sqlserver-spatial

The sample has been updated to work with our latest release, and to utilize Microsoft's nuget package for the supporting Microsoft.SqlServer.Types assembly. In case you'd like to compare, you can also follow this link to see the sample from the original blog post, updated for versions but otherwise unchanged.





Showcase Your Apps on DevExpress.com

Highlight your business app and share your development experiences with the DevExpress community. To include your app in our upcoming App Showcase, please forward an application screenshot to clientservices@devexpress.com and tell us which DevExpress products you currently use within your organization.
Tags
7 comment(s)
Murat YAVUZ
Murat YAVUZ

Thank yoıu very much

26 January 2017
Sigurd Decroos _
Sigurd Decroos _

Do you have a .Net Core 2.1 sample?

14 June 2018
Oliver Sturm (DevExpress)
Oliver Sturm (DevExpress)

I don't. Have you tried the described approach in .NET Core?

I find the question interesting and I'll look into it as soon as I can find some time to do it.

14 June 2018
Alejandro Padilla V
Alejandro Padilla V

Hi Oliver, did you tried to use NetTopologySuite instead of Microsoft Sql Server Spatial Types?.. they released the latest version and it is compatible with .Net Standard 2.0, they has a lot of GIS Tools, it might help us?


greetings


18 May 2020
Oliver Sturm (DevExpress)
Oliver Sturm (DevExpress)

Hi Alejandro,

I have not tried it, but I just had a look at the project and the underlying OpenGIS spec that NetTopologySuite and its ancestor JTS adhere to. I didn't check that the support in NTS is complete.

OpenGIS includes an interesting document (https://www.ogc.org/standards/sfs) which describes how to persist relevant information in a SQL database in the absence of special storage data types. It includes interfaces of sets of SQL functions which could be used for server-side calculations on the basis of that data.

A quick look at the source code of NTS does not show an implementation of these server-side features. It appears that the client-side support for functions working with the data types is strong, but it relies on the idea that all relevant data is downloaded to the client before being processed. OpenGIS follows the idea that server-side processing could be used, albeit with standard SQL datatypes, and implemented in SQL. SQL Server Spatial Types could technically have a performance advantage over the OpenGIS approach - I would expect this to be true, but I have not tested it.

From an XPO point of view, persisting types to be compatible with the OpenGIS schema (or the NTS types) should be easy - they are simply structures that aggregate standard SQL types to represent spatial information. You gain cross-platform compatibility, but you lose the ability to process directly on the database server - I imagine this could be a problem in many scenarios.

Thanks

Oliver


19 May 2020
Alejandro Padilla V
Alejandro Padilla V
Hi Oliver
Excellent information, thank you very much for sharing. I hope that the implementation is easy for the XPO. Suddenly perhaps the information might interest for you, the EF Core used it with the NTS library and it has really worked well, so I think it might a useful information for you.  Although I am working to migrate the ORM Project (NHIbernate) to XPO, but  it is not a idea to migrate to EF Core due to several reasons.


Now the next question, do you have any plans for its short-term implementation? Because I'm working on the migration from the .NET Framework to the .NET Core, that's why I'm restless on this topic.

Thank you




19 May 2020
Oliver Sturm (DevExpress)
Oliver Sturm (DevExpress)

Hi Alejandro,

I just tried the approaches shown in the blog post with .NET Core 3.1, and basically everything works without issues. I have created a sample in a branch of the repository, here: https://github.com/oliversturm/xpo-sqlserver-spatial/tree/netcore-3.1

There is one caveat: I'm not entirely clear on compatibility. I encountered two issues that I haven't researched in much detail. First, I had to add the package System.Data.SqlClient because I received a runtime exception complaining about the version of that assembly. Adding the package solved the problem, but during installation I saw warnings about the target framework - apparently the package is not configured to target .NET Core.

Second, when I installed the package Microsoft.SqlServer.Types I found that the sub-folder SqlServerTypes was not created (as it was for the older sample project). I commented the line that called SqlServerTypes.Utilities.LoadNativeAssemblies(), but then I saw an exception because SqlServerSpatial140.dll could not be found at runtime. I researched this and found a suggestion on StackOverflow (https://stackoverflow.com/questions/42654893/unable-to-load-dll-sqlserverspatial140-dll-the-specified-module-could-not-be) to copy the DLL from the nuget folder. I did this and it solved the problem.

As a result of these two issues, I'm not clear whether Microsoft intends these two packages to be used with .NET Core. I strongly suspect that it wouldn't be possible to run the application on a non-Windows platform using this approach. There may be a way, but that's a question for Microsoft. As far as XPO is concerned, everything works correctly on .NET Core, assuming that the SQL Server specific infrastructure is in place.


Thanks

Oliver


20 May 2020

Please login or register to post comments.