Why ArcSDE?

This posting lists some interesting points why using ArcSDE:

First, one of the primary functions of ArcSDE is that it provides the connectivity to the DBMS; meaning that the out-of-the-box ArcGIS Desktop clients, ArcIMS and ArcEngine and ArcGIS Server development environments don't need to know which DBMS you are working with or how the data is stored in the database.

Second, ArcSDE enables ArcIMS to scale by managing access by many users to subsets of a large, continuous data set.

Third, ArcSDE combined with ArcCatalog provides the mechanism to load and maintain data in an enterprise geodatabase (which is a geodatabase stored in a relational DBMS like Oracle, SQL Server, DB2, Informix).

Fourth, ArcSDE with ArcIMS provides ArcIMS Metadata Services.

Fifth, ArcSDE provides pyramiding for improved raster/image data access.

Sixth ArcSDE provides the transaction model that is required for a multi-user editing environment (long transaction, versions, disconnected editing, geodatabase replication (at 9.2), and history archiving (at 9.2)).

Lastly, ArcSDE provides low level data integrity checks (check that polygons are closed, that lines don't intersect themselves, etc) that complement the higher level data integrity checks of the geodatabase (relationships, domains, topology, traversable networks, etc.).

When feature classes should not be put into a feature dataset?

In general, it is a bad idea using feature dataset to group feature classes for the following reasons:

1) While accessing just one feature class, the whole feature dataset is locked.

2) When one feature class in the feature dataset is used, all feature classes in the feature dataset will be cached, not just the layer being using.

3) Don't put any layer/ feature class into a feature dataset unless you need it for geodatabase functionality (e.g. geometric network, topology, etc.)

Show friendly time in ArcSDE system tables

I came across a posting about time conversion in ArcSDE system tables in ESRI Support web site:

http://support.esri.com/index.cfm?fa=knowledgebase.techarticles.articleShow&d=25700

In the ArcSDE system tables, the dates are stored as a number that represents the time when the table was registered, or the layer was created, in seconds since 1970.

SELECT registration_id, table_name, OWNER, TO_CHAR(NEW_TIME(TO_DATE('1970-01-01', 'YYYY-MM-DD'),'GMT','PDT') + registration_date / 86400.0, 'Month DD, YYYY HH:MI:SS am')
FROM sde.table_registry;

SELECT LAYER_id, table_name, OWNER, TO_CHAR(NEW_TIME(TO_DATE('1970-01-01', 'YYYY-MM-DD'),'GMT','PDT') + CDATE / 86400.0, 'Month DD, YYYY HH:MI:SS am')
FROM sde.LAYERS;

Calculate free space of a tablespace

The following script calculates the free space of a tablespace in an Oracle database:

SELECT TABLESPACE_NAME,
(SUM(BYTES)/1024) FREE_KB,
(SUM(BYTES)/(1024*1024)) FREE_MB
FROM DBA_FREE_SPACE
GROUP BY TABLESPACE_NAME;

DBA_FREE_SPACE describes the free extents in all tablespaces in the database.

Shape Validation in ArcSDE 9.1

ArcSDE maintains valid shapes internally by applying a set of rules to each shape type. All ArcSDE commands that create or update shapes use these rules.

Verification rules for point shapes are:
- The area and length of points are set to 0.0.
- A single point's envelope is equal to the point's x,y values.
- The envelope of a multipart point shape is set to the minimum bounding box.

Verification rules for simple lines, or linestring shapes, are:
- Sequential duplicate points are removed.
- Each part must have at least two distinct points.
- Each part may not intersect itself. The start and endpoints may be the same, but the resulting 'ring' is not treated as an area shape.
- Parts may touch each other at the endpoints.
- The length is the sum of all the parts.

Verification rules for lines, or spaghetti shapes, are:
- Lines can intersect themselves.
- Each part must have at least two distinct points.
- Sequential duplicate points are deleted.
- The length is the length of all of its parts added together.

