How to select a subset from a feature class and add the subset as a layer in ArcMap?

[ShanshanZhou]

Hi, I am working on a application developed on .NET platform that interacts with ArcMap, and I would like to know if the proposed process I need to develop can be done or not. If can, how to do it. Your help and advices are highly appreciated.

Basically, we have a feature class in ArcSDE that contains about 20 million of points. Each point feature has a "NAME" field, and normally around 50 - 2000 points are associated with one pacific NAME value. I have developed a form using VB.NET to allow user to input the NAME value they would like to show on the map. My question is: how can I select a subset of the point features based on the user input and display them as a layer in ArcMap.

As I mentioned before, there are huge volume of point features, so I don't want to load the whole feature class into ArcMap - it takes ages to draw. I only want to select a subset of the points from ArcSDE feature class, and show these points in map. Is this difficult to do? I haven't found any relevant information on the net so far.

Please let me know if I didn't explain the question clearly. Thanks in advance for your help!

[RichardFairhurst]

Hi, I am working on a application developed on .NET platform that interacts with ArcMap, and I would like to know if the proposed process I need to develop can be done or not. If can, how to do it. Your help and advices are highly appreciated.

Basically, we have a feature class in ArcSDE that contains about 20 million of points. Each point feature has a "NAME" field, and normally around 50 - 2000 points are associated with one pacific NAME value. I have developed a form using VB.NET to allow user to input the NAME value they would like to show on the map. My question is: how can I select a subset of the point features based on the user input and display them as a layer in ArcMap.

As I mentioned before, there are huge volume of point features, so I don't want to load the whole feature class into ArcMap - it takes ages to draw. I only want to select a subset of the points from ArcSDE feature class, and show these points in map. Is this difficult to do? I haven't found any relevant information on the net so far.

Please let me know if I didn't explain the question clearly. Thanks in advance for your help!

Are you wanting a definition query on the data or a Selection Layer?  This post has code toward the end of the post that illustrates how to create a Selection Layer from a larger set of features from a user define query result.  Alternatively a Definition Query can be applied as a variant of that code.  This code assumes you have a layer in the map that may not be displayed, but it could be rewriten to open an SDE connection in memory without a layer and only create a prefiltered layer with a definition query or selection layer from the connection.

If you are wanting to extract the features to a new local geodatabase and add it to the map, that could be potentially done as well as a variant of the code, but there are likely to be database locking issues that interfere with that approach and I personally have not tried it.  With every approach at some level you have to hit the original dataset with a query and display the results, so the basic approach of the code in the post linked above is relavant to your problem.

If the data is not updated often and you know that your users only cared about a much smaller subset of the points you could extract the smaller subset of features into a new feature class through a nightly or weekly script and serve that to your users.  That avoids locking issues or managing multiple local temporary copies of the data.  I do this with a polygon layer over each weekend and the speed improvement is significant for the layers that I base on the subset over the original full set.

[ShanshanZhou]

Hi Richard, thank you very much for your reply and the suggestions are very helpful.

I am looking to apply a definition query on the data not a select layer. As background info, the point feature class contains the locations of trucks collected every 15 seconds, and the table is updated quite frequently (not real-time tho). There is a master table  of the data contains X,Y coordinates for location, and we spatilaise the data into a SDE feature class using these coordinates. The reason we doing so is that we hope this can speed up the drawing. I assume it will take longer time to create points on the fly using X,Y coordinates. Please correct me if I'm wrong. Honestly, I still do not have a clear picture how this thing will work out.

I guess what I wanted to do is  "open an SDE connection in memory without a layer and only create a prefiltered layer with a definition query", could you please provide me some more info/sample code how to do this?

If you have any other better solutions, please advice. Many thanks!

[RichardFairhurst]

Hi Richard, thank you very much for your reply and the suggestions are very helpful.

I am looking to apply a definition query on the data not a select layer. As background info, the point feature class contains the locations of trucks collected every 15 seconds, and the table is updated quite frequently (not real-time tho). There is a master table  of the data contains X,Y coordinates for location, and we spatilaise the data into a SDE feature class using these coordinates. The reason we doing so is that we hope this can speed up the drawing. I assume it will take longer time to create points on the fly using X,Y coordinates. Please correct me if I'm wrong. Honestly, I still do not have a clear picture how this thing will work out.

