We’ve spent some time looking at how to create spatial interaction models using PIVOT tables and spatial constructs in SQL. The one last thing I wanted to add was to create an interaction model for point data. In this case, we will use the cities file we looked at previously. I’ve highlighted in red the only change we have to make. You’ll notice, we took our previous SQL code and changed the adjacency clause to a Distance clause. In this example, if two cities are within 50 miles, we consider them neighbors.
TRANSFORM COUNT(*) SELECT [Cities].name FROM [Cities], [Cities] AS [Cities2] WHERE DistanceEarth(Cities.[Geom (I)],Cities2.[Geom (i)],”mi”) < 50 GROUP BY [Cities].[Name] PIVOT [Cities].[Name]
which gives us a binary, symmetrical matrix like:
You can see that based on our 50 mile distance, Binghamton is a neighbor of Elmira and Ithaca, but is not considered a neighbor of Auburn, Rochester, or Syracuse.
In all of these examples, we have created a spatial weight matrix of some form – whether it be distance or adjacency. These matrices are often used in fields like transportation modeling, economic geography, environmental modeling, or spatial regression. I hope you have an opportunity to explore some of the really cool models that are used in these fields, and if you do, you now know how to create a spatial interaction matrix to feed that beast!