We have a large, corporate address dataset which is stored in a SQL Server 2005 database. The table obviously has no geometry field but does contain two fields with easting and northing coordinates.
I would like to know if it is possible to use this data in an instance of SQL Server 2008 which is to become our corporate spatial data store.
Preferably I would like to keep the base address table in the 2005 instance (as it's maintained via a seperate front end), and pass the data via a linked table to the 2008 database. I'd rather not have to keep a copy of the data on 2008. Essentially I'd want a 'view' which uses the data from 2005 and casts the easting and northing coordinates (British National Grid), to a geometry type on the fly in 2008.
Is this possible? Is there a better way to achieve the same aim without having to replicate the data?
The problem with the approach you describe is that the geometry column containing the points representing each address would never be materialised. If it's only ever created "on-the-fly" then you can't create an index on the column, and having a spatial index is pretty crucial if you want to perform any kind of queries that involve filtering the data (to find customers within a particular region, say), which I'm guessing you do want to do, or else why bother moving to the 2008 spatial datatypes in the first place...
A preferable solution (from a performance POV, at least) would simply be to migrate the entire database to 2008+, populate a computed geometry column from your existing Northing/Easting values, and place one or more geometry indexes on that column.
twitter: @alastaira blog: http://alastaira.wordpress.com/
Assuming we can't migrate the entire database to 2008+, what would be the best method to copy this data from 2005 to create a spatial version in 2008?
The dataset consists of roughly 200,000 addresses with 30 fields. Having this automatically run once a night to update the 2008 version would be sufficient for our needs.
I agree with tanoshimi and propose next solution.
I'm not sure if you have other geometries in 2008+ database, but almost all GIS applications have ability to read Easting/Northing coordinates (float, numeric,... fileds in DB) and transform them into geometries (points) internally. Moreover, it is possible to do the same through ODBC with easy.