This scenario gives you an introduction to Spatial Relationships in PostGIS.
The database has already been started and the spatial data has already been loaded. This scenario will use data from New York City (NYC). If you want to dig in deeper on the data please go ahead and do this scenario first. Data from this scenario will be used in all the other exercises as well.
We have already logged you into the PostgreSQL command line but, if you get disconnected here are the details on the database we are connecting to:
- Username: groot
- Password: password (same password for the postgres user as well)
- A database named: nyc
And with that, let's dig in.
The container used in this class is available in Dockerhub. As long as you have Docker on your machine you can use the same version of PostgreSQL as the workshop. All the data from the workshop was intentionally chosen from public domain or permissive licenses so that you can use it for commercial and non-commercial purposes. Feel free to download it and play some more at your own pace on your own machine.
I hope you enjoyed learning a little about PostGIS and some new analysis.
And with that, we are finished.
Enjoy learning about PostgreSQL? Sign up for our newsletter and get the latest tips from us each month.
A powerful feature of PostGIS is the ability to compare spatial relationships between geometries.
This allow answering questions like “Which are the closest bike racks to a park?” or “Where are the intersections of subway lines and streets?” by comparing geometries representing the bike racks, streets, and subway lines.
PostGIS provides the full set of spatial relationship functions defined in the Open Geospatial Consortium/SQL-MM standard. The functions allow testing both spatial (topological) relationships and relationship based on distance between geometries.
Most of the relationship functions accept two geometries as input, and return the value of the specified relationship as a boolean value. You can try this with the following query:
SELECT ST_Intersects('POINT(1 1)', 'LINESTRING(1 1, 2 2)');
Usually the boolean relationship functions are used in the
WHERE clause of SQL queries (this is known as a spatial filter).
You will see examples of this using the NYC sample dataset later in this course.
They are also used in
JOIN .. ON clauses to specify a spatial join. This is covered in another course on this site.