3  Spatial SQL I

3.1 Introduction

In this lesson, you will learn how to use SQL to query spatial data. You will learn how to use the duckdb Python library to connect to a DuckDB database and run SQL queries. You will also learn how to use the leafmap Python library to visualize spatial data.

3.2 Learning Objectives

By the end of this lesson, you will be able to:

  • Connect to a DuckDB database using Python
  • Run SQL queries to query spatial data
  • Visualize spatial data using Leafmap
  • Run spatial SQL queries using PgAdmin

3.3 Materials

To get started, work your way through the following two notebooks:

  1. Geometries
  2. Spatial Relationships

Clicking the link will take you to Google Colab, where you can run the notebook in your browser. These workbooks use the duckdb Python library to connect to a DuckDB database and run SQL queries. They also use the leafmap Python library to visualize spatial data. Most of the spatial functions and syntax are the same or very similar to their PostGIS equivalents.

Once you’ve completed these you can test your knowledge by answering the questions in the lab below. To complete this lab, open PgAdmin and connect to the nyc database. Then, open a new query window and write your SQL queries there.

4 Geometry Exercises

Here's a reminder of all the functions we have seen so far. They should be useful for the exercises!

  • sum(expression) aggregate to return a sum for a set of records
  • count(expression) aggregate to return the size of a set of records
  • ST_GeometryType(geometry) returns the type of the geometry
  • ST_NDims(geometry) returns the number of dimensions of the geometry
  • ST_SRID(geometry) returns the spatial reference identifier number of the geometry
  • ST_X(point) returns the X ordinate
  • ST_Y(point) returns the Y ordinate
  • ST_Length(linestring) returns the length of the linestring
  • ST_StartPoint(geometry) returns the first coordinate as a point
  • ST_EndPoint(geometry) returns the last coordinate as a point
  • ST_NPoints(geometry) returns the number of coordinates in the linestring
  • ST_Area(geometry) returns the area of the polygons
  • ST_NRings(geometry) returns the number of rings (usually 1, more if there are holes)
  • ST_ExteriorRing(polygon) returns the outer ring as a linestring
  • ST_InteriorRingN(polygon, integer) returns a specified interior ring as a linestring
  • ST_Perimeter(geometry) returns the length of all the rings
  • ST_NumGeometries(multi/geomcollection) returns the number of parts in the collection
  • ST_GeometryN(geometry, integer) returns the specified part of the collection
  • ST_GeomFromText(text) returns geometry
  • ST_AsText(geometry) returns WKT text
  • ST_AsEWKT(geometry) returns EWKT text
  • ST_GeomFromWKB(bytea) returns geometry
  • ST_AsBinary(geometry) returns WKB bytea
  • ST_AsEWKB(geometry) returns EWKB bytea
  • ST_GeomFromGML(text) returns geometry
  • ST_AsGML(geometry) returns GML text
  • ST_GeomFromKML(text) returns geometry
  • ST_AsKML(geometry) returns KML text
  • ST_AsGeoJSON(geometry) returns JSON text
  • ST_AsSVG(geometry) returns SVG text

Also remember the tables we have available:

  • nyc_census_blocks
    • blkid, popn_total, boroname, geom
  • nyc_streets
    • name, type, geom
  • nyc_subway_stations
    • name, geom
  • nyc_neighborhoods
    • name, boroname, geom

4.1 Exercises

  • What is the area of the 'West Village' neighborhood? (Hint: The area is given in square meters. To get an area in hectares, divide by 10000. To get an area in acres, divide by 4047.)

  • What is the geometry type of ‘Pelham St’? The length?

  • What is the GeoJSON representation of the 'Broad St' subway station?

  • What is the total length of streets (in kilometers) in New York City? (Hint: The units of measurement of the spatial data are meters, there are 1000 meters in a kilometer.)

  • What is the area of Manhattan in acres? (Hint: both nyc_census_blocks and nyc_neighborhoods have a boroname in them.)

  • What is the most westerly subway station?

  • How long is 'Columbus Cir' (aka Columbus Circle)?

  • What is the length of streets in New York City, summarized by type?

Answers (only check after you’ve given it your best shot!)

5 Spatial Relationships Exercises

Here's a reminder of the functions we saw in the last section. They should be useful for the exercises!

  • sum(expression) aggregate to return a sum for a set of records
  • count(expression) aggregate to return the size of a set of records
  • ST_Contains(geometry A, geometry B) returns true if geometry A contains geometry B
  • ST_Crosses(geometry A, geometry B) returns true if geometry A crosses geometry B
  • ST_Disjoint(geometry A , geometry B) returns true if the geometries do not "spatially intersect"
  • ST_Distance(geometry A, geometry B) returns the minimum distance between geometry A and geometry B
  • ST_DWithin(geometry A, geometry B, radius) returns true if geometry A is radius distance or less from geometry B
  • ST_Equals(geometry A, geometry B) returns true if geometry A is the same as geometry B
  • ST_Intersects(geometry A, geometry B) returns true if geometry A intersects geometry B
  • ST_Overlaps(geometry A, geometry B) returns true if geometry A and geometry B share space, but are not completely contained by each other.
  • ST_Touches(geometry A, geometry B) returns true if the boundary of geometry A touches geometry B
  • ST_Within(geometry A, geometry B) returns true if geometry A is within geometry B

Also remember the tables we have available:

  • nyc_census_blocks
    • blkid, popn_total, boroname, geom
  • nyc_streets
    • name, type, geom
  • nyc_subway_stations
    • name, geom
  • nyc_neighborhoods
    • name, boroname, geom

5.1 Exercises

  • What is the geometry value for the street named 'Atlantic Commons'?

  • What neighborhood and borough is Atlantic Commons in?

  • What streets does Atlantic Commons join with?

  • Approximately how many people live on (within 50 meters of) Atlantic Commons?

Answers (only check after you’ve given it your best shot!)

6 References