Spatial join issues

[Paul Ramsey]

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 <shishaozhong at gmail.com>
wrote:

> 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/ba843278/attachment.htm>