Spatial join issues

[Paul Ramsey]

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