How To: How to work with SDE layers with Multi-entity types


If you create a layer which contains more than one entity type, i.e. Polygons and Lines, then the recordset of the layer will contain all of the layer's records (i.e. Lines and Polygons). This is the behavior at both MapObjects 1.2 and MapObjects 2.0.

For example, let's say you've created an SDE layer called "multiGeom" on your server. This layer contains both river features (100 lines) and country features (200 polygons). If you return all of the records in the multiGeom layer (Layer.Records), you will return a recordset with 300 records, using either MapObjects 2.0 or MapObjects 1.2. However, if you only ask for line entity types to be displayed, then only line features will be displayed; for example, MYUSER.MULTIGEOM.SHAPE.LINES would only draw lines.
One of the differences between MapObjects 1.2 and 2.0 occurs when you access the value of the "Shape" field for a record which does not contain the expected entity type for that layer, such as returning the value of a line record in a polygon-defined theme. For example, using the following code

Set pLine = pField.Value

in MapObjects 2.0, an error will be generated if the layer was defined as MYUSER.MULTIGEOM.SHAPE.POLYGONS, whereas with MapObjects 1.2, it will simply return nothing. This is due to internal changes in the way that MapObjects 2.0 handles NULL shapes and is not affected by the Visual Basic definition of the pLine object -- that is, dimensioning the pLine object as an Object rather than a MapObjects2.Line will not matter.


Ideally, MapObjects should only return those records which are of the specified type for the layer and this is something which we will be looking at for a future release. However, in the meantime, we suggest a workaround. Basically, we want to filter out only those features which are of the required entity type for our layer. To do this, we can user the FilterExpression method to narrow down the layer. For this, we need to have a field on which we can query, a field which describes the entity type for that record. However, no such field exists by default, in the layer's Business table in SDE. As a result, two options are available:

  • To amend the business table to include a field that declares the entitry type for each record and use this for the layer's filter expression. For example:

    myLayer.FilterExpression = "SHP_TYP = 'POLY'"
    This first option is perhaps simple to implement but has many disadvantages, such as maintaining the integrity of the data, etc.

  • Join to the layer's FTable and query the value of its Entity Field in the filter expression. This requires the following steps:

    1. We must know the name of the layer you want to use.
    2. Using Step #1, if our user has adequate privileges, we can get the layer_id from the sde.layers table.
    3. Once you have the layer_id, we know what the Ftable's name is.
    4. Using the Ftable, we can easily determine what the feature entities are (i.e., 8 = polygon, 2 = line, etc.).
    5. Using this information, we can use the Layer.FilterExpression method to filter out those feature type that are not required.

    Some Visual Basic code to implement the above:
    [1] Create an SDE table using MapObjects


    Dim oTable As New MapObjects2.Table
    oTable.Name = "SDE.LAYERS"
    oTable.Server = "drfinlay"
    oTable.User = "sde_user"
    oTable.Password = "sde_user"
    oTable.Database = "esri_sde"

    [2] Create a temporary recordset based on the SDE.LAYERS table and extract the required layer_id. The following code snippet hard-codes the layer name, but this can easily be parsed from a ListBox, etc.

    Dim oRec As MapObjects2.Recordset
    Set oRec = oTable.SearchExpression("sde.layers.table_name = 'MULTIGEOM'")
    Dim layerid As String
    layerid = oRec.Fields.Item("LAYER_ID").Value

    [3] Create your SDE data connection as usual. Once that is done, define your filter expression - this is the SQL statement that you will need to effectively join the F table. Note that the IN clause should be supported by most RDBMS'. This SQL statement has been hard-coded for entity type 8 = Polygons. (2=spaghetti lines, 1=points etc.)

    Dim filterExpr As String
    filterExpr = "mo2dev.multigeom.shape IN (select mo2dev.F" & layerid & ".fid from mo2dev.F" & layerid & " where mo2dev.F" & layerid & ".entity=8)"
    pLayer.FilterExpression = filterExpr

    (4) Retrieve your records

    Set pRec = pLayer.Records

    This will now only return records that are polygons, so long as sLayerName = "MO2DEV.MULTIGEOM.SHAPE.POLYGONS"