Latest Contributions by GeoffLau

The environment ------------------------------------- Oracle on Solaris, SDE , using st_geometry Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production PL/SQL Release 11.2.0.3.0 - Production CORE    11.2.0.3.0      Production TNS for Solaris: Version 11.2.0.3.0 - Production NLSRTL Version 11.2.0.3.0 - Production ArcSDE 10.1 The problem ------------------------------------ While testing an Oracle trigger using a table with st_geometry I found that an valid sdo_geometry, when cast to st_geometry and back becomes invalid because the polygon orientation is reversed (Oracle requires exterior polygons to have anti-clockwise orientation). I created the attached script to test the various combinations of casting using well-known-text and well-known-binary and have found that when an st_geometry is converted to an sdo_geometry using well-known-binary, the polygon orientation is reversed (anti-clockwise becomes clockwise and vice versa) This seems to me to be a bug in st_asbinary, at least as implemented in the Solaris libraries we have on our database server. Can someone please test on their environment and confirm? The critical parts of my script are the tests 8 and 9 where I construct an st_geometry with coordinates 100 100, 200 100, 200 200, 100 200, 100 100. Casting to sdo_geometry using wkt gives the correct result, but casting using wkb gives 100 100, 100 200, 200 200, 200 100, 100 100. ... View more

We have an ARCSDE 10.0 geodatabase in Oracle 11.2, using both sdo_geometry and st_geometry (in different tables). The extproc listener is configured and the st_geometry functions work correctly - for example: SDEPROD>   select sde.st_astext(shape) from cgdweb.crashes where rownum < 2; SDE.ST_ASTEXT(SHAPE) ---------------------------------------------------------------------------- POINT  ( 694076.36722689 6093035.09159664) However the xml modules do not work. Querying the gdb_items_vw view where xml blobs are mapped to clobs with the sde.sdexmltotext operator fails as shown below: SDEPROD> select definition from gdb_items_vw   2  where definition is not null; ERROR: ORA-06521: PL/SQL: Error mapping function ORA-06522: ld.so.1: extproc: fatal: SDEXML_To_Text: can't find symbol ORA-06512: at "SDE.ST_GEOMETRY_SHAPELIB_PKG", line 1339 ORA-06512: at "SDE.ST_GEOM_UTIL", line 760 Not all modules in st_geom_util fail, for example: declare   mysrid   number; begin   sde.st_geom_util.validate_geom_srid    (owner   => 'cgdweb',     table_name                  => 'crashes',     column_name   => 'shape',     srid   => mysrid);   dbms_output.put_line(mysrid); end; / 30 but the modules which seem to make external callouts do fail, including st_geom_util.sdexml_to_text_f as above, and st_geom_util.checklibraryversion and st_geom_util.getlibraryversion: SDEPROD> select st_geom_util.getlibraryversion from dual; select st_geom_util.getlibraryversion from dual        * ERROR at line 1: ORA-06521: PL/SQL: Error mapping function ORA-06522: ld.so.1: extproc: fatal: getVersion: can't find symbol ORA-06512: at "SDE.ST_GEOM_UTIL", line 646 ORA-06512: at "SDE.ST_GEOM_UTIL", line 682 Our library has cksum: $ cksum /app/oracle/db/11.2.0/lib/libst_shapelib.so 3989627067      167888  /app/oracle/db/11.2.0/lib/libst_shapelib.so Might this be a faulty library? It seems to have definitions of some functions and procedures but not others. ... View more

Environment: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production ArcSDE 10.0  for Oracle11g Using st_geometry as the spatial column datatype. I pre-create my table in sql with just my attribute columns, then use sdelayer -o add to add the objectid and st_geometry columns, using -R 30 to set the srid: sdelayer -o add -l skate_park,shape -e a -t ST_GEOMETRY -R 30 -E 640000,6000000,740000,6120000 -C objectid,SDE -P HIGH -s prdapp008 -u cgdweb -p ***** Both SRIDs 30 and 45 are defined in st_spatial_references: select srid, sr_name from st_spatial_references where srid in (30,45);       SRID SR_NAME ---------- ----------------------         30 GDA_1994_MGA_Zone_55         45 ACT Stromlo Grid After the sdelayer command, querying st_geometry_columns shows the srid has been set to 45, not 30: select column_name, geometry_type, srid from st_geometry_columns where owner = 'CGDWEB' and table_name = 'SKATE_PARK'; COLUMN_NAME                      GEOMETRY_TYPE                          SRID -------------------------------- -------------------------------- ---------- SHAPE                            ST_GEOMETRY                              45 Using sdelayer -o alter ... -P HIGH -R 30 ... makes no change. After I insert data in which the srid is set to 30 and then try to create a spatial index specifying srid 30 I get an error: create index skate_park_idx0 on cgdweb.skate_park (shape) indextype is sde.st_spatial_index parameters('st_grids=100, 0, 0 st_srid=30'); ERROR at line 1: ORA-29855: error occurred in the execution of ODCIINDEXCREATE routine ORA-20085: Parameter ST_SRID 30 is different from ST_GEOMETRY_COLUMNS srid (45). ORA-06512: at "SDE.SPX_UTIL", line 1015 ORA-06512: at "SDE.ST_DOMAIN_METHODS", line 1319 Using sdetable -o create to create the table instead of pre-creating it in sql gives the same result. Somehow srid 45 seems to be the local default! If I pre-create the table including the objectid and shape columns in sql and then use sdelayer -o register, the correct srid is set and the index creation does not error. However only some of my records show when browsing the attribute table in ArcMap and are selectable. Other records appear on the map but are not selectable. Hence my attempts to allow sdelayer to add the objectid and shape columns. Any suggestions? ... View more

