Go to content Go to navigation and search

Home

Current SQL Server Blog Articles


Search

Browse

RSS / Atom

Email me

textpattern

Creative Commons License
All Blog Articles, Data Models and Free Source Code by Simon Greener, The SpatialDB Advisor is licensed under a Creative Commons Attribution-ShareAlike 3.0 Unported License.

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

Thursday June 17 2010 at 09:12

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

Creative Commons License

post this at del.icio.uspost this at Diggpost this at Technoratipost this at Redditpost this at Farkpost this at Yahoo! my webpost this at Windows Livepost this at Google Bookmarkspost this to Twitter

Comment [2]

Simon,

This is pretty neat. Never thought to create a geometry_columns to get ogr2ogr work.

You’ve mentioned that with oracle spatial too as I recall.

This comes up every so often in discussions people wanting to load data with ogr2ogr and using SQL Server 2008 / Oracle spatial etc. It would be really nice to see a write up of the details of that.

Regina · 18 June 2010, 11:28 · #

Regina,

The work with GEOMETRY_COLUMNS and ogr2ogr was rather fun. In the end I got ogr2ogr working generating Mapinfo native TAB files with styling. Very cool.

However, loading is another question. One can use ogr2ogr to load the data but only as WKB or WKT. You then have to run SQL Server 2008 Spatial functions to generate an STGeometry from the loaded WKB/WKT. The new generated data is, of course, written in to another geometry column. One can do this via a second batch script or manual processing but most people was a tool that does everything…..

I will blog on this at some stage.

regards
Simon

Simon · 20 June 2010, 08:59 · #