topic Re: HELP!! SE_SHAPE has radically different vert coords when loaded from different DB in Data Management Questions

HELP!! SE_SHAPE has radically different vert coords when loaded from different DBs

DougMatthews — Fri, 10 Feb 2012 20:01:19 GMT

ArcSDE 9.3
SQL Server 2008

We have a C++ application that uses ArcSDE C-api to perform database IO. After years of working properly, all of a sudden, when running against one of our databases we are reading SE_SHAPEs from ONE PARTICULAR table that have really wacky coordinates. Thousands of shapes from dozens of other tables are correct. Copying the table into a different database and running the same application again works properly.

Table is nothing fancy. WGS84. NOT versioned.

I wrote some code to call SE_shape_get_points RIGHT AFTER the SE_SHAPE is read and am experiencing this:

DATABASE_1:
Inspect geometry using ArcMap, vertices look to have correct coordinates.
Run application against DATABASE_1, dumping shape points immediately after reading SE_SHAPE:
********SHAPE DUMP********
PART 0
    SUBPART 0
      0: x=-4.551839, y=-0.129920 <<<< Correct coordinates
      1: x=-4.522037, y=-0.368338
      2: x=-4.504342, y=-0.627246
      3: x=-4.427042, y=-0.904780
      4: x=-4.269648, y=-1.187902
      5: x=-4.268717, y=-0.904780
      6: x=-2.745073, y=-0.904780
      7: x=-1.243781, y=-0.952277
      8: x=-0.582542, y=-0.952277
      9: x=-0.582542, y=-1.185108
      10: x=-0.519212, y=-1.185108
      ...

DATABASE_2:
Copy table (using ArcCatalog) from DATABASE_1.
Inspect geometry using ArcMap, vertices look to have correct coordinates.
Run application against DATABASE_2, dumping shape points immediately after reading SE_SHAPE:
********SHAPE DUMP********
PART 0
    SUBPART 0
      0: x=4049780.593044, y=4049790.639405 <<<< VERY Incorrect coordinates
      1: x=4049782.309627, y=4049790.639405
      2: x=4049782.309627, y=4049778.306904
      3: x=4049790.410000, y=4049778.306904
      4: x=4049790.410000, y=4049785.352211
      5: x=4049791.173365, y=4049785.352211
      6: x=4049791.173365, y=4049785.829831
      7: x=4049797.705849, y=4049785.830204
      8: x=4049796.939666, y=4049784.594846
      9: x=4049796.928548, y=4049784.598277
      10: x=4049796.928269, y=4049784.598734
      ...

DATABASE_3:
Copy table (using ArcCatalog) from DATABASE_1.
Inspect geometry using ArcMap, vertices look to have correct coordinates.
Run application against DATABASE_3, dumping shape points immediately after reading SE_SHAPE:
********SHAPE DUMP********
PART 0
    SUBPART 0
      0: x=-4.551839, y=-0.129920 <<<< Correct coordinates
      1: x=-4.522037, y=-0.368338
      2: x=-4.504342, y=-0.627246
      3: x=-4.427042, y=-0.904780
      4: x=-4.269648, y=-1.187902
      5: x=-4.268717, y=-0.904780
      6: x=-2.745073, y=-0.904780
      7: x=-1.243781, y=-0.952277
      8: x=-0.582542, y=-0.952277
      9: x=-0.582542, y=-1.185108
      10: x=-0.519212, y=-1.185108
      ...

I have tried:

Regenerating the table (using our tool that does that) several times.

Renaming the table (in case there is something stale associated with the filename in SDE).

Inspecting table schema, coordinate system definintions, etc.

Restarting DATABASE_2's service.

Shaking chicken bones over the keyboard...

Any ideas on what to try next?

Thanks in advance!

-Doug

Re: HELP!! SE_SHAPE has radically different vert coords when loaded from different DB

VinceAngelo — Fri, 10 Feb 2012 20:17:26 GMT

What are SRIDs involved in the respective tables, and what are the parameters
associated with those tables?

- V

Re: HELP!! SE_SHAPE has radically different vert coords when loaded from different DB

DougMatthews — Fri, 10 Feb 2012 20:33:00 GMT

Hi Vangelo -

Type is Polygon Features. (Features are metric footprints of objects).

Projection: Local
False_Easting: 0.000000
False_Northing: 0.000000
Scale_Factor: 1.000000
Azimuth: 0.000000
Longitude_Of_Center: 0.000000
Latitude_Of_Center: 0.000000
Linear Unit: Meter (1.000000)

Geographic Coordinate System: GCS_WGS_1984
Angular Unit: Degree (0.017453292519943299)
Prime Meridian: Greenwich (0.000000000000000000)
Datum: D_WGS_1984
Spheroid: WGS_1984
    Semimajor Axis: 6378137.000000000000000000
    Semiminor Axis: 6356752.314245179300000000
    Inverse Flattening: 298.257223563000030000

Tolerance: 0 meters
Resolution: 0.000000000222045 meter

XY Domain
MaxY: 999999.999068677 meter
MinY: -1000000.00093132 meter
MaxX: 999999.999068677 meter
MinX: -1000000.00093132 meter

Thanks!

-Doug

Re: HELP!! SE_SHAPE has radically different vert coords when loaded from different DB

VinceAngelo — Fri, 10 Feb 2012 21:25:57 GMT

No, I want you to look at the actual values of the SRID column of the SDE.LAYERS table
in the three databases, and then look at the FALSEX, FALSEY and XYUNITS values in
SDE.SPATIAL_REFERENCES that correspond to those SRIDs. I suspect there's a discrepancy
between the instances, so that the values in one are using the wrong decompression rules.

- V

