SQL Server2008 Table Space

This paper takes SQL Server2008 as an example to describe the process of converting a spatial table into a normal attribute table.

Reference: Microsoft Official SQL SERVER2008 Documentation

Demand:

Add spatial fields to the normal attribute table and dump the existing latitude and longitude coordinates in the table into spatial data to facilitate subsequent use of tools such as ArcMap to load and publish services to the data.

Solve:

1. New Geometry Type field, named Geom
   

2. Update the existing property data into the Geom field, where the latitude and longitude coordinates are assumed to be numeric for the x, y type, respectively.
   

   Update tablename Set Geom=geometry::stgeomfromtext (' Point (' +convert (varchar,x) + ' +convert (varchar,y) + ') ', 4326) where x is isn't null and y is not null;

   Note here to cast the numeric type with the CONVERT function to varchar.

   The database log file is full error during the update and is documented here.

   Workaround:

   Switch database to Simple recovery mode ALTER DATABASE dbname set recovery simple
   Then switch the database back to the original recovery mode alter DB dbname set recovery full
   

3. Create a spatial index on the Geom field

   Create spatial index Idx_tablename_geom on TableName (Geom) with (bounding_box= (xmin=-180,ymin=-90,xmax=180,ymax=90));

SQL Server2008 Table Space