System table spatial queries

The GDB_Items table contains a geometry column that maintains the extents of feature classes stored in the geodatabase. Because the column's data type is a spatial type that can be queried using SQL, you can use SQL to perform spatial queries on the table and discover which feature classes intersect a search area.

The geometries stored for the extent of each feature class have a spatial reference of WGS 84, regardless of what spatial reference the feature class has. Therefore, any query geometry should also be constructed with a WGS 84 spatial reference.

The following example shows how to find the names of feature classes that fall into a search area. Note that while this example uses a polygon and the intersects operator, other geometry types and relational operators can also be used.

--Queries a dbo-schema geodatabase in SQL Server

-- Defines the extents of the search area.
-- VARCHARs are used rather than FLOATs to reduce casting.
DECLARE @MAX_X VARCHAR(3);
DECLARE @MIN_X VARCHAR(3);
DECLARE @MAX_Y VARCHAR(2);
DECLARE @MIN_Y VARCHAR(2);

SET @MAX_X = '-85';
SET @MIN_X = '-86';
SET @MAX_Y = '33';
SET @MIN_Y = '32';

-- Create a polygon for the search area.
-- 4326 is the spatial reference ID for WGS84 in the SQL Server system table.
DECLARE @WKT_CONST nvarchar(max);
DECLARE @SEARCH_AREA GEOMETRY;

SET @WKT_CONST = 'POLYGON ((' +
 @MIN_X + ' ' + @MIN_Y + ', ' +
 @MAX_X + ' ' + @MIN_Y + ', ' +
 @MAX_X + ' ' + @MAX_Y + ', ' + 
 @MIN_X + ' ' + @MAX_Y + ', ' +
 @MIN_X + ' ' + @MIN_Y + '))';
SET @SEARCH_AREA =GEOMETRY::STPolyFromText(@WKT_CONST, 4326);

 -- Find the classes that intersect the extent.
SELECT Name
FROM dbo.GDB_ITEMS
WHERE Shape.STIntersects(@SEARCH_area) = 1