topic Re: force shp2sde follow database sequencer for ID column - with database mapping in Data Management Questions

force shp2sde follow database sequencer for ID column - with database mapping

anikpal — Thu, 18 Jul 2013 05:02:15 GMT

Hello World,
We are facing following issue when try to load polygon data into seabed Area using shp2sde utility. When in append mode it�??s not taking AREA.ID value from SDS_ID_SEQ, rather taking value from shapefile ID column. In Create mode it works fine.

./shp2sde -o append -l area_,shape -f /home/users/install/tmp/shpfile/AFG_adm0.shp -a file=/home/users/install/tmp/shpfile/mapping_attribute.txt -i esri_sde -u prod -p prod

Using above command to load shpfile data to area layer.

When I specify �??a file=mapping.txt then id column in area layer taking the objectid of the shapefile and
when I specify �??a none, then id in area filled with sequence in oracle.

Is there any way that I specify �??a file=mapping.txt then id of area should take from sequence in oracle.

Mapping file contain: LEASE_NUMB NAME STRING 255

Format of mapping columns: <shpCol> [sdeCol] [type] [size] [nDecs] [NOT_NULL]

LEASE_NUMB---column from shape file
NAME------column from area layer
STRING----data type of name column

Thank you,

Regards
Anik Pal

Re: force shp2sde follow database sequencer for ID column - with database mapping

VinceAngelo — Thu, 18 Jul 2013 11:24:32 GMT

Welcome to the Esri Forums. Please remember to always include version
information for ArcGIS and database software with each new thread.

It's impossible to tell what should be happening without knowing how you
created the layer and what its current properties are. 'sdetable -o describe'
'sdetable -o describe_reg', and 'sdelayer -o describe_long' output would go
a long way toward understanding what's going on. The complete contents
of the mapping.txt file would help as well.

- V

Re: force shp2sde follow database sequencer for ID column - with database mapping

anikpal — Mon, 19 Aug 2013 08:09:19 GMT

I am using Arcsde 10.1 in linux

I am using a commercial s/w that came with this layers created Name "Area" and definition of ARea [ATTACH=CONFIG]26784[/ATTACH]

Sde registry of ARea Layers is attached

Sdelayer-o describe of ARea layer below

Created by layer gen
----------------------------------------------------------------
Table Owner        : PDD
Table Name         : AREA
Spatial Column     : SHAPE
Layer id           : 1114
Entities           : na3+M
Layer Type         : Extended SQL Type
I/O Mode           : NORMAL
Autolocking        : Enabled
Precision          : High
User Privileges    : SELECT
Layer Configuration: DEFAULTS

sdetable -o describe_reg of ARea layer is as below

----------------------------------------------------------------
Table Owner            : PDD
Table Name             : AREA
Registration Id        : 1119
Row ID Column          : ID
Row ID Column Type     : User Maintained
Row Lock               :
Minimum Row ID         :
Dependent Objects      : Layer, View
Registration Date      : Mon Aug 19 08:52:20 2013
Config. Keyword        : DEFAULTS
User Privileges        : SELECT, UPDATE, INSERT, DELETE
Visibility             : Visible

followins out of sdelayer -o describe_long' for lAyer Area

Layer Description ....: Created by layer gen
Table Owner ..........: PDD
Table Name ...........: AREA
Spatial Column .......: SHAPE
Layer Id .............: 1114
SRID .................: 4
Auth SRID.............: 4
Minimum Shape Id .....: 1
Offset ...............:
falsex:       -180.000000
falsey:        -90.000000
System Units .........:    1000000.000000
Z Offset..............:    -100000.000000
Z Units ..............:      10000.000000
Measure Offset .......:    -100000.000000
Measure Units ........:      10000.000000
XY Cluster Tolerance .:          0.0
Z Cluster Tolerance .:          0.0
M Cluster Tolerance .:          0.0
Spatial Index ........:
parameter:    SPIDX_GRID,GRID0=10,FULL
exist:        Yes
array form:   10,0,0
Layer Envelope .......: <EMPTY>
Entities .............: na3+M
Layer Type ...........: Extended SQL Type/ST_GEOMETRY
Creation Date ........: Mon Aug 19 08:52:21 2013
I/O Mode .............: NORMAL
Autolocking ..........: Enabled
Precision.............: High
User Privileges ......: SELECT
Coordinate System ....: GEOGCS["GCS_Nahrwan_1967",DATUM["D_Nahrwan_1967",SPHEROID["Clarke_1880_RGS",6378249.145,293.465]],PRIMEM["Greenwich",0.0],UNIT["Degree",0.0174532925199433]]

Layer Configuration ..: DEFAULTS

Mapping_attribute.txt file is attached. which maches col from incoming columns with database.

Regards
Anik Pal UAE

