jiayuasu opened a new issue, #1085: URL: https://github.com/apache/sedona-db/issues/1085
## Description One polygon out of 156 095 in the [Overture divisions](https://huggingface.co/datasets/apache-sedona/spatialbench) dataset comes out **inverted** when read with `ST_GeogFromWKB` / `ST_GeogFromWKT`: `ST_Area` returns a negative value and the polygon behaves as though its interior is the rest of the globe, so `ST_Within` / `ST_Intersects` match points anywhere on Earth. Read as a geometry the same ring is unremarkable: a single-ring `ST_Polygon`, 258 points, area 1.0014 deg², envelope 32.69–34.41 °E / 15.68–17.06 °N (Shendi, Sudan; GERS id `f6d8044a-ea25-4b22-a8d7-bf8738058dd3`). Its geography **perimeter** is computed correctly (574 613 m). ## Observations | Expression | Result | |---|---| | `ST_Area(ST_GeogFromWKB(boundary))` | **−11 876 076 760.37** | | `ST_Perimeter(ST_GeogFromWKB(boundary))` | 574 613.63 (correct) | | `ST_Area(ST_GeomFromWKB(boundary))` | 1.0014 (deg², correct) | | `ST_Within(ST_GeogFromWKT('POINT (-150 0)'), geog)` | **true** — a point in the middle of the Pacific | | `ST_Within(ST_GeomFromWKT('POINT (-150 0)'), geom)` | false (correct) | | `ST_Area(ST_GeogFromWKB(ST_AsBinary(ST_Reverse(geom))))` | −11 876 076 760.37 (unchanged) | | `ST_Area(ST_GeogFromWKB(ST_AsBinary(ST_Normalize(geom))))` | −11 876 076 760.37 (unchanged) | Neither `ST_Reverse` nor `ST_Normalize` changes the outcome. ## Simplifying the same ring flips the sign back The magnitude is right — only the sign is wrong — and it depends on the ring's fine detail: | Input | Points | Geography area (m²) | |---|---|---| | original ring | 258 | **−11 876 076 760** | | `ST_Simplify(geom, 0.05)` | 13 | +11 809 095 115 | | `ST_Simplify(geom, 0.2)` | 6 | +11 408 089 105 | | `ST_Envelope(geom)` | 5 | +27 946 637 939 | So ~11.8 × 10⁹ m² (≈ 11 876 km²) is the correct area, and the full-precision ring is the only version that inverts. That points at orientation/interior determination being confused by something in the detailed ring — a near-duplicate vertex or a very small self-intersection — rather than at the winding order as such. The 6-point simplification, which behaves correctly, is: ```sql SELECT ST_Area(ST_GeogFromWKT('POLYGON((33.71683365600006 17.059304755000028,32.69311523400006 16.334899902000075,34.21551513700007 16.053710937000062,34.38067615800003 15.683288987000026,34.32468025600008 16.632472731000064,33.71683365600006 17.059304755000028))')); -- 11408089104.759718 ``` ## Reproduction ```python import sedonadb sd = sedonadb.connect() wkt = open("shendi.wkt").read() # full 258-point ring, attached below sd.sql(f"SELECT ST_Area(ST_GeogFromWKT('{wkt}')) AS geog_area, " f"ST_Within(ST_GeogFromWKT('POINT (-150 0)'), ST_GeogFromWKT('{wkt}')) AS contains_pacific_point" ).to_pandas() # geog_area = -11876076760.37291, contains_pacific_point = True ``` Or from the published dataset: ```python sd.read_parquet("<spatialbench>/v0.1.0/sf1/zone/*.parquet").to_view("zone") sd.sql("SELECT ST_Area(ST_GeogFromWKB(z_boundary)) FROM zone WHERE z_zonekey = 150480").to_pandas() ``` Only 1 of the 156 095 zones is affected; the other 156 094 return positive areas, and the largest values are correct (Alaska's Unorganized Borough = 8.39 × 10¹¹ m²). ## Impact Because the inverted polygon matches points anywhere on Earth, it silently corrupts every spatial join against this table. In the geography version of SpatialBench at SF1: - Q4 (zone distribution of the top 1 000 tipped trips) attributes **all 1 000** trips to this one Sudanese locality. Removing its row leaves 258 rows that are identical to the planar answer — same zones, same counts, same order — so this polygon is the sole source of divergence. - Q6 reports **5 999 980** pickups for it, essentially every trip in the dataset, for a county that does not intersect the query's bounding box at all. - Q11 (cross-zone trip count) returns **6 290 682** against the planar answer's 176 391, because nearly every trip now also falls inside this polygon and so appears to cross a zone boundary. A wrong-signed area is recoverable by taking the absolute value, but a predicate that returns true for the whole planet is not detectable downstream. ## Environment - `sedonadb` 0.4.0 (PyPI wheel), Python 3.13, macOS arm64 - `sedonadb.__features__ == ['s2geography']` <details> <summary>Full 258-point WKT</summary> ``` POLYGON((33.71683365600006 17.059304755000028,33.70164088900003 17.058765779000055,33.707677594000074 17.048276114000032,33.720780138000066 17.006884205000063,33.72402909400006 16.995050366000044,33.72224787600004 16.985606362000055,33.72002465700007 16.974360740000066,33.71677282500008 16.970340393000072,33.71568646900005 16.967352927000036,33.70333946900007 16.944160553000074,33.690695711000046 16.93151679500005,33.68732784100007 16.927845826000066,33.687429774000066 16.91161001000006,33.684582066000075 16.907252705000076,33.672731835000036 16.889898430000073,33.65651640700003 16.88429067800007,33.64371135700003 16.872460338000053,33.64013792700007 16.847625626000024,33.63853415700004 16.83487255400007,33.611837843000046 16.804839178000066,33.60906876700005 16.80154906300004,33.60349528200004 16.792492115000073,33.58965692800007 16.77743871900003,33.585383163000074 16.773406626000053,33.57605665600005 16.765029741000035,33.571040432000075 16.762187237000035,33.565975854000044 16 .758894932000032,33.56043115600005 16.756175365000047,33.544845907000024 16.753058303000046,33.534407058000056 16.75264527400003,33.519587546000025 16.74971146300004,33.50305040600006 16.74510617800007,33.489841590000026 16.748998361000076,33.484696719000056 16.74610730300003,33.47478954400003 16.74492789100003,33.47068115600007 16.744438803000037,33.464674468000055 16.74210286500005,33.455154 16.734508048000066,33.43608259400003 16.707955817000027,33.43103802100006 16.70383650200006,33.42745834500005 16.701918939000052,33.42042534300003 16.69738655200007,33.415428838000025 16.69731192200004,33.40633565100006 16.69798804800007,33.38312634300007 16.698978865000072,33.37266817400007 16.694403688000023,33.36175707500007 16.689453250000042,33.355770577000044 16.687011427000073,33.34734055800004 16.683625471000028,33.344207406000066 16.682703616000026,33.33520143100003 16.682521933000032,33.32618740600003 16.681702490000077,33.30041053800005 16.667437619000054,33.292818338000075 16.66316 3215000054,33.27723324900006 16.66176993700003,33.25859290400007 16.659662749000063,33.24423644900003 16.647269263000055,33.23141546800008 16.63531761400003,33.20505290500006 16.61462799000003,33.190036218000046 16.612625801000036,33.18453382900003 16.608590711000033,33.17969502100004 16.60387599300003,33.17501953100003 16.583927240000037,33.16994952300007 16.579586179000046,33.152607551000074 16.564644077000025,33.13667624100003 16.54762756300005,33.12562115500003 16.532324551000045,33.10426185600005 16.52117587400005,33.090749396000035 16.515262033000056,33.082409968000036 16.51206630200005,33.07462913400008 16.511725029000047,33.05968920500004 16.51027454900003,33.05616353000005 16.510915115000046,33.05056360900005 16.51740385100004,33.041705068000056 16.526895058000036,33.01527227300005 16.540865573000076,33.00218521800008 16.546471489000055,32.98749097700005 16.54873213700006,32.97721653000008 16.550312865000024,32.96415596800006 16.543398427000056,32.95588412800004 16.54261741 8000077,32.94892721900004 16.541454115000022,32.93881660900007 16.53800728500005,32.925635690000036 16.53193712600006,32.91893840500006 16.527997552000045,32.910095530000035 16.525690740000073,32.906132902000024 16.523083746000054,32.89418053000003 16.510151677000067,32.87113140400004 16.506216490000043,32.844147030000045 16.486540365000053,32.82896834200005 16.473048177000067,32.81266528000003 16.462085802000047,32.79861090500003 16.45168555200007,32.79327028000006 16.445782739000038,32.77331309300007 16.43425817700006,32.76572371700007 16.421609239000077,32.76291284300004 16.41120905200006,32.75082609300006 16.405306177000057,32.73705284300007 16.403338615000052,32.732555468000044 16.387878802000046,32.73058784300002 16.362862052000025,32.72103090600007 16.35218074000005,32.699668280000026 16.34346705200005,32.69311523400006 16.334899902000075,32.69526151500003 16.333367174000045,32.696215980000034 16.332685560000073,32.69700835700007 16.33211969900003,32.70237730100007 16.3282855 66000034,32.70283773800003 16.32795675400007,32.70374952800006 16.327305616000046,32.70410992400008 16.32704824600006,32.70488294000006 16.326496211000062,32.70912119800005 16.32346953700005,32.70980173500004 16.322983544000067,32.71453707200004 16.319601890000058,32.717235962000075 16.317674528000055,32.728933924000046 16.309320644000024,32.735297109000044 16.30477649200003,32.77832776200006 16.27404694200004,32.78847690500004 16.26679911800005,32.78928987300003 16.266218552000055,32.791634560000034 16.26454413700003,32.80627441400003 16.254089355000076,32.82710442300004 16.250428274000058,32.90166169300005 16.23732409300004,33.01841918400004 16.216802804000054,33.065368609000075 16.208550976000026,33.22567289800003 16.180375904000073,33.25552026200006 16.17512993300005,33.26547830700002 16.173379708000027,33.276123047000056 16.17150878900003,33.278565342000036 16.17001084800006,33.280659006000064 16.168726734000074,33.28070068400007 16.168701172000056,33.28188780700003 16.16827916 7000037,33.28519926100006 16.167101993000074,33.28741216000003 16.16631533900005,33.304073146000064 16.160392597000055,33.31749931600007 16.155619787000035,33.33446050800006 16.149590327000055,33.35830688500005 16.14111328200005,33.43591308600003 16.113525391000053,33.45770917900006 16.09633109200007,33.510498047000056 16.054687500000057,33.531311035000044 16.038085937000062,33.58522965000003 16.015111491000027,33.66510009800004 15.981079102000024,33.842362671000046 15.994043453000074,33.87070973900006 15.996116656000027,33.88075426100005 15.99685127600003,33.96887207000003 16.003295898000033,33.97874392500006 16.00524385600005,33.98712158200004 16.00689697300004,33.99904138200003 16.009395135000034,34.01770019500003 16.01330566400003,34.07391357400007 16.024902344000054,34.18395165000004 16.047289400000068,34.21551513700007 16.053710937000062,34.23309326200007 16.028076172000056,34.23561050400008 16.023575648000076,34.23712158200004 16.020874023000033,34.26167004400003 15.978177384 000048,34.30828857400007 15.897094727000024,34.33551025400004 15.849487305000025,34.38067615800003 15.683288987000026,34.352474476000054 15.787083773000063,34.33558306300006 15.849561236000056,34.30836139300004 15.897168666000027,34.26174287900005 15.978251337000074,34.25851196000008 15.983870812000077,34.246655333000035 16.004492811000034,34.23719442500004 16.02094798400003,34.23568334700008 16.023649609000074,34.233166106000056 16.028150134000043,34.215628638000055 16.053725619000033,34.21560442100008 16.05376093600006,34.21819893500003 16.055213864000052,34.23687744100005 16.065673828000058,34.23691917200006 16.065730355000028,34.24747016300006 16.080022257000053,34.27525844300004 16.117663023000034,34.28062500500005 16.124932329000046,34.31216152300004 16.16765029000004,34.32093133200004 16.179529483000067,34.32953465400004 16.191183159000047,34.33579394900005 16.19966172200003,34.34358774300006 16.21021884700002,34.344009336000056 16.210789919000035,34.34439677000006 16.2113147 1900003,34.34808349600007 16.216308594000054,34.34828397100006 16.21657200800007,34.354133234000074 16.224257667000074,34.35540699300003 16.22593132800006,34.35844851300004 16.229927743000076,34.36064801300006 16.23281778300003,34.36287091000003 16.235738567000055,34.363187820000064 16.236154972000065,34.36907959000007 16.24389648400006,34.37406792000007 16.250491225000076,34.37450963300006 16.251075184000058,34.37817220200003 16.255917225000076,34.37893789000003 16.25692949000006,34.37985639500005 16.258143785000073,34.381015336000075 16.259675944000037,34.38200150500006 16.260979693000024,34.384250773000076 16.26395330200006,34.38649378000002 16.266918634000035,34.38697283500005 16.26755196000005,34.38836325400007 16.269390142000077,34.388509906000024 16.269584020000025,34.388871676000065 16.270062292000034,34.38954083500005 16.27094694300007,34.38956626400005 16.270980561000044,34.38994258500003 16.271478071000047,34.39068052300007 16.272453651000035,34.39152645200005 16.27357199 7000033,34.39178623200007 16.27391543500005,34.392219081000064 16.274487677000025,34.393124717000035 16.275684958000056,34.393761676000054 16.27652703900003,34.39585328800007 16.27929222100005,34.396041 16.27954038200005,34.39708580100006 16.280921645000035,34.397164478000036 16.281025658000033,34.39758834200006 16.281586021000066,34.397812428000066 16.28188227000004,34.398041904000024 16.282185646000073,34.39823176600004 16.282436650000022,34.398427265000066 16.282695106000062,34.39862045900003 16.28295051400005,34.39865047400008 16.282990194000035,34.39921180700003 16.28373229500005,34.39959427900004 16.284237936000068,34.40041770500005 16.285326534000035,34.40101395700003 16.286114799000075,34.401226887000064 16.286396300000035,34.401625523000064 16.286923311000066,34.403096783000024 16.288868367000077,34.40364768200004 16.28959667500004,34.40371393600003 16.289684265000062,34.40396460300008 16.290015655000047,34.404406778000066 16.29060022500005,34.40452285900005 16.290753688000 052,34.40463645500006 16.290903866000065,34.40502903600003 16.291422871000066,34.40504481500005 16.29144373200006,34.40508438200004 16.291496040000027,34.40496477200003 16.291946720000055,34.40217416100006 16.302461513000026,34.37839484400007 16.40461156600003,34.32468025600008 16.632472731000064,34.29274697000005 16.657669503000022,34.110308134000036 16.801700202000063,34.082710217000056 16.82348803700006,33.911753531000045 16.958453879000047,33.79291447000003 17.04161194200003,33.75369465600005 17.048983692000036,33.71683365600006 17.059304755000028)) ``` </details> -- 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: [email protected] For queries about this service, please contact Infrastructure at: [email protected]
