SQL SERVER – World Shape files Download and Upload to Database – Spatial Database

During my recent, training I was asked by a student if I know a place where he can download spatial files for all the countries around the world, as well as if there is a way to upload shape files to a database. Here is a quick tutorial for it.

You can download the spatial file from here. If you cannot find the spatial file you are looking for, please leave a comment here, and I will send you the necessary details. Unzip the file to a folder and it will have the following content.

SQL SERVER - World Shape files Download and Upload to Database - Spatial Database spatial1

Then, download Shape2SQL tool from SharpGIS. This is one of the best tools available to convert shapefiles to SQL tables. Afterwards, run the .exe file.

When the file is run for the first time, it will ask for the database properties. Provide your database details.

SQL SERVER - World Shape files Download and Upload to Database - Spatial Database spatial2

Select the appropriate shape files and the tool will fill up the essential details automatically. If you do not want to create the index on the column, uncheck the box beside it. The screenshot below is simply explains the procedure. You also have to be careful regarding your data, whether that is GEOMETRY or GEOGRAPHY. In this example,  it is GEOMETRY data.

Click “Upload to Database”. It will show you the uploading process. Once the shape file is uploaded, close the application and open SQL Server Management Studio (SSMS).

SQL SERVER - World Shape files Download and Upload to Database - Spatial Database spatial3

SQL SERVER - World Shape files Download and Upload to Database - Spatial Database spatial4

Run the following code in SSMS Query Editor.

USE Spatial
GO
SELECT *
FROM dbo.world
GO

This will show the complete map of world after you click on Spatial Results in Spatial Tab.

SQL SERVER - World Shape files Download and Upload to Database - Spatial Database spatial5

SQL SERVER - World Shape files Download and Upload to Database - Spatial Database spatial6

In Spatial Results Set, the Zoom feature is available. From the Select label column, choose the country name in order to show the country name overlaying the country borders.

Let me know if this tutorial is helpful enough. I am planning to write a few more posts about this later.

Note: Please note that the images displayed here do not reflect the original political boundaries. These data are pretty old and can probably draw incorrect maps as well. I have personally spotted several parts of the map where some countries are located a little bit inaccurately.

DOWNLOAD SHAPE FILE

Reference : Pinal Dave (https://blog.sqlauthority.com)

Spatial Database, SQL Scripts, SQL Server, SQL Spatial, SQL Utility
Previous Post
SQL SERVER – Generate Report for Index Physical Statistics – SSMS
Next Post
SQLAuthority News – MS Access Database is the Way to Go – April 1st Humor

Related Posts

124 Comments. Leave new

  • aakash sonawane
    March 31, 2017 1:24 pm

    I am unable to get the shape files from the above given site. I need shape files for Asian countries like India,China,Malaysia,Singapore and for Europe I need it for UK, Germany and France. I appreciate if you can share those shape files with me thru my [email removed].

    Regards,
    aakash

    Reply
  • Hi All,

    Shapefile location is updated in the blog!

    Reply
  • Hi, My Shape to SQL tool is ot doing anything when I click on Upload to Database button. Tried downloading many times.

    Thanks!

    Reply
  • Hi

    Pinal I am looking for spatial data fro Australia only.

    Reply
  • aswini narayan
    April 7, 2019 5:03 pm

    Hi shape2sql is not working can you assist me

    Reply
  • Hi,

    Pinal, I am trying to use it for SQL 2012, but it giving me error that “could not insert row #1. Please help.

    Reply
  • I use SQL Server 2019, and I download the world + Shape2Sql. but it don’t work. nothing was happen.

    Reply
  • Is there an updated Shape2SQL application that doesn’t use .net 3.5?

    Reply
  • Hi Pinal, I am looking spatial data for Canada. Is it possibe to find?

    Reply
  • Hi Pinal,shape2sql tools at sharpgis doesn`t exists any more. can you send me it? or pleease send me a extract of dbo.world table. many thanks.

    Reply

Leave a Reply