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 :-) >>>> >>>>
