Thursday, January 19, 2012 5:31 PM
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?
Thursday, January 19, 2012 7:27 PMAnswerer
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/
Friday, January 20, 2012 4:10 PM
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.
Friday, January 27, 2012 8:29 AM
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.