Symbolize using date field

[SamuelHenderson]

Hi All,

I'm making an ICommand Extension for ArcMap 9.3.1.  Basically, I'd like to be able to look at a date field in a featureclass, and if it is older than 5 years (from the current date) it should be symbolized as a red point, and if the date is less than 5 years old it should be symbolized with a green point.

At first I thought a ClassBreakRenderer would be the solution using something like:

Dim classBreakRenderer As IClassBreaksRenderer = New ClassBreaksRenderer
classBreakRenderer.Field = "LAST_ACTIVITY_DATE"
classBreakRenderer.BreakCount = 2
classBreakRenderer.Break(0) = " < #" & DateTime.Now.AddYears(-5).ToShortDateString & "#"
classBreakRenderer.Break(1) = " >= #" & DateTime.Now.AddYears(-5).ToShortDateString & "#"

But it seems that the Break property requires a number and will not work for a date or string...

Is there some better way to go about doing what I need to do?  It seems like this should be a pretty typical operation...

[JamesCrandall]

Use the UniqueValueRendering to accomplish this.  These are the basic steps to it:

1. Get a SelectionSet/FeatureCursor on the Layer you want to symbolize.  (this means you'd want to set the IQueryFilter's where clause twice, once for each date.

2. Setup a SimpleMarkerSymbol.  In your case, esriSMSCircle will work.

3. Create/Setup a New UniqueValueRenderer

4. Cycle thru each of the IFeatures in the FeatureCursor, setting the Renderer.

Here is an example Sub that is out of one of my applications that renders a point feature layer based upon it's "flag" field.  This could really be any field in the layer, so just replace it with your own requirements.

Good luck!

 Private Sub UniqueValuePointRenderer(ByVal pFL As IFeatureLayer, ByVal inFlag1 As String, ByVal inFlag2 As String)

        Try

            Dim pFClass As IFeatureClass = pFL.FeatureClass
            Dim pfeature As IFeature

            Dim pQF As IQueryFilter = New QueryFilter
            '' Set the where clause
            Dim tStr, pStr As String
            tStr = "FlagType = '" & inFlag1 & "'"
            pStr = "FlagType = '" & inFlag2 & "'"

            pQF.WhereClause = tStr

            Dim pFeatureSelection As IFeatureSelection
            pFeatureSelection = pFL
            pFeatureSelection.SelectFeatures(pQF, esriSelectionResultEnum.esriSelectionResultNew, False)
            pFeatureSelection.SelectionChanged()

            Dim pSelectionSet As ISelectionSet
            pSelectionSet = pFeatureSelection.SelectionSet

            Dim pFeatureCursor As IFeatureCursor
            pFeatureCursor = Nothing
            pSelectionSet.Search(Nothing, True, pFeatureCursor)

            Dim symd As SimpleMarkerSymbol
            symd = New SimpleMarkerSymbol
            symd.Style = esriSimpleMarkerStyle.esriSMSCircle
            symd.Size = 4

            'Create unique value renderer
            Dim pUVRenderer As IUniqueValueRenderer
            pUVRenderer = New UniqueValueRenderer
            pUVRenderer.FieldCount = 1
            pUVRenderer.Field(0) = "FlagType"
            pUVRenderer.DefaultSymbol = symd
           
            Dim pSym As ISimpleMarkerSymbol
            Dim col1, col2 As IRgbColor
            col1 = New RgbColor
            col1.Red = 145
            col1.Green = 145
            col1.Blue = 145

            col2 = New RgbColor
            col2.Red = 200
            col2.Green = 200
            col2.Blue = 200

            pfeature = pFeatureCursor.NextFeature
            Do Until pfeature Is Nothing
                pSym = New SimpleMarkerSymbol
                pSym.Style = esriSimpleMarkerStyle.esriSMSCircle
                pSym.Size = 4
                pSym.Outline = True
                pSym.Color = col1
                pUVRenderer.AddValue(pfeature.Value(pFClass.FindField("FlagType")), "", pSym)

                pfeature = pFeatureCursor.NextFeature
            Loop

            'reset the where clause of the queryfilter
            pQF.WhereClause = pStr

            pFeatureSelection.SelectFeatures(pQF, esriSelectionResultEnum.esriSelectionResultNew, False)
            pFeatureSelection.SelectionChanged()

            pSelectionSet = pFeatureSelection.SelectionSet

            pfeature = Nothing
            pFeatureCursor = Nothing
            pSelectionSet.Search(Nothing, True, pFeatureCursor)

            pfeature = pFeatureCursor.NextFeature
            Do Until pfeature Is Nothing
                pSym = New SimpleMarkerSymbol
                pSym.Style = esriSimpleMarkerStyle.esriSMSCircle
                pSym.Size = 8
                pSym.Outline = True
                pSym.Color = col2
                pUVRenderer.AddValue(pfeature.Value(pFClass.FindField("FlagType")), "", pSym)

                pfeature = pFeatureCursor.NextFeature
            Loop

            Dim pGFLayer As IGeoFeatureLayer
            pGFLayer = pFL

            pGFLayer.Renderer = pUVRenderer

        Catch ex As Exception
            MsgBox(ex.ToString)
        End Try
    End Sub

[SamuelHenderson]

Thanks jamesfreddyc, your solution almost worked...

The only issue with it is that it creates a symbol for each unique date which looks rather ugly in the TOC.  Ultimately there should only be two symbols in the TOC; one for old features (> than 5 years old) and one for new features (<= 5 years old).

Is it still possible to achieve this using a UniqueValueRenderer?

[JamesCrandall]

Thanks jamesfreddyc, your solution almost worked...

The only issue with it is that it creates a symbol for each unique date which looks rather ugly in the TOC.  Ultimately there should only be two symbols in the TOC; one for old features (> than 5 years old) and one for new features (<= 5 years old).

Is it still possible to achieve this using a UniqueValueRenderer?

huh.  The code I provided does exactly that for me --- it symbolizes the point layer with ONLY 2 specific symbols that is based upon the values found in "FlagType".  So if FlagType = 'x', then the point symbols were size 4 and if FlagType = 'Y' then the point symbols were size 8 (of course that's just a generalization).

Maybe the problem lies in the WHERE clause?  Not sure.

What exactly are you seeing for symbols?  And lets see the code you are using (we might be able to help you alter it a bit to accomplish what you want).

[SamuelHenderson]

huh. The code I provided does exactly that for me --- it symbolizes the point layer with ONLY 2 specific symbols that is based upon the values found in "FlagType". So if FlagType = 'x', then the point symbols were size 4 and if FlagType = 'Y' then the point symbols were size 8 (of course that's just a generalization).

Hmm.  I think I know why...

 
Dim queryFilter As IQueryFilter = New QueryFilter '
            Dim needsExcerciseFilter As String = "LAST_EXERCISE_DATE < TO_DATE('" & DateTime.Now.AddYears(-5).ToString("yyyy-MM-dd HH:mm:ss") & "', 'YYYY-MM-DD HH24:MI:SS')"
            Dim okExcerciseFilter As String = "LAST_EXERCISE_DATE >= TO_DATE('" & DateTime.Now.AddYears(-5).ToString("yyyy-MM-dd HH:mm:ss") & "', 'YYYY-MM-DD HH24:MI:SS')"

            queryFilter.WhereClause = needsExcerciseFilter
            Dim featSelection As IFeatureSelection = featLayer
            featSelection.SelectFeatures(queryFilter, esriSelectionResultEnum.esriSelectionResultNew, False)
            featSelection.SelectionChanged()

            Dim selectionSet As ISelectionSet = featSelection.SelectionSet
            Dim featCursor As IFeatureCursor = Nothing
            selectionSet.Search(Nothing, True, featCursor)

            Dim symd As New SimpleMarkerSymbol
            symd.Style = esriSimpleMarkerStyle.esriSMSCross
            symd.Size = 4

            Dim uvRenderer As IUniqueValueRenderer = New UniqueValueRenderer
            uvRenderer.FieldCount = 1
            uvRenderer.Field(0) = "LAST_EXERCISE_DATE"
            uvRenderer.DefaultSymbol = symd

            Dim sym As ISimpleMarkerSymbol = Nothing
            Dim col1, col2 As IRgbColor

            col1 = New RgbColor
            col1.Red = 255
            col1.Green = 0
            col1.Blue = 0

            col2 = New RgbColor
            col2.Red = 0
            col2.Green = 255
            col2.Blue = 0

            feature = featCursor.NextFeature
            Do Until feature Is Nothing
                sym = New SimpleMarkerSymbol
                sym.Style = esriSimpleMarkerStyle.esriSMSDiamond
                sym.Size = 4
                sym.Outline = True
                sym.Color = col1
                uvRenderer.AddValue(feature.Value(feature.Fields.FindField("LAST_EXERCISE_DATE")), "", sym)
                feature = featCursor.NextFeature()
            Loop

            queryFilter.WhereClause = okExcerciseFilter
            featSelection.SelectFeatures(queryFilter, esriSelectionResultEnum.esriSelectionResultNew, False)
            featSelection.SelectionChanged()

            selectionSet = featSelection.SelectionSet
            feature = Nothing
            featCursor = Nothing
            selectionSet.Search(Nothing, True, featCursor)

            feature = featCursor.NextFeature
            Do Until feature Is Nothing
                sym = New SimpleMarkerSymbol
                sym.Style = esriSimpleMarkerStyle.esriSMSSquare
                sym.Size = 4
                sym.Outline = True
                sym.Color = col2
                uvRenderer.AddValue(feature.Value(feature.Fields.FindField("LAST_EXERCISE_DATE")), "", sym)

                feature = featCursor.NextFeature
            Loop

            featSelection.Clear()
            featSelection.SelectionChanged()

            Dim geoFeatLayer As IGeoFeatureLayer = featLayer
            geoFeatLayer.Renderer = uvRenderer

            mapDoc.UpdateContents()
            mapDoc.ActiveView.Refresh()

So you see, my renderer is symbolizing on the actual date field, which I think is why there is a symbol for every unique date...

To further elaborate on what happens with this code...  It does render the correct symbols with the correct colours, (so valves with exercise dates older than 5 years are red squares and valves with exercise dates less than 5 years ago are green diamonds, it just adds each specific date to the TOC...

[JamesCrandall]

One solution (just a first thought) is to add and populate another field (say you call it "Exercised") similar to the way I have implemented a "FlagType".  Basically, you'd populate this field with values that can only be one of two things (strings): Exercised>5yrs, Exercised<5yrs

This would occur before any rendering would happen, so that when you do get to your rendering you could set your queryFilter to either of these strings and the renderer to this field as well:

uvRenderer.AddValue(feature.Value(feature.Fields.FindField("Exercised")), "", sym)


Hope this is clear and was just a quick thought. 

james

[SamuelHenderson]

One solution (just a first thought) is to add and populate another field (say you call it "Exercised") similar to the way I have implemented a "FlagType". Basically, you'd populate this field with values that can only be one of two things (strings): Exercised>5yrs, Exercised<5yrs

This would occur before any rendering would happen, so that when you do get to your rendering you could set your queryFilter to either of these strings and the renderer to this field as well:

Yeah I was thinking something like that as well.  It would need to 'virtual' and populated by my code though because we don't want to add an extra (physical) column to our FeatureClass which would mean someone would need to update it periodically.

Is there a way using a DefinitionQuery or QueryDef to have some kind of an SQL CASE in the Fields property that returns a 'computed' column?

[SamuelHenderson]

Yeah I was thinking something like that as well.  It would need to 'virtual' and populated by my code though because we don't want to add an extra (physical) column to our FeatureClass which would mean someone would need to update it periodically.

Is there a way using a DefinitionQuery or QueryDef to have some kind of an SQL CASE in the Fields property that returns a 'computed' column?

Well, guess not.  I tried every which way I could think of to add that field on-the-fly (short of actually starting an editing session and adding it anyways) and it just would not work.

What I basically wound up doing was creating a queryfilter for dates past the 'old/need exercising' threshold and use it to get a selection set.  I then defined a FeatureLayerDefinition for the main layer and used it's CreateSeletionLayer()  method to create a new layer.  I then created a SimpleMarkerSymbol and SimpleRenderer for this new layer.  Then I set the queryFilter's WhereClause to grab the non-old dates, created another selection set, another new layer from the selection set, added the symbol and renderer for the new layer (with a different color of course :))
and then had the MapDocument add both these new layers (and refreshed the active view of course).