I have a two tables.
1 table have srid=4326
2 table have srid=70066

I want to compare geometry columns of this tables. I find function ST_Equals(geometry, geometry) but its not what i want. I need something what can compare geometries which slightly different from each other like on picture.

enter image description here

i have test polygon in tables with area=9098.79m^2 in first table and with area=9721.82m^2 in sacond table. I try this query

SELECT   st_area(ST_Intersection(ST_TRANSFORM(filedata.the_geom,70066),filedata_temp.the_geom))
FROM filedata, filedata_temp
Where filedata.num=1

srid=70066 is rectangular coordinate system

and i get query result 8910.5625 why so much?

  • 1
    ST_Intersection gives you the common area between the two geometries. As bigger is the result as bigger is the coincidence. Your condition to select the "equals geometries" should looks like: st_area(OF_THE_INTERSECION)/st_area(the_geom1) > threshold Commented Aug 7, 2012 at 10:36
  • Ok i gonna use function ST_Difference().
    – Kliver Max
    Commented Aug 7, 2012 at 10:55

1 Answer 1


I think you will have to decide on certain criteria that define equality for you and that are likely to occur in your application. It is very hard to define likeness for all cases that are "visually" similar. For example: Is a clockwise circle the same as a counterclockwise circle in the same location? Is a circle the same as its approximation with straight lines?

Simple solutions in the above case could be to compare the area of their intersection or bounding boxes within an error range. That will at least ensure that the polygons are roughly in the same location and have roughly the same size. Make sure you evaluate test cases on both sides of the threshold of what you expect to be equal and unequal.

A basic query could look like select ... where st_area(st_intersection(geom1, st_transform(geom2, srid1))) < threshold (not syntax checked)

  • My english is tarrible but how i understand i gonna deduct geometries. But for example i get this result 2.82489490928128e-005 what is it?
    – Kliver Max
    Commented Aug 7, 2012 at 9:09
  • 3
    the threshold can be better represented as an area independent fraction by dividing by st_area(geom1), which will help how to interpret a good cut-off, like 0.95 means 95% area intersect
    – Mike T
    Commented Aug 7, 2012 at 9:25

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.