In my book Introduction to Statistical Problem Solving in Geography, we talk about relationships among geographic objects. These relationships are often used in a spatial adjacency matrix. In the book we refer to the age-old question of who is my neighbor, to better understand what signifies a neighbor. Well, neighbors could be next to each other (adjacent), they could be within a specific distance (near), or they could be contained within one another. Also, once we have a neighbor we need to determine what kind of neighbor it is – is it a strong neighbor or a weak neighbor? That is known as a spatial interaction model. The next three posts are going to look at forming a spatial interaction model for distance by examining distance matrices. If you are going to be a quantitative geographer, you are going to have to learn about distance matrices. I am going to use a Manifold .map file located here.
Lets assume that we have a table of 6 cities called Cities, with a geometry column called ‘g’.
The basic distance computation for a set of cities is:
SELECT int(DistanceEarth([Cities].g, [Cities2].g,”mi”)) AS [Miles], [Cities].name, [Cities2].name FROM [Cities], [Cities] AS [Cities2]
The DistanceEarth function is a spatial function that computes the ellipsoidal distance between two points, and in our example we are returning the distance as miles. Because we want to compute the distance from each city to every other city, we select the data from the same table twice, but we call one of the tables [Cities2] so that SQL thinks we are working with a different table. The result of this query is a table that has “from” city and the “to” city along with the distance in miles.
If you are paying attention, you would say “wait, that isn’t a matrix”. That’s right. To create a matrix, we have to turn the resulting table into a PIVOT table to present the data in a matrix form (we did this in an earlier post):
TRANSFORM Min(int(DistanceEarth([Cities].g, [Cities2].g, “mi”))) SELECT [Cities].name FROM [Cities], [Cities] AS [Cities2] GROUP BY [Cities].[Name] PIVOT [Cities2].[Name]
In this case, we grab the Min Distance for each pair of cities, and GROUP it by the City name. This way, we have the minimum distance for each pair of cities. The PIVOT function will pivot the table so that we create an n x n symmetrical matrix of the distances. The result is a table that looks like this:
In our next post, we will look at an inverse and inverse distance squared matrix. Our final post will create an adjacency matrix both for adjacent geographic objects, and those within a specified lag distance.