Latest Contributions by RichardFairhurst

I have corrected the definition query statement in bold where you misspelled the field name (you left out an underscore).  It should work with that correction. Hi, I am having problem with my code I am trying to use zoom Modules in my project, BUT after I run the code, it goes directly to the layer but with deleting all the information in Attribute table? here is my code ? Option Explicit Private pMxDoc As IMxDocument Private pFLayer As IFeatureLayer Private pEnumLayer As IEnumLayer Private pCurrentLayer As IFeatureLayer Private pFClass As IFeatureClass Private pFeature As IFeature Public Sub ZoomToSDnumber(SDnumberId As String)     Set pMxDoc = ThisDocument     Set pFLayer = pMxDoc.FocusMap.Layer(0)     Set pFClass = pFLayer.FeatureClass     Dim pEnv As IEnvelope     Dim pActView As IActiveView     Dim pFCursor As IFeatureCursor     Dim pQFilt As IQueryFilter     Dim queryStr As String     queryStr = "SD_NUMBER = '" & SDnumberId & "'"     Set pActView = pMxDoc.ActiveView     Set pQFilt = New QueryFilter     pQFilt.WhereClause = queryStr         Set pFCursor = pFClass.Search(pQFilt, True)     Set pFeature = pFCursor.NextFeature         If pFeature Is Nothing Then             MsgBox "Check spelling and case", vbCritical + vbExclamation, "State Not Found!"         Else             pActView.Extent = pFeature.Shape.Envelope             Set pEnv = pActView.Extent             pEnv.Expand 1.1, 1.1, True             pActView.Extent = pEnv         End If             Dim flDef As IFeatureLayerDefinition     Set flDef = pFLayer     flDef.DefinitionExpression = "SD_NUMBER='" & SDnumberId & "'"            pActView.Refresh     pMxDoc.UpdateContents     End Sub Public Function CreateSelLayer(SDnumberId As String)     Set pMxDoc = ThisDocument     Set pFLayer = pMxDoc.FocusMap.Layer(0)         ''** Make a selection on the FeatureLayer     Dim queryStr As String     Dim qF As IQueryFilter     Set qF = New QueryFilter     queryStr = "SD_NUMBER='" & SDnumberId & "'"     qF.WhereClause = queryStr     Dim pFSel As IFeatureSelection     Set pFSel = pFLayer     pFSel.SelectFeatures qF, esriSelectionResultNew, False         Dim pFDef As IFeatureLayerDefinition     Set pFDef = pFLayer             Dim SelFeatLayer As IFeatureLayer     Set SelFeatLayer = pFDef.CreateSelectionLayer(StateId, True, "", "")         With pMxDoc         .AddLayer SelFeatLayer         .FocusMap.MoveLayer SelFeatLayer, 2         .FocusMap.ClearSelection         .ActiveView.Refresh         .UpdateContents     End With         End Function ... View more

If you really really have to preserve the original OIDs but still transform the feature class Spatial Reference and/or Schema rather than creating a true copy, then about the only way I can think of to do that would involve running an insert cursor to transfer the features from a TableSort object that is sorted on the original ObjectID field.  A counter would have to keep track of the record number being inserted and every time an ObjectID skipped one of the counter numbers a bogus feature would have to be inserted with a known bogus attribute value instead of a real feature.  Then when all real features are inserted, select the bogus features using the bogus attribute value and run a delete cursor to remove those features.  At the end in theory you should have a new feature class loaded with features that have the same OID values as the original feature class, but loaded to a new schema or spatial reference. ... View more

Copy is the only built in function that will do this that I have found, but you are restricted to transferring all Spatial and Attribute settings the same into the target database.  Feature datasets may have to be copied in their entirety in some cases. All other methods, such as using Export and Load to create a new schema or Spatial Reference, always rebuild the OID values.  The internal tables that perserve the OIDs for the copy method are not exposed by ESRI to end users through any methods I know of and they are not inclined to let users access these internal tables directly for fear of all the bugs that we would create (and that ESRI willl not support us in resolving). For all methods other than copy, the old OID values must be preserved into another LONG field on the output to the new gdb, so far as I know.  That long field can be used to join the target data back to the original.  A number of methods can be used to do that if you cannot modify the original schema to add the field and calculate the OID values to it.  This method can be used for rebuilding Feature-Linked Annotation to connect to a new Linked Feature-Class converted to a new Spatial Reference and Schema (I just did it 3 times in the past 3 weeks and finally got a method that worked in about 2 hours for a large SDE dataset with 5 Feature-Linked Annotations that had to be copied twice and reregistered with new permissions.) Even if you use copy to transfer the old OID values to the new gdb, if you make additions and deletions of records to both the oriiginal and copy independently they will not stay in synch.  If synchronizable editing in both the original and copied data is part of your operation, look at using global IDs and Replication through the Distributed Database toolset. ... View more