I guess what I wanted to do is  "open an SDE connection in memory without a layer and only create a prefiltered layer with a definition query", could you please provide me some more info/sample code how to do this?

If you have any other better solutions, please advice. Many thanks!

Here is some sample code I adapted from the Query Samples website that adds an SDE layer with a definition query to a Map.

Option Explicit

Public Sub AddSDECoverageLayer(byRef TruckID As String)
  Dim pWorkFact As IWorkspaceFactory2
  Dim pFWorkspace As IFeatureWorkspace
  Dim pFClass As IFeatureClass
  Dim pFLayer As IFeatureLayer
  Dim pMxDoc As IMxDocument
  Dim pMap As IMap
  Dim pAV As IActiveView
  
  'Open Feature Class
  Set pWorkFact = New SdeWorkspaceFactory
  Set pFWorkspace = pWorkFact.OpenFromString("server=luxor;instance=5156;user=avuser;password=avtest", 0)
  Set pFClass = pFWorkspace.OpenFeatureClass("TruckPoints")
  
  'Create new layer
  Set pFLayer = New FeatureLayer
  Set pFLayer.FeatureClass = pFClass
  pFLayer.Name = pFClass.AliasName & " TruckID " & TruckID

  ' Define a definition query for the layer from the input variable
  Dim pFeatDef As IFeatureLayerDefinition
  Set pFeatDef = pFLayer
  pFDef.DefinitionExpression = "TRUCKID = '" & TruckID & "'"  ' Build a real Query by Attribute that works.  Remove single quotes if the field is not a Text field.
  
  'Add layer to map
  Set pMxDoc = ThisDocument
  Set pMap = pMxDoc.FocusMap
  Set pAV = pMap
  pMap.AddLayer pFLayer
  pAV.Refresh
End Sub

I would not hard code the user name and password into the code as the sample shows since you do not want to store specific account information that anyone could read.  Instead I would develop code to prompt the user to provide their user name and password and store them in a global variable at the time that Arcmap opens.

You may not want to keep adding layers.  Or you may want to make the name of the layer unique so that you can find it and replace it.

My usual approach is to actually to store a layer called "Search " & LayerName in my map.  That layer has all of the connection information to SDE that will prompt the user for their name and password when they start the document.  It is preloaded with a definition query to prevent the user from seeing anything.  Then I just update the definition query of that layer rather than adding a new layer and change the visibility property of the layer.  I also use that layer to zoom to the feature(s) the user requested.  I tell my user they can do anything they want with the map except rename that layer, delete it or connect it to a data source that does not match the expected schema and my code will work.  So users can customize the symbols, label properties, layer order transparency, primary display field, etc.  My users value my code and observe my restrictions.

Anyway, the above code does the basics of what you need and can be adapted and extended an variety of ways.

[ShanshanZhou]

Hi Richard,

Hope you had a great weekend.

Thank you very much for the sample code. I tested it in the morning, and it worked very well, the definition query seems quite effective.

The only problem I found is that if I would like to zoom to the layer extent with definition query, it always zoom to the full extent of the layer. After investigation, I found some code to loop through all the features and join the extent together to get the correct extent. However when large number of points (over 5000 pts) encountered, this method will take significant amount of time (over 20 secs). Do you have any better idea which can get the extent faster?

While browsing, I also found the code below quite interesting. According to the description, QueryDef can be used to generate a virtual table or feature class. I tried to get a feature class using this method, however it seems CType method only works for ITable, but not IFeatureClass. I guess if this method can return a feature class, it may be a faster approach than definition query. Any idea how to cast to feature class? or any comments about this method? 

http://edndoc.esri.com/arcobjects/9.2/NET/3b7e4013-8886-4c51-8cec-77368a1c51bb.htm

The IQueryName2 interface
Along with creating cursors, a QueryDef can be used to generate a virtual table or feature class.  By using the IQueryName2 interface on a co-created table query name object, a QueryDef can be specified.  Following this, the name of the virtual table and the workspace it will be created in must be specified through the IDatasetName.Name and IDatasetName.WorkspaceName interfaces.  After the object is cast to the IName interface, the IName.Open method can then be called, as shown in the following code example:
[VB.NET]

