[I] ST_Covers and ST_Contains output does not match DuckDB or Shapely [sedona-db]

["utkarshkukreti (via GitHub)" <gi...@apache.org>]

utkarshkukreti opened a new issue, #709:
URL: https://github.com/apache/sedona-db/issues/709

   Hi,
   
   I was porting some DuckDB and Shapely code to SedonaDB to see if I could get better performance. While it does run faster, I found that the output of `ST_Covers` was not identical to PostGIS, DuckDB and Shapely in some cases.
   
   I narrowed it down to the case when the child geometry is identical to a hole of the parent geometry.
   
   Here's the smallest reproduction I could create:
   
   ```
   # /// script
   # requires-python = ">=3.14"
   # dependencies = [
   #     "apache-sedona[db]>=1.8.1",
   #     "duckdb>=1.5.0",
   #     "shapely>=2.1.2",
   # ]
   # ///
   
   import duckdb
   import sedona.db
   import shapely
   
   parent_wkt = "POLYGON ((0 0, 3 0, 3 3, 0 3, 0 0), (1 1, 1 2, 2 1, 1 1))"
   child_wkt = "POLYGON ((1 1, 1 2, 2 1, 1 1))"
   
   duckdb_connection = duckdb.connect()
   duckdb_connection.execute("install spatial")
   duckdb_connection.execute("load spatial")
   
   sedona_connection = sedona.db.connect()
   
   parent_geometry = shapely.from_wkt(parent_wkt)
   child_geometry = shapely.from_wkt(child_wkt)
   
   for predicate, shapely_value in [
       ("ST_Intersects", shapely.intersects(parent_geometry, child_geometry)),
       ("ST_Covers", shapely.covers(parent_geometry, child_geometry)),
       ("ST_Contains", shapely.contains(parent_geometry, child_geometry)),
   ]:
       duckdb_value = duckdb_connection.execute(
           f"select {predicate}(ST_GeomFromText('{parent_wkt}'), ST_GeomFromText('{child_wkt}'))"
       ).fetchone()[0]
       sedona_value = sedona_connection.sql(
           f"select {predicate}(ST_GeomFromWKT('{parent_wkt}'), ST_GeomFromWKT('{child_wkt}')) as value"
       ).to_pandas()["value"][0]
       print(
           f"{predicate} duckdb={duckdb_value} sedona={sedona_value} shapely={shapely_value}"
       )
   ```
   
   Output:
   
   ```
   ST_Intersects duckdb=True sedona=True shapely=True
   ST_Covers duckdb=False sedona=True shapely=False
   ST_Contains duckdb=False sedona=True shapely=False
   ```
   
   Postgis:
   
   ```
   postgres=# SELECT
     ST_Intersects(parent, child) AS intersects,
     ST_Covers(parent, child) AS covers,
     ST_Contains(parent, child) AS contains
   FROM (
     SELECT
       'POLYGON ((0 0, 3 0, 3 3, 0 3, 0 0), (1 1, 1 2, 2 1, 1 1))'::geometry AS parent,
       'POLYGON ((1 1, 1 2, 2 1, 1 1))'::geometry AS child
   ) t;
    intersects | covers | contains
   ------------+--------+----------
    t          | f      | f
   (1 row)
   ```
   
   `ST_Covers` and `ST_Contains` return true with SedonaDB but false with the other 3.


-- 
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.

To unsubscribe, e-mail: issues-unsubscribe@sedona.apache.org.apache.org

For queries about this service, please contact Infrastructure at:
users@infra.apache.org

Re: [I] ST_Covers and ST_Contains output does not match DuckDB or Shapely [sedona-db]

["paleolimbot (via GitHub)" <gi...@apache.org>]

paleolimbot commented on issue #709:
URL: https://github.com/apache/sedona-db/issues/709#issuecomment-4744788103

   I am sorry I couldn't get here before 0.4.0...I'll make these upstream updates before our next release.


-- 
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.

To unsubscribe, e-mail: issues-unsubscribe@sedona.apache.org

For queries about this service, please contact Infrastructure at:
users@infra.apache.org

Re: [I] ST_Covers and ST_Contains output does not match DuckDB or Shapely [sedona-db]

["paleolimbot (via GitHub)" <gi...@apache.org>]

paleolimbot closed issue #709: ST_Covers and ST_Contains output does not match DuckDB or Shapely
URL: https://github.com/apache/sedona-db/issues/709


-- 
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.

To unsubscribe, e-mail: issues-unsubscribe@sedona.apache.org

For queries about this service, please contact Infrastructure at:
users@infra.apache.org

Re: [I] ST_Covers and ST_Contains output does not match DuckDB or Shapely [sedona-db]

["paleolimbot (via GitHub)" <gi...@apache.org>]

paleolimbot commented on issue #709:
URL: https://github.com/apache/sedona-db/issues/709#issuecomment-4060655697

   Thanks for opening! There's one other case we know about too: Contains(`GEOMETRYCOLLECTION (POINT (0 0), LINESTRING (0 0, 0 1))`, `POINT (0 0)`) should evaluate to false, but actually evaluates to true ( https://github.com/tidwall/tg/issues/20 ).
   
   We use tg to evaluate predicates because it's faster and because it has a thread-safe prepared geometry (the same reason why DuckDB's DWithin is faster). I'll add this one to that issue and see if we can fix upstream.


-- 
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.

To unsubscribe, e-mail: issues-unsubscribe@sedona.apache.org

For queries about this service, please contact Infrastructure at:
users@infra.apache.org