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.
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.
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).
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.
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)
124 Comments. Leave new
i have sql server 2008 r2 enterprise edition , whether need to install spatial db ..pls help
I am not able to download the worldmap data..can u plz send me
Could you please send world map spatial files? Thank you
Thanks Pinal, this is very good. I’m finding that when I’m using SSRS to create a map, I try to browse to the .shp file but it is unable to open the file as it is too big. I then write a SQL Spatial query with a join to my data & the .shp file but the whole map doesn’t show as it’s beyond the limits of the number of Geometric points I think. Is there way around this??
Excellent work Mr pinal dave..can u suggest me how to integrate these files in reporting tools like pentaho…qlikview
Here is link to get World Shape File,
Hello Pinal Dave The shape file here
https://www.census.gov/cgi-bin/geo/shapefiles2012/layers.cgi
shows a complete upload to the database, but no table is created.
Also failing to upload it to Visual Studio 2008 and 2012 map…
Excellent guid to the spatial world in SSRs. Thanx a lot Dave.
Hello Pinal,
I am new to SSRS Maps and looking for Maps Continent wise, like Africa, Asia. But cant find it anywhere. I checked VDS Technologies as well but could not find it there.
My requirement is to display Suppliers geographically so thought instead of having it on a single Map, why not do it continent wise.
Would appreciate your help.
Regards
Pinal –
I followed your instructions regarding loading SHP file using Shape2SQL, and selecting Geometry, and it works perfectly. Thank you for the article.
I am on 2008 R2, and now I have a requirement to show data by region, Europe,Asia-ME Africa, The Americas,etc meaning I must aggregate. I only have the plug-ins for spatial aggregation for Geography data. Whenever I attempt to use Shape2SQL to write tables with a Geography data type (SRID 4326), I get several errors saying many countries cannot load. I would think that the problem is the data in the world.shp file is potentially crossing hemispheres which I know can be a problem. Do you know of a reliable method / source I can use to get Geography data so I can aggregate? OR do you know of a way to do spatial aggregations with Geometry data?
i Have used shape2sql tool and store the shape file dat to sql server database now i want to retrive that store file into mapwingis activex object which i am using in c#…pls provide the code to open the shapefile from database..
[email removed]
I need dubai shape files can you please send that files ………
Hi, I need shape Files (.shp file) for all the countries in World Map Individually.And also Dubai comes under United Arab Emirates,Please provide me the shape file for United Arab Emirates too.Thanks in advance.
Hi Pinal,
This is very nice article …
Can you please upload world.shp (and related files) as they are not there anymore on the link which you have mentioned here.
Thanks in advance …
I am using this tool and after selecting the shape file I want to upload into MS SQL server and after selecting the data base and selecting the planar projection as my data is projected not geographic and clicking the ” upload to database button ” I can see the process of loading the rows into the database indeed.. the surprise is after finishing I don’t find the table in the databse although I saw the process of loading the rows into DB
Thanks for the tutorial. Please, I cant find the .shp files for Nigeria.
Any clues?
Hi there, my SSRS report requirement is based on whole World Map. Where can I download the World Map (NOT the individual country) shape file (.shp)?
The Zip5 file throws errors.
could not insert row#1937
Zip5: 996HH
System.ArgumentException: 24200: The specified input does not represent a valid geography instance.
Just FYI it throws a similar error several times… and the map that report builder draws is only a portion of the US… likely because data is missing.
I can’t tell you how many times I’ve had a problem that you’ve solved for me in one of your posts! Thank you so much! This blog is invaluable.
I am looking a tool for uploading database from local system to server automatically, when i change the structure of database in my local system then the tool upload the modifed structure on server.
I am looking a tool for uploading database from local system to server automatically, when i change the structure of database in my local system then the tool upload the modifed structure on server.