I have two tables with the same street network as geometry (but not a same id).
What I want to do is to join both tables with all attributes to one for all geometries which overlap exactly.
Unfortunately my query just gives me emtpy lines back. I am working with left join, but all other joins won't work as well.
DROP TABLE IF EXISTS test;
CREATE TABLE test AS
SELECT *
FROM table1
RIGHT JOIN table2 ON ST_Equals(table1.the:geom, table2.the_geom);
Does anybody know an answer for this?
Best Answer
I agree with MarHoff's comment. I think the following condition does the job:
'the geometry in table1 is completely inside a small buffer around the geometry in table2 AND the geometry in table2 is completely inside a small buffer around the geometry in table1'.
Here 0.1 is the buffer radius in meters / feet / degrees / whatever, depending on your coordinate reference system. If your CRS is in degrees (GPS coordinates), you probably should use a value that is smaller than 0.1.
This solution might take some computation time if your tables are very large. Another solution would be to use st_snaptogrid in order to simplify both geometries before comparing them.
Hope this helps!