Layered symbols from one database field

Original User: pinghanling

Hi,

We have a client in the oil industry that need to symbolise their wells according to criteria that I will explain below.

By the way, I have looked at the built-in Esri symbols, but this client takes the symbology further by combining oil, gas and condensate wells into one symbol.

They represent the characteristics of a well in one attribute field that has the the form C<number>G<number>O<number>.

The number always has one digit and ranges from 1 to 5, an example of an attribute value is C2G4O1.

The numbers indicate an increasing show of a resource in that well. So a G5 well contains more gas than a G1 well. In fact, G1 indicates no gas is present.

The symbols are designed to be stacked on top of one another. A basic well is shown in the 'basic well.png' attachment. (Sorry, didn't manage to insert images directly into text.)

Gas wells get an increasing number of lines around the edges of the basic well as shown in the attached 'G2 to G5.png' image.

Oil and condensate potentials are shown through filled in quadrants and triangles respectively as shown in the attached 'O2 and C2.png'.

So an attribute value of C5G5O5 should be symbolised like the attached 'C5G5O5.png'.

I could go and create 64 symbols that represent all the possible combinations of C, O and G, but my question is whether there is some way to layer the symbols on top of one another so that I only have to create 12 symbols - 4 each for C, O and G.

One solution that I can see is to extract the C<number>, O<number> and G<number> from the attribute field and store it in three new columns. Then I add the layer three times to the map and symbolise on the C column for the first, the O column for the second and the G column for the third. The problem I can see with this solution is that when a new row is added to the database, the C, O and G columns need to be automatically calculated. Could this be done with a calculated value in the backend database - SQL Server?

Does anyone have any ideas for a fully automatic solution, i.e. if scripts, they need to run automatically.

Thanks
Hanlie