Verification rules and operations on area shapes are:
- Delete duplicate sequential occurrences of a coordinate point.
- Delete dangles.
- Verify that the line segments close (z coordinates at start and endpoints must also be the same) and don't cross.
- Correct rotation to counterclockwise (see the previous section for an explanation of how ArcSDE stores area shapes).
- For area shapes with holes, ensure that holes reside wholly inside the outer boundary. ArcSDE eliminates any holes that are outside the outer boundary.
- Convert a hole that touches an outer boundary at a single common point into an inversion of the area shape.
- Combine multiple holes that touch at common points into a single hole.
- Multipart area shapes may not overlap. However, two parts may touch at a point.
- Multipart area shapes may not share a common boundary. Common boundaries are dissolved.
- If two rings have a common boundary, they are merged into one ring.
- Calculate the total geometry perimeter, including the boundaries of all holes in donut polygons, and store the perimeter as the length of the geometry.
- Calculate the area.
- Calculate the envelope.

How to obtain information about the geometry of the features in a layer?

Applies to: ArcSDE 9.1, 9.2

“sdelayer –o feature_info” included in ArcSDE package can be used to get information about the geometry of a feature.

The syntax for the command is as follows:

sdelayer -o feature_info -l [-V ] [-r {valid all invalid}] [-w <"where_clause">] [-c] [Spatial_Index] [-S ] [-q] [-i ] [-s ] [-D ] [-u [-p ] [-q]

“-o feature_info” operation reports information about the feature, such as shape validity, measurements and extent information, the FID, the presence of annotation, whether the feature contains CAD data, the presence of inclusions or cojoined inner rings, the minimum precision of the layer, the number of points, parts and subparts contained in the feature.

-l : the layer name and its spatial column name.

-r {valid all invalid}: Specifies if only valid shapes are read or all shapes are read. If all shapes read, error returned to indicate invalid shapes. If invalid is specified, only features with invalid geometry are returned with error code.

-w: Where clause
! -w "a = 1" double quote ".
! -w 'a = 1' wrong. ArcSDE error -42 (SE_INVALID_SQL) will be reported in the SDE error log file:
[03/13/2007 16:07:24;SdeId=33433;Client=Client_PC] load_buffer error -42

See here for the meaning of ArcSDE error code.

“sdelayer -o feature_info” operation returns up to 21 different characteristics of each feature in the layer. These characteristics are presented in a series of comma-delimited fields. Fields are returned in the following order:
1. Row ID (Integer) - The row ID of the table containing the spatial column. If the table does not have an ArcSDE or user-maintained row ID column (usually objectid), the Feature ID (FID) column value is returned here instead. If it has neither row ID nor FID, the value returned is 0.
2. FID (Integer) - The Feature ID of the shape. If there is no FID, the row ID is returned instead. If there is neither row ID nor FID, 0 is returned.
3. Entity Type (one character) - A single character indicating the entity type, either N (Nil), P (Point), S (Simple), L (Line), or A (Area).
4. Annotation (Boolean) - Indicates whether or not the shape has ArcSDE annotation. Values are either T (true) or F (false).
5. CAD Data (Boolean) - Indicates whether or not the shape contains ArcSDE CAD Data. Returned values are either T (true) or F (false).
6. Number of Points (Integer) - The total number of points in the shape.
7. Number of Parts (Integer) - The number of parts in the shape. If an error is encountered when attempting to obtain the parts, that error code, from sdeerno.h, is supplied instead.
8. Number of Subparts (Integer) - The number of subparts in the shape. If this is a type of shape that does not have subparts, the value is 0. If instead an error is encountered when attempting to obtain the subparts, that error code from sdeerno.h is supplied.
9. Self-Touching Rings (Boolean) - Indicates the presence (T) or absence (F) of inclusions or cojoined inner rings in the shape. T is always returned for area shapes.
10. Minimum Precision (Integer) - The minimum layer precision to contain this feature; either LOW (32-bit) or HIGH (64-bit).
11. Verification (Integer) - Indicates whether or not ArcSDE considers a shape valid. A value for this field is returned if you use the -r option to specify 'all' the features in the layer be evaluated for validity. Possible return values are 0 if the shape is verified as correct or a negative error code from the sdeerno.h file if it is incorrect. This information is most helpful when you want to determine which features in an Oracle Spatial database are not valid so you can correct this.
Values for numbers 12 through 21 are only returned if the [-c] [Spatial_Index] option is specified.
12. Area (Floating Point) - The area of the shape or 0.0 if this shape is not a polygon. Units depend on the coordinate system of the layer.
13. Length (Floating Point) - The length or perimeter of the shape or 0.0 if this shape is a point or multipoint. Units depend on the coordinate system of the layer.
14. Minimum X (Floating Point) - The minimum x-coordinate of this shape.
15. Maximum X (Floating Point) - The maximum x-coordinate of this shape.
16. Minimum Y (Floating Point) - The minimum y-coordinate of this shape.
17. Maximum Y (Floating Point) - The maximum y-coordinate of this shape.
18. Minimum Z (Floating Point) - The minimum z-coordinate of this shape. This field is only present if the layer has z-coordinates.
19. Maximum Z (Floating Point) - The maximum z-coordinate of this shape. This field is only present if the layer has z-coordinates.
20. Minimum Measure (Floating Point) - The minimum measure of this shape. This field is only present if the layer has measures.
21. Maximum Measure (Floating Point) - The maximum measure of this shape. This field is only present if the layer has measures.

Here is an example to report all invalid geometries in a layer where column land_code equals 1:

sdelayer -o feature_info -l LAND.LAND_POLY,SHAPE -r invalid -i 5151 -w "land_code = 1" -s sdeserver -u user1 -p user1

ArcSDE 9.1 Oracle10g Build 391 Tue Oct 24 11:44:47 PDT 2006
Layer Administration Utility
-----------------------------------------------------
Row Id,FID,Entity Type,Annotation,Cad Data,Number of Points,Number of Parts,Number of Subparts,Self-Touching Rings,Minimum Precision,Verification

1,2,A,F,T,29,2,3,F,32,-155
3,3,A,F,F,3682,2,2,F,32,-148
5,5,A,F,F,66,2,2,F,32,-148

These results indicate that three geometries are invalid. Take the first one for example:
- Has a Row ID of 1
- Has a Feature ID of 2
- Is an area entity (a polygon)
- Does not contain annotation
- Contains CAD data
- Contains 29 points
- Is made up of 2 parts and 3 subparts
- Does not contains self-touching rings
- Is stored in low precision (has a minimum precision of 32 bits)
- Is an invalid shape (error code -155: SE_SELF_INTERSECTING, a simple line or polygon boundary intersects itself.)

See here for the meaning of ArcSDE error code.

Loading spatial data between layers with different spatial columns

If you create a feature class in ArcCatalog, the default spatial column name to store spatial data is automatically called SHAPE. There are some existing layers whose spatial column is called GEOMETRY. Sdelayer is capable of creating feature classes by specifying GEOMETRY as the spatial column.

How you do load spatial data from a layer with SHAPE column to a layer with GEOMETRY column? Let us examine the following options.

Option 1: Use sdeexport / sdeimport.

First use the following script to create an ArcSDE export file:
sdeexport -o create -l source_layer,shape -a all -f source.sdx -i 5151 -s sde_server -u user1 -p user1

Then use this script to import ArcSDE export data into the target layer:
sdeimport -o init -l destination_layer,geometry -f source.sdx -i 5151 -s sde_server -u user2 -p user2
Warning: SDEX's column "SHAPE" not found in target table.

Exactly as the warning message says, only the attribute data are loaded into the target layer. Geometry column is null for all loaded records.

If the append operation is used, it is the same thing, which makes sense:
sdeimport -o append -l destination_layer,geometry -f source.sdx -i 5151 -s sde_server -u user2 -p user2
Warning: SDEX's column "SHAPE" not found in target table.

So this option does not work.

Option 2: Use FME / Data Interoperability Extension.
FME is the spatial ETL (extract, transform and load) tool provided by Safe software from Surrey of British Columbia in Canada. Data Interoperability Extension is an extension in ArcGIS desktop package. It is FME actually. Matching SHAPE and GEOMETRY will do the job: load spatial data in SHAPE into GEOMETRY column.

Option 3: Use Simple Data Loader in ArcCatalog.

In the dialog of Simple Data Loader, it provides the option to match attribute columns, but no option to match the spatial column.

I will check it out tomorrow and write the result here.

Dec 08: I tried this today in ArcCatlog. Values in SHAPE column are loaded into GEOMETRY column. Since ArcCatalog is based on ArcObjects, it makes sense. Actually it can automatically match them.

Of course there are other options, such as writing an ArcObjects utility.

ITIL - Information Technology Infrastructure Library

I had an ITIL training in Victoria at the beginning of Novermber. It is a three-day intensive course. The afternoon in the third day was for a test. The test was quite formal, which reminded me of CET4 and CET6 tests when I was in my university. The training and test are not easy. A thick book needed to be read, and you really need to use your brain to analyze the scenario and use your IT service management experience to understand ITIL and pass the test. For ESL people, it is an English language test to some extent. My manager got the result and she sent me her congratulations immediately because she said it was difficult, even though she has more than 20 years of ITSM experience. I got my formal certificate in about two weeks.

If you are interested in ITIL, here is the web site:
http://en.wikipedia.org/wiki/ITIL

BTW, my course is v2. v3 is coming.

Edit the DBTUNE table

DBTUNE table in SDE schema stores the DBTUNE storage parameters that control how ArcSDE clients create objects within an Oracle database. The table is populated with specific default values. sdedbtune utility is used to initialize or alter DBTUNE table contents.

To edit the DBTUNE table, the following steps can be followed:

1. Use "sdedbtune -o export" to export the DBTUNE table to a text file.
sdedbtune -o export -f dbtune_out.sde -i 5151 -s sdeserver -u sde -p password

ArcSDE 9.1 Oracle10g Build 371 Tue May 9 10:13:38 PDT 2006Attribute Administration Utility-----------------------------------------------------
Successfully exported to file SDEHOME\etc\dbtune_out.sde on server sdeserver

2. Edit the resulting file with vi (UNIX) or Notepad (Windows).

3. Use "sdedbtune -o import" to import the modified file into DBTUNE table.

sdedbtune -o import -f dbtune_out.sde -i 5151 -s sdeserver -u sde -p password

ArcSDE 9.1 Oracle10g Build 371 Tue May 9 10:13:38 PDT 2006Attribute Administration Utility-----------------------------------------------------
Import DBTUNE Table. Are you sure? (Y/N): y
Successfully imported from file SDEHOME\etc\dbtune_out.sde

4. Test. Always.

sdedbtune tool in ArcSDE 9.1 always exports and imports from the $SDEHOME/etc directory. In ArcSDE 9.2, the tool picks up the directory the command is issued if no directory is specified with the path name.

Some of my published translation books

Microsoft.NET Compact Framework技术内幕

http://www.tup.tsinghua.edu.cn/book/SHOWBOOK.asp?cpbh=012299-01
http://www.amazon.cn/detail/product.asp?ref=DT_RV&uid=000-0000000-0000000&prodid=zjbk205043
Note: I am one of the translators of the book, not the author.


精通Asp.NET程序设计

http://www.tup.tsinghua.edu.cn/book/Showbook.asp?csbh=76&CPBH=009461-01
http://www.amazon.cn/detail/product.asp?prodid=zjbk367284&ref=SR&uid=168-7234512-8780244
Note: I am the translator, not the author.

Here I Come

This is my first post. So it is a good chance for me to write about this blog. My intention is to share my knowledge and idea about the stuff I am practicing every day, learning every day and thinking every day. It has nothing to do with my employers. Hopefully it will create a friendly web community for my friends.