SedonaDB is less likely to consider geography polygons that touch at boundaries as intersects with each other

[Kontinuation]

Here is a query that reproduces this problem:

SELECT ST_Intersects(
  ST_GeogFromText('POLYGON((61.210817 35.650072,60.803193 34.404102,60.52843 33.676446,60.9637 33.528832,60.536078 32.981269,60.863655 32.18292,60.941945 31.548075,61.699314 31.379506,61.781222 30.73585,60.874249 29.829239,62.549857 29.318573,63.550261 29.468331,64.148002 29.340819,64.350419 29.560031,65.046862 29.472181,66.346473 29.887943,66.381458 30.738899,66.938891 31.304911,67.683394 31.303154,67.792689 31.58293,68.556932 31.71331,68.926677 31.620189,69.317764 31.901412,69.262522 32.501944,69.687147 33.105499,70.323594 33.358533,69.930543 34.02012,70.881803 33.988856,71.156773 34.348911,71.115019 34.733126,71.613076 35.153203,71.498768 35.650563,71.262348 36.074388,71.846292 36.509942,72.920025 36.720007,74.067552 36.836176,74.575893 37.020841,75.158028 37.133031,74.980003 37.41999,73.948696 37.421566,73.260056 37.495257,72.63689 37.047558,72.193041 36.948288,71.844638 36.738171,71.448694 37.065645,71.541918 37.905774,71.239404 37.953265,71.348131 38.258905,70.806821 38.486282,70.376304 38.138396,70.270574 37.735165,70.116578 37.588223,69.518785 37.608997,69.196273 37.151144,68.859446 37.344336,68.135562 37.023115,67.83 37.144994,67.075782 37.356144,66.518607 37.362784,66.217385 37.39379,65.745631 37.661164,65.588948 37.305217,64.746105 37.111818,64.546479 36.312073,63.982896 36.007958,63.193538 35.857166,62.984662 35.404041,62.230652 35.270664,61.210817 35.650072))'),
  ST_GeogFromText('POLYGON((53.921598 37.198918,53.82579 36.965031,52.264025 36.700422,50.842354 36.872814,50.147771 37.374567,49.199612 37.582874,48.883249 38.320245,48.634375 38.270378,48.010744 38.794015,48.355529 39.288765,48.060095 39.582235,47.685079 39.508364,46.50572 38.770605,46.143623 38.741202,45.457722 38.874139,44.952688 39.335765,44.79399 39.713003,44.109225 39.428136,44.421403 38.281281,44.225756 37.971584,44.77267 37.17045,45.420618 35.977546,46.07634 35.677383,46.151788 35.093259,45.64846 34.748138,45.416691 33.967798,46.109362 33.017287,47.334662 32.469155,47.849204 31.709176,47.685286 30.984853,48.004698 30.985137,48.014568 30.452457,48.567971 29.926778,48.941333 30.31709,49.57685 29.985715,50.115009 30.147773,50.852948 28.814521,51.520763 27.86569,52.483598 27.580849,53.493097 26.812369,54.71509 26.480658,55.72371 26.964634,56.492139 27.143305,56.970766 26.966106,57.397251 25.739902,58.525761 25.609962,59.616134 25.380157,61.497363 25.078237,61.874188 26.239975,63.316632 26.756533,63.233898 27.217047,62.755426 27.378923,62.72783 28.259645,61.771868 28.699334,61.369309 29.303276,60.874249 29.829239,61.781222 30.73585,61.699314 31.379506,60.941945 31.548075,60.863655 32.18292,60.536078 32.981269,60.9637 33.528832,60.52843 33.676446,60.803193 34.404102,61.210817 35.650072,61.12307 36.491597,60.377638 36.527383,59.234762 37.412988,58.436154 37.52231,57.330434 38.029229,56.619366 38.121394,56.180375 37.935127,55.511578 37.964117,54.800304 37.392421,53.921598 37.198918))'));

On PostGIS:

