GeoDjango Distance Test Failures on Oracle 11g

[jbronn]

Ran the GeoDjango test suite on Oracle 11g and received the following errors:

======================================================================
FAIL: Testing GML output from the database using GeoManager.gml().
----------------------------------------------------------------------
Traceback (most recent call last):
  File "C:\Program Files\GeoDjango\Django-1.0.2-final\django\contrib\gis\tests\geoapp\tests.py", line 155, in test03b_gml
    self.assertEqual(True, bool(gml_regex.match(ptown.gml)))
AssertionError: True != False

======================================================================
FAIL: Testing the `distance` GeoQuerySet method on projected coordinate systems.

----------------------------------------------------------------------
Traceback (most recent call last):
  File "C:\Program Files\GeoDjango\Django-1.0.2-final\django\contrib\gis\tests\distapp\tests.py", line 129, in test03a_distance_method
    self.assertAlmostEqual(m_distances[i], c.distance.m, tol)
AssertionError: 147075.06981300001 != 44828.570935898977 within 2 places

======================================================================
FAIL: Testing the `distance_lt`, `distance_gt`, `distance_lte`, and `distance_gte` lookup types.
----------------------------------------------------------------------
Traceback (most recent call last):
  File "C:\Program Files\GeoDjango\Django-1.0.2-final\django\contrib\gis\tests\distapp\tests.py", line 200, in test04_distance_lookups
    self.assertEqual(cities, ['Bellaire', 'Pearland', 'West University Place'])
AssertionError: [] != ['Bellaire', 'Pearland', 'West University Place']

The first failure is simply different precision values in the GML output, and is easily solved with a better regex in the test suite. The next two problems from the distance test suite, however, will require more investigative work.

[jbronn]

On 10g (10.2) the spatial reference WKT for 2278 is:

PROJCS["NAD83 / Texas South Central (ftUS)",
    GEOGCS["NAD83",
        DATUM["North American Datum 1983 (EPSG ID 6269)",
            SPHEROID["GRS 1980 (EPSG ID 7019)",6378137,298.257222101]],
        PRIMEM["Greenwich",0.000000],
        UNIT["Decimal Degree",0.01745329251994328]],
    PROJECTION["SPCS83 Texas South Central zone (US Survey feet) (EPSG OP 15360)"],
    PARAMETER["Latitude_Of_Origin",27.83333333333333333333333333333333333333],
    PARAMETER["Central_Meridian",-98.99999999999999999999999999999999999987],
    PARAMETER["Standard_Parallel_1",30.28333333333333333333333333333333333333],
    PARAMETER["Standard_Parallel_2",28.38333333333333333333333333333333333333],
    PARAMETER["False_Easting",1968500],
    PARAMETER["False_Northing",13123333.33300000000000000000000000000001],
    UNIT["U.S. Foot",.3048006096012192024384048768097536195072]]

Here's simplified SQL of what's going on in the distapp tests and just using two points on 10g:

SQL> SELECT SDO_GEOM.SDO_DISTANCE(SDO_CS.TRANSFORM(SDO_GEOMETRY('POINT (-95.3704010173142930 29.7048674094754650)', 4326), 2278), SDO_GEOMETRY('POINT (3092499.2911236398000000 13821123.0329729000000000)', 2278), 0.05) FROM DUAL;
SDO_GEOM.SDO_DISTANCE(SDO_CS.TRANSFORM(SDO_GEOMETRY('POINT(-95.37040101731429302
--------------------------------------------------------------------------------
                                                                      28041.9753

So the units come back as expected, that is in U.S. feet, and this is why all tests pass on 10g.

However, on Oracle 11g (11.1), the spatial reference WKT for 2278 is:

PROJCS["NAD83 / Texas South Central (ftUS)",
    GEOGCS["NAD83",
        DATUM["North American Datum 1983 (EPSG ID 6269)",
            SPHEROID["GRS 1980 (EPSG ID 7019)",6378137,298.257222101]],
        PRIMEM["Greenwich",0.000000],
        UNIT["Decimal Degree",0.01745329251994328]],
    PROJECTION["Lambert Conformal Conic"],
    PARAMETER["Latitude_Of_Origin",27.83333333333333333333333333333333333333],
    PARAMETER["Central_Meridian",-98.99999999999999999999999999999999999987],
    PARAMETER["Standard_Parallel_1",30.28333333333333333333333333333333333333],
    PARAMETER["Standard_Parallel_2",28.38333333333333333333333333333333333333],
    PARAMETER["False_Easting",1968500],
    PARAMETER["False_Northing",13123333.33300000000000000000000000000001],
    UNIT["U.S. Foot",.3048006096012192024384048768097536195072]]

The only difference I can tell is in the PROJECTION element. Besides this, it still appears to be a projected coordinate system (units are still in U.S. Feet), but Oracle seems to think it's actually in a geodetic coordinate system and thus returns the units in meters instead:

SQL> SELECT SDO_GEOM.SDO_DISTANCE(SDO_CS.TRANSFORM(SDO_GEOMETRY('POINT (-95.3704010173142930 29.7048674094754650)', 4326), 2278), SDO_GEOMETRY('POINT (3092499.2911236398000000 13821123.0329729000000000)', 2278), 0.05) FROM DUAL;

SDO_GEOM.SDO_DISTANCE(SDO_CS.TRANSFORM(SDO_GEOMETRY('POINT(-95.37040101731429302
--------------------------------------------------------------------------------
                                                                      8547.21118

So at this point, either Oracle corrected something that was wrong in 10g or they've mislabeled/misidentified projected coordinate systems as geographic ones. The answer is still correct, but not in the right units so I'm not quit sure what to do here. At least we're not totally in the dark anymore as to why GeoDjango "won't go to 11" with Oracle.

[jbronn]

(In [10197]) Refactored and cleaned up parts of the spatial database backend. Changes include:

• Laid foundations for SpatiaLite support in GeoQuerySet, GeoWhereNode and the tests.
• Added the Collect aggregate for PostGIS (still needs tests).
• Oracle now goes to 11.
• The backend-specific SpatialRefSys and GeometryColumns models are now attributes of SpatialBackend.
• Renamed GeometryField attributes to be public that were private (e.g., _srid -> srid and _geom -> geom_type).
• Renamed create_test_db to create_test_spatial_db.
• Removed the legacy classes GeoMixin and GeoQ.
• Removed evil \ from spatial backend fields.
• Moved shapefile data from tests/layermap to tests/data.

Fixed #9794. Refs #9686.

[Jacob]

Milestone 1.1 deleted