Spatial Reference Issue - SDE backwards compatability from 9.2 to 8.3

[m***@sonoma-county.org]

Can't get ArcSDE 9.2 data in the same DB2 spatial reference as existing ArcSDE 9.1 or ArcSDE 8.3 data. Backwards compatablity is a requirement while building our new 9.2 system. We are migrating from 8.3 & 9.1 to 9.2. Attempted to do an ST_TRANSFORM on data to transform spatial reference to current reference received an error as follows:

insert into tra_streets_transform (objectid,fraddl,fraddr,toaddl,toaddr,pre_dir,
st_name,st_TYPE,SUF_DIR,SOURCE_,L_ZIP,R_ZIP,VERIFY_ACY,VERIFY_SRC,SEGID,MUNL,MUN
R,SPEEDLIMIT,SPEEDSRC,STRCLASS,PUBPRIV,SPAT_ACCY,COM,BLOCKAGE,SHAPE) select obje
ctid, coalesce(fraddl,0), coalesce(fraddr,0), coalesce(toaddl,0), coalesce(toadd
r,0), coalesce(pre_dir,''), coalesce(st_name,''), coalesce(st_type,''), coalesce
(suf_dir,''), coalesce(source_,''), coalesce(l_zip,''), coalesce(r_zip,''), coal
esce(verify_acy,''), coalesce(verify_src,''), segid, coalesce(munl,'unk'), coale
sce(munr,'unk'), coalesce(speedlimit,25), coalesce(speedsrc,''), coalesce(strcla
ss,''), coalesce(pubpriv,''), coalesce(spat_accy,''), coalesce(com,''), coalesce
(blockage,0), db2gse.st_transform(shape,1006) from edit.tra_streets92


DB21034E The command was processed as an SQL statement because it was not a
valid Command Line Processor command. During SQL processing it returned:
SQL0408N A value is not compatible with the data type of its assignment
target. Target name is "SHAPE". SQLSTATE=42821

Thanks in advance,

Michelle & Shawna

[Knut Stolze]

Post by m***@sonoma-county.org
Can't get ArcSDE 9.2 data in the same DB2 spatial reference as existing
ArcSDE 9.1 or ArcSDE 8.3 data. Backwards compatablity is a requirement
while building our new 9.2 system. We are migrating from 8.3 & 9.1 to
9.2. Attempted to do an ST_TRANSFORM on data to transform spatial
insert into tra_streets_transform
(objectid,fraddl,fraddr,toaddl,toaddr,pre_dir,

st_name,st_TYPE,SUF_DIR,SOURCE_,L_ZIP,R_ZIP,VERIFY_ACY,VERIFY_SRC,SEGID,MUNL,MUN

Post by m***@sonoma-county.org
R,SPEEDLIMIT,SPEEDSRC,STRCLASS,PUBPRIV,SPAT_ACCY,COM,BLOCKAGE,SHAPE)
select obje ctid, coalesce(fraddl,0), coalesce(fraddr,0),
coalesce(toaddl,0), coalesce(toadd r,0), coalesce(pre_dir,''),
coalesce(st_name,''), coalesce(st_type,''), coalesce (suf_dir,''),
coalesce(source_,''), coalesce(l_zip,''), coalesce(r_zip,''), coal
esce(verify_acy,''), coalesce(verify_src,''), segid, coalesce(munl,'unk'),
coale sce(munr,'unk'), coalesce(speedlimit,25), coalesce(speedsrc,''),
coalesce(strcla ss,''), coalesce(pubpriv,''), coalesce(spat_accy,''),
coalesce(com,''), coalesce (blockage,0), db2gse.st_transform(shape,1006)
from edit.tra_streets92

Do you really need all the COALESCE? I would assume that someone thought
about the data model and where to allow/use NULLs. You are discarding all
those NULLs.

Post by m***@sonoma-county.org
DB21034E The command was processed as an SQL statement because it was not a
SQL0408N A value is not compatible with the data type of its assignment
target. Target name is "SHAPE". SQLSTATE=42821

What's the static data type of columns EDIT.TRA_STREETS92.SHAPE and
TRA_STREETS_TRANSFORM.SHAPE? For example, if you have a column type of
ST_Polygon in the target table, you can't insert the results of
ST_Transform without casting/treating it because ST_Transform returns
values with a static type ST_Geometry.

Also, it can happen that ST_Transform() converts a single-part geometry into
a multi-part one due to the transformation/projection algorithm. That is
normal. Thus, ST_Transform() may produce a geometry with dynamic type
ST_MultiPolygon from an ST_Polygon value.