I've created quite a few spatially enabled views of SDE feature classes. Pointview1 is a point table in the databse hps_gis that I am trying to make spatial. Here is a sample command that works. I am probably missing something obvious.
Thanks for all your help! I'll let you know if and when I figure this out. I wonder why ESRI doesn't incorporate this function in their web products...
If you would like to refer to this comment somewhere else in this project, copy and paste the following link:
ArcSDE should support arbitrary coordinates, though it helps to specify a projection (e.g., with -G).
As for -x, you could use rounder values, but this should work:
-x 1433736.8,381870.39,100
Real-world coordinates will be translated into internal coordinates by subtracting 1433736.8 from the x-values and 381870.39 from the y-values, then multiplying by 100.
Internal coordinates must be non-negative integers within the range 0 - 2,147,483,648 (2^31). You've got a little room to play with your stateplane coordinates so an easier to type -x would be:
-x 10000000,1000000,100
Bear in mind that if you are bringing more than one layer into ArcSDE and you want to overlay them, life will be simpler if you use the same -x for all of them -- so find the "lowest common denominator" and use the same scale and offsets for all your related layers.
If this doesn't work please include your dbms2sde command-line and error response.
Cheers,
Mark
If you would like to refer to this comment somewhere else in this project, copy and paste the following link:
Maybe if I specify a projection it will take my -xscale. I've tried both of your suggestions but they haven't worked so far. Where can I find the text file listing the projections? The projection is State Plane, NAD 27, Zone 3, feet. Here is all the command information:
DBMS2SDE alpha 0.342 May 23 2003 - an ArcSDE 8.2 application
For fastest performance, use -stable, -load, and -buffer.
Prior spatial layer; ignoring -x 10000000.000000,10000000.000000,100.000000
-- Using -200.000000,-110.000000,1000000.000000
Summary information for rows being spatially enabled:
Disagreement in row count: 10981 versus 0
Key column(s) is not unique -- 0 duplicate rows may have incorrect shapes.
Rows Enabled Elapsed Time Estimated Time
------------ ------------ --------------
X-coordinate does not convert to ArcSDE internal storage format.
x-coordinate is 1460651.8000000000 and converts to 1460851800000, but must be an integer between 0 and 21474836
47.
Try adjusting x offset (-200.0000000000) and/or scale (1000000.0000000000)
Y-coordinate does not convert to ArcSDE internal storage format.
y-coordinate is 453829.0600000000 and converts to 453939060000, but must be an integer between 0 and 2147483647
.
Try adjusting y offset (-110.0000000000) and/or scale (1000000.0000000000)
Failed attempt to spatially enable where "PTKEY=1020698 and (pointview1.EASTING > 1000)"
SE_shape_generate_point encountered a -139 error:
The specified coordinate exceeds the valid coordinate range
Thanks again,
Kurt
If you would like to refer to this comment somewhere else in this project, copy and paste the following link:
Look in pedef.h, but I only found NAD 1987 State Plane projections in meters.
As for your dbms2sde error, the problem is that you originally spatially enabled the layer with the default x/y scale and offsets of -200,-110,1000000 (appropriate for lat/lon) and that until you delete the spatial layer (i.e., sdelayer -o delete) you are UPDATING an EXISTING spatial layer and cannot change the coordinate reference system.
The (buried) text in the error response indicates this:
Prior spatial layer; ignoring -x 10000000.000000,10000000.000000,100.000000
-- Using -200.000000,-110.000000,1000000.000000
So simply run sdelayer -o delete and then run dbms2sde again.
Good luck, and let me know how it goes.
If you would like to refer to this comment somewhere else in this project, copy and paste the following link:
It's getting better all the time Mark. I didn't realize the command was creating a layer all the other times.
I did read somewhere that projection information was in the file you mentioned but I have not been able to locate the file called pedef.h. I gues that will be my next step because I'm getting an invalid grid size error.
pedef.h might be in something like C:\arcgis\ArcSDE8.2\include or /usr/local/esri/sdeexe82/include/
The default grid size is -g 1,3,9 (run dbms2sde -h and look for the description of -g). This is okay for lat/lon (which is mostly what I see) but is certainly too fine a grid for state plain coordinates. Try something big like -g 1000,3000,9000 or more.
Keep the comments and questions coming!
If you would like to refer to this comment somewhere else in this project, copy and paste the following link:
The answer in both cases is 10981. I guess I don't my where clause after all. So I made the change and tried again only to find another error message. One day this will work and we'll have a dynamic point SDElayer. Here is the command dump for your amusement:
For fastest performance, use -stable, -load, and -buffer.
SE_stream_query encountered a -51 error:
Underlying DBMS error
Extended error code: 308, extended error string:
Microsoft OLE DB Provider for SQL Server: Deferred prepare could not be completed.
If you would like to refer to this comment somewhere else in this project, copy and paste the following link:
My current command line causes this connection:
SDE error code: -1
DBMS or OS errno code: 0
What I would really like to do is create a new spatially enabled table from a bussiness table.
Also, if you use a -key how does the command know where to pull the EASTINGS and NORTHINGS?
Can someone point me to a good source of help on this utility that Mr. Ohrenscall created? I'm having a few problems understanding the proper syntax.
Thanks!
Have you looked at http://sourceforge.net/docman/?group_id=58951 ?
The -key option is used on a primary key column (in addition to -xname and -yname).
Can you post your command line (w/o username or password, naturally)?
Thanks for responding Mark.
Here is my command line. I've been messing around with trying different combinations but I keep getting the same error.
dbms2sde -o point -l gis_user.pointview1,geometry -xname EASTING -yname NORTHING -key PTKEY -s hpsdb -u ***** -p *******
Are you able to run any SDE command, e.g., sdelayer -o describe?
Yes,
I've created quite a few spatially enabled views of SDE feature classes. Pointview1 is a point table in the databse hps_gis that I am trying to make spatial. Here is a sample command that works. I am probably missing something obvious.
sdetable -o describe -D hps_gis -t master_point_view -u gis_user -p *********
I hope it's obvious because I'm at a loss as to what else to suggest! ;-)
But I notice you're using the -D option with sdetable, but not with dbms2sde. Try the -D option with dbms2sde and let me know what happens.
Thanks again Mark,
Here is what the table looks like.
sdetable -o describe -D hps_gis -t pointview1 -u gis_user -p *********
Table pointview1:
Column name Attribute type Null? Length,DPs
----------------------------------------------------------------
PT_ID SE_STRING NOT NULL 15
PT_TYPE SE_STRING NOT NULL 5
EXCAVATION SE_STRING NULL 10
LOC_ACC SE_STRING NOT NULL 11
NORTHING SE_DOUBLE NOT NULL 10,2
EASTING SE_DOUBLE NOT NULL 10,2
GS_ELEV_FT SE_FLOAT NULL 5,2
GS_REF SE_STRING NULL 5
ABANDON_DT SE_DATE NULL 0
PT_COMM SE_STRING NULL 100
PTKEY SE_INTEGER NOT NULL 7
LOCKEY SE_INTEGER NOT NULL 7
PARCEL SE_STRING NULL 3
IRSITE SE_STRING NULL 12
REUSE SE_STRING NULL 30
ZONING SE_STRING NULL 16
RES_GRID SE_STRING NULL 6
IND_GRID SE_STRING NULL 4
And here is my current statement for dbms2sde. Any final comments before I sign off this string.
dbms2sde -o point -l pointview1,geometry -xname EASTING -yname NORTHING -key PTKEY -D hps_gis -s HPSDB -u gis_user -p *********
Thanks for all your help! I'll let you know if and when I figure this out. I wonder why ESRI doesn't incorporate this function in their web products...
Hey Mark,
I figured this out. It was a 8.2 issue.
Can you help me figure out my offset and scale now? I'm using stateplane coordinates. Is that even possible? Everything I put dbms2sde doesn't like.
Left 1433736.8
right 1530459.7
top 521603.36
bottom 381870.39
What do you think?
Kurt
ArcSDE should support arbitrary coordinates, though it helps to specify a projection (e.g., with -G).
As for -x, you could use rounder values, but this should work:
-x 1433736.8,381870.39,100
Real-world coordinates will be translated into internal coordinates by subtracting 1433736.8 from the x-values and 381870.39 from the y-values, then multiplying by 100.
Internal coordinates must be non-negative integers within the range 0 - 2,147,483,648 (2^31). You've got a little room to play with your stateplane coordinates so an easier to type -x would be:
-x 10000000,1000000,100
Bear in mind that if you are bringing more than one layer into ArcSDE and you want to overlay them, life will be simpler if you use the same -x for all of them -- so find the "lowest common denominator" and use the same scale and offsets for all your related layers.
If this doesn't work please include your dbms2sde command-line and error response.
Cheers,
Mark
Thanks Mark,
Maybe if I specify a projection it will take my -xscale. I've tried both of your suggestions but they haven't worked so far. Where can I find the text file listing the projections? The projection is State Plane, NAD 27, Zone 3, feet. Here is all the command information:
DBMS2SDE alpha 0.342 May 23 2003 - an ArcSDE 8.2 application
Running: dbms2sde -o point -l pointview1,geometry -x 10000000.000000,10000000.000000,100.000000 -xname EASTING
-yname NORTHING -w "pointview1.EASTING > 1000" -key PTKEY -D hps_gis -s HPSDB -u gis_user -p *********
For fastest performance, use -stable, -load, and -buffer.
Prior spatial layer; ignoring -x 10000000.000000,10000000.000000,100.000000
-- Using -200.000000,-110.000000,1000000.000000
Summary information for rows being spatially enabled:
Column Count Minimum Maximum Average Std. Dev.
-------- ----- ---------- ---------- ---------- ---------
EASTING 10981 1433736.80 1530459.70 1460622.38 2132.38
NORTHING 10981 381870.39 521603.36 451929.98 2418.61
PTKEY 10981 1013034 1030241 1021238.69 4819.12
Disagreement in row count: 10981 versus 0
Key column(s) is not unique -- 0 duplicate rows may have incorrect shapes.
Rows Enabled Elapsed Time Estimated Time
------------ ------------ --------------
X-coordinate does not convert to ArcSDE internal storage format.
x-coordinate is 1460651.8000000000 and converts to 1460851800000, but must be an integer between 0 and 21474836
47.
Try adjusting x offset (-200.0000000000) and/or scale (1000000.0000000000)
Y-coordinate does not convert to ArcSDE internal storage format.
y-coordinate is 453829.0600000000 and converts to 453939060000, but must be an integer between 0 and 2147483647
.
Try adjusting y offset (-110.0000000000) and/or scale (1000000.0000000000)
Failed attempt to spatially enable where "PTKEY=1020698 and (pointview1.EASTING > 1000)"
SE_shape_generate_point encountered a -139 error:
The specified coordinate exceeds the valid coordinate range
Thanks again,
Kurt
Hi Kurt,
Look in pedef.h, but I only found NAD 1987 State Plane projections in meters.
As for your dbms2sde error, the problem is that you originally spatially enabled the layer with the default x/y scale and offsets of -200,-110,1000000 (appropriate for lat/lon) and that until you delete the spatial layer (i.e., sdelayer -o delete) you are UPDATING an EXISTING spatial layer and cannot change the coordinate reference system.
The (buried) text in the error response indicates this:
Prior spatial layer; ignoring -x 10000000.000000,10000000.000000,100.000000
-- Using -200.000000,-110.000000,1000000.000000
So simply run sdelayer -o delete and then run dbms2sde again.
Good luck, and let me know how it goes.
It's getting better all the time Mark. I didn't realize the command was creating a layer all the other times.
I did read somewhere that projection information was in the file you mentioned but I have not been able to locate the file called pedef.h. I gues that will be my next step because I'm getting an invalid grid size error.
Running: dbms2sde -o point -l pointview1,geometry -x 1000000.000000,10000000.000000,100.000000 -xname EASTING
-yname NORTHING -w "pointview1.EASTING > 1000" -key PTKEY -D hps_gis -s HPSDB -u gis_user -p *********
For fastest performance, use -stable, -load, and -buffer.
set_layerinfo encountered a -33 error:
Invalid grid size
I get the same error for the other suggested -xscale you mentioned too.
I'm not giving up on this. Please keep helping if you have the time and patience. :-)
I'm glad it's going better.
pedef.h might be in something like C:\arcgis\ArcSDE8.2\include or /usr/local/esri/sdeexe82/include/
The default grid size is -g 1,3,9 (run dbms2sde -h and look for the description of -g). This is okay for lat/lon (which is mostly what I see) but is certainly too fine a grid for state plain coordinates. Try something big like -g 1000,3000,9000 or more.
Keep the comments and questions coming!
Thanks - Still making progress. Got rid of the grid problem and got a permission problem. Here is what I got:
E:\gis_files>dbms2sde82 -o point -l pointview1,geometry -g 1000,3000,9000 -x 1433736.8,381870.39,100 -xname EAS
TING -yname NORTHING -key PTKEY -w "pointview1.EASTING > 1000" -D hps_gis -s HPSDB -u gis_user -p gis_user1
DBMS2SDE alpha 0.342 May 23 2003 - an ArcSDE 8.2 application
Running: dbms2sde -o point -l pointview1,geometry -g 1000.000000,3000.000000,9000.000000 -x 1433736.800000,381
870.390000,100.000000 -xname EASTING -yname NORTHING -w "pointview1.EASTING > 1000" -key PTKEY -D hps_gis -s HP
SDB -u gis_user -p *********
For fastest performance, use -stable, -load, and -buffer.
Summary information for rows being spatially enabled:
Column Count Minimum Maximum Average Std. Dev.
-------- ----- ---------- ---------- ---------- ---------
EASTING 10981 1433736.80 1530459.70 1460622.38 2132.38
NORTHING 10981 381870.39 521603.36 451929.98 2418.61
PTKEY 10981 1013034 1030241 1021238.69 4819.12
Disagreement in row count: 10981 versus 0
Key column(s) is not unique -- 0 duplicate rows may have incorrect shapes.
Rows Enabled Elapsed Time Estimated Time
------------ ------------ --------------
SE_stream_update_table encountered a -25 error:
Insufficient permissions
I checked pointview1 out in SQL and I have full rights to that table. Any more ideas?
Kurt
Hi Kurt,
I'm not sure about the permissions problem yet, but could you execute the following two SQL statements against your database and tell me the results?
select count(*) from pointview1 where pointview1.EASTING > 1000;
select count(distinct(PTKEY)) from pointview1 where pointview1.EASTING > 1000;
Thanks,
Mark
Hi Mark,
The answer in both cases is 10981. I guess I don't my where clause after all. So I made the change and tried again only to find another error message. One day this will work and we'll have a dynamic point SDElayer. Here is the command dump for your amusement:
E:\gis_files>dbms2sde82 -o point -l pointview1,geometry -g 1000,3000,9000 -x 1433736.8,381870.39,100 -xname
TING -yname NORTHING -key PTKEY -D hps_gis -s HPSDB -u gis_user -p gis_user1
DBMS2SDE alpha 0.342 May 23 2003 - an ArcSDE 8.2 application
Running: dbms2sde -o point -l pointview1,geometry -g 1000.000000,3000.000000,9000.000000 -x 1433736.800000,
870.390000,100.000000 -xname EASTING -yname NORTHING -key PTKEY -D hps_gis -s HPSDB -u gis_user -p *********
For fastest performance, use -stable, -load, and -buffer.
SE_stream_query encountered a -51 error:
Underlying DBMS error
Extended error code: 308, extended error string:
Microsoft OLE DB Provider for SQL Server: Deferred prepare could not be completed.