As described in http://www.davidlitchfield.com/blog/archives/00000023.htm "For Oracle 11g, Oracle has introduced some security changes to the DBMS_SQL package ... A third change to DBMS_SQL is checking the user ID executing the query and the user ID of the person that parsed the query. If the two don't match an error is generated: ORA-29470: Effective userid or roles are not the same as when cursor was parsed". It seems that mainenance of a spatial index on a st_geometry column uses dbms_sql, and that updating of a st_geometry column in a trigger raises this error. One workaround is to set security back to 10g levels by setting the initialization parameter _dbms_sql_security_level = 0 but this is not acceptable. Apart from getting ESRI to revisit index maintenance, has anyone another workaround? My application allows entry of an applications for one or more services - in the simple test I have tried to devise they are rubbish bin services as in my part of Sydney - red (general rubbish), yellow (recyclable materials), green (green garden waste). The system creates an application record for each requested service, but the point geometries are arranged around the centroid of the relevant land parcel so that in a map viewer they are not exactly coincident. An operator can drag a point onto another land parcel in a map editor if the original application was found to be wrong and my trigger tries to move the points for any related applications to again be arranged around the centroid of the new land parcel. My test code is: -- create a test table to hold rubbish bin applications; requests for different bin types from -- the one applicant will be arranged around the centroid of the applicant's land parcel (parcel and -- other details are omitted here for simplicity) so that in a map viewer they are not exactly coincident drop table applications; create table applications (objectid  number(5,0) not null,   point_shape  sde.st_geometry,   application_number nvarchar2(20),   application_type nvarchar2(20)); -- insert applications for three different bin types from one applicant insert into applications values (1,sde.st_point(100,100,0),'A1','GREEN'); insert into applications values (2,sde.st_point(105,100,0),'A1','RED'); insert into applications values (3,sde.st_point(100,105,0),'A1','YELLOW'); commit; -- add indexes and constraints alter table applications add constraint app_pk primary key (objectid) using index; alter table applications add constraint app_uk unique (application_number,application_type) using index; create index app_idx0 on applications(point_shape) indextype is sde.st_spatial_index   parameters('st_grids=10, 0, 0 st_srid=0'); -- now in the map viewer/editor, an application found to be linked to the wrong land parcel can be moved -- by dragging one of the bin points to the correct land parcel; we need a trigger to move related -- bin points as well create or replace trigger app_u for update of point_shape on applications compound trigger   type num_tabtype    is table of number index by pls_integer;   type nvc20_tabtype    is table of nvarchar2(30) index by pls_integer;   type shape_tabtype    is table of sde.st_geometry index by pls_integer;   objectids     num_tabtype;   application_numbers    nvc20_tabtype;   point_shapes     shape_tabtype;   client_info     varchar2(64);   j      pls_integer;   -- cursor to find related applications whose positions will also need to be updated   cursor related_applications (p_application_number in nvarchar2, p_objectid in number) is     select       objectid     from applications     where application_number = p_application_number     and objectid <> p_objectid;   rarec      related_applications%rowtype;   ---------------------------------------------------------   before each row is     begin       -- check whether this update is the result of an application_point being dragged by a user,       -- or the result of the statement part of this trigger       dbms_application_info.read_client_info(client_info);       if nvl(client_info,'null') <> 'after app_u trigger after upd stmt part' then         -- record details of the application being updated and the new application point         objectids(nvl(objectids.last,0) + 1):= :old.objectid;         application_numbers(nvl(application_numbers.last,0) + 1):= :old.application_number;         point_shapes(nvl(point_shapes.last,0) + 1):= :new.point_shape;       end if;     end before each row;   ---------------------------------------------------------   after statement is     begin       -- check whether this update is the result of an application_point being dragged by a user,       -- or the result of the statement part of this trigger       dbms_application_info.read_client_info(client_info);       if nvl(client_info,'null') <> 'after app_u trigger after upd stmt part' then         -- this update is caused by a user moving an application point; process it         -- set some client info which can be tested to determine whether the update         -- is triggered by a user or by the 'after statement' part         dbms_application_info.set_client_info('after app_u trigger after upd stmt part');         -- loop through all updated applications         for i in application_numbers.first..application_numbers.last loop           -- check for related applications           j:= 0;           for rarec in related_applications (application_numbers(i), objectids(i)) loop             -- reposition the points near the moved point             j:= j + 1;             update applications set point_shape =               sde.st_point(sde.st_x(point_shapes(i))+mod(j,2)*5,                            sde.st_y(point_shapes(i))+mod(j-1,2)*5,                            sde.st_srid(point_shapes(i)))             where objectid = rarec.objectid;           end loop;         end loop;         -- clear the global application context         dbms_application_info.set_client_info(null);       end if;     end after statement; end app_u; / -- check the data before update column objectid heading "OBJECT|ID" format 9999 column application_number heading "APP|NUMBER" format a6 column application_type heading "APP|TYPE" format a6 column point heading "APP|POINT" format a50 select objectid, application_number, application_type, sde.st_astext(point_shape) point from applications order by 2,3; /* output is: OBJECT APP    APP    APP     ID NUMBER TYPE   POINT ------ ------ ------ --------------------------------------------------      1 A1     GREEN  POINT  ( 100.00000000 100.00000000)      2 A1     RED    POINT  ( 105.00000000 100.00000000)      3 A1     YELLOW POINT  ( 100.00000000 105.00000000) */ -- try an update with the spatial index in place begin dbms_application_info.set_client_info(null); end; / update applications set point_shape = sde.st_point(200,200,sde.st_srid(point_shape)) where objectid = 1; /* output is: update applications set point_shape = sde.st_point(200,200,sde.st_srid(point_sha pe))                                       * ERROR at line 1: ORA-29877: failed in the execution of the ODCIINDEXUPDATE routine ORA-29470: Effective userid or roles are not the same as when cursor was parsed ORA-06512: at "SYS.DBMS_SQL", line 1350 ORA-06512: at "SDE.SPX_UTIL", line 1906 ORA-06512: at "SDE.ST_DOMAIN_METHODS", line 2112 ORA-06512: at "SDE.ST_DOMAIN_METHODS", line 2239 ORA-06512: at "WEBDATA.APP_U", line 59 ORA-04088: error during execution of trigger 'WEBDATA.APP_U' */ -- try an update without the spatial index drop index app_idx0; begin dbms_application_info.set_client_info(null); end; / update applications set point_shape = sde.st_point(200,200,sde.st_srid(point_shape)) where objectid = 1; /* output is: 1 row updated. */ -- check the data after the update select objectid, application_number, application_type, sde.st_astext(point_shape) point from applications order by 2,3; /* output is: OBJECT APP    APP    APP     ID NUMBER TYPE   POINT ------ ------ ------ --------------------------------------------------      1 A1     GREEN  POINT  ( 200.00000000 200.00000000)      2 A1     RED    POINT  ( 205.00000000 200.00000000)      3 A1     YELLOW POINT  ( 200.00000000 205.00000000) */ -- clean up drop table applications; ... View more

