Hi Friends,

In my last blog post, I explained what shape files are and demonstrated how you can explore them. You can read the post here;

Continuing with my spatial series, in this post, I am going to demonstrate how you can import shapefiles into a SQL Server database.

Starting from SQL Server 2008, there are 2 new data types, Geometry and Geography. The geometry data type supports planar, or Euclidean (flat-earth), data. The geometry data type conforms to the Open Geospatial Consortium (OGC) Simple Features for SQL Specification version 1.1.0. Geography data type stores ellipsoidal (round-earth) data, such as GPS latitude and longitude coordinates.

When you import shapefiles in to SQL Server database, the spatial data is converted to Geometry data type.

In order to import the shapefiles, you will need a tool called ShapeToSQL. SharpGIS provides this tool and can downloaded from:

After you download and extract the contents of zip file, run Shape2SQL.exe

1_SQL_Server_Learning_Spatial_stuff_Importing_shapefiles_in_SQL_Server_database

If you are running this for the first time, the tool will ask you to provide a connection to a SQL Server instance and a database were relevant tables can be created.

You will get a dialog box something like this:

2_SQL_Server_Learning_Spatial_stuff_Importing_shapefiles_in_SQL_Server_database

Now is the time to create a blank database. In case you already have an existing database in which you want to import spatial data, you may skip creating a new database.

Connect to your SQL Server instance and create a database by the name of spatial. You can give any name. For this demo I am creating a database by the name of spatial.

Next, fill in the required details in the connection dialog box:

3_SQL_Server_Learning_Spatial_stuff_Importing_shapefiles_in_SQL_Server_database

Click OK.

Next, select the shapefile and rest of the fields will automatically be filled. I have selected the same shape file (usa_st.shp) that I used in my last blog post. Here is the explanation of the dialog box:

4_SQL_Server_Learning_Spatial_stuff_Importing_shapefiles_in_SQL_Server_database

Once you are done with the options, click on “Upload to Database”. Depending on the size of data, the process of uploading data may take time.

5_SQL_Server_Learning_Spatial_stuff_Importing_shapefiles_in_SQL_Server_database

Once done, switch back to SSMS and browse the table.

6_SQL_Server_Learning_Spatial_stuff_Importing_shapefiles_in_SQL_Server_database

Create a new query window and select from the table:

You will see the same result set that you saw in the map browser. If you scroll horizontally, you will see the geom column containing spatial data:

7_SQL_Server_Learning_Spatial_stuff_Importing_shapefiles_in_SQL_Server_database

Starting SQL Server 2008, spatial data can be browsed using the Spatial Results tab in the result set pane. Click on the Spatial Results and you will observe that the spatial data has been rendered on the surface.

8_SQL_Server_Learning_Spatial_stuff_Importing_shapefiles_in_SQL_Server_database

This is how you can import shapefile data into SQL Server database and now work with it. In my next post, I shall show some interesting stuff that you can do with spatial data.