topic Re: MSSQL Geography Layer Not Drawing with SDE in Data Management Questions

MSSQL Geography Layer Not Drawing with SDE

Jeri_SuePonder — Tue, 15 Mar 2011 15:05:31 GMT

Help! I can't get my Geography type spatial data to draw. I've tried to find info on this on the forums, but no luck. I've loaded data into SDE 4 different ways.

[INDENT]1. Import in ArcCatalog using standard SDE format - Works just fine
2. Create the table in SQL Server with Geography type, register with SDE and load data in ArcCatalog
3. Create and load the table in SQL Server, then register wth SDE.
4. Import in ArcCatalog using the Geography Config Keyword[/INDENT]

Case 2, 3 and 4 the data is there in SQL Server and in ArcMap or ArcCatalog the table shows data but nothing draws.

I know that I need to use the Geography type on Sql Server because I want all my data in Geograhpic NAD83 and that is the data type that supports it.

The command I've used to register the tables with SDE

sdelayer -o register -l sde.TZS2,SHAPE -e nac+ -t GEOGRAPHY -R 3 -P HIGH -C TZS2_ID,sde -E 18.865824,-179.864760,71.434459,-66.884958 -D sde -u XXX -p XXXXXXX

Where -R 3 is the number for GG NAD83 also have tried -G 4269 instead of -R makes no difference.

I've reviewed the logs the only error that is reported is: load_buffer error -51 on TZS2, rowid 0, where TZS2 is the layer from case 3.

I've got ArcSDE 10 SP 1 64-bit installed with SQL Server 2008 r2 64-bit on a Windows Server 2008 r2 Virtual Machine.

Any help would be appreciated!

Re: MSSQL Geography Layer Not Drawing with SDE

VinceAngelo — Tue, 15 Mar 2011 15:31:00 GMT

The real reason the query fails will be reflected in the logfiles in SDEHOME\etc. Odds are,
it will be one of the topology errors of the SgShape library ("ring crosses ring" "no area"
"too few points" "coordinate out of bounds").

Your command-line registration may create a very different coordinate reference than using
Desktop would. You can see this by using 'sdelayer -o describe_long'. It's not only the
coordinate system that matters, but also the origin and scale.

For best practice, load the GEOGRAPHY table as a user other than SDE (this user should
be reserved for instance administration, with a very closely held password). You don't
need to specify the user during layer registration, since it must be done as the owner.

- V

Re: MSSQL Geography Layer Not Drawing with SDE

Jeri_SuePonder — Tue, 15 Mar 2011 18:04:08 GMT

Your command-line registration may create a very different coordinate reference than using
Desktop would. You can see this by using 'sdelayer -o describe_long'. It's not only the
coordinate system that matters, but also the origin and scale.

For best practice, load the GEOGRAPHY table as a user other than SDE (this user should
be reserved for instance administration, with a very closely held password). You don't
need to specify the user during layer registration, since it must be done as the owner.

- V

I redid my test cases following the above info. I also discoved that I had the extents were mess up and I added the -z:

[INDENT]sdelayer -o register -l CASE2,SHAPE -e nac+ -t GEOGRAPHY -G 4269 -P HIGH -x -400.000000,-400.000000,1111948722.222222 -C TIMEZONES_ID,sde -E -179.864760,18.865824,-66.884958,71.434459, -D sde -u XXXX -p XXXXXXXX[/INDENT]

The layers still do not draw in ArcCatalog. When I pulled them into ArcMap. Case 1 draws fine as expected. When I turn Case1 off then Case2 shows. If I turn Case2 off and try to turn it back on it doesn't draw again. If I turn on Case1 and Case2 then turn off Case1, Case2 shows again. Case3 and Case4 behave the same as Case2.

The real reason the query fails will be reflected in the logfiles in SDEHOME\etc. Odds are,
it will be one of the topology errors of the SgShape library ("ring crosses ring" "no area"
"too few points" "coordinate out of bounds").

Review the log files in SDEHOME\etc as you suggested. The only error was:

[INDENT][Tue Mar 15 13:42:29 2011] [130] [N14704AKD] Table list query: {call sp_tables}
[Tue Mar 15 13:48:16 2011] [129] [N14704AKD] load_buffer error -51 on CASE2, rowid 0 [/INDENT]Any other thoughts on what I'm missing?

