Spatial join versus classical join [closed]

Question

What are the main differences between a spatial join and a classical database join such as an equi (inner) join?

Answer 1

The main difference is that in a classical join, inner (equi), left or right the joined fields or field must match exactly on both sides of the join, ie, in both tables you are joining.

In a spatial join, there is no notion of exactness. Instead you are joining on an intersection, containment or even distance between a geometry field in one table and a geometry field in another table.

While you can write a spatial join using traditional looking join syntax, it is probably more common, and I think clearer, to use the comma operator, which is actually equivalent to a CROSS JOIN, which is a Cartesian product between two tables, which you then restrict by the spatial join condition, eg,

SELECT b.* FROM some_table a, some_table b WHERE ST_Contains (a.geom, b.geom)

will get everything from table b where the geometry is contained by table a's geometry. Replace ST_Contains with ST_Intersects to get everything where a and b's geometries intersect or contain each other.

Naturally, it is important to have a spatial index on the geometry columns being spatially joined to avoid an actual cross join, which highlights another difference between a traditional and spatial join, the indexes used: in a traditional join a B-tree is typically used, whereas spatial joins use R-tree indexes, or some variation thereof, which allows for comparing objects for likely intersection based on their two-dimensional bounding boxes, before doing the more exact intersection/containment/distance calculation.

Another example, which might look more like a traditional join, is given in the Postgis ST_DWithin examples, which I repeat here:

SELECT DISTINCT ON (s.gid) s.gid, s.school_name, s.the_geom, h.hospital_name
FROM schools s
    LEFT JOIN hospitals h ON ST_DWithin(s.the_geom, h.the_geom, 3000)
ORDER BY s.gid, ST_Distance(s.the_geom, h.the_geom);

which uses a familiar looking LEFT JOIN, but where the join condition is really that of being within 3000 metres of something else, in this case schools and hospitals.

EDIT As @jpmc26 has noted in the comments, it is quite possible to do joins without exact matches using BETWEEN with > and/or < conditions, for example:

SELECT * FROM x JOIN y ON x.timestamp BETWEEN y.start_timestamp AND y.end_timestamp. 

So, perhaps a spatial join should be thought of as a 2 dimensional (or 3-dimensional, with the sfgcal extension) analog of a join involving BETWEEN, > and/or < conditions.

I still maintain that in the vast majority of cases, both in the wild and in text books, non-spatial joins are done on exact matches, whereas a spatial join on contains, within, intersects, etc conditions are implicitly based on ranges, while recognizing the usefulness of the comment.

Answer 2

You are just adding location or proximity attribute data to your tabular query. If I have a sales order code number on in an order table, then in a "classical" join I might just look up the description of that sales order code number. If that same order table also has, say, an x_coord and y_coord column, I can use those values to find out sales districts or other location based information. Now if the order table had an area code to find the same sales district, then there's no real difference to a spatial join and a classical join. Both have accomplished the same goal: You have the sales district. I had to think of the spatial column as just another foreign key to get to this idea. However, if you look at the spatial column, it just doesn't look the same as sales order code "BR549".

Where spatial starts excelling over the lookup idea is in proximity analysis or finding other district information that you don't have by the example sales district code. What if you want to know about a hotel or event that occurred near where the sales took place. The spatial query allows you to and another spatial layer or table to query on spatially. That means you don't have to go back to your order table to add an attribute for the new layer so that you can query on the join columns.

Where the classical approach may feel superior to a "classical" database user is that primary/foreign keys provide a feeling of keeping connecting data clean by the constraints on the data. In the spatial world, you'd want to make sure that you are in the same coordinate systems and your boundaries are in good condition. You'd also want to make sure that your point data in the orders table is accurate to begin with or in the correct location based on some tolerance. The nosql crowd would say that you don't need some of these "classical" constraints.

I hope this helps. This reflects how I thought about the transition for the "classical" world into the "spatial" world. I thought about things I knew and related old query ideas to the new query ideas found in the spatial world.

Answer 3

Conceptually, nothing. A JOIN simply pairs up rows based on whether a condition is true. In a spatial join, the condition is just a geometric operation on geometric data (e.g., two polygons must intersect). This does not change with spatial queries.

The reason a big deal is made of it is that spatial data is considerably more complex than other typical data types. They are a variable length, multidimensional amalgamation of several disparate pieces of data, and this means there is no obvious way to order them. The relationships between them are also more complicated as a result. Consider simple equality. What does it mean for polygon geometries to be equal? Does it mean they contain the exact same region of space, they are constructed with exactly the same vertices in exactly the same order, or something in between? All of these are valid definitions, but each one is useful only in very different contexts.

This complexity makes both indexing and querying efficiently more difficult. Performance is usually improved using bounding box filtering (possibly via an R-Tree index) or grid spatial indexes. Even with these improvements, the overall operation is often still more expensive than with simpler data types, where the simplicity of the data, the more simply defined relations, and the obvious ordering make it easier to leverage clever index structures and statistical analysis to automatically optimize queries.