Tag Archives: NetTopologySuite

Airbnb Dataset – NetTopologySuite spatial searching

Posted on by

I have used Entity Framework Core a “full fat” Object Relational Mapper (ORM) for a couple of projects, and it supports mapping to spatial data types using the NetTopologySuite library. On Stackoverflow there was some discussion about Dapper’s spatial support so I thought I would try it out.

The DapperSpatialNetTopologySuite project has Dapper SQLMapper TypeHandler, Microsoft SQL Server stored procedure name and ASP.NET Core Minimal API for each location column handler implementation.

app.MapGet("/Spatial/NearbyGeography", async (double latitude, double longitude, int distance, [FromServices] IDapperContext dapperContext) =>
{
   var origin = new Point(longitude, latitude) { SRID = 4326 };

   using (var connection = dapperContext.ConnectionCreate())
   {
      var results = await connection.QueryWithRetryAsync<Model.ListingNearbyListGeographyDto>("ListingsSpatialNearbyNTS_____", new { origin, distance }, commandType: CommandType.StoredProcedure);

      return results;
   }
})
.Produces<IList<Model.ListingNearbyListGeographyDto>>(StatusCodes.Status200OK)
.Produces<ProblemDetails>(StatusCodes.Status400BadRequest)
.WithOpenApi();

After adding the NetTopologySuite spatial library to the project the schemas list got a lot bigger.

NetTopology Suite additional schemas

My first attempt inspired by a really old Marc Gravell post and the NetTopologySuite.IO.SqlServerBytes documentation didn’t work.

CREATE PROCEDURE [dbo].[ListingsSpatialNearbyNTSLocation]
	@Origin AS GEOGRAPHY,
	@distance AS INTEGER
AS
BEGIN
DECLARE @Circle AS GEOGRAPHY = @Origin.STBuffer(@distance); 

SELECT TOP(50) UID AS ListingUID
	,[Name]
	,listing_url as ListingUrl
	,Listing.Location.STDistance(@Origin) as Distance
	,Listing.Location
FROM Listing
WHERE (Listing.Location.STWithin(@Circle) = 1) 
ORDER BY Distance
END
NetTopology Suite Microsoft.SqlServer.Types library load exception

I could see the Dapper SQLMapper TypeHandler for the @origin parameter getting called but the not locations

NetTopology Suite @Origin parameter typehandler in Visual Studio 2022 Debugger

Then found a Brice Lambson post about how to use the NetTopologySuite.IO.SqlServerBytes library to read and write geography and geometry columns.

CREATE PROCEDURE [dbo].[ListingsSpatialNearbyNTSSerialize]
	@Origin AS GEOGRAPHY,
	@distance AS INTEGER
AS
BEGIN
DECLARE @Circle AS GEOGRAPHY = @Origin.STBuffer(@distance); 

SELECT TOP(50) UID AS ListingUID
	,[Name]
	,listing_url as ListingUrl
	,Listing.Location.STDistance(@Origin) as Distance
	,Location.Serialize() as Location
FROM [listing] 
WHERE (Location.STWithin(@Circle) = 1) 
ORDER BY Distance
END

class PointHandlerSerialise : SqlMapper.TypeHandler<Point>
{
   public override Point Parse(object value)
   {
      var reader = new SqlServerBytesReader { IsGeography = true };

      return (Point)reader.Read((byte[])value);
   }

   public override void SetValue(IDbDataParameter parameter, Point? value)
   {
      ((SqlParameter)parameter).SqlDbType = SqlDbType.Udt;  // @Origin parameter?
      ((SqlParameter)parameter).UdtTypeName = "GEOGRAPHY";

      var writer = new SqlServerBytesWriter { IsGeography = true };

      parameter.Value = writer.Write(value);
   }
}

Once the location column serialisation was working (I could see a valid response in the debugger) the generation of the response was failing with a “System.Text.Json.JsonException: A possible object cycle was detected. This can either be due to a cycle or if the object depth is larger than the maximum allowed depth of 64″.

NetTopology Suite serialisation “possible object cycle detection” exception

