Data Generation and Query Analysis Finding

[timbow78]

Question...is the data generated with geodetic and queries are using cartesian and it's not reflective in accurate query analysis. For example, in Oracle Spatial...if I use your latitude and measure it's 41 km and not the 50 km.
SELECT sdo_geom.sdo_distance (
sdo_geometry(2001,4326,sdo_point_type(0,34.8697,null),null,null),
sdo_geometry(2001,4326,sdo_point_type(.45,34.8697,null),null,null), .005, 'unit=km') AS distance_in_kilometers
FROM dual;

41.1446555135459.

--At Equator yes...the .45 is correct and reflects 50 km
SELECT sdo_geom.sdo_distance (
sdo_geometry(2001,4326,sdo_point_type(0,0,null),null,null),
sdo_geometry(2001,4326,sdo_point_type(.45,0,null),null,null), .005, 'unit=km') AS distance_in_kilometers
FROM dual;

50.0937708570127

[jiayuasu]

@timbow78 Hi, in the current SpatialBench release, both the data generator and the queries use Cartesian coordinates. In other words, we currently focus only on the geometry type. We intentionally avoided geography and spherical distance calculations to eliminate variations caused by different edge interpolation algorithms and ST_Transform implementations. Support for spherical distance calculations is planned for a future release.

[jiayuasu]

However, you are welcome to modify these queries to test on the geography data type in a spatial database. For example, you can replace ST_Distance with ST_DistanceSphere, or you can call ST_GeogFromWKB to create a geography column

[timbow78]

Ok thank you!

[timbow78]

Other issue I've noticed is the dataset generated is not OGC Valid Geometries for buildings and zones. Some are reverse order, others are not closed (meaning line).

[jiayuasu]

@timbow78 Thank for the issue.

I will let @prantogg answer in more details. But I think we can update the dbgen to

1. Remove invalid geometries.
2. Enforce counter-clock wise for exterior rings of polygons.
3. Remove non-closed polygons.

[prantogg]

Thanks for surfacing this @timbow78.

I ran some checks on the SF1000 buildings and zones:

• All geometries are closed
• ~0.4% of buildings (889 out of 219k) are invalid due to ST_Area = 0 - likely caused by degenerate polygons from the random coordinate generator. I’ll patch this.
• The rest pass Sedona's ST_IsValid, which under the hood uses JTS’s IsValidOp with standard OGC SFS rules — it checks topology (e.g. self-intersections, closure), but not ring orientation.
• Buildings have mixed winding (mostly clockwise); zones are all CCW.

I couldn’t find where OGC strictly enforces ring orientation - some engines like Oracle and PostGIS interpret it more strictly though, so I’ll update the generator to standardize on CCW for exterior rings. Appreciate you flagging this!

[jiayuasu]

@timbow78 We've fixed in the latest commit. Please give a try. We plan to make a new release sometime this week.

[timbow78]

I see several values on SF1 that shows trip points outside valid ranges
select rowid from trip a
where a.t_pickuploc_geom.sdo_point.x not between -180 and 180
or a.t_pickuploc_geom.sdo_point.y not between -90 and 90

2078

[prantogg]

@timbow78 Im not able to reproduce this. Did you try running the check after reinstalling the latest spatialbench and then regenerating trip table for sf=1?

cargo install --path ./spatialbench-cli
spatialbench-cli -s 1 -T trip --output-dir sf1