1

We are using a SQL Server Spatial database with the following table setup:

  • SRID of 27700
  • geometry_column of ogr_geometry
  • geometry_type of POLYGON

I am using a SQL Server stored procedure to perform the following test:

geometry::STGeomFromText(@poly, @srid).STIsValid();

When tested the using the following it correctly returns true:

.Parameters.Append .CreateParameter("@poly ", 200, 1, 50, "POLYGON((1 1, 3 3, 3 1, 1 1))")
.Parameters.Append .CreateParameter("@srid ", 3, 1, 10, 0)

When I run the live data (held in the recordset rs) it always returns false even though I suspect about half the polygons should be true:

.Parameters.Append .CreateParameter("@poly ", 200, 1, 1000000000, rs("ogr_geometry"))
.Parameters.Append .CreateParameter("@srid ", 3, 1, 10, 27700)

Am I doing anything that is obviously wrong or could it be the case that although some of the geometry works in our system none of it is strictly valid?

I have prepared a ZIP file with an example polygon that works and one that does not. Both are being flagged by SQL Server as invalid.

  • 1
    If you have SQL Server Management Studio, you can use Profiler to check the query sent to SQL Server and ensure it's what you expect it to be. – David Browne - Microsoft Nov 3 '18 at 15:43
  • 1
    Are you able to share an example of the ogr_geometry you are putting through. I would suspect that you having a projection/srid issue. I am unfamiliar with the projections around the UK (Isle of Man), but with SQL Server you cannot compare geometries without the same SRID or projection. You may need to do a reprojection of one before the comparison – MickyT Nov 5 '18 at 1:08
  • Hi @MickyT sure no problem. I would be very grateful if you could take a look at the polygons from the ogr_geometry fields. I have prepared a ZIP file with an example polygon that works and one that does not. Both are being flagged by SQL Server as invalid. The zip file is at drive.google.com/open?id=1ZaI2_JoPsqqpzrocdbduuCSrFHcoSVnw – Mike Poole Nov 7 '18 at 5:45
  • @DavidBrowne-Microsoft yes I have Profiler 2017 installed. I think you are onto something there because the RCP log for each query has thousands of encoding ??? in the results with the occasional recognizable character instead of the polygon that I am trying to pass in from the recordset. Here is a truncated example: exec dbo.spTestPolygon '? ?? ?????????????z?????? ????'. It makes me wonder if I am doing something stupid with the way I am passing the recordset field into the sproc. – Mike Poole Nov 7 '18 at 6:01
  • 1
    I don't see any problems with the polygons posted. When I checked them using SELECT Geometry::STGeomFromText(' ... ',27700).STIsValid() they are both valid. – MickyT Nov 7 '18 at 17:39
0

This issue was being caused because the ogr_geometry coming from the recordset had not been cast to a string.

When I changed the field in the recordset to ogr_geometry.ToString() AS sGeometry and used sGeometry instead of ogr_geometry all worked as expected.

0

It looks like you're using an ETL Script, and your .NET client has the geometry type in something that is NOT text. My assumption is that it has in an internal OGR format. Rather than creating your geometry instance from text with STGeomFromText in SQL Server, instead create the instance in SQL Server from WKB,

  • STGeomCollFromWKB(SqlBytes, Int32)

WKB is the binary transfer representation. You can create it from ogr using, OGRGeometry::exportToWkb

Just change those two methods. Export to WKB in OGR. Import from WKB in SQL Server.

geometry::STGeomCollFromWKB(@poly, @srid).STIsValid();

As an aside, OGR itself can determine whether or not the polygon is valid, there is no reason to use SQL Server at all for this if you already thave the geometry in OGR.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.