SQL Server – Spatial Geometry Properties on Index

indexspatialsql serversql-server-2012

Is there a way to add geometry.STSrid(object property) to a spatial index or is it already added when I create a spatial index in SQL Server 2012.

I have mixed SRID table and I was wondering if it could help?

We collect spatial data from several source to one database structure. I don't have a simple solution to transform spatial data on our stack (SQL Server) and I need to keep original data. One solution that I have considered is to keep original geometry and add another transformed geometry on side.

Best Answer

The SRID does not appear to be an integral part of the spatial index nor can it be added.

It is integral to the geometry even though there are no real tools for manipulating geometries based on the SRIDs in SQL Server. It does have a few rules about SRIDs.

  • Geographies must have a SRID from a list of specific SRIDs.
  • When comparing, manipulating geometries and geographies they must have the same SRID.

For example using the following set up.

CREATE TABLE TEST  (
    ID INT IDENTITY(1,1) NOT NULL PRIMARY KEY
    ,GEOM GEOMETRY
    )

INSERT INTO TEST (GEOM) VALUES
(Geometry::Point(0,1,0)),
(Geometry::Point(3,2,1)),
(Geometry::Point(2,3,2)),
(Geometry::Point(1,0,2))

CREATE SPATIAL INDEX TEST_SDX ON TEST(GEOM) WITH (BOUNDING_BOX=(0,0,5,5))

The first query determines distances between points. It will only return a distance between the 3rd and 4th point as they are the only ones with the same SRID. The other distances will be NULL.
The second query is for selecting geometries inside a buffer. Only a single row is returned since the query buffer was created with SRID 2.

SELECT a.ID, b.ID. a.GEOM.STDistance(b.GEOM)
FROM TEST a
    INNER JOIN TEST b ON a.ID < b.ID

ID          ID          
----------- ----------- ----------------------
1           2           NULL
1           3           NULL
2           3           NULL
1           4           NULL
2           4           NULL
3           4           3

SELECT * 
FROM TEST a
WHERE Geometry::Point(1,1,1).STBuffer(3).STContains(a.GEOM) = 1

ID          GEOM
----------- --------------------------------------------------
2           0x01000000010C00000000000000000000000000000040

Also note SQL Server let me build a spatial index over the geometry column that contains mixed SRIDs, but it is of limited use. Even if there was a lot more data, the index would be of limited use.

I would suggest to make life easier that you pick a single projection to use across all you data and transform geometries to that. This can be done alongside the current geometries or replace them. Also if you choose a Lat/Lon projection, use the Geography data type.

There is an open source project, SQL Server Spatial Tools, that has some tools that will allow you to do projections. You will however have to know the parameters for the projections you are using as it does not appear to have a list of projections to work with. I can't advise how good these are, as I haven't used them.

Related Question