SQL SERVER – World Shapefile 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.

VDS Technologies has all the spatial files for every location for free. 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.

Reference : Pinal Dave (http://blog.SQLAuthority.com)

About these ads

106 thoughts on “SQL SERVER – World Shapefile Download and Upload to Database – Spatial Database

  1. Wonderful, these information are really great. Thanks pinal and keep it up. Also I want to know a detail tutorial on how to handle spatial data from SQL Server 2008 and also from my application.

    2ndly What about PostGIS, which one is the best(PostGIS Or SQL server 2008 regarding handling spatial data) apart from the fact that PostGis is open source.

    Thanks a lot.

  2. Thanks Pinal, this is really cool. Report Builder 3.0 has spatial capabilties, with the features you have described above one can generate really cool map based reports.

  3. Hi Pinal,

    I am using SS 2008 build 10.0.1442.32 in my laptop. I have followed all your steps but I am not getting the “Spatial Result” tab in my result window.

    Please suggets.

    –Prashant

  4. Hi Pinal,

    Your post about GIS is very useful. thank you very much.
    i never know to convert shapefile to sql before.
    i need more info about this.please create other post about spatial data to sql.

    Thx

  5. Hi Pinal,
    Thanks for the wonderful post..i implemented it…it is awesome…i didn’t know about this earlier..
    thanks,
    Nitin Sharma

  6. Pingback: SQL SERVER – Spatial Database Queries – What About BLOB – T-SQL Tuesday #006 Journey to SQL Authority with Pinal Dave

  7. Are INDIA ki Shape file kahaan hai…………….
    Bahut galat baat hai…………….
    humare desh ki koi shape file nahi…………..

  8. Thanks Pinal, these info are quite usefull.
    I need to know if it is possible to add a chart to a polygone ?
    Thanks again

  9. strange…very strange for me….i mean i uploaded to 2008 and its preocessed but i cant see that file in the database i selected…..i dnt know y

  10. I have an application that allows the upload of file attachments using the asp:FileUpload object.

    I retrieve the mimetypes with a Case statement:
    Case “.pptm”
    GetMimeType = “application/vnd.ms-powerpoint.presentation.macroEnabled.12″.

    How do I allow for the upload of fileshapes: .dbf, .prj, .sbn, .sbx, .shp, .shx, which have no mimetype, without having the user download and use the Shape2SQL tool? They would not have access to our database.

  11. The URL that point to Shape2SQL.exe is not working, and the tool isn’t in other site… please, can anyone send me it to my email ?

    Thanks

  12. Hi,

    Where I can download Shape2SQL.exe. the url is not working is there any alternative url to get Shape2SQL.exe file.

    Please let me know.

    Thanks and Regards,

    Venkat

  13. Hi Pinal,

    I have tried upload shape file to the database. Process gets complete without any problem but I cant see the table.

    Please advise,
    Thanks,
    Satyesh

    • Hello, I have the same problem as Satyesh – I can’t see the uploaded table in SQL server Management Studio. What could be the mistake and the solution? Thank you. Josef

    • Hi, I also have the same problem as Satyesh and Mahesh. The Shape2SQL tool looked like it loaded the shp file, but I can’t see it in the database.

      Can anyone suggest how to find out what’s going wrong?

  14. Hi,

    I tried to use shape2sql to upload shp files, but when I pressed “upload to database”, nothing happened, I didn’t see the progress bar. I am using sql2008 and I am sure that all info input in the editor is right, I tried both remote server and local server, both are the same..is it because of the firewall or something else? Any ideas? Thank you!!!

  15. Pingback: SQL SERVER – SSRS 2008 R2 – MapGallery and Codeplex – World Map Journey to SQL Authority with Pinal Dave

  16. Pinal,

    Thanks for the great article as usual. I did run into an odd issue. The application appears to be loading the data but I don’t find any tables in the database. I even ran a trace and can see the statements to generate the table and even insert the data. Am I missing something here? It’s almost as if they are not being committed.

  17. Hi – The India shape file located under Asia is an older version (when UP, Bihar, and MP were not bifurcated). Is there an updated shape file for India (with state boundaries) that we can find somewhere? not have had much luck so far!

    thanks for your help!
    Manish

    Jan 11, 2011

  18. hi….i have a question like if i want to map my sql table into map control can i map to direct to the the map control or what is the procedure do i need to follow….

    thanks in advance…

  19. hi,

    I have downloaded usstates shapefiles and shape2sql…but with shape2sql i am not able to see table in my database. If i am downloading world shape file that i can see that in my database but us state is not there…any reply on this will be appreciated

  20. Hi, great stuff you’ve put on here.

    How do I edit the text data (i.e. country code) in the shape file? For example, my country ISO code for the United Kingdom is GB, and in the shape file it’s UK. So all my UK data is not showing up on the map.

    Appreciate your help.

  21. Sir,
    I want to create shape file.i have idea about how to import data into sql server from shape file,but how to create shape file?please let me know?

    Thanks in advance..

  22. Sir ,
    i m working on vehicle tracking project , i want to use aspmap tool for that i need .shp file of maps of india.
    i m not geting those map .also looking for examples to use .shp files as well as .tab files of maps.
    I m not getting .shp files for maps of india for various category.

  23. Hi Pinal, I use this program really often and it works fine, but I was wandering if it can also do a reverse swich from sql server to shp. Or do you kwon any program that can do this?

  24. Hello Pinal, I’m looking for the map of the whole world in the form of a shapefile, how can I get it? Alternatively, what procedure can I use to convert google earth maps into a shapefile?

  25. Hello Pinal i need shapefiles for whole over because i need to show Drill down SSRS report Containing google map, what procedure can I use to convert a shapefile into sql spatial data?

  26. Hi Dev,

    Good morning.

    Can you please share the information of world.zip file.
    I would like to work with world.zip file to embed into map report in sql reports2008.

    I am eagrly looking for your reply.

    Thanks,
    kishore.
    [phone removed]

  27. Hi,
    I think I found the world map here http://www.diva-gis.org/Data (in the Global level, country boundaries).
    However, SSRS runs into error “The number of map point elements for map ‘Mapxxx’ exceeds the maximum limit for the map”. I even try it with 1 country and still have same error.
    Please help.
    Thank

  28. Hi,
    After I changed Map Resolution to 5 in Map Viewport Properties, the problem goes away. The map does not look nice like with the resolution is 0 but at least I can see the data which is really nice.
    Cheers
    PhuTri

  29. Hi Pinal,

    I am unable to find World.Zip Spatial file in VDS Technologies,Please send me the Required Links

  30. when i execute this query
    USE Spatial
    GO
    SELECT *
    FROM dbo.world
    GO
    it is taking long time in debugging…..and debugging.
    its neither gave error nor any other msg….
    only time increases.what to do?

  31. I have Followed the steps, I downloaded the US Zip file, and shape2sql, but when I run I am getting a SQL connection error for some reason. I’ve done the normal degugging (remote access, permissions, Windows authentication vs user) but I can not seem to figure out what the problem is. My goal is to build a table of zip codes with Lat / Long, as I only have address data and what to chart on a map using RSS 3.0.
    Thanks,

    Jeremy

  32. Hi, Pinal:
    Fantanstic! But I was unable to find world map (only Administrative, Coastline and son on). Can you help?

    Many thanks,
    Steven

  33. Hi Pinal, thanks for the great article. I downloaded the US states map, and see the progress bar upload. But no tables under the Database. Any help is appreciated.

  34. 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??

  35. 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

  36. 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?

  37. 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]

  38. 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.

  39. Pingback: SQL SERVER – Weekly Series – Memory Lane – #022 | SQL Server Journey with SQL Authority

  40. 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 …

  41. 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

  42. 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)?

  43. 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.

  44. 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.

  45. 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.

  46. Are there any particular settings/configuration for SQL Server 2008 express to be able to import shape file? I have downloaded Shape2Sql.exe , opened one of the shape file, clicked on Upload to databse and nothing is happening or Cancel shows up and then “Upload to databse” shows up again.Am I missing something?

  47. Hi,

    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:nikkijyos@yahoo.com.

    Regards,
    Nirmal

  48. Hi Pinal,
    I want to know the location name based on longitude and latitude. How can we get using spatial database. Is there any option available.

  49. Hi. I need the 3 world items that you put in the photo. Please reply me and help. I accesed the link and i don’t works. Regards, Florin.

  50. Hi Pinal, Urgently I need India state and district wise geom data.. Can you please tell where i need to download..? and i download SqlspatialTool but it’s show badimagefileformat error while loading the .shp file.. Thanks in Advance

  51. Hi am looking for indian state level shape file please give me the link or send me the …zip..urgently needed….

  52. HI ,
    I need Europe Shape file, can any one give me exact link where to download it
    its some what urgent

    Thanks in ADvance..Sri

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s