Data Types

Tables can store data in the following generic forms, which are supported with specific native data types as listed in the subsequent table.

Overview

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.

 

Data Types

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

Tooltips on table column headers will show the name of the field as well as the data type:

 

Layers Pane and Data Types

 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 and Rounding

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.  

 

VARCHAR and NVARCHAR Data Types for Databases

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.

Notes

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.

See Also

Tables

 

Editing Tables

 

Indexes

 

Add an Index to a Table

 

Queries

 

Spatial Data

 

Collations

 

DBMS Data Sources - Notes