# How to compare geometry in PostGiS?

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

• 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 – Francisco Puga Aug 7 '12 at 10:36
• Ok i gonna use function ST_Difference(). – Kliver Max Aug 7 '12 at 10:55

## 1 Answer

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 Aug 7 '12 at 9:09
• 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 Aug 7 '12 at 9:25