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

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.

CREATE DATABASE spatial
GO

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:

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:

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.

 

 

Data Platform Virtual Summit 2020

Subscribe to SQLServerGeeks YouTube channel. If you want more learning content in your inbox, subscribe to SQLServerGeeks Bulletin.

SQLServerGeeks YouTube | SQLServerGeeks Bulletin | SQLServerGeeks Twitter

Amit Bansal

About Amit Bansal

Amit Bansal is always brainstorming around SQL Server. Despite working with SQL since 1997, he is amazed that he keeps learning new things every single day. SQL Server is AB's first love, and his wife does not mind that. He tries to share as much and spreads the SQL goodness. Internals and Performance Tuning excites him, and also gives him sleepless nights at times, simply because he is not a genius, but quite a hard worker and does not give up. It has been a long and exciting journey since 1997, you can read here: http://sqlmaestros.com/amit-bansal/ He is on Twitter: https://www.twitter.com/A_Bansal

View all posts by Amit Bansal →

2 Comments on “SQL Server – Learning Spatial stuff – Importing shapefiles in SQL Server database”

Leave a Reply

Your email address will not be published. Required fields are marked *