ISO/IEC 19075-8:2021

Information technology - Guidance for the use of database language SQL - Part 8: Multidimensional arrays

This document describes the definition and use of multidimensional 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 document discusses the syntax and semantics of operations on the MD-array data type defined in ISO/IEC 9075-15.

Technologies de l'information — Recommandations pour l'utilisation du langage de base de données SQL — Partie 8: Matrices multidimensionnelles

General Information

• Status: Published
• Publication Date: 31-Aug-2021
• ICS: 35.060 - Languages used in information technology
• Technical Committee: ISO/IEC JTC 1/SC 32 - Data management and interchange
• Drafting Committee: ISO/IEC JTC 1/SC 32/WG 3 - Database language
• Current Stage: 6060 - International Standard published
• Start Date: 01-Sep-2021
• Due Date: 11-Feb-2024
• Completion Date: 31-Aug-2021

Relations

• Consolidated By: ISO/IEEE 11073-10471:2024 - Health informatics - Device interoperability - Part 10471: Personal health device communication - Device specialization - Independent living activity hub
• Effective Date: 11-Nov-2023
• Revises: ISO/IEC TR 19075-8:2019 - Information technology database languages - SQL technical reports - Part 8: Multi-dimensional arrays (SQL/MDA)
• Effective Date: 23-Apr-2020

Overview

ISO/IEC 19075-8:2021 - "Information technology - Guidance for the use of database language SQL - Part 8: Multidimensional arrays" provides guidance on defining and using multidimensional arrays (MD-arrays) in SQL. The standard explains the syntax and semantics for the SQL/MD-array data type as defined in ISO/IEC 9075-15, positioning arrays as first-class data types for Big Data, scientific and engineering applications. It documents the data model, creation and update mechanisms, array operations, exporting/encoding, and realistic examples (notably a remote sensing use case).

Key topics and technical scope

• MD-array data model: concepts such as MD-axis, MD-extent, MD-dimension (rank), coordinates and cardinality; how array values are represented and typed.
• Type and creation rules: MD-array typedefinition, element types, axis names and limits; multiple construction methods (explicit enumeration, table-to-array conversion, iteration, decoding encoded formats).
• Update and storage semantics: guidelines for updating arrays, handling arrays with differing dimensions and single-element updates.
• SQL/MDA operations: a catalog of operations including subsetting, reshaping, shifting, axis renaming, scaling, concatenation, induced operations, join-on-coordinates, and aggregation; extent probing and element referencing are also covered.
• Import/export and encoding: recommendations for ingesting array data, converting arrays to/from table representations and serial formats (e.g., JSON examples).
• Practical examples: a detailed remote sensing example (band math, NDVI, histograms, change detection, feature extraction) illustrating querying and processing of MD-arrays in SQL.

Applications and who should use it

ISO/IEC 19075-8:2021 is directly relevant to:

• Database implementers and vendors extending SQL engines with array datatypes and operators.
• Data engineers and architects designing data platforms that integrate relational and multidimensional data.
• GIS, remote sensing and imaging specialists who process raster and multiband imagery (examples in the standard include NDVI and band operations).
• Scientific computing, IoT and ML practitioners handling tensor-like time-series, sensor grids, and multidimensional simulation outputs.
• Analysts needing integrated querying of relational and array data for Big Data workflows.

Practical benefits include streamlined ingestion of array data, unified SQL-based querying and transformations, improved interoperability with ISO/IEC 9075 SQL parts, and clearer semantics for array manipulation in analytics pipelines.

Related standards

• ISO/IEC 9075-15 (SQL/MDA) - defines the MD-array datatype referenced by this guidance
• ISO/IEC 9075 series (SQL framework & foundation) - provides the broader SQL framework and foundation standards
• ISO/IEC 19075 series - companion guidance on SQL usage

Keywords: ISO/IEC 19075-8:2021, SQL multidimensional arrays, MD-array, SQL/MDA, ISO/IEC 9075-15, Big Data, array operations, remote sensing, NDVI.

Standards Content (Sample)

INTERNATIONAL ISO/IEC
STANDARD 19075-8
First edition
2021-08
Information technology — Guidance
for the use of database language
SQL —
Part 8:
Multidimensional arrays
Technologies de l'information — Recommandations pour l'utilisation
du langage de base de données SQL —
Partie 8: Matrices multidimensionnelles
Reference number
©
ISO/IEC 2021
© ISO/IEC 2021
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: [phone]
Email: [e-mail]
Website: www.iso.org
Published in Switzerland
ii © ISO/IEC 2021 – All rights reserved

ISO/IEC19075-8:2021(E)
Contents Page
Foreword.vii
Introduction.ix
1 Scope.1
2 Normativereferences.2
3 Termsanddefinitions.3
4 Multidimensionalarrays(MDA)concepts.4
4.1 Contextofmultidimensionalarrays.4
4.2 Concept.4
4.3 WhyconsidersupportforMDAinSQL?.4
4.4 Arrayrepresentations.6
4.5 UsecasesforMDAsupportinSQL.6
4.5.1 Theusecases.6
4.5.2 Arraydataingestionandstorage.6
4.5.3 Integratedqueryingofarrayandrelationaldata.7
4.5.4 Updatingstoredarraydata.7
4.5.5 Exporting arrays.7
4.6 Non-Usecases:Directaccesstoexternalarraydata.7
5 SQL/MDAdatamodel.8
5.1 Datamodelconcepts.8
5.2 MD-array.8
5.3 MD-arraytypedefinition.9
5.3.1 Typedefinitionconcepts.9
5.3.2 Element type.9
5.3.3 MD-dimension.10
5.3.4 MD-axis names.10
5.3.5 MD-axislowerandupperlimits.10
5.3.6 Puttingitalltogether.11
5.4 MD-arraycreation.13
5.4.1 MD-arraycreationconcepts.13
5.4.2 Explicitelementenumeration.14
5.4.3 FromSQLtablequeryresult.15
5.4.4 Constructionbyimplicititeration.16
5.4.5 Decodingaformat-encodedarray.17
5.5 MD-arrayupdating.18
5.5.1 MD-arrayupdatingintroduction.18
5.5.2 UpdatingMD-arraysofequalMD-dimension.19
5.5.3 UpdatingMD-arraysofgreaterMD-dimension.20
5.5.4 UpdatingasingleelementofanMD-array.21
©ISO/IEC2021–Allrightsreserved iii

