Tables can store data in the following generic forms, which are supported with specific native data types as listed in the subsequent table.
Binary data |
Variable length binary data. Maximum storage size is 2 GB |
Boolean values |
TRUE or FALSE, also expressed as 1 or 0 |
Dates and time |
The datetime type represents data that contains a calendar date (day, month, year) and time (hour, minute, second, millisecond) ranging from January 3, 0001, 00:00:00.000 to December 31, 9999, 23:59:59.999 |
Geometry |
Geometry is vector data that defines the shape and location of vector objects. Geometry can be Manifold-specific geometry data types for 3D and 2D geometry and also support for OGC (Open Geospatial Consortium) geometry. Manifold geometry types can mix area, line, and point objects, including multi-branched versions of such objects, and can also include in the mix objects created from curvilinear segments such as spline arcs. |
Numbers |
Numeric data can be either scalar numbers, a single number in the field, or vector numeric data, an ordered set of one, two, three, or four scalar numbers per field. Scalar numbers are simply called numbers for short. Vector values are called vectors, with the individual numbers in the vector set called vector 0, vector 1, vector 2, and vector 3.
A wide range of data types support storage of scalar numbers, for signed or unsigned, floating point or integer numbers, from 8 to 64 bits per number.
Vectors can be a set of one, two, three, or four numbers. All of the numbers in a vector are the same numeric data type. For example, we could have a vector that is a set of three float64 numbers, but we could not have a vector that is a set of a uint8, a float64, and an int32. |
Text |
Text data is sequences of characters, also called strings. Text is stored as variable length Unicode or non-Unicode text. An individual text field has 2 GB maximum storage size for a total of 1 GB Unicode characters or 2 GB non-Unicode characters. Text types always can store variable length text: to constrain a field to allow only a specific number of text characters, add a constraint on that field in the table's schema. |
Tiles |
Tiles save raster data as a rectangular array of numbers of specified size, such as 128 x 128, where each number in the array is an allowed Manifold numeric type, including vector numeric types such as uint8x3. |
UUIDs |
A Universally Unique IDentifier (UUID), is a 128 bit value represented by a text string of lower-case hexadecimal digits in standard form of groups of digits separated by hyphens. Each UUID is unique. |
The following specific data types implement the above generic forms and can be used in a table:
boolean |
TRUE or FALSE, also expressed as 1 or 0 |
datetime |
A calendar date (day, month, year) and time (hour, minute, second, millisecond) ranging from January 3, 0001, 00:00:00.000 to December 31, 9999, 23:59:59.999 |
float32 |
32 bit floating point number with a range of 1.5 x 10-45 to 3.4 x 1038 When using floating point numbers, resist the temptation to save space by using float32. At some future point it is easy to forget that a floating point number is a float32 and does not have the full precision afforded by float64. Use float64 instead. |
float32x2 float32x3 float32x4 |
Vectorsof 2, 3 or 4 numbers, each of which is a float32 |
float64 |
64 bit floating point number with a range of 5.0 x 10-324 to 1.7 x 10308 When using floating point numbers it is a good idea to use float64, since float64 provides full precision. Manifold uses float64 internally for maximum precision. |
float64x2 float64x3 float64x4 |
Vectors of 2, 3 or 4 numbers, each of which is a float64
|
geom geommfd |
Manifold geometry. geom is the native Manifold geometry type. geommfd is the equivalent written in binary for storage in external databases. Manifold geometry types can mix area, line, and point objects, including multi-branched versions of such objects, and can also include in the mix objects created from curvilinear segments such as spline arcs. Manifold geometry supports both 3D and 2D geometry. |
geomwkb |
OGC (Open Geospatial Consortium) WKB ("Well Known" Binary) geometry, limited to the OGC Simple Feature specification of point, line, and polygon objects. |
int8 |
8 bit signed integer for values from -128 to 127 |
int8x2 int8x3 int8x4 |
Vectors of 2, 3 or 4 numbers, each of which is an int8 |
int16 |
16 bit signed integer for values from -32,768 to 32,767 |
int16x2 int16x3 int16x4 |
Vectors of 2, 3 or 4 numbers, each of which is an int16 |
int32 |
32 bit signed integer for values from -2,147,483,648 to 2,147,483,647 |
int32x2 int32x3 int32x4 |
Vectors of 2, 3 or 4 numbers, each of which is an int32 |
int64 |
64 bit signed integer for values from -9,223,372,036,854,775,808 to 9,223,372,036,854,775,807 |
int64x2 int64x3 int64x4 |
Vectors of 2, 3 or 4 numbers, each of which is an int64 |
nvarchar |
Variable length Unicode text using the UNICODE UCS-2 character set, two bytes per character. 2 GB maximum storage size for a total of 1 GB characters. |
tile |
A Manifold array of numbers of specified size, such as 128 x 128, where each number in the array is an allowed Manifold numeric type, including vector numeric types such as uint8x3. Tiles are used to store raster data. |
uint8 |
8 bit unsigned integer for values from 0 to 255 |
uint8x2 uint8x3 uint8x4 |
Vectors of 2, 3 or 4 numbers, each of which is a uint8 |
uint16 |
16 bit unsigned integer for values from 0 to 65,535 |
uint16x2 uint16x3 uint16x4 |
Vectors of 2, 3 or 4 numbers, each of which is a uint16 |
uint32 |
32 bit unsigned integer for values from 0 to 4,294,967,295 |
uint32x2 uint32x3 uint32x4 |
Vectors of 2, 3 or 4 numbers, each of which is a uint32 |
uint64 |
64 bit unsigned integer for values from 0 to 18,446,744,073,709,551,615 |
uint64x2 uint64x3 uint64x4 |
Vectors of 2, 3 or 4 numbers, each of which is a uint64 |
uuid |
Universally Unique IDentifier (UUID), a 128 bit value represented by a text string of lower-case hexadecimal digits in standard form of groups of digits separated by hyphens. Each UUID is unique. |
varbinary |
Variable length binary data. Maximum storage size is 2 GB |
varchar |
Variable length non-Unicode text, one byte per character. 2 GB maximum storage size for a total of 2 GB characters. |
Tooltips on table column headers will show the name of the field as well as the data type:
Press the filter button and choose Show
Type to show data types for each field in the Layers pane.
The Layers pane is a convenient way to have an "always open" display, if desired, of data types for all fields in a table.
Automatic conversions, for example, using CAST always round down from FLOATxxx to INTxxx data types, so a floating point number that is 39.95 will be CAST into an integer as 39, not as 40. To convert a floating point number to an integer using conventional rounding up and down, first use the Round function to round the floating point number up or down and then convert.
Dataports for databases expose all text fields as NVARCHAR (Unicode) even if they are stored as VARCHAR. Attempting to create a VARCHAR field will create it as VARCHAR on the database, but the field will look like an NVARCHAR data type in Manifold. What VARCHAR means varies between data sources and converting between different meanings frequently loses data, so this convention helps to preserve data.
Consider an example: In Manifold SQL, VARCHAR means 'characters in the currently active codepage, whatever that currently active codepage may be.' However, in databases VARCHAR usually means 'characters in the codepage associated with the field / table / database'. If we connect to a database, we can ask it to return data as VARCHAR, and the database might return data in a German codepage if a German code page was used for that field. If we then try to use that data as VARCHAR on an English system, the characters will be interpreted wrongly. That will affect both correct display of the characters as well as comparisons and orderings. Treating the data as NVARCHAR, and doing the conversion on the fly, avoids such wrong interpretation. Here is why:
Different client systems use different ways of dealing with such issues. For example, Manifold Release 8, an older product, allows setting a codepage for the field. However, the end result of such adaptations is to convert the data in all codepages different from the default code page into Unicode and then handle data as Unicode. If Unicode ends up being the intermediate form, it is better, as newer Manifold products do, to simply make the conversion as close to the data as possible.
See related comments in the DBMS Data Sources - Notes topic.
Why "N"varchar? - Why the n prefix for Unicode versions of varchar? Before Unicode got traction, character sets for different languages were known as National Character data sets. MySQL still calls these types national character and national varchar. Manifold and SQL Server call these nvarchar (SQL Server adds an nchar type to match SQL Server's char type), and Oracle calls them nchar and nvarchar2. PostgreSQL and DB2 have char and varchar without "N" versions. See the Collations topic for more on how national languages are handled.
Computed fields - Fields in tables can be computed fields, which automatically calculate a value for the field based on an expression. See the Example: Add a Computed Field to a Table topic for a step by step example.
Geometry collections - Reading geometry collection values automatically merges individual values of the same underlying type used in Manifold geometry, such as area, line or point, with differences between subtypes such as line and multiline being ignored. The result of the merge is returned. This applies to all data which support geometry collection values, including WKB, GeoJSON, JSON, native geometry in database-specific formats, and so on.
Reading geometry collection values with individual values of mixed underlying types automatically converts areas to lines and lines to points in order to return all coordinates. Example: reading a geometry collection with an area and several points will return a multipoint containing all coordinates of all individual values.
3D conversions - Geometry values with mixed 2Dand 3Dcoordinates in GML, GeoJSON, and TopoJSON are automatically converted to 3D with 2D coordinates padded with zeros.