Formulae FAQ's
|
 |
This
document contains the answers to frequently asked questions (FAQ)
about formulae.
Questions in this FAQ:
- How powerful are formulae and
where can they be used?
- How Do I put today's date on a
Print Template?
- How can I convert Lat & Lon
from Decimal degrees to DMS?
- How can I compare dates using
a formula?
1)
Question: How powerful are formulae and where can they be used?
Answer:
Cadcorp SIS formulas are often used to
reformat properties or attributes into a more informative style,
particularly when used in Label Themes.
The area of a land parcel could be displayed by simply using the
_area# property, but is often required in hectares
instead of square metres. (1 hectare = 10,000 sq. metres.) The formula
_area# / 10000 would achieve this, and it could be further improved
by displaying the value to two decimal places, with the abbreviation
"Ha" after the value:
FormatFlt (_area# / 10000, "%8.2f")
+ " Ha."
Now suppose you want to display the area in square
metres if it is less than 1 hectare.
If you have ever looked at a computer program, you may have seen a
set of instructions which allow the program to make a decision. This
is known as an If..Then structure:
If condition is true Then
Perform action A
ElseIf condition is false Then
Perform action B
End If
This sort of multi-line programming structure cannot
be reproduced in a single line SIS formula, so you are provided with
Iif, which lets you state the condition, action A
and action B all on one line. (Iif is pronounce "eye
if".)
Iif is used in the following way:
Iif (condition, action if true, action
if false)
If the area is less than 10000 square metres, display
metres result, otherwise display hectares result.
Condition:
_area# < 10000
Action if true:
FormatFlt (_area#, "%8.2f") + " Sq.m."
Action if false:
FormatFlt (_area# / 10000, "%8.2f") + "
Ha."
Put this together using Iif:
Iif (_area# < 10000, FormatFlt (_area#, "%8.2f")
+ " Sq.m.", FormatFlt (_area# / 10000, "%8.2f")
+ " Ha.")
"Iif you can keep your head while
all about you are losing theirs…" Rudyard
Kipling
Sometimes you will want to make more than simple "yes/no"
decisions. For example, you may want to display a date in the format
5th November 2004 using the FormatDate
function. Most days require "th" after the number, but days
1, 2, 3, 21, 22, 23 and 31 all require a different suffix. These sort
of decisions can still be made by "nesting" one Iif statement
inside another.
It's best to work out each bit of the statement first, possibly using
Windows Notepad, then join the bits together when you've tested each
bit. Put the formula together using placeholders for longer bits,
then substitute these at the end.
Firstly the day; this is retrieved by using FormatDate
on the Date() function:
Day = FormatDate(Date(), "%d")
If Day is 1, 21 or 31 (but not 11) then use st
as the suffix, e.g. 21st.
If Day is 2 or 22 (but not 12) then use nd as
the suffix, e.g. 2nd.
If Day is 3 or 23 (but not 13) the use rd as
the suffix, e.g. 23rd.
Otherwise use th.
Start with the decision for the st
suffix:
If Day = 1 or Day = 21 or Day = 31 then suffix
= st, otherwise …
In other words, if the condition is true, use the
st suffix. If the condition is false, make another
decision. That other decision will be to decide if the day requires
the nd suffix:
If Day = 2 or Day = 22 then suffix = nd,
otherwise…
This next "otherwise" requires yet another
decision:
If Day = 3 or Day = 23 then use rd suffix, otherwise
use th suffix.
All this requires 3 Iif formulas to be "nested"
together:
Iif (If Day = 1 or Day = 21 or Day = 31, "st",
Iif (Day = 2 or Day = 22, "nd", Iif (Day = 3 or Day
= 23, "rd", "th")))
Notice that the left and right brackets must be
equal in number.
Now wherever "Day" is referred to, you will need to substitute
the FormatDate formula. Also remember that FormatDate gives you a
'string' value,, so you will need to put all the Day numbers in quotes
(double or single) in two digit format:
Iif(FormatDate(Date(), "%d")=
'01' or FormatDate(Date(), "%d")= '21' or FormatDate(Date(),
"%d") = '31', "st", Iif(FormatDate(Date(),
"%d") = '02' or FormatDate(Date(), "%d") ='22',
"nd", Iif(FormatDate(Date(), "%d") = '03'
or FormatDate(Date(), "%d") = '23', "rd",
"th")))
…and all that just gets the suffix!
The full formula needs the day number, the suffix, then the month
then the year, so here goes:
Str(Val(FormatDate(Date(), "%d")))
+ Iif(FormatDate(Date(), "%d")= '01' or FormatDate(Date(),
"%d")= '21' or FormatDate(Date(), "%d") =
'31', "st", Iif(FormatDate(Date(), "%d") =
'02' or FormatDate(Date(), "%d") ='22', "nd",
Iif(FormatDate(Date(), "%d") = '03' or FormatDate(Date(),
"%d") = '23', "rd", "th"))) + FormatDate(Date(),
" %B %Y")
Note: Before you paste the above text into Cadcorp
SIS, paste it into Notepad first and make sure there are no carriage
returns. It must be all on one line.
2)
Question: How Do I put today's date on a Print Template?
Answer: Many
GIS datasets hold date information. Dates are generally represented
in one of two ways:
- As a string of characters in a format recognisable
to people, e.g. 12/10/2004
- As a numeric value meaningful to a computer database
or spreadsheet, e.g. 38299.4877893519
Cadcorp SIS provides two formula functions to help
with managing date information, CvDate and FormatDate.
There is also the Date function to provide you with
the current system date and time.
FormatDate
Generally you will find that date information is held in databases
as a floating point (decimal) number, where the digits before the
decimal represent the date, and the digits after the decimal represent
the time. In the example above the number represents 11:42:25 on 8/11/2004.
To extract the date in the conventional British format of day/month/year
you need to use the FormatDate function. This requires
a string containing special characters to indicate the position and
format of the date component.
The day of the month is represented by a lowercase d,
so this can be displayed using:
FormatDate(38299.4877893519,"%d")
Similarly the month is represented by a lowercase
m, and the year, in full, as a capital Y. By including
all these placeholders, each prefixed by a percent (%) sign, into
a string of characters, the full date can be displayed:
FormatDate(38299.4877893519,"%d/%m/%Y")
becomes
08/11/2004
FormatDate(38299.4877893519,"%H:%M:%S")
becomes
11:42:25
This result is a string value, and cannot be used
to compare with other dates or times. The full list of date/time placeholder
options is shown below:
| %a
|
Abbreviated weekday
name |
| %A |
Full weekday name |
| %b |
Abbreviated month name |
| %B |
Full month name |
| %c |
Date and time representation
appropriate for locale |
| %d |
Day of month as decimal number (01 to 31) |
| %H |
Hour in 24-hour format (00
to 23) |
| %I |
Hour in 12-hour format (01 to 12) |
| %j |
Day of year as decimal number
(001 to 366) |
| %m |
Month as decimal number (01 to 12) |
| %M |
Minute as decimal number (00
to 59) |
| %p |
Current locale's A.M./P.M. indicator for 12-hour
clock |
| %S |
Second as decimal number (00
to 59) |
| %U |
Week of year as decimal number, with Sunday as
first day of week (00 to 51) |
| %w |
Weekday as decimal number
(0 to 6; Sunday is 0) |
| %W |
Week of year as decimal number, with Monday
as first day of week (00 to 51) |
| %x |
Date representation for current
locale |
| %X |
Time representation for current locale |
| %y |
Year without century, as decimal
number (00 to 99) |
| %Y |
Year with century, as decimal number |
| %z, %Z |
Time-zone name or abbreviation;
no characters if time zone is unknown |
| %% |
Percent sign |
The Date() function returns today's date and time
as held by your PC in its system settings. By using this in a FormatDate
expression you can easily display today's date.
To place this date as text you will need to use the ^
(caret) character. This tells Cadcorp SIS to convert the formula which
follows into plain text:
^(FormatDate(Date(),"%A, %d %B %Y"))
will be displayed in the format Monday, 08 November 2004
By storing your Print Template with this text
on it, the actual date will be correctly represented whenever the
template is used.
Troubleshooting
The formula appears in the text, rather
than the actual date.
This is probably because you have used Box Text, rather than Point
Text. Only Pont Text will translate formulas using the caret symbol.
A ? (question mark) appears instead of
a date
The formula has to be exactly as shown above, or another valid formula.
If SIS can't translate it, you will just see a question mark. Check
that each opening bracket has a corresponding closing bracket. Like
all computer programs, SIS is very fussy about formula syntax.
3)
Question: How can I convert Lat & Lon from Decimal degrees to
DMS?
Answer: Add
these formula as new schema items:
Latitude:
Str(Fix(_oLat#))+chr(176)+" "+Str(Fix((Abs(_oLat#)-Fix(Abs(_oLat#)))*60))+chr(39)+"
"+FormatFlt(Abs(((((_oLat#-Fix(_oLat#))*60)-Fix((_oLat#-Fix(_oLat#))*60))*60)),"%2.2f")+chr(34)
Longitude:
Str(Fix(_oLon#))+chr(176)+" "+Str(Fix((Abs(_oLon#)-Fix(Abs(_oLon#)))*60))+chr(39)+"
"+FormatFlt(Abs(((((_oLon#-Fix(_oLon#))*60)-Fix((_oLon#-Fix(_oLon#))*60))*60)),"%2.2f")+chr(34)
4)
Question: How can I compare dates using a formula?
Answer:
Many GIS datasets hold date information. Dates
are generally represented in one of two ways:
- As a string of characters in a format recognisable
to people, e.g. 12/10/2004
- As a numeric value meaningful to a computer database
or spreadsheet, e.g. 38299.4877893519
Cadcorp SIS provides two formula functions
to help with managing date information, CvDate
and FormatDate. There is also the Date
function to provide you with the current system date and time.
CvDate
Generally you will find that date information is held in databases
as a floating point (decimal) number, where the digits before the
decimal represent the date, and the digits after the decimal represent
the time. In the example above the number represents 11:42:25 on
8/11/2004.
The CvDate function lets you specify date (and
time) information in a way which a computer can compare with other
dates.
Your spatial data may be joined to a database table which holds
the dates on which crops were planted. Because the dates are in
a database they are held in Date/Time format, which SIS represents
as a double-precision floating point number. Using FormatDate
you can display this in a human-readable format.
To compare the planting dates in the database with a specified date,
you will need to convert the specified date to a decimal number.
All fields planted after 31/01/2004 could be found using the following
formula:
PlantingDate# > CvDate (2004, 1, 31)
Note the order that CvDate requires the year,
month and day.
|
|
|
|
|
Technical
FAQ's |
 |
Not what you are looking for, check out frequently
asked questions (FAQ) about:
More information?
Guide
to Transparency in Cadcorp SIS
Cadcorp
SIS Control Development Modules (CDM) - Questions & Answers
Cadcorp
Positional Accuracy Manager
|