Re: force shp2sde follow database sequencer for ID column - with database mapping

anikpal — Mon, 19 Aug 2013 08:46:45 GMT

I am using Arcsde 10.1 in linux

I am using a commercial s/w that came with this layers created Name "Area" and definition of ARea
[ATTACH=CONFIG]26787[/ATTACH]

Sde registry of ARea Layers is attached

Sdelayer-o describe of ARea layer below

Created by layer gen
----------------------------------------------------------------
Table Owner        : TEST
Table Name         : AREA
Spatial Column     : SHAPE
Layer id           : 1114
Entities           : na3+M
Layer Type         : Extended SQL Type
I/O Mode           : NORMAL
Autolocking        : Enabled
Precision          : High
User Privileges    : SELECT
Layer Configuration: DEFAULTS

sdetable -o describe_reg of ARea layer is as below

----------------------------------------------------------------
Table Owner            : TEST
Table Name             : AREA
Registration Id        : 1119
Row ID Column          : ID
Row ID Column Type     : User Maintained
Row Lock               :
Minimum Row ID         :
Dependent Objects      : Layer, View
Registration Date      : Mon Aug 19 08:52:20 2013
Config. Keyword        : DEFAULTS
User Privileges        : SELECT, UPDATE, INSERT, DELETE
Visibility             : Visible

followins out of sdelayer -o describe_long' for lAyer Area

Layer Description ....: Created by layer gen
Table Owner ..........: TEST
Table Name ...........: AREA
Spatial Column .......: SHAPE
Layer Id .............: 1114
SRID .................: 4
Auth SRID.............: 4
Minimum Shape Id .....: 1
Offset ...............:
falsex:       -180.000000
falsey:        -90.000000
System Units .........:    1000000.000000
Z Offset..............:    -100000.000000
Z Units ..............:      10000.000000
Measure Offset .......:    -100000.000000
Measure Units ........:      10000.000000
XY Cluster Tolerance .:          0.0
Z Cluster Tolerance .:          0.0
M Cluster Tolerance .:          0.0
Spatial Index ........:
parameter:    SPIDX_GRID,GRID0=10,FULL
exist:        Yes
array form:   10,0,0
Layer Envelope .......: <EMPTY>
Entities .............: na3+M
Layer Type ...........: Extended SQL Type/ST_GEOMETRY
Creation Date ........: Mon Aug 19 08:52:21 2013
I/O Mode .............: NORMAL
Autolocking ..........: Enabled
Precision.............: High
User Privileges ......: SELECT
Coordinate System ....: GEOGCS["GCS_Nahrwan_1967",DATUM["D_Nahrwan_1967",SPHEROID["Clarke_1880_RGS",6378249.145,293.465]],PRIMEM["Greenwich",0.0],UNIT["Degree",0.0174532925199433]]

Layer Configuration ..: DEFAULTS

Mapping_attribute.txt file is attached. which maches col from incoming columns with database.

Regards
Anik Pal

Re: force shp2sde follow database sequencer for ID column - with database mapping

VinceAngelo — Mon, 19 Aug 2013 10:55:19 GMT

The 'sdetable -o describe' output was the most important piece of the puzzle;
you provided 'sdelayer -o describe' (which is a subset of describe_long) instead.

You seem to have attached a binary file instead of including the mapping file
as text (I don't open binary attachments).

When you have a USER-set NOT NULL rowid column in a table, 'shp2sde'
assumes you will be providing the value as part of the load. I don't know of
any way to hide the rowid as part of load. You could load with '-o init' into
another layer with an SDE-set rowid column, then use an SQL statement to
copy from the clone to the production table, allowing the sequence to fire.
Or, if only 'shp2sde' is used for loading, you could change the rowid to SDE-set.

- V

Re: force shp2sde follow database sequencer for ID column - with database mapping

anikpal — Thu, 22 Aug 2013 11:15:04 GMT

Hi
following is the content for mapping file

LEASE_NUMB NAME STRING 255
LEASE_ ID NUMBER 38

and sample file like as follow

AREA PERIMETER LEASE_ LEASE_ID LEASE_NUMB SALE_NUMBE
0.00003505696 0.05324608376 2 1 G05057 67
0.00201875943 0.18091138300 3 2 G05056 67
0.00000028353 0.05156744450 4 0
0.00208263540 0.23331615268 5 4 G05057 67
0.00008860368 0.10276651698 6 5 G19873 169

question 1. is there any way that I can specify database sequencer for row id either fromcommand line or via the mapping text?
2. or is there some way to change the table definition of Area so that it always take row_id from sequencer

regards
Anik

Re: force shp2sde follow database sequencer for ID column - with database mapping

VinceAngelo — Thu, 22 Aug 2013 11:25:52 GMT

1. No
2. No

I offered a viable work-around with my previous post.

- V