ISO/IEC19075-8:2021(E)
5.6 Exporting MD-arrays.21
5.6.1 Encodingtoadataformat.21
5.6.2 ConvertingtoanSQLtable.23
6 SQL/MDA operations.25
6.1 IntroductiontoSQL/MDAoperations.25
6.2 MD-extentprobingoperators.25
6.3 MD-arrayelementreference.27
6.4 MD-extentmodifyingoperations.28
6.4.1 IntroductiontoMDE-extentmodifyingoperations.28
6.4.2 Subsetting.28
6.4.3 Reshaping.30
6.4.4 Shifting.32
6.4.5 MD-axisrenaming.32
6.5 MD-arrayderivingoperators.33
6.5.1 IntroductiontoMD-arrayderivingoperators.33
6.5.2 Scaling.33
6.5.3 Concatenation.35
6.5.4 Inducedoperations.35
6.5.5 JoinMD-arraysontheircoordinates.42
6.6 MD-arrayaggregation.43
6.6.1 Generalaggregationexpression.43
6.6.2 Shorthandaggregationfunctions.44
7 Remotesensingexample.46
7.1 Introductiontoremotesensingexample.46
7.2 Data setup.46
7.3 Bandmath.48
7.3.1 Introductiontobandmath.48
7.3.2 NDVI.48
7.3.3 Band Swapping.51
7.4 Histograms.52
7.5 Changedetection.53
7.6 Extracting features.54
7.7 Datasearchandfiltering.55
Bibliography.57
Index.58
iv ©ISO/IEC2021–Allrightsreserved

ISO/IEC19075-8:2021(E)
Tables
Table Page
1 ExamplesofMD-arraytypedefinitions.12
2 ExamplesofMD-arraysconstructedbyelementenumeration.15
3 ExamplesofMD-arrayscreatedwiththeconstructorbyiteration.17
4 ExamplesofMD-arrayscreatedfromJSON-encodedarrays.18
5 ExamplesofMD-arraysencodedtoJSONarrays.22
6 ResultofexampleUNNESTquery.24
7 ResultofexampleUNNESTqueryspecifyingWITHORDINALITY.24
8 ExampleswithMD-extentprobingfunctions.26
9 ResultofMDEXTENT(kernel).26
10 ResultofMDMAX_EXTENT(kernel).26
11 ExamplesofreferencingasingleelementinanMD-array.27
12 ExamplesofMD-arraysubsetting.30
13 ExamplesofMD-extentreshaping.31
14 ExamplesofMD-extentshifting.32
15 ExamplesofMD-axisrenaming.33
16 InterpolationmethodsdefinedinISO19123:2005.34
17 ExamplesofMD-arrayconcatenation.35
18 ExamplesofinducedfunctionapplicationtoMD-arrays.38
19 Operationscorrespondingtothegrammarrules.40
20 ExamplesofinducedMD-arrayexpressions.40
21 ExampleofinducedMD-arraycasting.41
22 ExamplesofinducedCASEexpression.41
23 ExamplesofMDJOIN.43
24 Identityelementsforthes.43
25 ExamplesofgeneralMD-arrayaggregation.44
26 Predefinedaggregationoperators.45
27 LandsatTMbands.46
©ISO/IEC2021–Allrightsreserved v

ISO/IEC19075-8:2021(E)
Figures
Figure Page
1 Aerialgreyscaleimageofsize1024x1024(SanDiego).5
2 RelationshipsbetweenMDAandSQL/MDA.8
3 ThestructureofanMD-arrayvalueillustratedonasample3x3array.9
4 Placementofsatelliteimagesofeachcountryonaworldmap(fromGeographicBoundingBoxes).11
5 ExampleofanSQLtablethatcorrespondstoa3x3MD-array.16
6 ExampleofanSQLtableconvertedtoa3x3MD-arraywithMD-extent[i(-1:1),j(-1:1)].16
7 Exampleofarrayupdate.20
8 Updatinga3-DMD-arraywitha2-DsourceMD-array.21
9 MD-arraysubsettingexamples.28
10 MD-arrayreshapingexample.31
11 MD-arrayshiftingexample.32
12 MD-arrayscalingexample.33
13 Concatenation examples.35
14 ExampleofsummingtwoMD-arrays.36
15 Colorizedarray.42
16 Visiblecolor(RGB)bandsofaLandsatTMscene.47
17 NDVIresultstretchedtotherange(0,255).49
18 NDVIvaluesbetween0.2and0.4showninwhite,whileeverythingelseisblack.50
19 Color-mappedNDVIresult,fromdarkblue,throughgrey,todarkgreen.51
20 FalsecolorimageconstructedfromthenearIR,redandgreenbands.52
21 HistogramoftheNDVIindexofaLandsatTMscene.53
22 AcompositeimagewithanNDVIindexfromdifferentyearsineachchannel.54
23 NaturalRGBcolorofbarrierislandsarea.55
24 Binaryimageshowingisolatedislands.55
vi ©ISO/IEC2021–Allrightsreserved

