Spatial is Not Special – Spatial Interaction completion

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:

cityadj

 

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!

 

 

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s