A colleague has a new polygon dataset which will not draw in ARCCatalog. I have subset the dataset to 10 rows in Oracle, created a spatial index, inserted a sdo_geom_metada record with the correct srid and appropriate extents, registered the table with sdelayer (-e a+), and altered the layer to set the projection (-G file=c:\co-ord\gda94.prj) and the envelope (-E calc). When I open the layer in ARCCatalog nothing displays. When I pan the drawing I get errors like "The selected object failed to draw. (?)The layer(?) has no integer rowid or spatial column". (I don't have access to the work environment at the moment so I'm unsure of the exact wording). In another test I replaced a geometry in the non-drawing layer with one from another drawable layer using the same projection. In this case the layer displayed the new object but panning the drawing gave a different error "The given coordinate references are incompatible" - which I don't understand as the coordinates, the projection and the srid are the same for both layers. I've checked the users_sdo_geom_metadata contents, the sdo_geometry srid (82474 in Oracle, 1894 in ESRI sdelayer -o describe_long), the sdo_geometry gtype (2003) and the sdo_geometry elem_info etype (1003). Oracle's validate_layer_with_context reports no errors. In my software environment (SDE 8.3, Oracle 9i, ARCCatalog 9.2) I don't seem to have any ESRI diagnostic tools, I can't find any client-side esri logfiles, and I don't have access to server-side logfiles. What more can I do to track down the problems with this layer? ... View more