postgres=# SELECT ST_Intersects(
postgres(#   ST_GeogFromText('POLYGON((61.210817 35.650072,60.803193 34.404102,60.52843 33.676446,60.9637 33.528832,60.536078 32.981269,60.863655 32.18292,60.941945 31.548075,61.699314 31.379506,61.781222 30.73585,60.874249 29.829239,62.549857 29.318573,63.550261 29.468331,64.148002 29.340819,64.350419 29.560031,65.046862 29.472181,66.346473 29.887943,66.381458 30.738899,66.938891 31.304911,67.683394 31.303154,67.792689 31.58293,68.556932 31.71331,68.926677 31.620189,69.317764 31.901412,69.262522 32.501944,69.687147 33.105499,70.323594 33.358533,69.930543 34.02012,70.881803 33.988856,71.156773 34.348911,71.115019 34.733126,71.613076 35.153203,71.498768 35.650563,71.262348 36.074388,71.846292 36.509942,72.920025 36.720007,74.067552 36.836176,74.575893 37.020841,75.158028 37.133031,74.980003 37.41999,73.948696 37.421566,73.260056 37.495257,72.63689 37.047558,72.193041 36.948288,71.844638 36.738171,71.448694 37.065645,71.541918 37.905774,71.239404 37.953265,71.348131 38.258905,70.806821 38.486282,70.376304 38.138396,70.270574 37.735165,70.116578 37.588223,69.518785 37.608997,69.196273 37.151144,68.859446 37.344336,68.135562 37.023115,67.83 37.144994,67.075782 37.356144,66.518607 37.362784,66.217385 37.39379,65.745631 37.661164,65.588948 37.305217,64.746105 37.111818,64.546479 36.312073,63.982896 36.007958,63.193538 35.857166,62.984662 35.404041,62.230652 35.270664,61.210817 35.650072))'),
postgres(#   ST_GeogFromText('POLYGON((53.921598 37.198918,53.82579 36.965031,52.264025 36.700422,50.842354 36.872814,50.147771 37.374567,49.199612 37.582874,48.883249 38.320245,48.634375 38.270378,48.010744 38.794015,48.355529 39.288765,48.060095 39.582235,47.685079 39.508364,46.50572 38.770605,46.143623 38.741202,45.457722 38.874139,44.952688 39.335765,44.79399 39.713003,44.109225 39.428136,44.421403 38.281281,44.225756 37.971584,44.77267 37.17045,45.420618 35.977546,46.07634 35.677383,46.151788 35.093259,45.64846 34.748138,45.416691 33.967798,46.109362 33.017287,47.334662 32.469155,47.849204 31.709176,47.685286 30.984853,48.004698 30.985137,48.014568 30.452457,48.567971 29.926778,48.941333 30.31709,49.57685 29.985715,50.115009 30.147773,50.852948 28.814521,51.520763 27.86569,52.483598 27.580849,53.493097 26.812369,54.71509 26.480658,55.72371 26.964634,56.492139 27.143305,56.970766 26.966106,57.397251 25.739902,58.525761 25.609962,59.616134 25.380157,61.497363 25.078237,61.874188 26.239975,63.316632 26.756533,63.233898 27.217047,62.755426 27.378923,62.72783 28.259645,61.771868 28.699334,61.369309 29.303276,60.874249 29.829239,61.781222 30.73585,61.699314 31.379506,60.941945 31.548075,60.863655 32.18292,60.536078 32.981269,60.9637 33.528832,60.52843 33.676446,60.803193 34.404102,61.210817 35.650072,61.12307 36.491597,60.377638 36.527383,59.234762 37.412988,58.436154 37.52231,57.330434 38.029229,56.619366 38.121394,56.180375 37.935127,55.511578 37.964117,54.800304 37.392421,53.921598 37.198918))'));
 st_intersects 
---------------
 t
(1 row)

On sedona-cli:

> SELECT ST_Intersects(
  ST_GeogFromText('POLYGON((61.210817 35.650072,60.803193 34.404102,60.52843 33.676446,60.9637 33.528832,60.536078 32.981269,60.863655 32.18292,60.941945 31.548075,61.699314 31.379506,61.781222 30.73585,60.874249 29.829239,62.549857 29.318573,63.550261 29.468331,64.148002 29.340819,64.350419 29.560031,65.046862 29.472181,66.346473 29.887943,66.381458 30.738899,66.938891 31.304911,67.683394 31.303154,67.792689 31.58293,68.556932 31.71331,68.926677 31.620189,69.317764 31.901412,69.262522 32.501944,69.687147 33.105499,70.323594 33.358533,69.930543 34.02012,70.881803 33.988856,71.156773 34.348911,71.115019 34.733126,71.613076 35.153203,71.498768 35.650563,71.262348 36.074388,71.846292 36.509942,72.920025 36.720007,74.067552 36.836176,74.575893 37.020841,75.158028 37.133031,74.980003 37.41999,73.948696 37.421566,73.260056 37.495257,72.63689 37.047558,72.193041 36.948288,71.844638 36.738171,71.448694 37.065645,71.541918 37.905774,71.239404 37.953265,71.348131 38.258905,70.806821 38.486282,70.376304 38.138396,70.270574 37.735165,70.116578 37.588223,69.518785 37.608997,69.196273 37.151144,68.859446 37.344336,68.135562 37.023115,67.83 37.144994,67.075782 37.356144,66.518607 37.362784,66.217385 37.39379,65.745631 37.661164,65.588948 37.305217,64.746105 37.111818,64.546479 36.312073,63.982896 36.007958,63.193538 35.857166,62.984662 35.404041,62.230652 35.270664,61.210817 35.650072))'),
  ST_GeogFromText('POLYGON((53.921598 37.198918,53.82579 36.965031,52.264025 36.700422,50.842354 36.872814,50.147771 37.374567,49.199612 37.582874,48.883249 38.320245,48.634375 38.270378,48.010744 38.794015,48.355529 39.288765,48.060095 39.582235,47.685079 39.508364,46.50572 38.770605,46.143623 38.741202,45.457722 38.874139,44.952688 39.335765,44.79399 39.713003,44.109225 39.428136,44.421403 38.281281,44.225756 37.971584,44.77267 37.17045,45.420618 35.977546,46.07634 35.677383,46.151788 35.093259,45.64846 34.748138,45.416691 33.967798,46.109362 33.017287,47.334662 32.469155,47.849204 31.709176,47.685286 30.984853,48.004698 30.985137,48.014568 30.452457,48.567971 29.926778,48.941333 30.31709,49.57685 29.985715,50.115009 30.147773,50.852948 28.814521,51.520763 27.86569,52.483598 27.580849,53.493097 26.812369,54.71509 26.480658,55.72371 26.964634,56.492139 27.143305,56.970766 26.966106,57.397251 25.739902,58.525761 25.609962,59.616134 25.380157,61.497363 25.078237,61.874188 26.239975,63.316632 26.756533,63.233898 27.217047,62.755426 27.378923,62.72783 28.259645,61.771868 28.699334,61.369309 29.303276,60.874249 29.829239,61.781222 30.73585,61.699314 31.379506,60.941945 31.548075,60.863655 32.18292,60.536078 32.981269,60.9637 33.528832,60.52843 33.676446,60.803193 34.404102,61.210817 35.650072,61.12307 36.491597,60.377638 36.527383,59.234762 37.412988,58.436154 37.52231,57.330434 38.029229,56.619366 38.121394,56.180375 37.935127,55.511578 37.964117,54.800304 37.392421,53.921598 37.198918))'));
┌───────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┐
│ st_intersects(st_geogfromwkt(Utf8("POLYGON((61.210817 35.650072,60.803193 34.404102,60.52843 33.676446,60.9637 33.528832,60.536078 32.981269,60.863655 32.18292,60.941945 31.548075,61.699314 31.379506,61.781222 30.73585,60.874249 29.829239,62.549857 29.318573,63.550 │
│ 261 29.468331,64.148002 29.340819,64.350419 29.560031,65.046862 29.472181,66.346473 29.887943,66.381458 30.738899,66.938891 31.304911,67.683394 31.303154,67.792689 31.58293,68.556932 31.71331,68.926677 31.620189,69.317764 31.901412,69.262522 32.501944,69.687147 33… │
╞═══════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════╡
│ false                                                                                                                                                                                                                                                                     │
└───────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┘

Visualization of the polygons:

[paleolimbot]

Yes, this is a configuration setting (polyline/polygon model) whose default should be changed in s2geography.

https://github.com/google/s2geometry/blob/5b5fbc04c773641a4a4e8d45ac035b85c8c47f24/src/s2/s2boolean_operation.h#L108-L154

[paleolimbot]

Failing test we could consider when this is resolved:

@pytest.mark.xfail(reason="https://github.com/apache/sedona-db/issues/63")
def test_spatial_join_geography(geoarrow_data):
    eng_sedonadb = SedonaDB.create_or_skip()
    eng_postgis = PostGIS.create_or_skip()

    eng_sedonadb.create_table_parquet(
        "sjoin_geog_all",
        geoarrow_data
        / "natural-earth/files/natural-earth_countries-geography_geo.parquet",
    )
    test_data = eng_sedonadb.execute_and_collect(
        "SELECT * FROM sjoin_geog_all LIMIT 100"
    )
    eng_sedonadb.create_table_arrow("sjoin_geog", test_data)
    eng_postgis.create_table_arrow("sjoin_geog", test_data)

    sql = """
           SELECT a.name a_name, a.continent a_continent, b.name b_name, b.continent b_continent
           FROM sjoin_geog a, sjoin_geog b
           WHERE ST_Intersects(a.geometry, b.geometry)
           ORDER BY a_name, a_continent, b_name, b_continent
           """

    sedonadb_results = eng_sedonadb.execute_and_collect(sql).to_pandas()
    assert len(sedonadb_results) > 0
    eng_postgis.assert_query_result(sql, sedonadb_results)