1

Introducing the Fundamentals of Geospatial Analytics

Understanding where something happened is often the key to understanding why it occurred in the first place. A flood wipes out a village in a remote country 1,000 miles away. If you are thinking geospatially, you are curious about climate forecasting, the demographics of the population, the nature of the soil, the topography of the land, and building footprints or structures. The ability to point to a spot on a map is only a small part of data collection. When you begin to think about roads and distances to impacted villages requiring disaster relief, you would be limited by point-to-point Cartesian geometry. This chapter will help you to understand why.

The process of detecting and quantifying patterns in datasets requires data exploration, visualization, data engineering, and the application of analysis and spatial techniques. At its core, geospatial technology provides an opportunity to explore location intelligence and how it informs the data we collect.

Geospatial information is location data that allows the assessment of geographically linked activities and locations on the Earth’s surface. Often called the science of “where,” geospatial analysis can provide insight into events occurring within geographic areas such as flooding but also patterns in the spread of wildfires and urban heat islands (increased temperatures in cities compared to rural areas).

Collecting and analyzing geographic information may also include non-spatial data providing opportunities to alter the appearance of a map, based on non-spatial attributes associated with a location. Attributes in geographic information systems (GIS) refer to data values that describe spatial entities. For example, perhaps an attribute table stores information not only on the building polygon footprint but also indicates that the building is a school or residence.

On your map, you may be interested in viewing wastewater treatment plants or buildings complying with green energy requirements within a city or neighborhood boundary. Although this information is non-spatial, you can view and label information associated with specific locations. You are able to direct map applications to render a map layer based on a wide variety of features.

As you learn how to query your data with Structured Query Language (SQL), the advantages will include flexibility in accessing and analyzing your data. This chapter will introduce SQL and expand upon concepts throughout the remaining chapters.

Before we explore the syntax of SQL, let’s introduce or review a few concepts unique to geospatial data.

In this chapter, we will cover the following topics:

• Spatial databases
• Spatial reference identifiers (SRIDs)
• Understanding geospatial data types
• Exploring SQL

First, let’s become familiar with a few characteristics of geospatial data. You will set up working environments later in the chapter.

Technical requirements

The following are the technical requirements for this chapter:

• PostgreSQL installation for your operating system (macOS, Windows, or Linux)
• PostGIS extension
• pgAdmin
• Quantum Geographic Information System (QGIS)
• GitHub link at https://github.com/PacktPublishing/Geospatial-Analysis-with-SQL

Spatial databases

Geospatial data is vast and often resides in databases or data warehouses. Data stored in a filesystem is only accessible through your local computer and is limited to the speed and efficiency of your local computer. Collecting and analyzing geographic information within storage systems of relationally structured data increases efficiency.

Spatial databases are optimized for storing and retrieving your data. Data stored in databases are accessed through client software. The client is a computer or hostname (where the database is located).

Next, the server listens for requests on a port. There are a variety of ports, but with PostgreSQL, the default is 5432—but more on that later. The final pieces of information are about security and accessibility. You will select a username and password to access the database. Now, the client has all it needs to access the database. The data within a database is formatted as tables and resides with the host either locally or in an external server linked to a port that listens for requests. This book will focus on local instances with a mention or two about other options in later chapters.

SQL is a standardization for interacting with databases that abides by American National Standards Institute (ANSI) data standards. Additionally, SQL has also been adopted as the international standard by the International Organization for Standardization (ISO), the International Electrotechnical Commission (IEC), and the Federal Information Processing Standard (FIPS).

SRIDs

Each spatial instance or location has an SRID due to the earth being a non-standard ellipsoid. We talk about the surface of the earth, but what does this mean exactly? Do we plunge to the depths of the ocean floor or the highest mountains? The surface of the earth varies depending on where you are on the surface of the earth.

Spatial reference systems (SRS) allow you to compare different maps. If they are superimposable, they have the identical SRS. The European Petroleum Survey Group (EPSG) is the most popular.

Figure 1.1 visually demonstrates the discrepancy in mapping distances on the surface of the earth. Rotational forces at the poles flatten the earth, creating bulging at the equator. This phenomenon complicates the accurate calculation of the distance between two points located at different latitudes and longitudes on the Earth’s surface. The coordinate system helps to standardize spatial instances:

Figure 1.1 – Ellipsoid shape of the earth

When working with location data, the use of reference ellipsoids helps to specify point coordinates such as latitude and longitude, for example. The Global Positioning System (GPS) is based on the World Geodetic System (WGS 84). You will notice different IDs based on the types of datasets you are working with—EPSG 4326 is for WGS 84. WGS 84 is the most used worldwide. A spatial column within a dataset can contain objects with different SRIDs, however, only spatial instances with the same SRID can be used when performing operations with SQL Server spatial data methods. Luckily, PostgreSQL will raise an exception when an incompatibility is detected.

SRIDs are stored in a Postgres table visible in the public schema named spatial_ref_sys. You can discover all of the SRIDs available within PostGIS (the spatial extension for PostgreSQL) with the following SQL query:

If you look down the column in Figure 1.2, you will notice EPSG 4326 but also a sample of other SRIDs available:

Figure 1.2 – PostGIS SRID

Figure 1.3 shows the SRID for data in the NYC database. These are two distinct datasets, but you can see how variation in SRID might be a problem if you would like to combine data from one table with data in another. When working with a graphical user interface (GUI) such as QGIS, for example, SRIDs are often automatically recognized.

Figure 1.3 – SRID for NYC database

You can check the geometry and SRID of the tables in your database by executing the command in the query that follows. Don’t worry if this is your first time writing SQL queries—these early statements are simply for context. The dataset included here is a standard practice dataset, but imagine if you wanted to combine this data with another dataset in a real-world project:

Next to the srid column in Figure 1.3, you can see geometry types. The next section will introduce you to the different geometries.

Understanding geospatial data types

To understand how to work with geospatial data and the functions...