New Presentation on Active (Searchable) Spatial Metadata for SQL Server 2008 Spatial and FreeText

I gave a presentation on some work I did last year for a customer on providing a sophisticated spatial/textual search capability against over 700 tables of spatial data stored in SQL Server 2008 Spatial. The searching combined Spatial and Free Text index based searching.

The basic idea is that the main thing in static spatial metadata (eg ANZLIC) that is of any use in dynamic systems is the Minimum Bounding Rectangle (MBR) of the data. But the problem is that it is static: populated as an after thought to the transactional update of the actual data.

What is needed is dynamic access to MBR data so that primary filtering can be achieved easily (ie my search area is inside/outside the actual extent of the spatial data). Some database vendors do this very well in their spatial index searches, but such searches are also expensive to execute. What one would like is to be able to filter out spatial data based on an efficient search of a single data source.

The only available offering from the Open GeoSpatial Consortium – the GEOMETRY_COLUMNS table (which SQL Server 2008 Spatial does not support) – for the database community doesn’t support MBRs (though Oracle does in its own proprietary metadata).

The presentation shows how a variant of the OGC GEOMETRY_COLUMNS table can be created (with an appropriately standards compliant View) that gives efficient access to the critical MBR data. (This MBR data can be kept up to date with the actual transactional data via appropriate triggers or scheduled tasks.)

The use of the bundled Free Text searching is a clever way to use something provided at no cost to deliver clever end user search requirements:

“To find all spatial objects for whom any field contains a particular piece of text within the current display MBR”

See the presentation here