Spatial join issues

[Paul Ramsey]

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