Distance Calculations
This section demonstrates measuring distance between ad-hoc features, features in the same table (self join), and features in different tables.
Calculating distance is fun and easy. You supply two spatial features and Ingres does the rest. The DISTANCE function has the form:
DISTANCE (geometry1, geometry2)
You do not even need data in a table. Here is an example of comparing the distance between two point locations. This can be done without tables and having to convert each point location from text:
SELECT DISTANCE(
POINTFROMTEXT('POINT(-121 52)'),
POINTFROMTEXT('POINT(-0.59 51.51)')
);
+-----------+
|col1 |
+-----------+
| 120.411|
+-----------+
Next we insert another point feature into our location_points table so we do not have to manually write them all the time:
INSERT INTO location_points VALUES (2, POINTFROMTEXT('POINT(-0.59 51.51)'));
To review the table:
SELECT id, ASTEXT(shape) FROM location_points;
+---------------+----------------------------------+
|id |col2 |
+---------------+----------------------------------+
| 1 |POINT (-121 52) |
| 2 |POINT (-0.59 51.51) |
+---------------+----------------------------------+
Rather than create a second table with points in it to compare them with, we can code a self join that calculates the distance between location of ID=1 and ID=2:
SELECT first.id, second.id, distance( first.shape, second.shape ) AS distance
FROM location_points first,
location_points second
WHERE first.id = 1
AND second.id = 2;
+-------+-------+-----------+
|id |id |distance |
+-------|-------+-----------+
| 1| 2| 120.411|
+-------+-------+-----------+
Taking it one step further, we can compute a Cartesian join between all features to create a distance matrix, simply by dropping the WHERE clause:
SELECT first.id, second.id, distance(first.shape, second.shape) AS distance
FROM location_points first,
location_points second
+-------+-------+-----------+
|id |id |distance |
+-------+-------+-----------|
| 1| 1| 0.000|
| 1| 2| 120.411|
| 2| 1| 120.411|
| 2| 2| 0.000|
+-------+-------+-----------+
In the following distance calculation example, we want to compare the features in our location_points table to the feature in our location_polygon table. In this case, we use an additional function called CENTROID() to calculate the center of the polygon. Then we use that center as the reference point for the DISTANCE() function. So it will calculate distance from point, to center of polygon point:
SELECT DISTANCE (
location_points.shape,
centroid(location_polygons.shape)
)
FROM location_points, location_polygons;
+-----------+
|col1 |
+-----------|
| 134.776|
| 46.411|
+-----------+
The result shows the distance from both records in the location_points table to the center of the polygon.
Note: This is a Cartesian join between two small tables; in larger tables you will want to write an effective WHERE clause.