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
FROM dbo.world

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)

SQL SERVER – Introduction to Spatial Coordinate Systems: Flat Maps for a Round Planet

Introduction to Spatial Coordinate Systems: Flat Maps for a Round Planet
SQL Server Technical Article
Writers: Isaac Kunen
Project Editor: Diana Steinmetz
Published: July 2008

I recently read this very interesting white paper. I really found it very interesting as this one was one very easy to read and humourous white paper related to SQL Server. The white paper is starts with very interesting note regarding Columbus.

Contrary to popular opinion, Columbus did not prove that the Earth is round. Pythagoras, Plato, and Aristotle claimed a round Earth based on philosophic and observational grounds. More impressively, Eratosthenes measured the Earth’s circumference around 240 B.C., a measurement which comes amazingly close to modern values—within 17 percent when taking pessimistic conversions into account and perhaps 2 percent if we’re more generous.

After the introductory humorous start, this white paper right away go to the point asking question.

If we all agree that the world is round, why would anyone willfully flatten it?

This white paper continues to discuss other important feature of SQL Server with regards to flat map and continuously keep on enhancing knowledge as easy read.

Read white paper here.

Abstract courtesy : Microsoft

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

SQL SERVER – Spatial Database Definition and Research Documents

Recently I was asked in meeting of SQL SERVER user group, what my opinion about spatial database. I answered from my basic knowledge. Spatial database is like database of space (not the star wars or star trek kind space). SQL Server database can understand the numeric and string values. If we ask to SQL Server what is multiplication of 6 and 3 it will provide answer as 18. If we ask to SQL Server what is distance between two points in polygon, it will be not able to answer using native functions. Custom SQL code written by user can do similar coding. In short spatial database is about lines, points, polygons and their interesting features. SQL Server is not spatial database system.

After I came back from home I searched more about it and found some interesting research and documentation. I am posting here assuming it will be interesting to all of my readers.

Another example of spatial and non spatial query is here:
Non-spatial: List the names of all company with more than 100 employees.
Spatial: List the names of all company within 10 miles of Las Vegas.

Here is the definition from Wikipedia about spatial database :
A spatial database is a database that is optimized to store and query data related to objects in space, including points, lines and polygons. While typical databases can understand various numeric and character types of data, additional functionality needs to be added for databases to process spatial data types.

Very interesting paper written by An Introduction to Spatial Database Systems by Ralf Hartmut Güting.
Author defines Spatial Database as
(1) A spatial database system is a database system.
(2) It offers spatial data types (SDTs) in its data model and query language.
(3) It supports spatial data types in its implementation, providing at least spatial indexing and efficient algorithms for spatial join.
Tutorial in form of slide show can be found here.

Reference for books and slide shows can be found here.
Reference : Pinal Dave (http://blog.SQLAuthority.com)