Take the 2-minute tour ×
Geographic Information Systems Stack Exchange is a question and answer site for cartographers, geographers and GIS professionals. It's 100% free, no registration required.

I have two polygonal layers: "existing" and "modified". enter image description here Attribute table of "existing" and "modified" layers have same column "feature_id" - number of feature. Please help me create query which write attributes 't' (if features matches) and 'f' (if features not matches) in 'compare_result' column of "modified" layer using ST_Equals function. Now I receive attributes 't' (if features matches) and 'f' (if features not matches) in pgAdmin result window using query:

SELECT  ST_Equals(modified.the_geom, existing.the_geom) AS compare_result FROM  modified, existing 
WHERE modified.feature_id=existing.feature_id

but I don't can create query? which write result in attribute table. Thanks!

EDIT: Solution without creating additional layer:

UPDATE
    modified
SET
    compare_result = compare_result_temp

FROM
    (
        SELECT
r."feature_id" AS idmodified,
ST_Equals(r.the_geom, m.the_geom) AS compare_result_temp
        FROM
     "modified" AS r,
     "existing" AS m
WHERE r."feature_id" = m."feature_id"
    ) AS foo
 WHERE 
 foo.idmodified = modified."feature_id";
share|improve this question
add comment

1 Answer 1

up vote 2 down vote accepted

You can try this :

-- create table temp

drop table if exists compare_result;
create table compare_result as (
SELECT  
existing.feature_id as id_init,
modified.feature_id as id_modified,
ST_Equals(modified.geom, existing.geom) AS compare_result 
FROM  modified RIGHT OUTER JOIN existing ON (modified.feature_id=existing.feature_id)
);

-- then you can update an attibut (compare_result) -- ALTER TABLE modified add column compare_result varchar(1);

UPDATE modified set compare_result = 't' 
FROM compare_result 
WHERE compare_result = 'TRUE' and modified.feature_id = compare_result.compare_result;

UPDATE modified set compare_result = 'f' 
FROM compare_result 
WHERE compare_result = 'FALSE' and modified.feature_id = compare_result.compare_result;

-- drop table

drop table if exists compare_result;

it should work...

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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