ISO/IEC19075-8:2021(E)
Foreword
ISO(theInternationalOrganizationforStandardization)andIEC(theInternationalElectrotechnical
Commission)formthespecializedsystemforworldwidestandardization.Nationalbodiesthatare
membersofISOorIECparticipateinthedevelopmentofInternationalStandardsthroughtechnical
committeesestablishedbytherespectiveorganizationtodealwithparticularfieldsoftechnicalactivity.
ISOandIECtechnicalcommitteescollaborateinfieldsofmutualinterest.Otherinternationalorganizations,
governmentalandnon-governmental,inliaisonwithISOandIEC,alsotakepartinthework.
Theproceduresusedtodevelopthisdocumentandthoseintendedforitsfurthermaintenanceare
describedintheISO/IECDirectives,Part1.Inparticular,thedifferentapprovalcriterianeededforthe
differenttypesofdocumentshouldbenoted.Thisdocumentwasdraftedinaccordancewiththeeditorial
rulesoftheISO/IECDirectives,Part2(seewww.iso.org/directivesorwww.iec.ch/mem-
bers_experts/refdocs).
Attentionisdrawntothepossibilitythatsomeoftheelementsofthisdocumentmaybethesubjectof
patentrights.ISOandIECshallnotbeheldresponsibleforidentifyinganyorallsuchpatentrights.Details
ofanypatentrightsidentifiedduringthedevelopmentofthedocumentwillbeintheIntroductionand/or
ontheISOlistofpatentdeclarationsreceived(seewww.iso.org/patents),ortheIEClistofpatent
declarationsreceived(seepatents.iec.ch).
Anytradenameusedinthisdocumentisinformationgivenfortheconvenienceofusersanddoesnot
constituteanendorsement.
Foranexplanationofthevoluntarynatureofstandards,themeaningofISOspecifictermsandexpressions
relatedtoconformityassessment,aswellasinformationaboutISO’sadherencetotheWorldTrade
Organization(WTO)principlesintheTechnicalBarrierstoTrade(TBT)seewww.iso.org/iso/fore-
word.html.IntheIEC,seewww.iec.ch/understanding-standards.
ThisdocumentwaspreparedbyTechnicalCommitteeISO/IECJTC1,Informationtechnology,Subcom-
mitteeSC32,Datamanagementandinterchange.
ThisfirsteditionofISO/IEC19075-8cancelsandreplacesISO/IECTR19075-8:2019.
ThisdocumentisintendedtobeusedinconjunctionwiththefollowingeditionsofthepartsoftheISO/IEC
9075series:
— ISO/IEC9075-1,sixtheditionorlater,
— ISO/IEC9075-2,sixtheditionorlater,
— ISO/IEC9075-3,sixtheditionorlater,
— ISO/IEC9075-4,seventheditionorlater,
— ISO/IEC9075-9,fiftheditionorlater,
— ISO/IEC9075-10,fiftheditionorlater,
— ISO/IEC9075-11,fiftheditionorlater,
— ISO/IEC9075-13,fiftheditionorlater,
— ISO/IEC9075-14,sixtheditionorlater,
— ISO/IEC9075-15,secondeditionorlater,
— ISO/IEC9075-16,firsteditionorlater.
©ISO/IEC2021–Allrightsreserved vii

ISO/IEC19075-8:2021(E)
AlistofallpartsintheISO/IEC19075seriescanbefoundontheISOandIECwebsites.
Anyfeedbackorquestionsonthisdocumentshouldbedirectedtotheuser’snationalstandardsbody.A
completelistingofthesebodiescanbefoundatwww.iso.org/members.htmlandwww.iec.ch/-
national-committees.
viii ©ISO/IEC2021–Allrightsreserved

ISO/IEC19075-8:2021(E)
Introduction
ThisdocumentdescribesthedefinitionanduseofmultidimensionalarraysinSQL.Multidimensional
arraysrepresentacoreunderlyingstructureofmanifoldscienceandengineeringdata.Itisgenerally
recognizedtoday,therefore,thatarrayshaveanessentialroleinBigDataandshouldbecomeanintegral
partoftheoveralldatatypeorchestrationininformationsystems.Thisdocumentdiscussesthesyntax
andsemanticsofoperationsontheMD-arraydatatypedefinedinISO/IEC9075-15.
Theorganizationofthisdocumentisasfollows:
1) Clause1,“Scope”,specifiesthescopeofthisdocument.
2) Clause2,“Normativereferences”,identifiesstandardsthatarereferencedbythisdocument.
3) Clause3,“Termsanddefinitions”,definesthetermsanddefinitionsusedinthisdocument.
4) Clause4,“Multidimensionalarrays(MDA)concepts”,introducestheconceptofMultidimensional
Arrays.
5) Clause5,“SQL/MDAdatamodel”,introducesthedatamodel.
6) Clause6,“SQL/MDAoperations”,coversthesupportedoperationsonMD-arrays.
7) Clause7,“Remotesensingexample”,illustratesthesupportedfunctionalitythroughrealisticexamples.
©ISO/IEC2021–Allrightsreserved ix

ISO/IEC19075-8:2021(E)
x ©ISO/IEC2021–Allrightsreserved

INTERNATIONAL STANDARD ISO/IEC 19075-8:2021(E)
Informationtechnology—GuidancefortheuseofdatabaselanguageSQL—
Part8:
Multidimensionalarrays
1 Scope
ThisdocumentdescribesthedefinitionanduseofmultidimensionalarraysinSQL.Multidimensional
arraysrepresentacoreunderlyingstructureofmanifoldscienceandengineeringdata.Itisgenerally
recognizedtoday,therefore,thatarrayshaveanessentialroleinBigDataandshouldbecomeanintegral
partoftheoveralldatatypeorchestrationininformationsystems.Thisdocumentdiscussesthesyntax
andsemanticsofoperationsontheMD-arraydatatypedefinedinISO/IEC9075-15.
©ISO/IEC2021–Allrightsreserved 1

ISO/IEC19075-8:2021(E)
2 Normativereferences
Thefollowingdocumentsarereferredtointhetextinsuchawaythatsomeoralloftheircontentconsti-
tutesrequirementsofthisdocument.Fordatedreferences,onlytheeditioncitedapplies.Forundated
references,thelatesteditionofthereferenceddocument(includinganyamendments)applies.
ISO/IEC9075-1,Informationtechnology—Databaselanguages—SQL—Part1:Framework
(SQL/Framework)
ISO/IEC9075-2,Informationtechnology—Databaselanguages—SQL—Part2:Foundation
(SQL/Foundation)
ISO/IEC9075-15,Informationtechnology—Databaselanguages—SQL—Part15:Multidimensional
Arrays(SQL/MDA)
2 ©ISO/IEC2021–Allrightsreserved

