Spatial join issues

[Shaozhong SHI]

Basically, is these a robust way to test approximate equal when spatial
fidelity exists.

On Wed, 29 Oct 2025, 17:39 Shaozhong SHI, <shishaozhong at gmail.com> 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, <pramsey at cleverelephant.ca> 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 <pramsey at cleverelephant.ca>
>> 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 <shishaozhong at gmail.com>
>>> 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 <gdt at lexort.com> wrote:
>>>>
>>>>> Shaozhong SHI <shishaozhong at gmail.com> 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 :-)
>>>>>
>>>>>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20251029/62e4323e/attachment.htm>