[GIS] Join two tables completely on geometry (postgis)

postgispostgresql

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'.

SELECT * FROM table1 INNER JOIN table2 ON
st_within(table1.the_geom, st_buffer(table2.the_geom, 0.1)) AND 
st_within(table2.the_geom, st_buffer(table1.the_geom, 0.1));

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!

Related Question