Re: Spatial join issues

--000000000000b01cb106424f4db9
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

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=E2=80=AFAM Shaozhong SHI <shishaozhong@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 <[email protected]> wrote:
>
>> Shaozhong SHI <[email protected]> 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 problem=
s?
>> > 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 :-)
>>
>>

--000000000000b01cb106424f4db9
Content-Type: text/html; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

<div dir=3D"ltr">Greg isn&#39;t passing judgement on how hard your problem =
is, he&#39;s saying you haven&#39;t explained it particularly well. Picture=
s help. Taking a guess at what you mean, here&#39;s some SQL that creates t=
wo polygons, with slightly different structure, and slightly different coor=
dinates, that describe the same general space in the universe, and then mas=
sages them until they pass an equals test.<div><br></div><div>WITH p AS (<b=
r>=C2=A0 SELECT &#39;POLYGON((0 0, 10 0, 10 10, 0 10, 0 0))&#39;::geometry =
AS p1,<br>=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0&#39;POLYGON((0 0, 10 0, 10 5, =
10 10, 0 10, 0 0))&#39;::geometry AS p2<br>),<br>shifted AS (<br>=C2=A0 SEL=
ECT p1, ST_Translate(p2, 0.0001, 0.0001) AS p2<br>=C2=A0 FROM p<br>),<br>rp=
 AS (<br>=C2=A0 SELECT ST_ReducePrecision(p1,0.1) AS p1,<br>=C2=A0 =C2=A0 =
=C2=A0 =C2=A0 =C2=A0ST_ReducePrecision(p2,0.1) AS p2<br>=C2=A0 FROM shifted=
<br>),<br>snap AS (<br>=C2=A0 SELECT ST_Snap(p1,p2,0.1) AS p1, <br>=C2=A0 =
=C2=A0 =C2=A0 =C2=A0 =C2=A0ST_Snap(p2,p1,0.1) AS p2<br>=C2=A0 FROM rp<br>)<=
br>SELECT ST_AsText(shifted.p1) AS p1_orig, <br>=C2=A0 =C2=A0 =C2=A0 =C2=A0=
ST_AsText(shifted.p2) AS p2_orig, <br>=C2=A0 =C2=A0 =C2=A0 =C2=A0ST_AsText(=
snap.p1) AS p1_snap, <br>=C2=A0 =C2=A0 =C2=A0 =C2=A0ST_AsText(snap.p2) AS p=
2_snap,<br>=C2=A0 =C2=A0 =C2=A0 =C2=A0ST_Equals(snap.p1, snap.p2)<br>FROM s=
nap, shifted;<br></div><div><br></div><div>-[ RECORD 1 ]-------------------=
---------------------------------------------------------------------------=
----<br>p1_orig =C2=A0 | POLYGON((0 0,10 0,10 10,0 10,0 0))<br>p2_orig =C2=
=A0 | 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))<br>p1_snap =C2=A0 | POLYGON((0 10,10 10,10 5=
,10 0,0 0,0 10))<br>p2_snap =C2=A0 | POLYGON((0 10,10 10,10 5,10 0,0 0,0 10=
))<br>st_equals | t<br></div></div><br><div class=3D"gmail_quote gmail_quot=
e_container"><div dir=3D"ltr" class=3D"gmail_attr">On Wed, Oct 29, 2025 at =
10:00=E2=80=AFAM Shaozhong SHI &lt;<a href=3D"mailto:[email protected]=
">[email protected]</a>&gt; wrote:<br></div><blockquote class=3D"gmail=
_quote" style=3D"margin:0px 0px 0px 0.8ex;border-left-width:1px;border-left=
-style:solid;border-left-color:rgb(204,204,204);padding-left:1ex"><div dir=
=3D"ltr">This is very challenging.=C2=A0 Take my words for it.=C2=A0 Try on=
 any polygons you created and modified.</div><br><div class=3D"gmail_quote"=
><div dir=3D"ltr" class=3D"gmail_attr">On Wed, 29 Oct 2025 at 14:36, Greg T=
roxel &lt;<a href=3D"mailto:[email protected]" target=3D"_blank">[email protected]=
m</a>&gt; wrote:<br></div><blockquote class=3D"gmail_quote" style=3D"margin=
:0px 0px 0px 0.8ex;border-left-width:1px;border-left-style:solid;border-lef=
t-color:rgb(204,204,204);padding-left:1ex">Shaozhong SHI &lt;<a href=3D"mai=
lto:[email protected]" target=3D"_blank">[email protected]</a>&gt=
; writes:<br>
<br>
&gt;=C2=A0 =C2=A0Visually, there appears some matching polygons.=C2=A0 Even=
 if two geometries<br>
&gt; represent the same shape visually, they might not be considered equal =
due<br>
&gt; to tiny differences in precision or metadata.=C2=A0 Have you encounter=
ed<br>
&gt; problems of failure of spatial join?=C2=A0 How did you overcome the pr=
oblems?<br>
&gt; Regards, David<br>
<br>
Could you post your example polygons, and the queries you are using?<br>
Your question is much too open ended.=C2=A0 It even sounds like it might be=
 a<br>
request for help with GIS homework, but it&#39;s hard to tell :-)<br>
<br>
</blockquote></div>
</blockquote></div>

--000000000000b01cb106424f4db9--