SQL Server – Learning Spatial stuff – Importing shapefiles in SQL Server database

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

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:

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.

CREATE DATABASE spatial
GO

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

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:

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.

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

Create a new query window and select from the table:

select * from dbo.usa_st

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:

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.

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.