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.

  • Wouldn't it be easier to use some kind of versioning system? Or do you already have those two layers and you have to deal with them now? – underdark Apr 12 '11 at 8:43
  • It would definitely be easier to use versioning, except that the data is not owned or maintained by me. It is data that is provided in quarterly updates, in a variety of formats (usually shapefiles), and I want to find out which features and attributes have changed between updates. – RyanDalton Apr 12 '11 at 14:13

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
  ( t1.id = t2.id and row(t1.att1, t1.att2, t1.geom) != row(t2.att1, t2.att2, t2.geom))
  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.

  • That would ignore any additions or deletions to the dataset (where an id is only present in one table), wouldn't it? – relet Apr 13 '11 at 13:50
  • 1
    The question doesn't mention deletes or additions but you could easily union that with a query to find ones that are new or deleted. You could also work in the difference between the features. – Sean Apr 13 '11 at 15:30
  • Sean, this is a great concept that looks really promising. 1) This finds all features that are the same, how would I find all the changes? 2) What would be the best method to join this result back to the changed features (geometries), so that you could visually compare the changed features to the source features? – RyanDalton Apr 13 '11 at 17:25
  • @Ryan: It finds both - it should return if the rows are identical (True or False) for each pair of identical ids. – relet Apr 14 '11 at 7:28
  • @Sean: I am so very close to making this work, but when I attempted to do a UNION (as I used in the ST_EQUALS answer) to include all of the new geometries, I couldn't get it to work because it said I needed the same number of rows in my union query. The Row()=Row() creates an boolean column that doesn't exist in my source data (part of the UNION statement), so how do I create/reference a "fake" column in my UNION query to match the ROW()=ROW() select statement? – RyanDalton Apr 14 '11 at 17:03

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

  • Does this compare two layers or just two specific features? Also, what does "spatially equal" mean? Does it mean that they represent the same set of points on earth, or does it meant that they represent the same set of points in exactly the same ways? (The latter is more stringent.) – whuber Apr 13 '11 at 2:38
  • What I really wanted to get as a result were all geometries in layer A (updated) that had been added or changed since layer B (original). I was able to accomplish that result with the following syntax: SELECT A.fields FROM A EXCEPT SELECT A.fields FROM A,B WHERE ST_EQUALS(A.geom,B.geom). The result is all of the feature geometries from layer A that have changed or been added. Thanks! – RyanDalton Apr 13 '11 at 19:55

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.