What are the differences between 81989 and 27700 CRS for the British National Grid?

Question

We are using Oracle Spatial to run point in polygon queries in Great Britain using Ordnance Survey MasterMap and point data. MasterMap is on British National Grid (BNG) and has the Coordinate Reference System (CRS) 27700, while our point data has the Oracle Spatial 'BNG version' 81989. Will it matter that we are using different systems?

Answer 1

The two coordinate systems are essentially equivalent: 81989 is the older "legacy" definition that used to ship with Oracle before 10gR2. 27700 is the EPSG definition that ships since 10gR2. Transformations give slightly different results due to the difference in datum shifts.

Onto your specific question: your Mastermap data is 27700, your point data is 81989. Will you be able to use your 81989 points to search the 27700 Mastermap data ? The answer is yes. Queries will automatically transform your points from 81989 to 27700 to perform the searches. That transformation will either retain the same coordinates, or slightly shift them. The main issue will be wasted performance due to this essentially unnecessary transformation.

A better solution will be to change the SRID in your points. For that, you need to drop the spatial index, update the SRID inside your points, change the SRID in your metadata and rebuild the index. To replace the SRIDs in the geometries use this approach:

update my_points p set p.geom.sdo_srid=27700;

i.e. remember to use an alias.

Generally speaking it is better to use the EPSG codes instead of the Oracle "legacy" SRIDs. EPSG codes are generally a bit more accurate, but more important they are more "standard" and make it easier to exchange data with others.

Answer 2

See https://community.oracle.com/thread/2210240

In summary, try:

select * from mdsys.sdo_cs_srs where srid = 27700 or srid = 81989

Moderator Edit:

OSGB 1936 / British National Grid: 27700

PROJCS["OSGB 1936 / British National Grid", 
     GEOGCS [ "OSGB 1936", 
          DATUM ["OSGB 1936 (EPSG ID 6277)", 
               SPHEROID ["Airy 1830 (EPSG ID 7001)", 6377563.396, 299.3249646], 
               446.448, -125.157, 542.06, .15, .247, .842, -20.489], 
          PRIMEM [ "Greenwich", 0.000000 ], 
          UNIT ["Decimal Degree", 0.01745329251994328]], 
     PROJECTION ["Transverse Mercator"], 
     PARAMETER ["Latitude_Of_Origin", 49], 
     PARAMETER ["Central_Meridian", -2], 
     PARAMETER ["Scale_Factor", .9996012717], 
     PARAMETER ["False_Easting", 400000], 
     PARAMETER ["False_Northing", -100000], 
     UNIT ["Meter", 1]]

British National Grid: 81989

PROJCS["British National Grid", 
     GEOGCS [ "Ordnance Survey Great Brit", 
          DATUM ["Ordnance Survey Great Brit", 
               SPHEROID ["Airy 1830", 6377563.396, 299.3249646], 
               375, -111,  431, 0, 0, 0, 1], 
          PRIMEM [ "Greenwich", 0.000000 ], 
          UNIT ["Decimal Degree", 0.01745329251994330]], 
     PROJECTION ["Transverse Mercator"], 
     PARAMETER ["Latitude_Of_Origin", 49.000000], 
     PARAMETER ["Central_Meridian", -2.000000], 
     PARAMETER ["Scale_Factor", 0.9996012717], 
     PARAMETER ["False_Easting", 400000.000000], 
     PARAMETER ["False_Northing", -100000.000000], 
     UNIT ["Meter", 1.000000000000]]

Answer 3

Both projection definitions are different in the datum shift parameters.

See also Raster incorrectly reprojected to OSGB(27700)

PROJ/GDAL/QGIS used to have the more correct 7 parameters, then changed to the less accurate 3 parameters, and now has returned to the 7 parameters.

If you have ever reprojected WGS84 based data to OSGB or BNG, one or the other definitions might fit better.