SQL Server – Validate Geometry Polygon Data Using Stored Procedure

geometryspatialsql serverstored-procedures

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.

Best Answer

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.

Related Solutions

Triggers on Insert or Update in SQL Server (Spatial)

Isn't this what you're looking for ?

CREATE TRIGGER [triggername] ON [table] 
AFTER INSERT, UPDATE 
AS            
BEGIN
    UPDATE [table] 
    SET [table].Updated = Getdate() 
    WHERE ID IN (SELECT ID FROM INSERTED)
END
GO

Sql-server – SQL Server spatial filter with additional conditions performance issue

The answer is yes it is possible to get performance with filters over multiple columns including spatial columns.

I've tried to replicate your tables and closely as I can to your brief description using RAND to create prices, location points and link them to events. But I can't duplicate you performance problem. Have I got the basic table details right?

--  Event table
SELECT TOP 30000000 
    N EventID, 
    RAND(CAST(NEWID() AS VARBINARY)) * 10000 + 5000 Price
    into Event
FROM Tally;

ALTER TABLE Event ALTER COLUMN EventID INT NOT NULL;
GO
ALTER TABLE Event ADD CONSTRAINT Event_PK PRIMARY KEY (EventID);
GO
CREATE INDEX Event_Price_IDX ON Event(Price);
GO

-- Location Table
SELECT TOP 35000000
    N LocationID, 
    CEILING(RAND(CAST(NEWID() AS VARBINARY)) * 30000000) EventID,
    Geometry::Point(RAND(CAST(NEWID() AS VARBINARY)) * 710000 + 40000, RAND(CAST(NEWID() AS VARBINARY)) * 710000 + 40000, 0) Location
    into Location
FROM Tally;

Running the 3 versions of your query with hopefully sensible parameters

-- Test Query
DECLARE @price FLOAT = 13000.00 -- ~20% of records
-- 10 sq km query window
DECLARE @BoundingBox Geometry = Geometry::STGeomFromText('POLYGON(( 300000 300000, 310000 300000, 310000 310000, 300000 310000, 300000 300000))',0)

SELECT TOP 2000 [E].*
FROM [dbo].[Event] AS [E]
INNER JOIN [dbo].[Location] AS [L] ON [E].[EventId] = [L].[EventId]
WHERE 
    --[E].[Price] >= @Price
    --AND
    @BoundingBox.STContains([L].[Location]) = 1

SELECT TOP 2000 [E].*
FROM [dbo].[Event] AS [E]
INNER JOIN [dbo].[Location] AS [L] ON [E].[EventId] = [L].[EventId]
WHERE 
    [E].[Price] >= @Price
    --AND
    --@BoundingBox.STContains([L].[Location]) = 1

SELECT TOP 2000 [E].*
FROM [dbo].[Event] AS [E]
INNER JOIN [dbo].[Location] AS [L] ON [E].[EventId] = [L].[EventId]
WHERE 
    [E].[Price] >= @Price
    AND
    @BoundingBox.STContains([L].[Location]) = 1

I get 93ms, 4ms and 93ms respectively, removing the TOP 2000 from the queries causes the price filtered query to blow out to a minute or so.

Are you able to give more details (execution plans, DDL, etc) to help us replicate your issue. My initial thought was that the TOP 2000 was hiding the underlying problem. I think that may still be the case.

Related Question