Re: HELP!! SE_SHAPE has radically different vert coords when loaded from different DB

DougMatthews — Fri, 10 Feb 2012 21:54:20 GMT

Oh, sorry. Here you go:

DATABASE_1
SRID = 2
falsex = -1000000.00093132
falsey = -1000000.00093132
xyunits = 4503599627.37049

DATABASE_2
SRID = 40
falsex = -1000000.00093132
falsey = -1000000.00093132
xyunits = 4503599627.37049

DATABASE_3
SRID = 1
falsex = -1000000.00093132
falsey = -1000000.00093132
xyunits = 4503599627.37049

-Doug

Re: HELP!! SE_SHAPE has radically different vert coords when loaded from different DB

VinceAngelo — Fri, 10 Feb 2012 23:23:36 GMT

Now, what is the contents of SRIDs 1 & 2 on Database 2? If you do the math, does the integer
representation of {-4.551839,-0.129920} map to {4049780.593044,4049790.639405}?

- V

Re: HELP!! SE_SHAPE has radically different vert coords when loaded from different DB

DougMatthews — Fri, 10 Feb 2012 23:32:37 GMT

For Database_2:

SRID = 1
falsex = -400
falsey = -400
xyunits = 1000000000

SRID = 2
falsex = -6378299.99911361
falsey = -6356899.99911501
xyunits = 8589.93458

Not sure exactly what math you refer to.

-Doug

Re: HELP!! SE_SHAPE has radically different vert coords when loaded from different DB

DougMatthews — Fri, 10 Feb 2012 23:39:07 GMT

Sorry, wrong DB. Here is the correct:

For Database_2:

SRID = 1
falsex = -399.999999934807
falsey = -399.999999892898
xyunits = 763549740.444444

SRID = 2
falsex = -1000000.00093132
falsey = -1000000.00093132
xyunits = 4503599627.37049

Not sure exactly what math you refer to.

-Doug

Re: HELP!! SE_SHAPE has radically different vert coords when loaded from different DB

VinceAngelo — Sat, 11 Feb 2012 00:05:56 GMT

The details are all in the whitepaper, but all Esri-formatted geometries are stored internally
in an integer format. The long (or long long, for HIGH precision coordrefs) value is calculated
by subtracting the offset, muliplying by the scale -- For example, {4.551839,0.129920}
would map to:

ix = ( 4.551839 - -1000000.00093132 ) * 4503599627.37049 = 4503620131225207
iy = ( 0.129920 - -1000000.00093132 ) * 4503599627.37049 = 4503600216672446

Converting the integer back to double is done by dividing by scale and adding the offset,
but let's use DB2's offsets and scale for SRID 1:

x = ( 4503620131225207.0 / 1000000000.0 ) + -400.0 = 4503220.1312252069
y = ( 4503600216672446.0 / 1000000000.0 ) + -400.0 = 4503200.2166724456

So, now we're at the same order of magnitude, indicating that I'm on the right track...

What does your C++ app look like? Do you set the coordinate reference at all?

Can you try downloading 'se_toolkit' and report what 'sdequery -v' reports for the
same shape?

- V

Re: HELP!! SE_SHAPE has radically different vert coords when loaded from different DB

DougMatthews — Mon, 13 Feb 2012 17:33:44 GMT

It does indeed seem like you're on the right track.

'sdequery -v' reports the expected (correct) result for the shapes in the table. Thanks for the link - the se_toolkit looks to be a very useful resource.

Our application does set the coordref. We use SE_layer_get_info and SE_layerinfo_get_coordref to get the Coordref from the table we are loading features from. I'll dig a little deeper here (unless you have something else I should investigate).

So is SE_SHAPE merely the quantized (integer) representation of the geometry? If so, does SE_SHAPE itself have any knowledge of the Coordref used to pack/unpack it?

-Doug

Re: HELP!! SE_SHAPE has radically different vert coords when loaded from different DB

VinceAngelo — Mon, 13 Feb 2012 20:18:20 GMT

The SE_SHAPE object keeps the shape type, envelope (4-D), LLONG (or LONG, for BASIC)
array, part array and SE_COORDREF. If you bind a shape to a query stream (or, less efficiently,
use SE_stream_get_shape), then the SE_COORDREF is that of the shape in the table.
If you use SE_shape_set_coordref on a shape, it *clobbers* the previous SE_COORDREF
without changing the other variables, shifting the location in simple transform (generally
speaking, you should not ever use set_coordref without first using make_nil).

If you use SE_shape_change_coordref (either directly, of by using SE_stream_change_coordref),
it will "project" (really just transform, when without a projection or coordsys change) the shape to
retain coordinate values (as much as possible -- precision cannot be increased).

Thus, a copy utility should SE_layer_get_coordref from the target, create source and target
shapes, bind the source shape (without setting coordref), bind the target shape (ditto), fetch
the row, call change_coordref to change to copy the source shape to the target coordref in the
target shape, and execute the insert. Using the stream function would catch change_coordref
errors as stream errors, so I prefer not to use it, but you could eliminate a shape and make the
copy loop into { fetch, execute }, which has some allure.

- V

Re: HELP!! SE_SHAPE has radically different vert coords when loaded from different DB

DougMatthews — Tue, 14 Feb 2012 02:09:23 GMT

Problem Solved!

The code that was reading the table's coordref was passing a recycled SE_COORDREF to SE_layerinfo_get_coordref, which was NOT overwriting the recycled data with the new. Seems we've been 'lucky' for years that the last user of this recycled SE_COORDREF left the coordref we wanted. Something seems to have tickled the order of things and the bug surfaced. Creating a new SE_COORDREF to receive the table's coordref data did the trick.

Thank you for your help (and the learnings)!

-Doug