Showing results for 
Search instead for 
Do you mean 

Error inserting or updating the specified coordinate system

by Technical Evangelist on ‎02-18-2016 10:54 AM - edited on ‎04-06-2016 06:22 PM by Moderator (398 Views)

Symptoms

Some custom CSF's (coordinate system files) are returning the following error when the coordinate system is being loaded into Oracle or SQL Server while in Access, the coordinate system load just fine:

Error inserting or updating the specified coordinate system.

An examination of the GOracle.log file shows the following error:

ORA-01426: Numeric Overflow

in the following statement:

insert into GDOSYS.GCOORDSYSTEM values ('{EB02F5E1-AED9-41EE-AF4E-6FDA069A3AF0}','2','orto','','0','1','0','0','0',
'0','1','0','0','0','0','1','0','0','0','0','1','0','','0','21','1',
'1.79769313486232E+308','20','','0','0','','0','','0','','1','','','','','','','','','','','','','','','','','','','','',
'','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','0','','','');


Diagnosis

The number '1.79769313486232E+308' is the source of the overflow problem. This is a BIG NUMBER and is too big for the data type FLOAT(126) used by Oracle.  It is also too big for the FLOAT data type used by SQL Server.

Solution

The value causing the problem is the InverseFlattening column. When you look at this value via the Reference Ellipsoid Parameters dialog from Define Coordinate System it is set to "infinite - sphere".  The issue that causes this problem has to do with how GeoMedia deals with infinity. When you define the ellipsoid as being a perfect sphere, then the eccentricity and flattening values are both zero. Inverse flattening is 1/Flattening, so as Flattening approaches zero, Inverse Flattening approaches infinity.  There is no numeric value for infinity so GM approximates it by using a very large number, in this case '1.79769313486232E+308'.  This works fine internally but causes a problem for both SQL Server and Oracle.  While the use of a perfect sphere was rare in the past, it is becoming increasingly more common since Google Maps make use of it extensively.   The workaround is to adjust the parameter manually until a fix is made in the product:

For oracle, this one fails:

testing@TSDB64> insert into GDOSYS.GCOORDSYSTEM values
  2   ('{EB02F5E1-AED9-41EE-AF4E-6FDA069A3AF0}','2','orto','','0','1','0','0','0',
  3   '0','1','0','0','0','0','1','0','0','0','0','1','0','','0','21','1',
  4   '1.79769313486232E+308','20','','0','0','','0','','0','','1','','','','','',
  5   '','','','','','','','','','','','','','','','','','','','','','','','','',
  6   '','','','','','','','','','','','','','','','','','','','','','','','','',
  7   '','','','','','','','','','','','','','','','','','','','','','','','0','',
  8   '','');
 '1.79769313486232E+126','20','','0','0','','0','','0','','1','','','','','',
 *
ERROR at line 4:
ORA-01426: numeric overflow

But this one will work:

Elapsed: 00:00:00.00
testing@TSDB64> insert into GDOSYS.GCOORDSYSTEM values
  2   ('{EB02F5E1-AED9-41EE-AF4E-6FDA069A3AF0}','2','orto','','0','1','0','0','0',
  3   '0','1','0','0','0','0','1','0','0','0','0','1','0','','0','21','1',
  4   '1.79769313486232E+125','20','','0','0','','0','','0','','1','','','','','',
  5   '','','','','','','','','','','','','','','','','','','','','','','','','',
  6   '','','','','','','','','','','','','','','','','','','','','','','','','',
  7   '','','','','','','','','','','','','','','','','','','','','','','','0','',
  8   '','');

In SQL Server, this one fails:

insert into dbo.GCOORDSYSTEM values
 ('{EB02F5E1-AED9-41EE-AF4E-6FDA069A3AF0}','2','orto','','0','1','0','0','0',
 '0','1','0','0','0','0','1','0','0','0','0','1','0','','0','21','1',
 '1.79769313486232E+308','20','','0','0','','0','','0','','1','','','','','',
 '','','','','','','','','','','','','','','','','','','','','','','','','',
 '','','','','','','','','','','','','','','','','','','','','','','','','',
 '','','','','','','','','','','','','','','','','','','','','','','','0','',
 '','');
Msg 8115, Level 16, State 2, Line 1
Arithmetic overflow error converting expression to data type float.
The statement has been terminated.

But this one will work:

insert into dbo.GCOORDSYSTEM values
 ('{EB02F5E1-AED9-41EE-AF4E-6FDA069A3AF0}','2','orto','','0','1','0','0','0',
 '0','1','0','0','0','0','1','0','0','0','0','1','0','','0','21','1',
 '1.79769313486232E+307','20','','0','0','','0','','0','','1','','','','','',
 '','','','','','','','','','','','','','','','','','','','','','','','','',
 '','','','','','','','','','','','','','','','','','','','','','','','','',
 '','','','','','','','','','','','','','','','','','','','','','','','0','',
 '','');
(1 row(s) affected)

 

Contributors