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
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.
I am not able to download the worldmap data..can u plz send me
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.
Hi Pinal,
How does one determine if the shape file has geometry data or geography data. Thank you
Ramdas
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
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
Hi Pinal,
Thanks for the wonderful post..i implemented it…it is awesome…i didn’t know about this earlier..
thanks,
Nitin Sharma
Are INDIA ki Shape file kahaan hai…………….
Bahut galat baat hai…………….
humare desh ki koi shape file nahi…………..
Thanks Pinal, these info are quite usefull.
I need to know if it is possible to add a chart to a polygone ?
Thanks again
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
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.
Hi Pinal,
The link to download Shape2SQL.exe shows error. Do we have any other EXEs to extact shape file datas.
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
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
Hi,
Can you refer below url.
Thanks and Regards,
Venkat Tammineni
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
Sorry Pinal, that was my mistake…I got it!
Thanks
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
You have to uncheck the option for “Create Spatial Index” and then the table will show up in the database.
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?
I have the same problem as Satyesh. I’ve uploaded the files but cannot see the tables. Any solutions to the problem.
thanks.
Dear Satyesh,
have tried below url
let me know..
Thanks and Regards,
Venkat Tammineni
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!!!
Hi,
I got a problem, to link a spatial database like mysql to ESRI’s shapefiles. what would you suggest me?
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.
Hello every body,
can anyone give me another link rather than www.http://download.geofabrik.de to download shapefiles for Saudi arabia and other MENA region countries ?
Thanx in advance for your reply.
Emam