ISO/IEC19075-8:2021(E)
3 Termsanddefinitions
Forthepurposesofthisdocument,thefollowingtermsanddefinitionsapply.
ISOandIECmaintainterminologicaldatabasesforuseinstandardizationatthefollowingaddresses:
— IECElectropedia:availableathttp://www.electropedia.org/
— ISOOnlinebrowsingplatform:availableathttp://www.iso.org/obp
3.1
coordinate
non-emptyorderedlistofintegers
3.2
cardinality
numberofelementsinanMD-array
3.3
MD-array
orderedcollectionofelementsofthesametypeassociatedwithanMD-extentwhereeachelementis1:1
associatedwithsomecoordinatewithinitsMD-extent
Note1toentry:AcoordinateiswithinanMD-extentifeverycoordinatevaluefromtheintegerlistis
greaterthanorequaltothelowerlimit,andlessthanorequaltotheupperlimitoftheMD-intervalof
theMD-axisatthepositionintheMD-extentasthecoordinatevaluehaswithinthecoordinate
3.4
MD-axis
namedMD-interval
3.5
MD-dimension
numberofMD-axesintheMD-extentofanMD-array
Note1toentry:Alsoknownas“rank”outsideofSQL/MDA
3.6
MD-extent
non-emptyorderedcollectionofMD-axeswithnoduplicatenames
3.7
MD-interval
integerintervalgivenbyapairoflowerandupperintegerlimitssuchthatthelowerlimitislessthanor
equaltotheupperlimit;theintervalisclosed,i.e.,,bothlimitsarecontainedinit
©ISO/IEC2021–Allrightsreserved 3

ISO/IEC19075-8:2021(E)
4 Multidimensionalarrays(MDA)concepts
4.1 Contextofmultidimensionalarrays
TherequirementsforthematerialdiscussedinthisdocumentshallbeasspecifiedinISO/IEC9075-1
andISO/IEC9075-15.
4.2 Concept
Thephrase“(Multidimensional)array,rasterdata”isusedtorefertoarraysgenerally,incontrasttothe
MD-arraytermconfinedtotherealmofSQL/MDA.Itisnottobeconfusedwiththeterm“array”inISO/IEC
9075-2.ThisdocumentusesthetermARRAYfortheoriginalSQLarraycollectiontype.
Thearrayconceptisasimpleandefficientdatarepresentationthatfindsitsuseinawidearrayoffields,
business-relatedaswellasscientificandengineering.Manysensors,images,imagetime-series,simulation
processes,statisticalmodels,andsoon,producerawdatathatcanimmediatelybeclassifiedasarray
data.Thesedatamaybenaturallyarrangedalongmorethanoneaxis:positionandtime,forexample.
Amultidimensionalarray(MDA)isasetofelementsorderedinamultidimensionalspace.Thespace
consideredhereisdiscretized(alsocalledrasterizedorgridded),thatis,onlyintegercoordinatesare
admittedaspositionsoftheindividualarrayelements.Thenumberofintegersneededtorefertoapar-
ticularpositioninthisspaceisthearray’sdimension(sometimesalsoreferredtoasitsdimensionality).
Anelementcanbeasinglevalue(suchasanintensityvalueincaseofgreyscaleimages)oracomposite
value(suchasintegertriplesforthered,green,andbluecomponentsofatrue-colorimage).Allelements
ofanarraysharethesamestructure,referredtoasthearray’selementtype.
4.3 WhyconsidersupportforMDAinSQL?
Largemultidimensionalarraysinparticularrepresentaprevalentdatatypeacrossmostscientificdomains,
withexamplesincluding1-Dsensordata,2-Dsatelliteimagesandmicroscopescans,3-Dx/y/timage
time-seriesandx/y/zvoxelmodels,aswellas4-Dand5-Dclimatemodels.
4 ©ISO/IEC2021–Allrightsreserved

ISO/IEC19075-8:2021(E)
4.3 WhyconsidersupportforMDAinSQL?
Figure1—Aerialgreyscaleimageofsize1024x1024(SanDiego)
Inarrayterms,theimageinFigure1,“Aerialgreyscaleimageofsize1024x1024(SanDiego)”,isa2-
dimensionalarrayofunsigned8-bitintegerelementspositionedatcoordinatesin{0,1,.,1023} space.
Arraysrarelyoccurisolatedinpracticeandaretypicallyornamentedwithmetadataandembeddedin
largeroverallinformationstructures.Supportingtheminnarrowlyspecializedadhoctoolsordedicated
arrayDBMSisthusinsufficientwhenitcomestobuildingmodern,complexservicesandapplications.
ThissuggeststhatintegrationofarrayqueryingintoastandardizedframeworklikeSQLisalogicalnext
stepthatwillbenefitthecommunitiesdealingwithmultidimensionalarraydatainonewayortheother.
SQLhashadbasicsupportfor1-dimensionalarrayssince1999.Insteadofattemptingtoextendthe
existing1-dimensionalarraymodeltoaddresstheneedsofmultidimensionalarraymanipulation,
SQL/MDAaddressesthoseneedswithanewfeaturesetintegratedintoSQL.
©ISO/IEC2021–Allrightsreserved 5

