It's quite robust, but of course it has a big magic number in the middle, in that you have to choose what area ratio value you will use as your "they are the same, as far as I am concerned" threshold.
P. On Wed, Oct 29, 2025 at 10:58 AM Shaozhong SHI <[email protected]> wrote: > Basically, is these a robust way to test approximate equal when spatial > fidelity exists. > > On Wed, 29 Oct 2025, 17:39 Shaozhong SHI, <[email protected]> wrote: > >> Very clever. I will spend time to understand. Approximate equal sounds >> very interesting. I just did intersection. Expecting a polygon but got >> partial lines. Suspect only part of lines of two seemingly the same >> polygons intersect. Thanks. >> >> On Wed, 29 Oct 2025, 17:25 Paul Ramsey, <[email protected]> >> wrote: >> >>> I would imagine that my little example is a bit error prone, but the >>> classic solution to "do these differently structured things with slightly >>> different coordinates cover basically the same space" is to inspect the >>> ratio "area(intersection(a, b)) / area(union(a, b))". You can see >>> intuitively how the more the same two polygons are, the closer that ratio >>> will be to 1.0. >>> >>> WITH p AS ( >>> SELECT 'POLYGON((0 0, 10 0, 10 10, 0 10, 0 0))'::geometry AS p1, >>> 'POLYGON((0 0, 10 0, 10 5, 10 10, 0 10, 0 0))'::geometry AS p2, >>> 0.001 AS shift >>> ), >>> shifted AS ( >>> SELECT p1, ST_Translate(p2, shift, shift) AS p2, shift >>> FROM p >>> ), >>> areas AS ( >>> SELECT ST_Area(ST_Union(p1, p2, shift/10)) AS area_union, >>> ST_Area(ST_Intersection(p1, p2, shift/10)) AS area_inter >>> FROM shifted >>> ) >>> SELECT ST_AsText(shifted.p1) AS p1_orig, >>> ST_AsText(shifted.p2) AS p2_orig, >>> area_inter/area_union AS ratio >>> FROM areas, shifted; >>> >>> On Wed, Oct 29, 2025 at 10:17 AM Paul Ramsey <[email protected]> >>> wrote: >>> >>>> Greg isn't passing judgement on how hard your problem is, he's saying >>>> you haven't explained it particularly well. Pictures help. Taking a guess >>>> at what you mean, here's some SQL that creates two polygons, with slightly >>>> different structure, and slightly different coordinates, that describe the >>>> same general space in the universe, and then massages them until they pass >>>> an equals test. >>>> >>>> WITH p AS ( >>>> SELECT 'POLYGON((0 0, 10 0, 10 10, 0 10, 0 0))'::geometry AS p1, >>>> 'POLYGON((0 0, 10 0, 10 5, 10 10, 0 10, 0 0))'::geometry AS p2 >>>> ), >>>> shifted AS ( >>>> SELECT p1, ST_Translate(p2, 0.0001, 0.0001) AS p2 >>>> FROM p >>>> ), >>>> rp AS ( >>>> SELECT ST_ReducePrecision(p1,0.1) AS p1, >>>> ST_ReducePrecision(p2,0.1) AS p2 >>>> FROM shifted >>>> ), >>>> snap AS ( >>>> SELECT ST_Snap(p1,p2,0.1) AS p1, >>>> ST_Snap(p2,p1,0.1) AS p2 >>>> FROM rp >>>> ) >>>> SELECT ST_AsText(shifted.p1) AS p1_orig, >>>> ST_AsText(shifted.p2) AS p2_orig, >>>> ST_AsText(snap.p1) AS p1_snap, >>>> ST_AsText(snap.p2) AS p2_snap, >>>> ST_Equals(snap.p1, snap.p2) >>>> FROM snap, shifted; >>>> >>>> -[ RECORD 1 >>>> ]-------------------------------------------------------------------------------------------------- >>>> p1_orig | POLYGON((0 0,10 0,10 10,0 10,0 0)) >>>> p2_orig | POLYGON((0.0001 0.0001,10.0001 0.0001,10.0001 >>>> 5.0001,10.0001 10.0001,0.0001 10.0001,0.0001 0.0001)) >>>> p1_snap | POLYGON((0 10,10 10,10 5,10 0,0 0,0 10)) >>>> p2_snap | POLYGON((0 10,10 10,10 5,10 0,0 0,0 10)) >>>> st_equals | t >>>> >>>> On Wed, Oct 29, 2025 at 10:00 AM Shaozhong SHI <[email protected]> >>>> wrote: >>>> >>>>> This is very challenging. Take my words for it. Try on any polygons >>>>> you created and modified. >>>>> >>>>> On Wed, 29 Oct 2025 at 14:36, Greg Troxel <[email protected]> wrote: >>>>> >>>>>> Shaozhong SHI <[email protected]> writes: >>>>>> >>>>>> > Visually, there appears some matching polygons. Even if two >>>>>> geometries >>>>>> > represent the same shape visually, they might not be considered >>>>>> equal due >>>>>> > to tiny differences in precision or metadata. Have you encountered >>>>>> > problems of failure of spatial join? How did you overcome the >>>>>> problems? >>>>>> > Regards, David >>>>>> >>>>>> Could you post your example polygons, and the queries you are using? >>>>>> Your question is much too open ended. It even sounds like it might >>>>>> be a >>>>>> request for help with GIS homework, but it's hard to tell :-) >>>>>> >>>>>>
