ISO/IEC TR 19075-8:2019
(Main)Information technology database languages — SQL technical reports — Part 8: Multi-dimensional arrays (SQL/MDA)
Information technology database languages — SQL technical reports — Part 8: Multi-dimensional arrays (SQL/MDA)
This Technical Report describes the support in SQL for Multi-Dimensional Arrays (MDA) as defined in ISO/IEC 9075-15.
Langages de base de données IT — SQL rapport techniques — Partie 8: Tableaux multidimensionnels
General Information
Relations
Standards Content (Sample)
TECHNICAL ISO/IEC TR
REPORT 19075-8
First edition
2019-05
Information technology database
languages — SQL technical reports —
Part 8:
Multi-dimensional arrays (SQL/MDA)
Langages de base de données IT — SQL rapport techniques —
Partie 8: Tableaux multidimensionnels
Reference number
ISO/IEC TR 19075-8:2019(E)
©
ISO/IEC 2019
---------------------- Page: 1 ----------------------
ISO/IEC TR 19075-8:2019(E)
COPYRIGHT PROTECTED DOCUMENT
© ISO/IEC 2019
All rights reserved. Unless otherwise specified, or required in the context of its implementation, no part of this publication may
be reproduced or utilized otherwise in any form or by any means, electronic or mechanical, including photocopying, or posting
on the internet or an intranet, without prior written permission. Permission can be requested from either ISO at the address
below or ISO’s member body in the country of the requester.
ISO copyright office
CP 401 • Ch. de Blandonnet 8
CH-1214 Vernier, Geneva
Phone: +41 22 749 01 11
Fax: +41 22 749 09 47
Email: copyright@iso.org
Website: www.iso.org
Published in Switzerland
ii © ISO/IEC 2019 – All rights reserved
---------------------- Page: 2 ----------------------
ISO/IEC TR 19075-8:2019(E)
Contents Page
Foreword. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . vii
Introduction. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . viii
1 Scope. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 1
2 Normative references. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .3
3 Terms and definitions. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 5
4 Multidimensional Arrays (MDA) concepts. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 7
4.1 Concept. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 7
4.2 Why consider support for MDA in SQL?. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 7
4.3 Array representations. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .9
4.4 Use cases for MDA support in SQL. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 9
4.4.1 The use cases. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 9
4.4.2 Array data ingestion and storage. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 9
4.4.3 Integrated querying of array and relational data. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 10
4.4.4 Updating stored array data. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 10
4.4.5 Exporting arrays. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .10
4.5 Non-Use cases: Direct access to external array data. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 10
5 SQL/MDA data model. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 11
5.1 Data model concepts. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 11
5.2 MD-array. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 11
5.3 MD-array type definition. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 12
5.3.1 Type definition concepts. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 12
5.3.2 Element type. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 12
5.3.3 MD-dimension. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 13
5.3.4 MD-axis names. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 13
5.3.5 MD-axis lower and upper limits. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 13
5.3.6 Putting it all together. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 14
5.4 MD-array creation. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 16
5.4.1 MD-array creation concepts. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 16
5.4.2 Explicit element enumeration. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 17
5.4.3 From SQL table query result. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 18
5.4.4 Construction by implicit iteration. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 19
5.4.5 Decoding a format-encoded array. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 20
5.5 MD-array updating. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 21
5.5.1 MD-array updating introduction. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 21
5.5.2 Updating MD-arrays of equal MD-dimension. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 22
5.5.3 Updating MD-arrays of greater MD-dimension. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 23
5.5.4 Updating a single element of an MD-array. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 24
5.6 Exporting MD-arrays. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .24
©ISO/IEC 2019 – All rights reserved Contents iii
---------------------- Page: 3 ----------------------
ISO/IEC TR 19075-8:2019(E)
5.6.1 Encoding to a data format. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 24
5.6.2 Converting to an SQL table. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 26
6 SQL/MDA operations. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .29
6.1 Introduction to SQL/MDA operations. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 29
6.2 MD-extent probing operators. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 29
6.3 MD-array element reference. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 31
6.4 MD-extent modifying operations. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 32
6.4.1 Introduction to MDE-extent modifying operations. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 32
6.4.2 Subsetting. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 32
6.4.3 Reshaping. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 34
6.4.4 Shifting. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 36
6.4.5 MD-axis renaming. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .36
6.5 MD-array deriving operators. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 37
6.5.1 Introduction to MD-array deriving operators. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 37
6.5.2 Scaling. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 37
6.5.3 Concatenation. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 39
6.5.4 Induced operations. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .39
6.5.5 Join MD-arrays on their coordinates. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 46
6.6 MD-array aggregation. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 47
6.6.1 General aggregation expression. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 47
6.6.2 Shorthand aggregation functions. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 48
7 Remote sensing example. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 51
7.1 Introduction to remote sensing example. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 51
7.2 Data setup. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 51
7.3 Band math. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 53
7.3.1 Introduction to band math. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 53
7.3.2 NDVI. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 53
7.3.3 Band Swapping. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 56
7.4 Histograms. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 57
7.5 Change detection. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 58
7.6 Extracting features. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .59
7.7 Data search and filtering. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 60
Bibliography. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 63
Index. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 65
iv Multi-Dimensional Arrays (SQL/MDA) ©ISO/IEC 2019 – All rights reserved
---------------------- Page: 4 ----------------------
ISO/IEC TR 19075-8:2019(E)
Tables
Table Page
1 Examples of MD-array type definitions. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 15
2 Examples of MD-arrays constructed by element enumeration. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 18
3 Examples of MD-arrays created with the constructor by iteration. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 20
4 Examples of MD-arrays created from JSON-encoded arrays. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 21
5 Examples of MD-arrays encoded to JSON arrays. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 25
6 Result of example UNNEST query. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 26
7 Result of example UNNEST query specifying WITH ORDINALITY. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 27
8 Examples with MD-extent probing functions. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 30
9 Result of MDEXTENT(kernel). . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 30
10 Result of MDEXTENT_MAX(kernel). . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 30
11 Examples of referencing a single element in an MD-array. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 31
12 Examples of MD-array subsetting. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 34
13 Examples of MD-extent reshaping. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 35
14 Examples of MD-extent shifting. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 36
15 Examples of MD-axis renaming. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 37
16 Interpolation methods defined in ISO 19123:2005. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 38
17 Examples of MD-array concatenation. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 39
18 Examples of induced function application to MD-arrays. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 42
19 Operations corresponding to the grammar rules. . . . . . . . . . . . . . . . . . . . . . . . . . . 44
20 Examples of induced MD-array expressions. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 44
21 Example of induced MD-array casting. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 45
22 Examples of induced CASE expression. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 45
23 Examples of MDJOIN. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 47
24 Identity elements for the s. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 47
25 Examples of general MD-array aggregation. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 48
26 Predefined aggregation operators. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 49
27 Landsat TM bands. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 51
©ISO/IEC 2019 – All rights reserved Contents v
---------------------- Page: 5 ----------------------
ISO/IEC TR 19075-8:2019(E)
Figures
Figure Page
1 Aerial greyscale image of size 1024x1024 (San Diego). . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 8
2 Relationships between MDA and SQL/MDA. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 11
3 The structure of an MD-array value illustrated on a sample 3x3 array. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 12
4 Placement of satellite images of each country on a world map (from Geographic Bounding Boxes). . . . . . . . . . 14
5 Example of an SQL table that corresponds to a 3x3 MD-array. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 19
6 Example of an SQL table converted to a 3x3 MD-array with MD-extent [i(-1:1), j(-1:1)]. . . . . . . . . . . . . . . . . . . 19
7 Example of array update. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 23
8 Updating a 3-D MD-array with a 2-D source MD-array. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 24
9 MD-array subsetting examples. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 32
10 MD-array reshaping example. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 35
11 MD-array shifting example. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 36
12 MD-array scaling example. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 37
13 Concatenation examples. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 39
14 Example of summing two MD-arrays. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 40
15 Colorized array. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 46
16 Visible color (RGB) bands of a Landsat TM scene. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 52
17 NDVI result stretched to the range (0,255). . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 54
18 NDVI values between 0.2 and 0.4 shown in white, while everything else is black. . . . . . . . . . . . . . . . . . . . . . . . . 55
19 Color-mapped NDVI result, from dark blue, through grey, to dark green. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 56
20 False color image constructed from the near IR, red and green bands. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 57
21 Histogram of the NDVI index of a Landsat TM scene. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 58
22 A composite image with an NDVI index from different years in each channel. . . . . . . . . . . . . . . . . . . . . . . . . . . 59
23 Natural RGB color of barrier islands area. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 60
24 Binary image showing isolated islands. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 60
vi Multi-Dimensional Arrays (SQL/MDA) ©ISO/IEC 2019 – All rights reserved
---------------------- Page: 6 ----------------------
ISO/IEC TR 19075-8:2019(E)
Foreword
ISO (the International Organization for Standardization) and IEC (the International Electrotechnical Commission)
form the specialized system for worldwide standardization. National bodies that are members of ISO or IEC
participate in the development of International Standards through technical committees established by the
respective organization to deal with particular fields of technical activity. ISO and IEC technical committees
collaborate in fields of mutual interest. Other international organizations, governmental and non-governmental,
in liaison with ISO and IEC, also take part in the work.
The procedures used to develop this document and those intended for its further maintenance are described in
the ISO/IEC Directives, Part 1. In particular, the different approval criteria needed for the different types of
document should be noted. This document was drafted in accordance with the editorial rules of the ISO/IEC
Directives, Part 2 (see www.iso.org/directives).
Attention is drawn to the possibility that some of the elements of this document may be the subject of patent
rights. ISO and IEC shall not be held responsible for identifying any or all such patent rights. Details of any
patent rights identified during the development of the document will be in the Introduction and/or on the ISO
list of patent declarations received (see www.iso.org/patents), or the IEC list of patent declarations
received (see http://patents.iec.ch).
Any trade name used in this document is information given for the convenience of users and does not constitute
an endorsement.
For an explanation of the voluntary nature of standards, the meaning of ISO specific terms and expressions
related to conformity assessment, as well as information about ISO's adherence to the World Trade Organization
(WTO) principles in the Technical Barriers to Trade (TBT) see www.iso.org/iso/foreword.html.
This document was prepared by Technical Committee ISO/IEC JTC 1, Information technology, Subcommittee
SC 32, Data management and interchange.
A list of all parts in the ISO/IEC 19075 series can be found on the ISO website.
Any feedback or questions on this document should be directed to the user's national standards body. A complete
listing of these bodies can be found at www.iso.org/members.html.
NOTE 1 — The individual parts of multi-part standards are not necessarily published together. New editions of one or more parts
may be published without publication of new editions of other parts.
©ISO/IEC 2019 – All rights reserved Foreword vii
---------------------- Page: 7 ----------------------
ISO/IEC TR 19075-8:2019(E)
Introduction
This Technical Report describes the definition and use of multi-dimensional arrays in SQL. Multidimensional
arrays represent a core underlying structure of manifold science and engineering data. It is generally recognized
today, therefore, that arrays have an essential role in Big Data and should become an integral part of the overall
data type orchestration in information systems. This Technical Report discusses the syntax and semantics of
operations on the MD-array data type defined in ISO/IEC 9075-15.
The organization of this Technical Report is as follows:
1) Clause 1, “Scope”, specifies the scope of this Technical Report.
2) Clause 2, “Normative references”, identifies standards that are referenced by this Technical Report.
3) Clause 4, “Multidimensional Arrays (MDA) concepts”, introduces the concept of Multidimensional Arrays.
4) Clause 5, “SQL/MDA data model”, introduces the data model.
5) Clause 6, “SQL/MDA operations”, covers the supported operations on MD-arrays.
6) Clause 7, “Remote sensing example”, illustrates the supported functionality through realistic examples.
viii Multi-Dimensional Arrays (SQL/MDA) ©ISO/IEC 2019 – All rights reserved
---------------------- Page: 8 ----------------------
TECHNICAL REPORT ISO/IEC TR 19075-8:2019(E)
Information technology database languages — SQL technical reports —
Part 8:
Multi-dimensional arrays (SQL/MDA)
1 Scope
This Technical Report describes the support in SQL for Multi-Dimensional Arrays (MDA) as defined in ISO/IEC
9075-15.
©ISO/IEC 2019 – All rights reserved Scope 1
---------------------- Page: 9 ----------------------
ISO/IEC TR 19075-8:2019(E)
(Blank page)
2 Multi-Dimensional Arrays (SQL/MDA) ©ISO/IEC 2019 – All rights reserved
---------------------- Page: 10 ----------------------
ISO/IEC TR 19075-8:2019(E)
2 Normative references
There are no normative references in this document.
ISO and IEC maintain terminological databases for use in standardization at the following addresses:
— ISO Online browsing platform: available at https://www.iso.org/obp.
— IEC Electropedia: available at http://www.electropedia.org/.
©ISO/IEC 2019 – All rights reserved Normative references 3
---------------------- Page: 11 ----------------------
ISO/IEC TR 19075-8:2019(E)
(Blank page)
4 Multi-Dimensional Arrays (SQL/MDA) ©ISO/IEC 2019 – All rights reserved
---------------------- Page: 12 ----------------------
ISO/IEC TR 19075-8:2019(E)
3 Terms and definitions
For the purposes of this document, the following terms and definitions apply.
3.1 coordinate
non-empty ordered list of integers
3.2 cardinality
number of elements in an MD-array
3.3 MD-array
ordered collection of elements of the same type associated with an MD-extent where each element
is 1:1 associated with some coordinate within its MD-extent.
NOTE 2 — A coordinate is within an MD-extent if every coordinate value from the integer list is greater than or
equal to the lower limit, and less than or equal to the upper limit of the MD-interval of the MD-axis at the position
in the MD-extent as the coordinate value has within the coordinate
3.4 MD-axis
named MD-interval
3.5 MD-dimension
number of MD-axes in the MD-extent of an MD-array
NOTE 3 — Also known as “rank” outside of SQL/MDA
3.6 MD-extent
non-empty ordered collection of MD-axes with no duplicate names
3.7 MD-interval
integer interval given by a pair of lower and upper integer limits such that the lower limit is less than
or equal to the upper limit; the interval is closed, i.e., both limits are contained in it
©ISO/IEC 2019 – All rights reserved Terms and definitions 5
---------------------- Page: 13 ----------------------
ISO/IEC TR 19075-8:2019(E)
(Blank page)
6 Multi-Dimensional Arrays (SQL/MDA) ©ISO/IEC 2019 – All rights reserved
---------------------- Page: 14 ----------------------
ISO/IEC TR 19075-8:2019(E)
4.1 Concept
4 Multidimensional Arrays (MDA) concepts
4.1 Concept
The phrase “(Multidimensional) array, raster data” is used to refer to arrays generally, in contrast to the MD-
array term confined to the realm of SQL/MDA. It is not to be confused with the term “array” in ISO/IEC 9075-
2:2016. This document uses the term ARRAY for the original SQL array collection type.
The array concept is a simple and efficient data representation that finds its use in a wide array of fields, business-
related as well as scientific and engineering. Many sensors, images, image timeseries, simulation processes,
statistical models, and so on, produce raw data that can immediately be classified as array data. These data may
be naturally arranged along more than one axis: position and time, for example.
A multidimensional array (MDA) is a set of elements ordered in a multidimensional space. The space considered
here is discretized (also called rasterized or gridded), that is, only integer coordinates are admitted as positions
of the individual array elements. The number of integers needed to refer a particular position in this space is
the array's dimension (sometimes also referred to as its dimensionality).
An element can be a single value (such as an intensity value in case of grayscale images) or a composite value
(such as integer triples for the red, green, and blue components of a true-color image). All elements of an array
share the same structure, referred to as the array's element type.
4.2 Why consider support for MDA in SQL?
Large multidimensional arrays in particular represent a prevalent data type across most scientific domains, with
examples including 1-D sensor data, 2-D satellite images and microscope scans, 3-D x/y/t image timeseries
and x/y/z voxel models, as well as 4-D and 5-D climate models.
©ISO/IEC 2019 – All rights reserved Multidimensional Arrays (MDA) concepts 7
---------------------- Page: 15 ----------------------
ISO/IEC TR 19075-8:2019(E)
4.2 Why consider support for MDA in SQL?
Figure 1 — Aerial greyscale image of size 1024x1024 (San Diego)
In array terms, the image in Figure 1, “Aerial
...
Questions, Comments and Discussion
Ask us and Technical Secretary will try to provide an answer. You can facilitate discussion about the standard in here.