ISO/IEC19075-8:2021(E)
4.4 Arrayrepresentations
4.4 Arrayrepresentations
Theencodinganddecodingfunctionsemanticsforotherexternalrepresentationsareimplementation-
defined.ExamplesmayincludedatainsuchrepresentationsasPDF,JPEG,PNG,andXML.
4.5 UsecasesforMDAsupportinSQL
4.5.1 Theusecases
Thequestionposedbythisusecaseis“HowisarraydataacquiredusingSQL?”
FollowingaretheprimaryusecasesthatsupportformultidimensionalarraysintheSQL-environment
isrequiredtosatisfy.
— Arraydataingestionandstorage.
— Integratedqueryingofarrayandrelationaldata,
— Updatingstoredarraydata.
— Exportingarrays.
ThefollowingSubclausesdiscusstheseusecasesingreaterdetail,andhowSQL/MDAaddressesthem.
4.5.2 Arraydataingestionandstorage
Thequestionposedbythisusecaseis“HowisarraydataacquiredusingSQL?”
AsdiscussedearlierinSubclause4.4,“Arrayrepresentations”,arraysexistinawidevarietyofformats.
InordertoworkwiththeminagenericwayinSQL,itisnecessarytobuildanabstractdatamodelthat
fitswiththeSQLphilosophy.TheMD-arrayasdefinedbySQL/MDAprovidesexactlysuchadatamodel,
implementedasanewattributetypeMDARRAY.Ingestionofarraydataencodedinanexternalformat
intoSQLinvolvestransformingitordecodingitintoaninstanceoftheinternalMD-arraydatamodel,
whichistheninsertedintoanMDARRAYcolumnofanappropriatetype.
What“decode”meansinpracticedependsonmanyfactors,includingthedataformat,thedetailsof
physicalstorageofMD-arraysinaspecificDBMS,systemarchitecture,etc.Thisdocumentandthestandard
donotdiveintothesetechnicaldetailsofarraydataingestionbeyondprovidingadefaultspecification
forJSONencodedarraysandasuitableinterfaceforimplementationstoattachtheiringestionextensions.
Itisworthdiscussingthestoragedatamodelhere.Theseveralpossibilitiesare:
— MD-arrayasafirst-classobjectinthesamewaythatSQLtablesare.
— DirectmappingofSQLtablesintoMD-arrays.
— Storewithinanopaquedatatype(SQLstringorLargeObjectforexample).
— Adedicatedcolumndatatypewithwell-definedsemantics.
MD-arrayisasimpledatastructuredefinedbyalistofMD-axes,eachspecifyinganame,lowerandupper
limits,pairedwithanelementtype.Thisledtoadoptionofthelastoption,followingtheexampleof
ARRAYandMULTISETcollectiondatatypes.Datatransformationishandledduringingestionwithspecial
6 ©ISO/IEC2021–Allrightsreserved

ISO/IEC19075-8:2021(E)
4.5 UsecasesforMDAsupportinSQL
functions,allowingworkingwithvalueswithclearlydefinedsemanticswithintheSQL-environment.It
isminimallyintrusivetotheSQLstandard,whileitneverthelesssupportsalloftherequirementsidentified
inthisdocument.
4.5.3 Integratedqueryingofarrayandrelationaldata
AswasintroducedinthepreviousSubclause,MD-arraysarestoredwithinanewcollectiondatatype
MDARRAYthatismanipulatedthroughafunctionalandoperationalinterfacedescribedinthisdocument.
ThisissimilartotheexistingARRAYandMULTISETcollectiondatatypes,exceptthattheoperationset
isricher.Integrationwithotherdatatypesisseamless(e.g.,,multiplyingthevaluesofallelementsofa
numericMD-arraycolumnAwiththesinglevalueofanumericcolumnCissimplyA * C),andthegeneral
SQLquerymechanicsareunchanged.Inaddition,itispossibletogenerateanSQLtablefromanMD-array
andvice-versa,anMD-arrayfromanSQLtablewiththeappropriatestructure.
4.5.4 Updatingstoredarraydata
Read-onlyaccesstoMD-arraydataisclearlyinsufficient.Arraydataisveryoftencontinuouslyandregularly
produced,e.g.,,atemperaturesensortakingareadingeveryhour,orasatelliteperiodicallytakingearth-
observationimagesasitorbitsaroundtheEarth.Inaddition,asinglearraycanexceedterabytesinsize,
andforpracticalreasonsitmightbesplitintomultiplesmallerarrays;ingestingthemallintoasingle
MD-arraycolumnrequirespiece-wiseextensionandupdatingofthecolumn.Therefore,SQL/MDAallows
updatingofentireMD-arrayvalues,aswellasspecificsubsetsofanMD-array.
4.5.5 Exportingarrays
FrequentlytheresultofoperationsonMD-arrayswillbeanMDarray,whichneedstobeexportedusing
someexternalrepresentation.Thisisthecounterpartofarraydataingestiondiscussedpreviouslyin
Subclause4.5.2,“Arraydataingestionandstorage”.
4.6 Non-Usecases:Directaccesstoexternalarraydata
AllaccesstoarraydatarequiresthatthearraydataisfirstimportedintotheSQLenvironment.Inorder
toqueryexternalarraydatausingSQL,applicationsarerequiredtoaccessexternalarraysthemselves,
theninsertthosedataintoMD-arrayvalues,perhapsbyusingtheMDDECODEfunction.
©ISO/IEC2021–Allrightsreserved 7

ISO/IEC19075-8:2021(E)
5 SQL/MDAdatamodel
5.1 Datamodelconcepts
TheSQL/MDAmodelisessentiallyrepresentedbytheconceptofMD-array.Itisnecessarytoclearly
distinguishbetweenarrayvalues“outside”theDBMS,andtheiranalogs“inside”theDBMS.Thethefol-
lowingconventionisused:
— Theterms“array”,“multidimensionalarray”,and“MDA”refertoarrayvaluesexternaltotheSQL-
environment,encodedinaparticularformatlikeTIFF,netCDF,HDF5,JSON,etc.
— Theterms“MD-array”and“SQL/MDA”refertoconstructswithintheSQL-environment.
Therelationshipbetween“MDA”and“SQL/MDA”isillustratedinFigure2,“RelationshipsbetweenMDA
andSQL/MDA”.
Figure2—RelationshipsbetweenMDAandSQL/MDA
5.2 MD-array
MD-arrayvaluesareinputsofallSQL/MDAoperations,andmostoftentheoutputs.Figure3,“The
structureofanMD-arrayvalueillustratedonasample3x3array”,showsthestructureofasampleMD-
arrayvalue.
8 ©ISO/IEC2021–Allrightsreserved

