In the previous section, you had the database buffer a point and return the polygon. In this section, you will learn how to query the database for the distance between two points, and you will query the database and have it return records based on the distance from a specified point.
The PostGIS function for distance is ST_Distance(a,b). You can pass a and b as geometry or geography. As geography, the result will be returned in meters. The following code will get the distance between two points in the database:
cursor.execute("SELECT ST_Distance(a.location::geography,b.location::geography) FROM art_pieces a, art_pieces b where a.name='101' AND b.name='102'")
dist=cursor.fetchall()
dist
The previous code executes the SQL query for ST_Distance(), passing the location column of a and b which are records, where the code equals 101 and 102. ::geography is how you cast a geometry to a geography in PostGIS. How far are they from each other? They are 9,560.45428363 meters apart.
In the previous example, you used two points from the database, but you can also pass a hard-coded point as in the following code:
cursor.execute("SELECT ST_Distance(a.location::geography,
ST_GeometryFromText('POINT(-106.5 35.1)')::geography)
FROM art_pieces a where a.name='101'")
cursor.fetchall()
The previous code is the same query, but this time you switch out point b (code=102) with a hard-coded WKT point. The results of the query should state that the points are 10,391.40637117 meters apart.
And, as in previous examples, you can also use shapely to pass the WKT of the point, as in the following code:
from shapely.geometry import Point
p=Point(-106.5,35.1)
cursor.execute("SELECT ST_Distance(a.location::geography,
ST_GeometryFromText('{}')::geography)
FROM art_pieces a where a.name='101'".format(p.wkt))
cursor.fetchall()
The previous code creates the point in shapely and then uses format(p.wkt) to pass the WKT to the {} placeholder.
You can get the distance between two points, but what if you want the distance of more than one point from another? To do that, you can remove the a.location and just use location as the first point. The following code will return five points and their distances from the specified point:
cursor.execute("SELECT code, ST_Distance(location::geography,
ST_GeometryFromText('POINT(-106.591838300225
35.1555000000615)')::geography)
as d from art_pieces LIMIT 5")
cursor.fetchall()
The results should look like the data showing the distance in meters:
[('101', 0.0),
('102', 9560.45428362),
('104', 4741.8711304),
('105', 9871.8424894),
('106', 7907.8263995)]
The database returned the first five points in the table with their code and distance from the specified point. If you remove the LIMIT, you will get all of the points.
By adding an ORDER BY clause and the k-nearest neighbor operator, you can extend this query to get the closest five points to the specified point. Look at the following code:
cursor.execute("SELECT code, ST_Distance(location::geography,
ST_GeometryFromText('POINT(-106.591838300225
35.1555000000615)')::geography) as d from art_pieces
ORDER BY location<-
>ST_GeometryFromText('POINT(-106.591838300225
35.1555000000615)') LIMIT 5")
cursor.fetchall()
The key element in the previous code is the symbol <->. This is the k-nearest neighbor operator. It returns the distance between two geometries. Using ORDER BY location <-> ST_GeometryFromText(), you are specifying two geometries. Because you set a LIMIT of 5, the database will return the 5 closest points to the one specified—including the point of origin. The results should look like the following points:
[('101', 0.0),
('614', 1398.08905864),
('492', 2384.97632735),
('570', 3473.81914218),
('147', 3485.71207698)]
Notice that the code value is not 101-106 or the first five from the database, and that the distance increases from 0.0. The closest point, code 101, is the point you specified in the query, so it is 0.0 meters away.