Spatial join issues

[Shaozhong SHI]

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