Public Function CreateQueryTable(ByVal workspace As IWorkspace, ByVal queryDef As IQueryDef, ByVal tableName As String) As ITable
    
    ' Create a reference to a TableQueryName object.
    Dim queryName2 As IQueryName2 = New TableQueryNameClass()
    queryName2.PrimaryKey = ""
    
    ' Specify the query definition.
    queryName2.QueryDef = queryDef
    
    ' Get a name object for the workspace.
    Dim dataset As IDataset = CType(workspace, IDataset)
    Dim workspaceName As IWorkspaceName = CType(dataset.FullName, IWorkspaceName)
    
    ' Cast the TableQueryName object to the IDatasetName interface and open it.
    Dim datasetName As IDatasetName = CType(queryName2, IDatasetName)
    datasetName.WorkspaceName = workspaceName
    datasetName.Name = tableName
    Dim Name As IName = CType(datasetName, IName)
    
    ' Open the name object and get a reference to a table object.
    Dim table As ITable = CType(Name.Open(), ITable)
    Return table
    
End Function

Again, many thanks for your help!

[RichardFairhurst]

The only problem I found is that if I would like to zoom to the layer extent with definition query, it always zoom to the full extent of the layer. After investigation, I found some code to loop through all the features and join the extent together to get the correct extent. However when large number of points (over 5000 pts) encountered, this method will take significant amount of time (over 20 secs). Do you have any better idea which can get the extent faster?

The code below is how you should get the geometry of the features to create an extent you can zoom to:

Option Explicit

Public Sub AddSDECoverageLayer(byRef TruckID As String)
  Dim pWorkFact As IWorkspaceFactory2
  Dim pFWorkspace As IFeatureWorkspace
  Dim pFClass As IFeatureClass
  Dim pFLayer As IFeatureLayer
  Dim pMxDoc As IMxDocument
  Dim pMap As IMap
  Dim pAV As IActiveView
  
  'Open Feature Class
  Set pWorkFact = New SdeWorkspaceFactory
  Set pFWorkspace = pWorkFact.OpenFromString("server=luxor;instance=5156;user=avuser;password=avtest", 0)
  Set pFClass = pFWorkspace.OpenFeatureClass("TruckPoints")
  
  'Create new layer
  Set pFLayer = New FeatureLayer
  Set pFLayer.FeatureClass = pFClass
  pFLayer.Name = pFClass.AliasName & " TruckID " & TruckID

  ' Define a definition query for the layer from the input variable
  Dim pFeatDef As IFeatureLayerDefinition
  Set pFeatDef = pFLayer
  pFDef.DefinitionExpression = "TRUCKID = '" & TruckID & "'"  ' Build a real Query by Attribute that works.  Remove single quotes if the field is not a Text field.
  
  'Add layer to map
  Set pMxDoc = ThisDocument
  Set pMap = pMxDoc.FocusMap
  Set pAV = pMap
  pMap.AddLayer pFLayer

  ' After you have applied your definition query and added the layer use the following code to zoom to the features.
  Dim pFeatSelection As IFeatureSelection
  Set pFeatSelection = pFLayer

  pFeatSelection.SelectFeatures Nothing, esriSelectionResultNew, False

  'zoom to all filtered features
  Dim pEnumGeom As IEnumGeometry
  Dim pEnumGeomBind As IEnumGeometryBind
 
  Set pEnumGeom = New EnumFeatureGeometry
  Set pEnumGeomBind = pEnumGeom
  pEnumGeomBind.BindGeometrySource Nothing, pSelectionSet

  Dim pGeomFactory As IGeometryFactory
  Set pGeomFactory = New GeometryEnvironment

  Dim pGeom As IGeometry
  Set pGeom = pGeomFactory.CreateGeometryFromEnumerator(pEnumGeom)

  'update the extent of the map to match the extent of the selected features
  pAV.Extent = pGeom.Envelope
  pMXDoc.FocusMap.ClearSelection
  pAV.Refresh
End Sub

[ShanshanZhou]

Hi Richard,

Thank you very much for the code. I have implemented it, and I reckon this selection method is slightly faster than the merging extent by looping through each features. However when it comes to large number of features, the time taken is still quite significant - users have to click on a button and wait for half a minute before map starts drawing.

I found it is very fast if I right click on the layer in ArcMap and then click "Zoom to Layer" button. Any idea how that function works? I want the application not only work but also work fast (at least time of response is reasonable). Any solution to improve the speed of zooming to the layer with definition query on?

Your suggestion is highly appreciated.

Cheers,