ISO/IEC19075-8:2021(E)
5.2 MD-array
Figure3—ThestructureofanMD-arrayvalueillustratedonasample3x3array
5.3 MD-arraytypedefinition
5.3.1 Typedefinitionconcepts
ThedefinitionofanMD-array(seeClause3,“Termsanddefinitions”)isagoodstartingpointinorderto
understandwhatcomponentsareneededforthetypeofanMD-array:
1) “AnMD-arrayisanorderedcollectionofelementsofthesametype.”So,onethingneededtospecify
thetypeofanMD-arrayisthetypeofitselements,morespecificallyknownastheelementtype.This
isnodifferentfromtheexistingARRAYandMULTISET.
2) “.whereeachelementis1:1associatedwithsomecoordinatewithinitsMD-extent.”Hence,the
otherpartneededisanMD-extentthatdelimitsthecoordinatesoftheelementsinanMD-array.
5.3.2 Elementtype
MD-arraysstandoutfromthespectrumofcollectiontypesinthatthestoragelocationofanelementcan
bederiveddirectlyfromitscoordinates,whichmakesstorageandaccessparticularlyefficient.This
requiresthatallelementsareofthesamelength.Therefore,variable-sizecollectionelementslikesets
andmultisetsdonotqualifyaselementtypes.MD-arraysaselementtypeisdisallowedaswellforthe
followingreasons:
1) NestinganMD-arrayofMD-dimensiond intoanMD-arrayofMD-dimensiond canequivalentlybe
1 2
modeledasasingleMD-arrayofMD-dimensiond+d.
1 2
2) Itkeepsthedatamodelsimplerandmoreconsistentinthatallcollectiontypesaredisallowed,and
nohandlingspecificallyofMD-arraysisneeded.
©ISO/IEC2021–Allrightsreserved 9

ISO/IEC19075-8:2021(E)
5.3 MD-arraytypedefinition
Allinall,anySQLdatatypeisallowedtobeanelementtypeofanMD-array,exceptforcollection-con-
tainingtypes.AdatatypeTYiscollection-containingifexactlyoneofthefollowingconditionsistrue:
— TYisacollectiontype.
— TYisarowtype,andthedeclaredtypeofsomefieldofTYisacollection-containingtype.
— TYisdistincttype,andthesourcetypeofTYisacollection-containingtype.
— TYisastructuredtypeandthedeclaredtypeofsomeattributeofTYisacollection-containingtype.
5.3.3 MD-dimension
TheMD-dimensionisanessentialpropertyofanMD-arraythatindicateshowmanyMD-axesithas.Two
MD-arraysofdifferentMD-dimensionsarefundamentallydifferent.Therefore,anMD-arraytypethat
specifiesacertainMD-dimensionadmitsonlyMD-arrayvaluesofthatMD-dimension.
AnMD-arrayhasanMD-extentthatisalistofMD-axes.EachMD-axishasaname,alowerlimit,andan
upperlimit.
5.3.4 MD-axisnames
ThenameofanMD-axisuniquelyidentifiesthatMD-axis,whichbecomesrelevantinoperationsthat
refertotheMD-axesofanMD-array.InoperationsontwoormoreMD-arrays,thenamesofcorresponding
MD-axesarerequiredtobethesame;aregular2Dx/yimageiscompletelydifferentfromatransposed
y/ximage,afterall.ItmighthappenthatsomeMD-arrayscorrespondsemantically,whilethecorresponding
MD-axisnamesaredifferent(forexample,“t”inoneMD-arrayand“time”inanother);SQL/MDAprovides
aCASTvariantforsuchcasesthatallowsexplicitlyrenamingtheMD-axisnames.
5.3.5 MD-axislowerandupperlimits
ThelowerandupperlimitsoftheMD-axesarenotfundamentaltothenatureofanMD-array.MD-arrays
withdifferentlowerandupperlimitsmightstillberelatedtoeachother,asthefollowingexampleillus-
trates.
Supposethereexistgreyscalesatelliteimagesofeachcountryintheworldinthesameresolution.In
SQL/MDAtheywouldbe2-dimensionalMD-arraysofdifferentsizes(the“width”ofthefirstMD-axisand
“height”ofthesecondMD-axis),astherearesmallerandlargercountries.Ina“map”ofthewholeworld
inthesameresolution,theMD-arrayforeachcountrywouldbeplacedatadifferentpositiononthe
overallmap(Figure4,“Placementofsatelliteimagesofeachcountryonaworldmap(fromGeographic
BoundingBoxes)”,i.e.,,thelowerandupperlimitsofitsMD-axeswouldbedifferentfromthoseofother
MD-arrays.Nevertheless,theyarerelatedtoeachother,anditwouldbebeneficialtopossiblytoput
theminasingleMDARRAYcolumn,connectingthemtofurthercolumnsholdingmetadatalikethe
countryname,geographicboundaries,population,etc.
1 “resolution”referstotherealsizeofasinglepixel,e.g.,,30meters
10 ©ISO/IEC2021–Allrightsreserved

ISO/IEC19075-8:2021(E)
5.3 MD-arraytypedefinition
Figure4—Placementofsatelliteimagesofeachcountryonaworldmap(fromGeographic
BoundingBoxes)
Therefore,MD-arrayvaluescanhavevaryinglowerandupperlimits.TheMD-arraytypecanoptionally
bedeclaredwithminimumlowerandmaximumupperaxislimits;ifnolimitisdefinedforanaxis,itcan
extendtotheimplementation-definedaxislimit.
5.3.6 Puttingitalltogether
SteppingthroughtheMD-arraytypedefinitionrulesofSubclause8.1,“”,inISO/IEC9075-
15:
::=
MDARRAY
SospecifyingacolumnofMD-arraytyperequiresspecifyingfirsttheelementtype,followedbythekeyword
MDARRAY,andamaximumMD-extentattheend.Continuingwiththespecificationdetailsof md-extent>:
::=

|
::=
[ { }. ]
::=
[ { }. ]
©ISO/IEC2021–Allrightsreserved 11

ISO/IEC19075-8:2021(E)
5.3 MD-arraytypedefinition
AmaximumMD-extentiseitheralistof“regular”maximumMD-axes,oralistof“anonymous”maximum
MD-axes.Thedifferencebecomesclearinthegrammarrulesbelow.Itisworthmentioningherethatthe
listofMD-axesis1-relative;thisismostrelevantinfunctionswhichreturntheaxisnamegivenitsindex,
orviceversa,asdescribedlaterinthisdocument.
::=
[

]
::=

::=

RegularhasamandatoryMD-axisname,while
dropstheneedforanMD-axisname.Thisisjustaconvenienceconstruct:sometimesthenamesare
irrelevant.However,forconsistencyandsimplicity,ISO/IEC9075-15assumesthatMD-axesalwayshave
aname.So,inthiscase,defaultMD-axisnamesareautomaticallygenerated(seeSubclause8.1,“ type>”inISO/IEC9075-15)intheformof“D1”forthefirstMD-axis,“D2”forthesecond,andsoon.
TheotherdifferenceisthattheregularcanbespecifiedwithjusttheMD-axisname,
whileleavingoutthelowerandupperlimits;inthecaseof,thisisnot
reallypossible,asthentherewouldbenothingtoindicatethepresenceofanMD-axis.Leavingoutthe
maximumlimitsmeansthatnomaximumlowernorupperlimitsareenforcedonaparticularMD-axis.
::=

::=

::=
|
::=

AmaximumMD-axislimitcanbespecifiedasanintegerliteral,butcanalsobespecifiedwitha“*”,which
marksthataparticularlowerorupperlimitofanMD-axisshouldnotbecheckedagainstanymaximum
value.So,specifyinga“*”forboththelowerandupperlimitsofanMD-axisisequivalenttoleavingthem
outaltogether.
Table1,“ExamplesofMD-arraytypedefinitions”,illustratestheseconceptswithacoupleofexamples.
Table1—ExamplesofMD-arraytypedefinitions
Example SQLtypedefinition
1-DMD-arraysoffloating-point FLOAT MDARRAY [temp(0:99)]
elements,withpossiblecoordi-
natesfrom[0]to[99].Thesingle
MDaxisiscalledtemp,shortfor
temperature.
Sameasthepreviousexample, FLOAT MDARRAY [temp(*:99)]
exceptthattheallowedcoordi-
natesarenowfrom[–∞](theoret-
ically)to[99].
12 ©ISO/IEC2021–Allrightsreserved

ISO/IEC19075-8:2021(E)
5.3 MD-arraytypedefinition
Example SQLtypedefinition
Allowanycoordinates. FLOAT MDARRAY [temp(*:*)]
Equivalenttothepreviouscase. FLOAT MDARRAY [temp]
2-DMD-arraysofintegerele- INT MDARRAY [*:*, *:*]
ments,withnoupper/lowerlimits
onthecoordinates.TheMD-axis
namesarenotspecified(anony-
mous).
2-DMD-arraysofintegerelements SMALLINT MDARRAY [i(-1:1), j(-1:1)]
andmaximumsize3x3elements.
TheMD-axisnamesareiandj.
3-DMD-arrayscorrespondingto SMALLINT MDARRAY [t(0:*), x(0:7999), y(0:7999)]
time-seriescubesofsatellite
imagesoveracertainarea.The
timeMD-axisthasnoupperlimit,
allowingnewimagestobe
appendedtoeachcubeindefi-
nitely.
2-DMD-arraysofmaximumsize CREATE TYPE RGBPixel AS (
1024x1024,correspondingtoRGB red SMALLINT, green SMALLINT, blue SMALLINT )
images(havingred,blue,and RGBPixel MDARRAY [x(0:1023), y(0:1023)]
greenchannelsas8-bitunsigned
integercomponents).
5.4 MD-arraycreation
5.4.1 MD-arraycreationconcepts
ThereareseveralwaystointroduceMD-arrayvaluesintotheSQL-environment“fromscratch”,i.e.,,the
oppositeofderivingfromexistingMD-arrayvalues:
1) Indirectenumeration,alltheMD-array’selementscanbelistedinrow-majororder(unrelatedto
anyinternalarrayrepresentation).
2) AtabularqueryresultcanbeconvertedtoanMD-arrayifitisintheappropriatestructure.
3) MD-arrayconstructorbyiterationallowsthegenerationofallelementsofanMD-arraybyevaluating
acoordinate-boundvalueexpressionforeachelement.
4) Bydecodinganarrayencodedinaparticularformat,e.g.,,TIFF,netCDF,PNG,etc.
Inmostcases,itiscommonlyrequiredtoexplicitlyspecifytheMD-extentofthecreatedMD-array,asit
cannotbegenerallyinferred.TheMD-extentisrequiredtospecifyallMD-axisnamesandexactupper
andlowerlimits,incontrasttothemorerelaxedrulesformaximumMD-extent,whichallowomission
oftheMD-axislimitsfromthetypedefinition.ThisensuresthateveryMD-arrayvalueintheSQLenviron-
menthasapreciselydefinedMD-extent.
©ISO/IEC2021–Allrightsreserved 13

