04-Spatial relationship query

Reference ¶

In [1]:

Copied!

import os
%load_ext sql
import os
%load_ext sql

Connecting to the database¶

In [2]:

Copied!





host = "localhost"
database = "nyc"
user = os.getenv('SQL_USER')
password = os.getenv('SQL_PASSWORD')
connection_string = f"postgresql://{user}:{password}@{host}/{database}"
%sql $connection_string
host = "localhost"
database = "nyc"
user = os.getenv('SQL_USER')
password = os.getenv('SQL_PASSWORD')
connection_string = f"postgresql://{user}:{password}@{host}/{database}"
%sql $connection_string

Out[2]:

'Connected: postgres@nyc'

In [3]:

Copied!

%%sql 

SELECT * from nyc_subway_stations LIMIT 5
%%sql 

SELECT * from nyc_subway_stations LIMIT 5

 * postgresql://postgres:***@localhost/nyc
5 rows affected.

Out[3]:

id	geom	objectid	name	alt_name	cross_st	long_name	label	borough	nghbhd	routes	transfers	color	express	closed
376	010100002026690000371775B5C3CE2141CBD2347771315141	1	Cortlandt St	None	Church St	Cortlandt St (R,W) Manhattan	Cortlandt St (R,W)	Manhattan	None	R,W	R,W	YELLOW	None	None
2	010100002026690000CBE327F938CD21415EDBE1572D315141	2	Rector St	None	None	Rector St (1) Manhattan	Rector St (1)	Manhattan	None	1	1	RED	None	None
1	010100002026690000C676635D10CD2141A0ECDB6975305141	3	South Ferry	None	None	South Ferry (1) Manhattan	South Ferry (1)	Manhattan	None	1	1	RED	None	None
125	010100002026690000F4CF3E3654032241B5704681A73C5141	4	138th St	Grand Concourse	Grand Concourse	138th St / Grand Concourse (4,5) Bronx	138th St / Grand Concourse (4,5)	Bronx	None	4,5	4,5	GREEN	None	None
126	01010000202669000084DADF7AED0422410C380E6E3A3D5141	5	149th St	Grand Concourse	Grand Concourse	149th St / Grand Concourse (4) Bronx	149th St / Grand Concourse (4)	Bronx	None	4	2,4,5	GREEN	express	None

Spatial Relationships¶

So far we have only used spatial functions that measure (ST_Area, ST_Length), serialize (ST_GeomFromText) or deserialize (ST_AsGML) geometries. What these functions have in common is that they only work on one geometry at a time.
Spatial databases are powerful because they not only store geometry, they also have the ability to compare relationships between geometries.
- Questions like “Which are the closest bike racks to a park?” or “Where are the intersections of subway lines and streets?” can only be answered by comparing geometries representing the bike racks, streets, and subway lines.
The OGC standard defines the following set of methods to compare geometries.

ST_Equals¶

ST_Equals(geometry A, geometry B)tests the spatial equality of two geometries.

ST_Equals returns TRUE if two geometries of the same type have identical x,y coordinate values, i.e. if the second shape is equal (identical) to the first shape.

First, let’s retrieve a representation of a point from our nyc_subway_stations table. We’ll take just the entry for ‘Broad St’.

In [4]:

Copied!

%%sql

SELECT name, geom, ST_AsText(geom)
FROM nyc_subway_stations
WHERE name = 'Broad St';
%%sql

SELECT name, geom, ST_AsText(geom)
FROM nyc_subway_stations
WHERE name = 'Broad St';

 * postgresql://postgres:***@localhost/nyc
1 rows affected.

Out[4]:

name	geom	st_astext
Broad St	0101000020266900000EEBD4CF27CF2141BC17D69516315141	POINT(583571.9059213118 4506714.341192182)

Then, plug the geometry representation back into an ST_Equals test:

In [5]:

Copied!

%%sql

SELECT name
FROM nyc_subway_stations
WHERE ST_Equals(geom, '0101000020266900000EEBD4CF27CF2141BC17D69516315141');
%%sql

SELECT name
FROM nyc_subway_stations
WHERE ST_Equals(geom, '0101000020266900000EEBD4CF27CF2141BC17D69516315141');

 * postgresql://postgres:***@localhost/nyc
1 rows affected.

Out[5]:

name
Broad St

ST_Intersects, ST_Disjoint, ST_Crosses and ST_Overlaps¶

ST_Intersects, ST_Crosses, and ST_Overlaps test whether the interiors of the geometries intersect.

ST_Intersects(geometry A, geometry B) returns t (TRUE) if the two shapes have any space in common, i.e., if their boundaries or interiors intersect.

The opposite of ST_Intersects is ST_Disjoint(geometry A , geometry B).

If two geometries are disjoint, they do not intersect, and vice-versa. In fact,
- it is often more efficient to test “not intersects” than to test “disjoint” because the intersects tests can be spatially indexed, while the disjoint test cannot.

