Unwanted rounding to 8 decimal places in Oracle

[ITCeMM]

There seems to be a bug or at least inconsistency (that might be important is some cases) in geolatte-geom at least when used with Oracle JDBC.

We are doing some redesign/migration of geo data both on app and database side. Requirement of our client is to have exactly the same precision of geo data as before. Migration script creates thousands Points/MultiLines/Polygons using Hibernate Spatial as SDO_GEOMETRY in Oracle DB. We've found out that some values are different, that is:

• if value being stored has up to 8 decimal places - value in db is exactly the same
• if value being stored has more than 8 decimal places - value in db seems to be rounded to 8 decimal places

After some debugging rounding seems to be done in constructor oracle.sql.NUMBER(double v) called from OracleJDBCTypeFactory. Our fix proposal is to use oracle.sql.NUMBER(BigDecimal v) rather than oracle.sql.NUMBER(double v).

Libraries used:

• Oracle Database (JDBC driver 19.7.0.0)
• Hibernate & Hibernate Spatial (5.4.18.Final, geolatte-geom 1.6.0)
• same problem in Oracle 12c and 18c

Below you can find a draft of integration test that fail with geolatte-geom 1.6.0 (and works fine with provided pull request). Flushing and clearing entityManager before second geometry comparision is crucial, as compared geometry comes from database (not a cache or smth).

shouldReturnSameCoordinatesForPointUseSDOPointToFalse

java.lang.AssertionError: Expected :SRID=2177;POINT(6435765.5225063935 5658605.667610391) Actual :SRID=2177;POINT(6435765.52250639 5658605.66761039)

shouldReturnSameCoordinatesForMultiPoint

java.lang.AssertionError: Expected :SRID=2177;MULTIPOINT((6435765.5225063935 5658605.667610391),(6435765.522506333 5658605.667613421)) Actual :SRID=2177;MULTIPOINT((6435765.52250639 5658605.66761039),(6435765.52250633 5658605.66761342))

@RunWith(SpringJUnit4ClassRunner.class)
@ContextConfiguration("classpath:test-service.xml")
@Transactional
public class GeometryIntegrationTest {

@PersistenceContext
private EntityManager entityManager;

private static GeometryFactory gf = new GeometryFactory(new PrecisionModel(FLOATING), 2177);

@Test
public void shouldReturnSameCoordinatesForPointUseSDOPointToTrue() {
    System.setProperty(Settings.USE_SDO_POINT, "true");
    TestGeometryEntity e = new TestGeometryEntity();
    Point originalGeoPoint = gf.createPoint(new Coordinate(6435765.5225063935, 5658605.667610391));
    Geometry<?> geolatteGeo = JTS.from(originalGeoPoint);
    setPersistGeometryReloadAndCheckIfSameReturnedFromDatabaseIncSessionFlushAndClear(e, geolatteGeo);

}

@Test
public void shouldReturnSameCoordinatesForPointUseSDOPointToFalse() {
    System.setProperty(Settings.USE_SDO_POINT, "false");
    TestGeometryEntity e = new TestGeometryEntity();
    Point originalGeoPoint = gf.createPoint(new Coordinate(6435765.5225063935, 5658605.667610391));
    Geometry<?> geolatteGeo = JTS.from(originalGeoPoint);
    setPersistGeometryReloadAndCheckIfSameReturnedFromDatabaseIncSessionFlushAndClear(e, geolatteGeo);

}

@Test
public void shouldReturnSameCoordinatesForMultiPoint() {
    TestGeometryEntity e = new TestGeometryEntity();
    Coordinate point1 = new Coordinate(6435765.5225063935, 5658605.667610391);
    Coordinate point2 = new Coordinate(6435765.5225063333, 5658605.667613421);

    MultiPoint originalGeoPoint = gf.createMultiPoint(new Point[] {gf.createPoint(point1), gf.createPoint(point2)});
    Geometry<?> geolatteGeo = JTS.from(originalGeoPoint);
    setPersistGeometryReloadAndCheckIfSameReturnedFromDatabaseIncSessionFlushAndClear(e, geolatteGeo);

}

private void setPersistGeometryReloadAndCheckIfSameReturnedFromDatabaseIncSessionFlushAndClear(TestGeometryEntity entity, Geometry<?> g) {
    entity.setGeometry(g);
    entityManager.persist(entity);

    Long geoId = entity.getId();
    TestGeometryEntity entityAfterStore = entityManager.find(TestGeometryEntity.class, geoId);
    assertEquals(entityAfterStore.getGeometry(), g);

    entityManager.flush();
    entityManager.clear();

    entityAfterStore = entityManager.find(TestGeometryEntity.class, geoId);
    assertEquals(g, entityAfterStore.getGeometry());
}
}