ISO/IEC19075-8:2021(E)
5.4 MD-arraycreation
Thedefinitionofisgivenbelow,indicatingthatitcanbeeitherspecified
explicitlywith,orsourcedfromanotherMD-arraythroughan(MDEX-
TENT)function:
::=

|
::=

::=
[ { }. ]
::=


::=

::=

::=
MDEXTENT
ThefollowingSubclausespresenteachcaseindetail.
5.4.2 Explicitelementenumeration
Indirectenumeration,allofanMD-array’selementscanbelistedinrow-majororder;theMD-extentis
requiredtobespecifiedwithan.
Fora2-dimensionalmatrix,“row-majororder”meansthatallelementsofthefirstrowarelistedinorder,
thenallelementsofthesecondrow,etc.Thisiseasilygeneralizedtomultipledimensions:theinner-most
(last)MD-axisvariesfastest,followedbythesecondlastMD-axis,andsoon.
Mathematically,themultidimensionalcoordinatetolinearindextranslationcanbespecifiedasfollows.
SupposeanMD-arrayofMD-dimensiond,withanMD-extentDdenotedas[N(LO :HI),.,N(LO :
1 1 1 d d
HI)].LetEbeHI–LO+1.Therow-majorlinearindex(startingfrom1)ofacoordinate[P,.,P]within
d i i i 1 d
Disgivenby:
d d
1+LP +E ·(LP +E ·(.+E ·LP).)=1+Σ LP·Π E
d d d-1 d-1 2 1 i=1 i j=i+1 j
whereLP=P–LO .
i i i
Syntactically,theisdefinedas:
::=
MDARRAY
::=

::=
[ { }. ]
2 Thisisnecessaryinordertonormalizethecoordinatetoanorigincoordinateof[0,.,0],ratherthan[LO,.,LO]
1 d
14 ©ISO/IEC2021–Allrightsreserved

ISO/IEC19075-8:2021(E)
5.4 MD-arraycreation
::=

