CODEWITHSUNDEEP
sql-servergisgeospatialspatialgeography
Assaf Lavie
Assaf Lavie

Reputation: 75953

MS SQL Server 2008 Spatial Indexing - Does it work?

Has anyone tried using it and can say if it's well implemented?

-- Assaf (who spent the last few weeks getting increasingly frustrated with MySQL's deficient implementation of OpenGIS functions and is now considering switching to MSSQL)

Upvotes: 1

Views: 2800

Answers (3)

rburhum
rburhum

Reputation: 1661

Yes. They are implemented correctly. You also have PostgreSQL's PostGIS as an option, Oracle Spatial. Informix and DB2 also have implementations of a spatial type.

Upvotes: 1

please delete me
please delete me

Reputation:

Yes, they work.

I just switched one my classes from an ESRI-ArcObject-based query using ISpatialFilter to a SqlCommand that returns the same data. It's a proximity search (return any records that are within 1000 feet of point x).

At first the ESRI query was still faster but that was due to a poorly constructed where clause that was very ineffecient (I'm still learning how to use the spatial functions in SQLSVR2008).

After some tweaking,my SQL method was faster than the ESRI method but not by much. Then I jacked my search distance up to 10000 feet and then I saw the difference. The SQL Server 2008 method was a lot faster.

(ESRI) Search Nearby Customers Elapsed Time(sec): 1.503 (SQL2008) Query Nearby Customers Elapsed Time(sec): 0.925

Although the speed differences don't have to do with the indexes, but with ESRI-ArcObjects. In my ESRI method, I'm getting my distance, x, and y from IProximityOperator and IPoint. In the SQL2008 method, I'm letting the database do all of the work: SHAPE.STX as X ,SHAPE.STY as Y ,SHAPE.STDistance(but the ArcObject developers know all about that overhead).

I'm impressed so far.

Upvotes: 0

SQLMenace
SQLMenace

Reputation: 134941

Yes it works, I have some sample code here SQL Server 2008 Proximity Search With The Geography Data Type

Upvotes: 2

Related Questions