Sign in
 
Home20122008 R2200820052000LibraryForums
SQL Server >
> 
> 
Creating a spatial SQL Server table from SQL Server 2005

Unanswered Creating a spatial SQL Server table from SQL Server 2005

  • Thursday, January 19, 2012 5:31 PM
     
     
    Sign In to Vote
    0

    Hi all,

    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?

     

All Replies

  • Thursday, January 19, 2012 7:27 PM
    Answerer
     
     
    Sign In to Vote
    0

    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
     
     
    Sign In to Vote
    0

    Thanks Alastair,

    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
     
     
    Sign In to Vote
    0

    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.

    HTH!