5

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?

  • 1
    Are you sure you have records that meet the clause? With this query geometry must be strictly identical at node level. Even if your two table comes from the same initial dataset, in real life different processing can lead to micro variation on floating point values, and ST_Equals by design will return 'false' even if visually the features seems to overlap. – MarHoff Oct 22 '15 at 12:38
  • 1
    table1.the:geom? – alphabetasoup Oct 23 '15 at 20:10
2

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!

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