fxsde: ESRI ArcSDE

A database interface used to connect to an ESRI ArcSDE server. ArcSDE is an ESRI tool that allows one to store and manage spatial data within any one of a number of supported RDMS's including Oracle, Informix, IBM DB2 and Microsoft SQL Server.

This interface is available on Microsoft Windows systems only.

Note: In order to successfully execute fxsde, make sure that the %PATH% environment variable includes the directory containing the ArcSDE client DLL's.

Connect String

To establish an ArcSDE connection between the client (fxsde) and an ArcSDE server, the following information must be provided:

the name of the service
the name of the database (not applicable to Oracle)
the user name
the user password

This information may also be passed via operating system evironment variable:

SDESERVER - the name of the SDE server
SDEINSTANCE - the name of the ArcSDE instance
SDEDATABASE - the name of the ArcSDE database (if required by the underlying database)
SDEUSER - the user name
SDEPASSWORD - the user password

Do not specify the value on the connect string if the value is set via the corresponding environment variable. The format of the connect string is:

-c=server=<SDESERVER>,instance=<SDEINSTANCE>,database=<SDEDATABASE>,uid=<SDEUSER>,pwd=<SDEPASSWORD>

To hide any sensitive connection information from prying eyes, an encoded string may be used. The utility fxencode is available for such encoding.

Extensions to Standard Commands

plsql

fxsde does not implement PL/SQL facilities.

query_tables

query_tables <client_string_array>

Returns the names of all tables, excluding system tables, in the database. This list is also selectable by the following command:

iselect * from fxtables

select

SQL query statements are passed to the ArcSDE server in two ways:

sdeselect - all spatial related queries must be submitted via this command.
select - SQL statement submitted via this command are passed directly to the underlying database with no intervention by either fxsde or the ArcSDE server.

Additional Commands

sdeselect

Any SQL statement that involves submitting or retrieving a spatial data column or uses spatial contraints (via the spfilter command ) as part of the selection criteria must be passed via this command.

db dbc {
    *sdeselect objectid, condo_name, shape from lots where objectid > 100
    *set class=~a.cmp_t, objectcolumn=objectid, objectprefix=obj_
    *fetch_class
    *spfilter(lots.shape,:w1,SM_SC, false)
    *objfetch
}

The above example will return a resultset each containing 3 values, one of which is a graphics entity - shape. Each row returned is composed into a Fire compound structure (defined via the set_class command) whose Fire instance name is defined as "obj_<objectid>" - for example obj_111. The result will contain all rows that satisfy both the non-spatial (where clause) and spatial (spfilter) contraints. The spatial constraint in this case specifies all objects that lie outside (enforced via the "false" directive) of the Fire polygon called w1.

A sdeselect command must also be used to retrieve non-spatial results that involve spatial contstraints (via the spfilter command). For example, the following code sets up a query that returns all condo names (string type) that are inside a rectangular window defined by a Fire polygon entity called w1:

string condo_name[]
db dbc {
    *sdeselect condo_name from lots
    *spfilter(boundary.shape,:w1,SM_AI)
    *multifetch into condo_name
}

spfilter

spfilter (<geometry column>, <:geometry>, <sde_spatial_function>, <true | false>)

where

<geometry_column> = The name of the geometry column in the table formatted as <table_name>.<column>
<:geometry> = The Fire entity host variable.
<sde_spatial_func> = The SDE spatial function filter. See the table below for values.
<true | false> = If true, all geometries satisfying the spatial conditions are included. If false, all geometries not satisfying the spatial constraints are included.

This command is used to define the spatial constraint component of a SQL statement. Multiple spatial contrainst may be set. Only those geometries satifying ALL constraints are returned.

The following table lists the supported search methods that can be used when defining a spatial filter.

ArcSDE Search Method	Description
SM_ENVP	Shape envelope test Requires the envelopes only of the search and candidate shape to overlap or touch. The envelope consists of the minimum and maximum values of a shape's coordinates. This is the fastest search method because only an envelope test is performed. Typically used for screen displays and approximate searches.
SM_ENVP_BY_GRID	Shape envelope test in spatial index order Same as SM_ENVP, but guarantees that shapes are returned in spatial index order.
SM_CP	Common point Specifies that the search and candidate shapes share at least one point. Can find all parcels of land adjacent to a given parcel (assuming topological integrity between parcels).
SM_LCROSS	Line cross The point of intersection cannot be a vertex in both shapes. Can find errors in topological integrity.
SM_COMMON	Common edge or line The search and candidate shapes share at least one line with the same vertices.
SM_CP_OR_LCROSS	Common point or line cross
SM_LCROSS_OR_CP	The search and candidate shapes must intersect or share a common point or line (edge touch).
SM_II_OR_ET SM_AI_OR_ET SM_ET_OR_II SM_ET_OR_AI	Area or interior intersection or edge touch. Search and candidate shape boundaries must touch or cross, or one shape must be at least partially inside the other shape which must be an area shape.
SM_AI SM_II	Area or interior intersection At least one of the shapes must be an area shape and the other shape must be at least partially inside it. Adjacency is not considered an area intersect. Can determine the parcel's zoning.
SM_AI_NO_ET	Area intersection without touching a shape boundary
SM_II_NO_ET	One shape lies wholly inside the other (area) shape, and the boundaries of the two shapes do not touch in any way.
SM_PC	Search shape wholly contained in candidate shape If the candidate shape is an area shape, the search shape must be wholly inside it, inclusive of the candidate shape's boundary. If both shapes are linear shapes, the search shape's path must be wholly included in the candidate shape's path. If the search shape is a point shape, the search shape must be one of the candidate shape's vertices.
SM_SC	Candidate shape wholly contained by search shape The candidate shape is wholly contained by the search shape, inclusive of the search shape's boundary. Opposite relationships apply as SM_PC.
SM_PC_NO_ET	Search shape wholly contained in candidate shape without edge touch The candidate shape must be an area shape, the search shape must be wholly inside it, and their boundaries may not intersect or touch.
SM_SC_NO_ET	Candidate shape wholly contained in search shape without edge touch The search shape must be an area shape, the candidate shape must be wholly inside it, and their boundaries may not intersect or touch.
SM_PIP	Point inside polygon Regardless of the search shape type, this test performs a point-inside-polygon test using only the first point of the search shape. The candidate shape must be an area shape, and the point used for the test must be inside it. Identifies area shapes by pointing.
SM_IDENTICAL	Identical Both shapes must be identical in terms of shape type and coordinate description. Typically used to find duplicate data.

Notes

The following table describes which SDE Shapes are supported and what Fire entities they are mapped to.

SDE Shape	Fire Entity
POINT	NODE
MULTIPOINT	POLYNODE
POLYLINE	POLYLINE
POLYGON	POLYGON