Chapter 3. Introduction to Geospatial Databases

In the previous chapters, you learned how to set up your Python environment and learned about the different libraries available for working with geospatial data using Python. In this chapter, you will start working with data.

Databases provide one of the most popular ways to store large amounts of data, and one of the most popular open source databases is PostgreSQL. PostGIS extends PostgreSQL, adding geographic objects and the ability to query records spatially. When PostgreSQL and PostGIS are combined, they create a powerful geospatial data repository.

Geospatial databases improve on basic relational database queries by allowing you to query your data by location or by location to other features in the database. You can also perform geospatial operations such as measurements of features, distances between features, and converting between projections. Another feature of geospatial databases is the ability to create new geometries from existing...

Installing PostgreSQL and PostGIS on Windows

You can install PostGIS by installing PostgreSQL and then install PostGIS afterwards, or you can install PostgreSQL and then use Stack Builder, which comes with PostgreSQL, afterwards to add PostGIS. Using Stack Builder allows you to download the desired PostgreSQL version and with a single-click also get the correct PostGIS version.

Note

When I installed PostgreSQL 10, Stack Builder did not include PostGIS. By the time of publication, this should have been added. Screenshots may show a different PostGIS version because I used an old copy of PostgreSQL to show how Stack Builder would work. You can download PostgreSQL from https://www.postgresql.org/download/.

As we proceed, I will walk you through installing PostgreSQL and then using Stack Builder to add PostGIS and a database. After downloading the executable, run it by double-clicking it. You will see the wizard as follows:

You can choose where to install PostgreSQL, but it is probably best to leave...

Installing PostgreSQL and PostGIS on Mac

To install PostgreSQL and PostGIS on a Mac, you can use Postgres.app. You can download the file from http://postgresapp.com/. After the file has downloaded, move it to the applications folder and double-click it. Click Initialize. You will have a server on localhost:5432. The username and database name is the same as your Mac user. There is no password.

You should then be able to use the psql commands to create a new database and enable PostGIS.

Working with PostgreSQL and PostGIS using Python

To connect and manipulate your PostgreSQL database in Python, you will need a library to assist you. psyscopg2is that library. It provides a wrapper around the official libpq client library. In this section, we will cover how to install the library, how to connect to the database, and how to add a table and perform basic geospatial queries.

Connecting to PostgreSQL using psycopg2

pscycopg2 is the most popular library for working with PostgreSQL in Python. It fully implements the Python DB API 2.0 specificationand works with Python 3. In the following sections, you will learn how to install the library, make connections, execute queries, and read the results. You can read the full documentation here: http://initd.org/psycopg/docs/

Installing psycopg2

Installing most Python libraries requires you to open your console and type:

pip install psycopg2

If that doesn't work, and you are using an Anaconda Python distribution, you can run the conda command...

Summary

This chapter covered the installation of PostgreSQL and PostGIS as well as psycogp2 and Shapely. Then, we gave a brief overview of the major functions used when working with a spatial database. You should now be familiar with connecting to the database, executing queries to insert data, and how to get your data out. Furthermore, we covered functions that return new geometries, distances, and areas of geometry. Understanding how these functions work should allow you to read the PostGIS documents and be comfortable with forming the SQL statement for that function.

In the next chapter, you will learn about the major data types in GIS and how to use Python code libraries to read and write geospatial data. You will learn how to convert between data types, and how to upload and download data from geospatial databases and remote data sources.