For multipoint/polygon, multipoint/linestring, linestring/linestring, linestring/polygon, and linestring/multipolygon comparisons, ST_Crosses(geometry A, geometry B) returns t (TRUE) if the intersection results in a geometry whose dimension is one less than the maximum dimension of the two source geometries and the intersection set is interior to both source geometries.

ST_Overlaps(geometry A, geometry B) compares two geometries of the same dimension and returns TRUE if their intersection set results in a geometry different from both but of the same dimension.

Let’s take our Broad Street subway station and determine its neighborhood using the ST_Intersects function:

In [6]:

Copied!

%%sql

SELECT name, ST_AsText(geom)
FROM nyc_subway_stations
WHERE name = 'Broad St';
%%sql

SELECT name, ST_AsText(geom)
FROM nyc_subway_stations
WHERE name = 'Broad St';

 * postgresql://postgres:***@localhost/nyc
1 rows affected.

Out[6]:

name	st_astext
Broad St	POINT(583571.9059213118 4506714.341192182)

In [7]:

Copied!

%%sql

SELECT name, boroname
FROM nyc_neighborhoods
WHERE ST_Intersects(geom, ST_GeomFromText('POINT(583571 4506714)',26918));
%%sql

SELECT name, boroname
FROM nyc_neighborhoods
WHERE ST_Intersects(geom, ST_GeomFromText('POINT(583571 4506714)',26918));

 * postgresql://postgres:***@localhost/nyc
1 rows affected.

Out[7]:

name	boroname
Financial District	Manhattan

ST_Touches¶

ST_Touches tests whether two geometries touch at their boundaries, but do not intersect in their interiors

ST_Touches(geometry A, geometry B) returns TRUE if either of the geometries’ boundaries intersect or if only one of the geometry’s interiors intersects the other’s boundary.

ST_Within and ST_Contains¶

ST_Within and ST_Contains test whether one geometry is fully within the other.

ST_Within(geometry A , geometry B) returns TRUE if the first geometry is completely within the second geometry. ST_Within tests for the exact opposite result of ST_Contains.

ST_Contains(geometry A, geometry B) returns TRUE if the second geometry is completely contained by the first geometry.

ST_Distance and ST_DWithin¶

An extremely common GIS question is “find all the stuff within distance X of this other stuff”.

The ST_Distance(geometry A, geometry B) calculates the shortest distance between two geometries and returns it as a float. This is useful for actually reporting back the distance between objects.

In [8]:

Copied!

%%sql

SELECT ST_Distance(
  ST_GeometryFromText('POINT(0 5)'),
  ST_GeometryFromText('LINESTRING(-2 2, 2 2)'));
%%sql

SELECT ST_Distance(
  ST_GeometryFromText('POINT(0 5)'),
  ST_GeometryFromText('LINESTRING(-2 2, 2 2)'));

 * postgresql://postgres:***@localhost/nyc
1 rows affected.

Out[8]:

st_distance
3.0

For testing whether two objects are within a distance of one another, the ST_DWithin function provides an index-accelerated true/false test.

This is useful for questions like “how many trees are within a 500 meter buffer of the road?”.
You don’t have to calculate an actual buffer, you just have to test the distance relationship.

Using our Broad Street subway station again, we can find the streets nearby (within 10 meters of) the subway stop:

In [9]:

Copied!





%%sql

SELECT name
FROM nyc_streets
WHERE ST_DWithin(
        geom,
        ST_GeomFromText('POINT(583571 4506714)',26918),
        10
      );
%%sql

SELECT name
FROM nyc_streets
WHERE ST_DWithin(
        geom,
        ST_GeomFromText('POINT(583571 4506714)',26918),
        10
      );

 * postgresql://postgres:***@localhost/nyc
3 rows affected.

Out[9]:

name
Broad St
Wall St
Nassau St

And we can verify the answer on a map. The Broad St station is actually at the intersection of Wall, Broad and Nassau Streets.

Function List¶

ST_Contains(geometry A, geometry B): Returns true if and only if no points of B lie in the exterior of A, and at least one point of the interior of B lies in the interior of A.
ST_Crosses(geometry A, geometry B): Returns TRUE if the supplied geometries have some, but not all, interior points in common.
ST_Disjoint(geometry A , geometry B): Returns TRUE if the Geometries do not “spatially intersect” - if they do not share any space together.
ST_Distance(geometry A, geometry B): Returns the 2-dimensional cartesian minimum distance (based on spatial ref) between two geometries in projected units.
ST_DWithin(geometry A, geometry B, radius): Returns true if the geometries are within the specified distance (radius) of one another.
ST_Equals(geometry A, geometry B): Returns true if the given geometries represent the same geometry. Directionality is ignored.
ST_Intersects(geometry A, geometry B): Returns TRUE if the Geometries/Geography “spatially intersect” - (share any portion of space) and FALSE if they don’t (they are Disjoint).
ST_Overlaps(geometry A, geometry B): Returns TRUE if the Geometries share space, are of the same dimension, but are not completely contained by each other.
ST_Touches(geometry A, geometry B): Returns TRUE if the geometries have at least one point in common, but their interiors do not intersect.
ST_Within(geometry A , geometry B): Returns true if the geometry A is completely inside geometry B