SQL Server STWithin Spatial index does not work

Question

1.Spatial index does not work(Query cost 10s)

SELECT geomFROM cj_pl_l WHERE geom.MakeValid ( ).STWithin (geometry :: STGeomFromText 
('POLYGON ((……))', 4547 ).MakeValid ()) =1

2.Spatial index work（Query cost 2s,I save above-mentioned polygon to temp table[testGeom]）

select b.* from  testGeom a,cj_pl_l b where b.geom.STWithin(a.geom)=1

If I want Condition1 the Spatial Index to work, how should I solve？

Thanks!

My first guess is that the MakeVald() call on the table side of the query is the culprit. Why is that necessary? Do you have invalid data in your table? — Ben Thul
– Ben Thul, Commented Dec 3, 2021 at 3:48
Due to the nature of spatial indexes they only support a limited number of predicate forms. Have you read the Methods Supported by Spatial Indexes documentation yet? — AlwaysLearning
– AlwaysLearning, Commented Dec 3, 2021 at 4:11
@BenThul Because the sql run error. System.ArgumentException: at Microsoft.SqlServer.Types.SqlGeometry.ThrowIfInvalid() at Microsoft.SqlServer.Types.SqlGeometry.STWithin(SqlGeometry other) When I use other table and don't use MakeVald() method，the sql not thorw error,but the Spatial Index also don't work. — FakeGISer
– FakeGISer, Commented Dec 6, 2021 at 2:24
What I'm saying is that if you have to continually run MakeValid() against the data in your table, you have invalid data. If suggest fixing the data (something like update yourTable set yourColumn = yourColumn MakeValid(). — Ben Thul
– Ben Thul, Commented Dec 6, 2021 at 3:03

Michael Entin · Accepted Answer · 2021-12-03 06:32:47Z

0

The culprit is the first MakeValid in geom.MakeValid ().

The index covers geom column, but MakeValid returns a new geometry value, which might be different and is not indexed, and thus SQL cannot use it.

Remove the first MakeValid call, preferably after making sure all the geometries in the table are valid. You generally don't want to fix stored geometries with MakeValid anywhere in the query path - it is slow and it prevents index usage.

That said, second MakeValid, on the constant geometry value, should be fine.

answered Dec 3, 2021 at 6:32

Michael Entin

7,8223 gold badges25 silver badges32 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

FakeGISer Over a year ago

Thank you suggestion, I tried,you can see comment above. When I use other spatial table and don't use MakeVald() method，but the Spatial Index also don't work.

Michael Entin Over a year ago

Can you please update the question? It currently says in the second case the index works.

Collectives™ on Stack Overflow

SQL Server STWithin Spatial index does not work

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related