Comparing two tables, using PostgreSQL Intersect (on PostGIS geometry)

Question

I am trying to compare two tables, that are suppose to be equal. One is a reference table (with the "correct" values) and the other is a test output. That is, they have same columns and columntypes. I then want to find the rows that are "identical". I figured that PostgreSQL's' Intersect (or maybe its opposite, Except) would work here, since it selects rows that appear in both tables.

This works fine if I give it rows that are completely the same:

select 2, ST_Polygon(st_geomfromtext('LINESTRING(0 0, 1 0, 1 1, 0 1, 0 0)'), 4326) 
intersect
select 2, ST_Polygon(st_geomfromtext('LINESTRING(0 0, 1 0, 1 1, 0 1, 0 0)'), 4326)

returns:

row 1: 2;0103000020E6100000010000000500000000000000000000000000000000000000000000000000F03F0000000000000000000000000000F03F000000000000F03F0000000000000000000000000000F03F00000000000000000000000000000000

However, if the start/end vertex is changed in the geometry column in one of the rows, it returns zero rows:

select 2, ST_Polygon(st_geomfromtext('LINESTRING(0 0, 1 0, 1 1, 0 1, 0 0)'), 4326) 
intersect
select 2, ST_Polygon(st_geomfromtext('LINESTRING( 1 0, 1 1, 0 1, 0 0, 1 0)'), 4326)

That is, if i used ST_Equals on the geometries, it would return true. But in Intersect, this is not true.

Is there a smarter way to compare entire tables?

Answer 1

You are mixing non-spatial functionality with spatial objects. PostgreSQL's intersect does not know about spatial. Stay in PostGIS for everything spatial. In other words, use the ST_ functions (Spatial Type) when you work with spatial objects.

For intersections there are ST_Intersects for a boolean condition and ST_Intersection for the geometric intersection.

SELECT ST_Intersects(
  ST_Polygon(ST_GeomFromText('LINESTRING(0 0, 1 0, 1 1, 0 1, 0 0)'), 4326),
  ST_Polygon(ST_GeomFromText('LINESTRING(1 0, 1 1, 0 1, 0 0, 1 0)'), 4326)
);

Answer 2

If you want to know if two geometry are geometrically equal. You should use St_equals which is the standard to get this information.

So let's say you have two table and you want to know if they are the same in a "geometric" way.

You could try something like this :

select count(t1.*) from table_1 as t1, table_2 as t2
where St_equals(t1.geom, t2.geom) and t1.id = t2.id

and then

select count(t1.*) from table_1 as t1

this will get you the number of records in your reference table, and then you can compare it to the number of identical geometric record in your tables. If you don't get the same number of row, then they are not "identical" as you state ...

hope this can help

Answer 3

You can make a regular join between the two tables. Use IS NOT DISTINCT FROM when comparing the other field values to properly handle any null.

SELECT t1.*
FROM t1 
    JOIN t2 ON (t1.id,t1.atr) IS NOT DISTINCT FROM (t2.id,t2.atr) 
            AND st_equals(t1.geom, t2.geom);

---

Test data:

create table t1 (id int, atr int, geom geometry(polygon,4326));

insert into t1 values (1,1, st_geomFromEWKT('srid=4326;polygon((0 0,0 1, 1 1, 1 0, 0 0))'));
insert into t1 values (2,2, st_geomFromEWKT('srid=4326;polygon((0 0,0 1, 10 10, 1 0, 0 0))'));
insert into t1 values (10,10, st_geomFromEWKT('srid=4326;polygon((10 10,10 10, 11 11, 11 10, 10 10))'));



create table t2 (id int, atr int, geom geometry(polygon,4326));

-- identical entry
insert into t2 values (1,1, st_geomFromEWKT('srid=4326;polygon((0 0,0 1, 1 1, 1 0, 0 0))'));
-- polygon starting/ending point has shifted by 1 vertex
insert into t2 values (2,2, st_geomFromEWKT('srid=4326;polygon((0 1, 10 10, 1 0, 0 0,0 1))'));
-- different attributes, same geometry
insert into t2 values (11,11, st_geomFromEWKT('srid=4326;polygon((10 10,10 10, 11 11, 11 10, 10 10))'));



SELECT t1.*
FROM t1 
    JOIN t2 ON (t1.id,t1.atr) IS NOT DISTINCT FROM (t2.id,t2.atr) 
            AND st_equals(t1.geom, t2.geom);

 id | atr |                                                                                                geom         
----+-----+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
  1 |   1 | 0103000020E61000000100000005000000000000000000000000000000000000000000000000000000000000000000F03F000000000000F03F000000000000F03F000000000000F03F000000000000000000000000000000000000000000000000
  2 |   2 | 0103000020E61000000100000005000000000000000000000000000000000000000000000000000000000000000000F03F00000000000024400000000000002440000000000000F03F000000000000000000000000000000000000000000000000
(2 rows)