After fixing that issue the response generation failed with “System.ArgumentException: .NET number values such as positive and negative infinity cannot be written as valid JSON.”

NetTopology Suite serialisation “possible object cycle detection” exception

Fixing these two issues required adjustment of two HttpJsonOptions

//...
builder.Services.ConfigureHttpJsonOptions(options =>
{
   options.SerializerOptions.ReferenceHandler = ReferenceHandler.IgnoreCycles;
   options.SerializerOptions.NumberHandling = JsonNumberHandling.AllowNamedFloatingPointLiterals;
});

var app = builder.Build();
//...
Swagger NetTopology Suite location serialisation response

After digging into the Dapper source code I wondered how ADO.Net handled loading Microsoft.SQLServer.Types library

app.MapGet("/Listing/Search/Ado", async (double latitude, double longitude, int distance, [FromServices] IDapperContext dapperContext) =>
{
   var origin = new Point(longitude, latitude) { SRID = 4326 };

   using (SqlConnection connection = (SqlConnection)dapperContext.ConnectionCreate())
   {
      await connection.OpenAsync();

      var geographyWriter = new SqlServerBytesWriter { IsGeography = true };

      using (SqlCommand command = connection.CreateCommand())
      {
         command.CommandText = "ListingsSpatialNearbyNTSLocation";
         command.CommandType = CommandType.StoredProcedure;

         var originParameter = command.CreateParameter();
         originParameter.ParameterName = "Origin";
         originParameter.Value = new SqlBytes(geographyWriter.Write(origin));
         originParameter.SqlDbType = SqlDbType.Udt;
         originParameter.UdtTypeName = "GEOGRAPHY";
         command.Parameters.Add(originParameter);

         var distanceParameter = command.CreateParameter();
         distanceParameter.ParameterName = "Distance";
         distanceParameter.Value = distance;
         distanceParameter.DbType = DbType.Int32;
         command.Parameters.Add(distanceParameter);

         var geographyReader = new SqlServerBytesReader { IsGeography = true };

         using (var dbDataReader = await command.ExecuteReaderAsync())
         {
            List<Model.ListingNearbyListGeographyDto> listings = new List<Model.ListingNearbyListGeographyDto>();

            int listingUIDColumn = dbDataReader.GetOrdinal("ListingUID");
            int nameColumn = dbDataReader.GetOrdinal("Name");
            int listingUrlColumn = dbDataReader.GetOrdinal("ListingUrl");
            int distanceColumn = dbDataReader.GetOrdinal("Distance");
            int LocationColumn = dbDataReader.GetOrdinal("Location");

            while (await dbDataReader.ReadAsync())
            {
               listings.Add(new Model.ListingNearbyListGeographyDto
               {
                  ListingUID = dbDataReader.GetGuid(listingUIDColumn),
                  Name = dbDataReader.GetString(nameColumn),
                  ListingUrl = dbDataReader.GetString(listingUrlColumn),
                  Distance = (int)dbDataReader.GetDouble(distanceColumn),
                  Location = (Point)geographyReader.Read(dbDataReader.GetSqlBytes(LocationColumn).Value)
               });
            }

            return listings;
         }
      }
   }
})
.Produces<IList<Model.ListingNearbyListGeographyDto>>(StatusCodes.Status200OK)
.Produces<ProblemDetails>(StatusCodes.Status400BadRequest)
.WithOpenApi();

The ADO.Net implementation worked and didn’t produce any exceptions.

Swagger ADO.Net location serialisation response

In the Visual Studio 2022 debugger I could see the Microsoft.SQLServer.Types exception but this wasn’t “bubbling” up to the response generation code.

ADO.Net location serialisation Microsoft.SqlServer.Types load failure

The location columns could also be returned as Open Geospatial Consortium (OGC) Well-Known Binary (WKB) format using the STAsBinary method.

CREATE PROCEDURE [dbo].[ListingsSpatialNearbyNTSWkb]
	@Origin AS GEOGRAPHY,
	@distance AS INTEGER
