Doing spatial join which is also based on time using ArcGIS for Desktop?

Question

I have a time series plot of house sales for 4 years, but in those 4 years some districts change. I was wondering if I need to split the data into 4 layers for each year in order to join them to their respective district at the time of the sale.

For example, house 1 sells 2 times, once in 2007 and again in 2010. During that time the district changed from district 229 to 271. Is there any way to join them so that the right observation takes the right district identifying when doing a spatial join?

Districts are polygons and addresses are points. The address points are geolocated based on address listed in the assessor information based on house number, road name, road direction, and zip code.

I am using ArcMap 10.1.

Answer 1

Because a "spatial join" in ArcGIS is in fact a geoprocessing tool (in the meaning, that new output in form of a file is generated) and this tools doesn't allow to add further join parameters (as in a plain database join) or to combine it with a classic join I would guess that there is not any chance to do this dynamically or even with some simple steps. At least you have to split the districts in a couple of district layers where no overlapping districts appears (for example with definition queries, making the position of district an unique locational factor for joining). Then you have to 'geoprocess' a spatial join for every districts version and the corresponding adresses in that time span.

As an option a true SQL/MM-spatial capable RDBMS (like Oracle SPatial, PostGIS, SQL Server Spatial, etc...) is able to combine classic joins with Spatial relations. So you can formulate a SQL query like this (SQL Server):

SELECT * from districts, adresses
where adresses.timestamp between districts.validfrom and districts.validto
and adresses.geom.STIntersects(districts.geom) = 1

At least this would also be very interesting for me, if I am wrong and there definitely IS a way to perform this easily in ArcGIS (favoured this question for further replies).

I did not test the SQL query here in any way, so it may not work out of the box. It only should demonstrate the difference.