1

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?

  • I don't think you can get around writing out columns, with a normalized geometry representation; either in an INNER JOIN on all columns AND ST_Equals, or a table intersection with subqueries returning e.g. ST_Envelope or ST_AsGeoHash or sth similar. – geozelot Jul 29 '19 at 12:35
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)
);
| improve this answer | |
  • 1
    I dont know if i should rephrase the question, but i am not looking for a way to check the spatial intersection of the geometry. i am looking for a way to compare an entire table's rows with another table's rows. I was looking for a simple method, such as postgres' intersect which behave like postgis on geometry types. – Jonas Olsen Jul 29 '19 at 6:09
  • You should definitely clarify the question then. Especially on what you consider equal. – bugmenot123 Jul 29 '19 at 7:49
1

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)
| improve this answer | |
0

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

| improve this answer | |

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.