...To answer you question, I wanna zoom to the layer which has a definition query on it, so extent should be the overall extent of the valid features only. I don't know why, but the "zoom to layer" function works in my ArcMap desktop, when I right click on the layer (with definition query) and click on "Zoom to Layer", the map zooms to the valid extent (not the whole extent of the layer). And when it comes to VB.NET, that zoom to layer function doesn't behave that way any more, it zooms to the extent of the entire layer. I am a bit confused actually. Thanks for the code.  Probably connecting to Oracle is the fastest, but I think that this is supposed to be possible thorugh one of the ArcObjects interfaces.  I know that IDataStatistics is supposed to be able to do this kind of output, but probably cannot do the query in one shot like your code and is probably not as fast. The behavior difference of Zoom to Layer could be dependent on how the menu command percieves the state of the layer being manipulated by ArcObjects.  Some ArcObjects actions occur in memory and do not get seen by the user level interface until the code stops or certain events are triggered.  I don't think I ever tried that command the way you have, but it may be useful outside of code even if the code doesn't work.  Thanks for the reply. ... View more

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.) ... View more

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 ... View more

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 ... View more

I tested your data with the Pivot tool in Excel and found that the behavior in Excel is different from the behavior of the ArcGIS Pivot Tool.  In Excel 2007 you can only have numeric values fill in the summary data area defined by the column and row values, but with the ArcGIS tool you can have numeric or string values in the summary data area.  So actually the ArcGIS Pivot Table tool is what you need. I exported your data to a CVS file from Excel.  I had to rename the field called 2nd_FamilyName to SecondFamilyName because ArcMap does not like fields that begin with numbers.  I opened the CVS file in ArcMap and exported that to a File Geodatabase table.  In the new table I added a new field called CONCAT where I concatenated the various fields (except for Block and Number) into a single string value.  Then I ran the Pivot Table tool and used Block as the row value and Number as the column value and CONCAT as the data values.  I did not preorganize the Number field values with a sort operation so that the correct column order was generated, but that should not be a problem for a label expression and you can drag the columns where you want in a tableview or in Excel.  The result is attached as a Zipped Text file that you should be able to open in ArcMap.  There are still a few blocks with two rows, because of there being two individuals in the same Number plot.  The Excel Pivot can reveal which block groups have two individuals in the same plot number from your original data and you then could edit the table to collapse those records, either by adding more fields or concatenating the two individuals in the same Block and Number to one row and column. With this data you should be able to create a stacked label.  The ArcGIS Pivot Table tool can be used to output mutiple variations of the table depending on the summary you wanted.  For example it could have just used the FamilyName fields for the data values. However, multiple fields need to be concatenated into a single field prior to running the tool.  Anyway, let me know if these results are basically what you needed and if you want other summary value variations. ... View more

I am using student edition of ArcGIS 9.3  in arctoolx does not contain pivot tables function. I was wondering if someone could help construct a model builder with excel data using  so that i can link it to shape file  the pivot table. found out my university has it. but because of the up comming thanksgiving breaks the GIS Lab will be closed for maintance. any help one give me is appreciated....... All the stuff been reading and looking at  has lead me to conlusion this probably my only way to preced further in joining to my shapefile WIthout the ArcInfo licence I do not think that there is any way to use ModelBuillder to acheive the results of the Pivot tool with a lower license.  Have you done the pivot in Excel already?  Is it Excel 2003 and can you export to dbase format?  Or are you using Excel 2007?  Anyway, perform the pivot with Excel and export the data first to see if the results are what you want before trying to script this. I do not think Modelbuilder can do interop with any Excel program interfaces to actually perform a Pivot, it can just use Excel files (in Excel 2003 format) with its own tools.  Integrating with Excel itself might be possible with native Python, but I don't know how to do that.  A Python script can acheive the results of the Pivot tool without interop with Excel, if you have the original non-pivoted data in ArcMap, but someone would need to breakdown how pivots work and recreate the bahavior.  Probably your lab will be back online before anyone can provide code to do that.  Anyway, please decribe your current configuration better so we can figure out your best option. Use a file geodatabasee if you want to be ready for the future of GIS. ... View more

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. ... View more

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. ... View more

I followed an example from an arcobjects' book how to create selection layer, but I need to modify in part of query and zoom to feature, when I got stuck in query, I ask to this forum by the title confusing query filter. Now, the problem is solved. Thanks Richard, Thank you so much.. Regards, Amie I am glad it is working for you now.  Did you end up with just the first Sub or did you keep 2 Subs?  Did you end up keeping the line that stored the Definition Query whereclaues on the Soils layer or deleting it?  Did you leave the features selected or clear the selection at the end of the Sub?  Just curious to know what behaviors you ultimately were looking for. ... View more