Thanks!

Re: MSSQL Geography Layer Not Drawing with SDE

MelitaKennedy — Tue, 15 Mar 2011 19:57:02 GMT

Help! I can't get my Geography type spatial data to draw. I've tried to find info on this on the forums, but no luck. I've loaded data into SDE 4 different ways.

[INDENT]1. Import in ArcCatalog using standard SDE format - Works just fine
2. Create the table in SQL Server with Geography type, register with SDE and load data in ArcCatalog
3. Create and load the table in SQL Server, then register wth SDE.
4. Import in ArcCatalog using the Geography Config Keyword[/INDENT]

Case 2, 3 and 4 the data is there in SQL Server and in ArcMap or ArcCatalog the table shows data but nothing draws.

I know that I need to use the Geography type on Sql Server because I want all my data in Geograhpic NAD83 and that is the data type that supports it.

The command I've used to register the tables with SDE

sdelayer -o register -l sde.TZS2,SHAPE -e nac+ -t GEOGRAPHY -R 3 -P HIGH -C TZS2_ID,sde -E 18.865824,-179.864760,71.434459,-66.884958 -D sde -u XXX -p XXXXXXX

Where -R 3 is the number for GG NAD83 also have tried -G 4269 instead of -R makes no difference.

I've reviewed the logs the only error that is reported is: load_buffer error -51 on TZS2, rowid 0, where TZS2 is the layer from case 3.

I've got ArcSDE 10 SP 1 64-bit installed with SQL Server 2008 r2 64-bit on a Windows Server 2008 r2 Virtual Machine.

Any help would be appreciated!

Hi Jeri Sue,

I'm wondering about your -E statement. You have

-E 18.865824,-179.864760,71.434459,-66.884958

But I think it should be

-E -179.864760,18.865824,-66.884958,71.434459

The usage is [-E <{empty | xmin,ymin,xmax,ymax}>]

In Esri terms, x = longitude, y = latitude, thus you need to swap values 1 and 2 and 3 and 4.

This may well not be the real problem, but I bet it doesn't help!

Melita

Re: MSSQL Geography Layer Not Drawing with SDE

Jeri_SuePonder — Tue, 15 Mar 2011 20:02:41 GMT

Hi Jeri Sue,

I'm wondering about your -E statement. You have

-E 18.865824,-179.864760,71.434459,-66.884958

But I think it should be

-E -179.864760,18.865824,-66.884958,71.434459

The usage is [-E <{empty | xmin,ymin,xmax,ymax}>]

In Esri terms, x = longitude, y = latitude, thus you need to swap values 1 and 2 and 3 and 4.

This may well not be the real problem, but I bet it doesn't help!

Melita

I did have the -E statement backwards, but like you said, it didn't really help. Thanks for the help!

Re: MSSQL Geography Layer Not Drawing with SDE

VinceAngelo — Tue, 15 Mar 2011 20:19:29 GMT

Can you provide the full 'sdelayer -o describe_long' output for the layers of Case1 and Case2?

Does the result change if you define the layer to have the SQL constraint "xxx_ID = 1"?
How many rows in the table?

What does a SQL query of the table return for each table (first row, if necessary).

Note that your initial assertion of "need to use the Geography type ... that is the data type that
supports it" is not completely accurate. GEOMETRY would also support the same data, but
without the single hemisphere limitation (and with a change in the result of area/length
calculations).

- V

Re: MSSQL Geography Layer Not Drawing with SDE

Jeri_SuePonder — Tue, 15 Mar 2011 20:49:14 GMT

Does the result change if you define the layer to have the SQL constraint "xxx_ID = 1"? How many rows in the table?

Does not make a difference if I defined the layer to ID = 1. There are 12 polygons that cover the US and part of Canada. This is a subset of the Timezone layer.

What does a SQL query of the table return for each table (first row, if necessary).

Query results from SQL Server Management Studio

Case1 - No Spatial Results

OBJECTID FID_North_ ZONE TIMEZONE GMT_OFFSET GMT_DST_OF Shape
1 -1 -9.00 Alaska -9.0 -8.0 1

CASE2 - Spatial Results

