How to work with SDE layers with Multi-entity types

Summary

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.

Procedure

• 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
  
  

  Code:
   
  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.
  
  

  Code:
  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.)
  
  

  Code:
  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
  
  

  Code:
  Set pRec = pLayer.Records

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