PostGIS feature compare function?

Question

I would like to be able to compare features in Layer 1 (source) to features in Layer 2 (updates to source) in order to see if any of the features geometry and/or attributes have changed. I would also then like have a "report" generated stating what has changed.

Edit for Clarification: Changes in geometry also include finding features that have been added to the new data layer. Deletions might be interesting to have, but additions are more important.

I've been looking through the PostGIS functions and can't seem to find any tools that are designed to compare and report differences between 2 layers features, both geometry and attributes.

I am looking for something similar to the ArcGIS "Feature Compare" tool.

If there is nothing like this in PostGIS, are there other open-source tools that will answer these types of questions? Thanks.

Answer 1

You can do row-wise comparisons in PostgreSQL with row constructors. I'm guessing that this would work with geometry fields but I haven't tried it.

Given two tables where the matching features have the same id field in each you could do something like:

select ROW(t1.att1, t1.att2, t1.geom) = ROW(t2.att1, t2.att2, t2.geom)
from t1, t2
where t1.id = t2.id

Update: This does work with geometry fields in my testing.

Update 2: Here's a more complete example based on your criteria.

Tables: t1(id, att1, att2, geom) t2(id, att1, att2, geom)

-- return the id and geometry from the updated table if the attributes or
-- geometry have changed or it's a new feature.

select t2.id, t2.geom
from t1, t2
where
  ( t1.id = t2.id and row(t1.att1, t1.att2, t1.geom) != row(t2.att1, t2.att2, t2.geom))
  or
  t2.id not in (select id from t1)

That should get you what you're looking for. You'll probably want to change the 'not in' clause to a 'not exists' for better performance.

Answer 2

The function ST_Equals(geometryA, geometryB) returns TRUE if they are spatially equal.