1

In an oracle 11g enterprise geodatabase I am trying the following:

INSERT INTO MySTATION (STATION_ID, DB_VERSION_NU, CREATED_DT, MOD_DT, SHAPE)
VALUES (1, 0, sysdate, sysdate, SDE.ST_GEOMETRY('multipoint z(1.2345 1.2345 10)', 4326));

INSERT INTO MySTATION (STATION_ID, DB_VERSION_NU, CREATED_DT, MOD_DT, SHAPE)
VALUES (2, 0, sysdate, sysdate, SDE.ST_GEOMETRY('point(1.2345 1.2345)', 4326));

SELECT STATION_ID, SDE.ST_X(SHAPE), SDE.ST_Y(SHAPE) FROM MySTATION ;

For both of the above inserts the x, y coordinates returned with the select are 1,23450000000003 instead of 1.2345. Is there a way to read/write data to an ST_GEOMETRY column without loss of precision?

Also, can anybody explain me why is the precision lost?

  • 1
    I'm having difficulty with having this classified as a "precision loss". – Vince Nov 24 '14 at 19:08
  • Thank you @Vince for your statemet. Feel free to suggest any correction so as to make it clearer. – Smalis Nov 24 '14 at 21:31
  • Precision loss cannot occur beyond the specified precision. By choosing "1.2345", you're implicitly specifying a value in the range [1.23445,1.23455), into which 1.23450000000003 clearly fits. If you were to assert nanometer precision you'd have a clear accuracy issue, since little geodata is even accurate to 1 meter, much less a billionth of one. – Vince Nov 24 '14 at 22:02
3

Every set of coordinates stored inside SDE.ST_GEOMETRY is stored as a compressed array of 8-byte long integers. The IEEE floating-point to integer conversion is performed by subtracting the coordinate reference X_OFFSET (or Y_OFFSET, or Z_OFFSET, or M_OFFSET) and multiplying by the XYUNITS (or Z_SCALE or M_SCALE). The process is reversed when querying for coordinate values (divide by scale, add offset). The difference between the original value and the stored representation will not exceed half the multiplicative inverse of the scale factor.

The slight difference you are seeing may have been introduced in IEEE floating point representation, when a mantissa which is not evenly divisible by two causes rounding at the fringe of the least significant bits, or it could be due to choosing a coordinate reference scale which is not a power of 10.

In my ArcGIS 10.2.2 instance, the 4326 SRID XYUNITS is one billion (10^9), and following your procedure shows the same precision out as in:

SQL> SELECT srid,x_offset,y_offset,xyunits FROM sde.st_spatial_references WHERE srid = 4326;

      SRID   X_OFFSET   Y_OFFSET    XYUNITS
---------- ---------- ---------- ----------
      4326       -400       -400 1000000000

SQL> create table foo(objectid number(38), shape SDE.st_geometry);

Table created.

SQL> insert into foo values (1, SDE.ST_GEOMETRY('point(1.2345 1.2345)', 4326));

1 row created.

SQL> select SDE.ST_X(SHAPE),SDE.ST_Y(SHAPE) from foo;

SDE.ST_X(SHAPE) SDE.ST_Y(SHAPE)
--------------- ---------------
         1.2345          1.2345

If you are capturing the coordinate values in a 'C', Java, or Python API, the slight difference in formatting (which is no more than 3.4 nanometers at the equator) may be an artifact of your choice in formatting (I use log10(scale_factor) digits to the right of the decimal when the log10 is integral, and log10(scale_factor) + 2 when it isn't).

  • Thank you @Vince for the detailed answer. I am using arcgis 10.2 too and srid 4326. Should I make any extra configuration so as to have the same results as you? – Smalis Nov 24 '14 at 21:38
  • I've provided more information than you have, so there is really no way for me to tell. – Vince Nov 24 '14 at 22:04
  • Indeed you have made a very comprehensive analysis and I am grateful for that @Vince. However, I am trying to understand what's the difference between your system and mine. I am performing the same query as you (SQL> SELECT srid,x_offset,y_offset,xyunits FROM sde.st_spatial_references WHERE srid = 4326;) and I am getting exactly the same results. However when storing a point with x=1.2345 I keep getting back, when qurying for this point a X=1.23450000000003. – Smalis Nov 25 '14 at 10:12
  • You haven't provided your XYUNITS for SRID 4326, or the client environment of your query (SQL*Plus/C/Java/Python), both of which were stated to be factors to outcome. – Vince Nov 25 '14 at 11:29

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.