AS
BEGIN
DECLARE @Circle AS GEOGRAPHY = @Origin.STBuffer(@distance); 

SELECT TOP(50) UID AS ListingUID
	,[Name]
	,listing_url as ListingUrl
	,Listing.Location.STDistance(@Origin) as Distance
	,Location.STAsBinary() as Location
FROM [listing] 
WHERE (Location.STWithin(@Circle) = 1) 
ORDER BY Distance
END

Then converted to and from NTS Point values using WKBReader and SqlServerBytesWriter 

SqlMapper.AddTypeHandler(new PointHandlerWkb());
//...
class PointHandlerWkb : SqlMapper.TypeHandler<Point>
{
   public override Point Parse(object value)
   {
      var reader = new WKBReader();

      return (Point)reader.Read((byte[])value);
   }

   public override void SetValue(IDbDataParameter parameter, Point? value)
   {
      ((SqlParameter)parameter).SqlDbType = SqlDbType.Udt;  // @Origin parameter?
      ((SqlParameter)parameter).UdtTypeName = "GEOGRAPHY";

      var geometryWriter = new SqlServerBytesWriter { IsGeography = true };

      parameter.Value = geometryWriter.Write(value);
   }
}
Successful Location processing with WKBReader

The location columns could also be returned as Open Geospatial Consortium (OGC) Well Known Text(WKT) format using the STAsText and SqlServerBytesWriter;

CREATE PROCEDURE [dbo].[ListingsSpatialNearbyNTSWkt]
	@Origin AS GEOGRAPHY,
	@distance AS INTEGER
AS
BEGIN
DECLARE @Circle AS GEOGRAPHY = @Origin.STBuffer(@distance); 

SELECT TOP(50) UID AS ListingUID
	,[Name]
	,listing_url as ListingUrl
	,Listing.Location.STDistance(@Origin) as Distance
	,Location.STAsText() as Location
FROM [listing] 
WHERE (Location.STWithin(@Circle) = 1) 
ORDER BY Distance
END

Then converted to and from NTS Point values using WKTReader and SqlServerBytesWriter

class PointHandlerWkt : SqlMapper.TypeHandler<Point>
{
   public override Point Parse(object value)
   {
      WKTReader wktReader = new WKTReader();

      return (Point)wktReader.Read(value.ToString());
   }

   public override void SetValue(IDbDataParameter parameter, Point? value)
   {
      ((SqlParameter)parameter).SqlDbType = SqlDbType.Udt;  // @Origin parameter?
      ((SqlParameter)parameter).UdtTypeName = "GEOGRAPHY";

      parameter.Value = new SqlServerBytesWriter() { IsGeography = true }.Write(value);
   }
}
Successful Location processing with WKBReader

I have focused on getting the spatial queries to work and will stress/performance test my implementations in a future post. I will also revisit the /Spatial/NearbyGeography method to see if I can get it to work without “Location.Serialize() as Location”.

Downloaded Microsoft.Data.SqlClient source code and in SqlConnection.cs this doesn’t help….

  // UDT SUPPORT
  private Assembly ResolveTypeAssembly(AssemblyName asmRef, bool throwOnError)
  {
      Debug.Assert(TypeSystemAssemblyVersion != null, "TypeSystemAssembly should be set !");
      if (string.Equals(asmRef.Name, "Microsoft.SqlServer.Types", StringComparison.OrdinalIgnoreCase))
      {
          if (asmRef.Version != TypeSystemAssemblyVersion && SqlClientEventSource.Log.IsTraceEnabled())
          {
              SqlClientEventSource.Log.TryTraceEvent("SqlConnection.ResolveTypeAssembly | SQL CLR type version change: Server sent {0}, client will instantiate {1}", asmRef.Version, TypeSystemAssemblyVersion);
          }
          asmRef.Version = TypeSystemAssemblyVersion;
      }
      try
      {
          return Assembly.Load(asmRef);
      }
      catch (Exception e)
      {
          if (throwOnError || !ADP.IsCatchableExceptionType(e))
          {
              throw;
          }
          else
          {
              return null;
          }
      }
  }