TIMEZONES_ID TIMEZONE_N TIMEZONE_C GMT_OFFSET GMT_DST_OF SHAPE
1 -9 Alaska -9 -8 0xAD100000001048.......

Note that your initial assertion of "need to use the Geography type ... that is the data type that
supports it" is not completely accurate. GEOMETRY would also support the same data, but
without the single hemisphere limitation (and with a change in the result of area/length
calculations).

All my data does fall in a single Hemisphere. The timezone layer covers the largest area and it is still in one hemisphere. I'm not sure how the area/length calcs would change in SQL Server using Geometry. That does concern me, because there will be people using this data that have no training or background in GIS, Projections or Geography. Unless I'm missing something, there is no reason why I shouldn't be using the Geography type.

Can you provide the full 'sdelayer -o describe_long' output for the layers of Case1 and Case2?

Results:
[INDENT] ArcSDE 10.0 for SQL Server Build 775 Fri Sep 17 11:45:27 2010
Layer   Administration Utility
-----------------------------------------------------
Layer Description ....: <None>
Database              : SDE
Table Owner ..........: SDE
Table Name ...........: CASE1
Spatial Column .......: SHAPE
Layer Id .............: 22
SRID .................: 2
Auth SRID.............: 4269
Minimum Shape Id .....: 1
Offset ...............:
falsex:       -400.000000
falsey:       -400.000000
System Units .........: 1111948722.222222
Z Offset..............:          0.000000
Z Units ..............:          1.000000
Measure Offset .......: <None>
Measure Units ........: <None>
XY Cluster Tolerance .:          0.000000008983
Spatial Index ........:
parameter:    SPIDX_GRID,GRID0=64,FULL
exist:        Yes
array form:   64,0,0
Layer Envelope .......:
minx:      -180.00000, miny:        13.48492
maxx:       -52.50598, maxy:        77.28271
Entities .............: nac+
Layer Type ...........: SDE-BINARY
Creation Date ........: 03/15/11 13:10:14
I/O Mode .............: NORMAL
Autolocking ..........: Enabled
Precision.............: High
User Privileges ......: SELECT, UPDATE, INSERT, DELETE
Coordinate System ....: GEOGCS["GCS_North_American_1983",DATUM["D_North_American_1983",SPHEROID["GRS_1980",6378137.0,298.257222101]],PRIMEM["Greenwich",0.0],UNIT["Degree",0.0174532925199433]]

Layer Configuration ..: DEFAULTS

Layer Description ....: <None>
Database              : SDE
Table Owner ..........: SDE
Table Name ...........: CASE2
Spatial Column .......: SHAPE
Layer Id .............: 25
SRID .................: 5
Auth SRID.............: 4269
Minimum Shape Id .....: 1
Offset ...............:
falsex:       -400.000000
falsey:       -400.000000
System Units .........: 1111948722.222222
Z Offset..............:          0.000000
Z Units ..............:          1.000000
Measure Offset .......: <None>
Measure Units ........: <None>
XY Cluster Tolerance .:          0.000000008983
Spatial Index ........:
parameter:    SPIDX_MSSQL
exist:        Yes
array form:   -6,0,0
Layer Envelope .......:
minx:      -180.00000, miny:        13.48492
maxx:       -52.50598, maxy:        77.28271
Entities .............: nac+
Layer Type ...........: In-Line Spatial Type/GEOGRAPHY
Creation Date ........: 03/15/11 13:20:28
I/O Mode .............: NORMAL
Autolocking ..........: Enabled
Precision.............: High
User Privileges ......: SELECT, UPDATE, INSERT, DELETE
Coordinate System ....: GEOGCS["GCS_North_American_1983",DATUM["D_North_American_1983",SPHEROID["GRS_1980",6378137.0,298.257222101]],PRIMEM["Greenwich",0.0],UNIT["Degree",0.0174532925199433]]

Layer Configuration ..: DEFAULTS[/INDENT]

Re: MSSQL Geography Layer Not Drawing with SDE

VinceAngelo — Tue, 15 Mar 2011 21:04:24 GMT

The difference in length/area is due to spherical unit awareness (e.g., not linear
decimal degrees).

It's probably time to work with Technical Support to open an incident.

- V