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