Querying a spatial database with SQL

Introduction

The most common operator for defining queries in a relational database is the language SQL, which stands for Structured Query Language.

Explanation

A spatial DBMS provides support for geographic coordinate systems and transformations. It will also provide storage of the relationships between features, including the creation and storage of topological relationships. As a result, one is able to use functions for “spatial query” (exploring spatial relationships). To illustrate, a spatial query using SQL to find all the Thai restaurants within 2 km of a given hotel would look like:

SELECT R.Name
FROM Restaurants AS R,
Hotels as H
WHERE R.Type = Thai AND
H.name = Hilton AND
Intersect(R.Geometry, Buffer(H.Geometry, 2))

The Intersect command creates a spatial join between restaurants and hotels. The Geometry column carries the spatial data. It is likely that in the near future all spatial data will be stored directly in spatial databases.

Examples

SELECT Owner
DeedDate FROM TitleDeed
Parcel WHERE TitleDeed.Plot = Parcel.PId AND AreaSize ¿ 1000

Learning outcomes

  • 5 - Data management: relational database and DBMS

    Describe and explain the structure and components of a relational data model and a relational database (level 1 and 2). Describe and explain what a Database Management System (DBMS) is and how it links to a GIS.

  • 6 - Data retrieval and queries

    Explain the basic concepts of data retrieval (attribute and spatial queries) and formulate queries to make a selection on attributes and geospatial data from a spatial database.

Prior knowledge

Incoming relations

  • Query is modelled by Querying a spatial database with SQL

Learning paths