[RichardFairhurst]

Hi Richard,

Thank you very much for the code. I have implemented it, and I reckon this selection method is slightly faster than the merging extent by looping through each features. However when it comes to large number of features, the time taken is still quite significant - users have to click on a button and wait for half a minute before map starts drawing.

I found it is very fast if I right click on the layer in ArcMap and then click "Zoom to Layer" button. Any idea how that function works? I want the application not only work but also work fast (at least time of response is reasonable). Any solution to improve the speed of zooming to the layer with definition query on?

Your suggestion is highly appreciated.

Cheers,

Zoom To Layer will not work for subselected data, whether by definition query or selection layer or whatever.  That only works for the total data extent of the entire feature class.  A definition query/feature selection/etc. does not alter the overall extent of the feature class to match your selection.  If you mean Zoom To Selected Features menu item than I may not know how ESRI pulls it off if you detect a significant speed improvement.

If you were using lines or polygons I would suggest the code below, since an Extent is simpler than the geometry of those types, but I don;t think it works for points:

    Dim pFCursor As IFeatureCursor
    pSelSet.Search Nothing, False, pFCursor
    
    Dim pEnv As IEnvelope
    Dim pBigEnv As IEnvelope
    Set pBigEnv = New Envelope
         
    Dim pFeature As IFeature
    Set pFeature = pFCursor.NextFeature
    
    Do Until pFeature Is Nothing
        Set pEnv = pFeature.Shape.Envelope
        pBigEnv.Union pEnv
        Set pFeature = pFCursor.NextFeature
    Loop
    
    pMxDoc.ActiveView.Extent = pBigEnv

You could also try to use the built in Zoom To Selection command (that command cannot be activated any faster than the code below, even by a user)  This code is for VBA, so you would need to look up the equivalent access to the CommandBars for .Net, possibly through some type of QI from the Application interface:

'Select features in ArcMap.
Dim pFSel As IFeatureSelection
Set pFSel = pFLayer ' Assume Definition query is already applied.
pFSel.SelectFeatures Nothing, esriSelectionResultNew, False
pDoc.ActiveView.Refresh

'Use the Built in Zoom Command to Zoom to the Selected Features
Dim pItem As ICommandItem
With Project.ThisDocument.CommandBars
Set pItem = .Find(arcid.Query_ZoomToSelected)
End With
pItem.Execute

I think for .Net you would use:

Dim pUID As New UID 
Dim pCmdItem As ICommandItem 
' Use the CLSID of the Zoom to Selected Features command 
pUID.Value = "{AB073B49-DE5E-11D1-AA80-00C04FA37860}"
pCmdItem = CommandBars.Find(pUID)
pCmdItem.Execute

[ShanshanZhou]

Hi Richard,

It turns out we figured out a different way. As I mentioned before, we have X,Y coordinates value of the points table. So I used SQL query to get min and max X, Y values from the selected points, and created a envelope with these coordinates. The map will be zoom to the envelope instead. It works quite fast, because running SQL takes very minimal amount of time. However, this method is only applicable for our particular exercise, for zooming to a layer with definition query I guess it is a still tough question.

Many thanks for your help! All the suggestions and code are very helpful!

[RichardFairhurst]

Hi Richard,

It turns out we figured out a different way. As I mentioned before, we have X,Y coordinates value of the points table. So I used SQL query to get min and max X, Y values from the selected points, and created a envelope with these coordinates. The map will be zoom to the envelope instead. It works quite fast, because running SQL takes very minimal amount of time. However, this method is only applicable for our particular exercise, for zooming to a layer with definition query I guess it is a still tough question.

Many thanks for your help! All the suggestions and code are very helpful!

Although the SQL might be specific for your server database, I have not understood how to create subqueries that get Min and Max values from the ESRI help.  Do you mind posting the SQL just for my reference and instruction.  I currently have Oracle SDE and am moving to SQL Server SDE, so it proabably applies to one or the other.  I am going to be using ArcGIS 10 and file GDBs are now also supposed to allow that kind of query I believe also.

Your approach may have more applications than you think (I also have X and Y data in many layers or could have it if I wanted it).  All features have either a single X/Y pair or two X/Y pairs for an extent, and that could be added as fields for manipulating this way.  Thanks.

(BTW, did you mean Zoom to Layer or did you try Zoom to Selected Features with the large selection set.  Just curious.)