The Open Source Geospatial Foundation (OSGeo) is a not-for-profit organization whose mission is to foster global adoption of open geospatial technology by being an inclusive software foundation devoted to an open philosophy and participatory community driven development.
~ https://www.osgeo.org/about/
There are a number of projects that have joined OSGeo Community or have been through the mentorship. This is similar to the Apache Incubator service. These projects include desktop applications, web applications, tools, utilities, and libraries. In this post we will see an overview of QGIS and the GDAL library.
QGIS is an open source geographic information system under GNU General Public License. It is an official project of the Open Source Geospatial Foundation. It is cross platform and runs on Windows, Linux, Max OSX, and Android (beta!). There is a large community of volunteers that drive the project, and the source code is available on QGIS GitHub.
Features of QGIS include, working with raster and vector layers, import and export of common GIS file formats, and connecting to a wide variety of databases. Next we will review the GDAL library and see how to load a shapefile into SQL Server.
The Geospatial Data Abstraction Library (GDAL) is a library for reading, writing, and modifying raster and vector geospatial formats. There are 95 vector drivers and 162 raster drivers. Each driver can read a data source, make transformations, and output to a different driver.
There are a number of applications that leverage GDAL. During the installation of these applications, GDAL will be installed with the application. Here are a few applications that you can download to install GDAL.
- Conda-Forge – https://anaconda.org/conda-forge/gdal
- GRASSGIS – https://grass.osgeo.org/download/software/ms-windows/
- OSGeo4W – https://trac.osgeo.org/osgeo4w/
- QGIS – https://www.qgis.org/en/site/forusers/download.html
- GDAL in Docker – https://hub.docker.com/r/osgeo/gdal/tags
Now let’s review the Microsoft SQL Server Spatial Database (MSSQLSpatial) driver. This will work with SQL Server 2008+ and can create both geometry and geography datatypes. To begin we need to have a connection string to the SQL Server. We will also need to know the file path to our GIS file.
We will use the data set from data.gov called Current State and Equivalent National shapefile. This is a TIGER format file from 2017. Let’s create a folder called spatial on the c: drive. This could be created anywhere, but this is the folder format I’ll use for the remainder of this post.
I have installed OSGeo4W in order to get GDAL. Now open up powershell and change directory to this new folder. Download the shapefile and extract the zip file. The following powershell script will perform these operations.
cd c:\spatial
Invoke-WebRequest https://www2.census.gov/geo/tiger/TIGER2017//STATE/tl_2017_us_state.zip -O tl_2017_us_state.zip
Expand-Archive tl_2017_us_state.zip -DestinationPath tl_2017_us_state
Now that we have the shapefile we can use a GDAL utility program called ogr2ogr to load this into SQL Server. This program has multiple switches for different operations and can be found on GDAL Documentation.
ogr2ogr -progress -a_srs "EPSG:4326" -overwrite -f MSSQLSpatial "MSSQL:server=<server>;database=<database>;trusted_connection=yes;" "tl_2017_us_state.shp"
We can then switch over to SSMS and view the contents of the table that was just created. It will look similar to the following.
Notice the new tab in the results called Spatial results. Clicking on this tab will show the following.
This tab allows for zooming and panning around in the results. If we zoom in state side and hover over a state we see the additional column values. We now have successfully loaded a shapefile into SQL Server.
In this post we reviewed the OSGeo Project, QGIS, and GDAL. We downloaded and installed the open source project and an open source geospatial data set. Then loaded the shapefile into SQL Server and viewed the information.
SQL Stack 