Thesarelistedascomma-separatedvaluesbetweenand
.Table2,“ExamplesofMD-arraysconstructedbyelementenumeration”,
showsseveralexamples.
Table2—ExamplesofMD-arraysconstructedbyelementenumeration
Example SQLfragment
1-DMD-arrayof10floating-point MDARRAY [temp(10:19)] [-0.5, -1.5, -0.34, 0.1, 1.12,
elementsatcoordinatesranging 0.34, 1.5, 0.2, 1.15, 0.033]
from[10]to[19].Theelementat
coordinate[10]is–0:5,at[11]is
–1:5,andsoon.
2-D3x3convolutionkernel,as MDARRAY [i(-1:1), j(-1:1)] [-1, -1, -1, -1, 8, -1, -1,
shownonFigure3,“Thestructure -1, -1]
ofanMD-arrayvalueillustrated
onasample3x3array”.Theele-
mentatcoordinate[0;0]is8,
whichisthefifthelementinthe
,whilethe
elementsatallothercoordinates
are–1.
3-D2x2x2MD-arrayof8SMALL- MDARRAY [x(0:1), y(1:2), z(2:3)] [1, 2, 3, 4, 5, 6, 7,
INTelements,suchthattheele- 8]
mentwithvalue1isatcoordinate
[0;1;2],2isatcoordinate[0;1;3],
3at[0;2;2],4at[0;2;3],5at
[1;1;2],andsoon.
5.4.3 FromSQLtablequeryresult
AtabularqueryresultcanbeconvertedtoanMD-arraywithan,
definedas:
::=
MDARRAY
ThespecifiesanMD-extentDwithdMD-axes,denotedas[N(LO :HI),.,
1 1 1
N(LO :HI).Basedonit,theSQLtableTproducedbytheisrequiredtosatisfycertain
d d d
criteriasothatconstructinganMD-arrayfromitwillbepossible:
— ThastobeofdegreeN=d+1.
— ThenamesofdcolumnsinTarerequiredtocorrespondtotheMD-axisnamesinD;thesecolumns
arecalledcoordinatecolumns.Theremainingcolumnistheelementcolumn.
— UNIQUEconstraintisassumedonthecoordinatecolumns(N,.,N).
1 d
©ISO/IEC2021–Allrightsreserved 15

ISO/IEC19075-8:2021(E)
5.4 MD-arraycreation
— TherowsatcoordinatecolumnwithnameN,for1(one)≤i≤d,arerequiredtocontainnon-null,
i
integervaluesrangingfromLOtoHI.
i i
Thecoordinatecolumnsspecifythecoordinates,andtheelementcolumntheelements,oftheMD-array.
So,takingsomerowinT,theelementintheconstructedMD-arrayatthecoordinatedefinedbythevalues
inthecoordinatecolumns(orderedtomatchtheorderofMD-axisnamesinD)willbethevalueinthe
elementcolumn.TheelementsatanycoordinateswithinthespecifiedMD-extentthathavenotbeen
definedbythecoordinatecolumnswillbesettothenullvalue.
Figure5,“ExampleofanSQLtablethatcorrespondstoa3x3MD-array”,providesanexampleofanSQL
tablethatsatisfiestheseconstraints.SeeFigure3,“ThestructureofanMD-arrayvalueillustratedona
sample3x3array”.
Figure5—ExampleofanSQLtablethatcorrespondstoa3x3MD-array
ThefollowingSQLqueryfragmentwouldconstructtheMD-arrayoutofthistableT:
MDARRAY [i(-1:1), j(-1:1)] (SELECT T.* FROM T)
Figure6,“ExampleofanSQLtableconvertedtoa3x3MD-arraywithMD-extent[i(-1:1),j(-1:1)].”,shows
theMD-arraythatresultswhensomeofthecoordinatesinthespecifiedMD-extentaremissingfromthe
inputtable.ThemissingelementsaresettoSQLnullvalues(denotedas“ω”onthefigure).
Figure6—ExampleofanSQLtableconvertedtoa3x3MD-arraywithMD-extent[i(-1:1),j(-1:1)].
5.4.4 Constructionbyimplicititeration
Anintroducesageneral,powerful,andflexiblemechanism
forconstructingnewarrays.Itisdefinedasfollows:
::=
MDARRAY
ELEMENTS
16 ©ISO/IEC2021–Allrightsreserved

ISO/IEC19075-8:2021(E)
5.4 MD-arraycreation
::=

ThefirstpartisthefamiliarMDARRAYthatiscommontothepreviouslyintro-
ducedconstructors:itallowsspecifyingtheMD-extentoftheconstructedMD-array.Thesecondpart
indicateshoweachelementinthatMD-extentistobederived.
Inthesimplestcase,couldbealiteral,orperhapsacolumnreference.Theresulting
“constant”MD-arraywouldconsistentirelyofelementswiththatliteralorcolumnvalue.Thisisofuse
ininitializinganMD-arraywithzerosorthenullvalue,forexample.
Tomakeitmoregenerallyuseful,thisconstructorisdefinedsothatinsomesenseitcreatesanimplicit
loopovertheexpression.TheMD-axisnamesareatthesametimeMD-axis“iterator”
variablesthatrangefromthelowertotheupperlimitoftheparticularMD-axis.ThescopeoftheMD-
axisvariablesisthe,wheretheycanbereferencedtodynamicallygeneratethe
valueofeachelement.ForeveryelementoftheconstructedMD-array,allMD-axisvariablestakentogether
(intheorderofMD-axesintheMD-extent)essentiallyrefertothecoordinateofthatelement;thevalue
ofeachvariableisthecorrespondingelementofthecoordinate.
Table3,“ExamplesofMD-arrayscreatedwiththeconstructorbyiteration”,showsexamplesofusing
thisconstructor,startingfromcreatingsimpleconstantMD-array,tomorecomplexMD-arrayderivation
cases.
Table3—ExamplesofMD-arrayscreatedwiththeconstructorbyiteration
Example SQLfragment
MDARRAY [x(0:9), y(0:9)]
2-DconstantMD-arraysuchthatthevalueofeachelement
ELEMENTS 0
is0(zero).
MDARRAY [x(0:9)]
1-D“gradient”MD-arrayof10elements
...