ISO/IEC 19075-8:2021

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: +41 22 749 01 11
Email: copyright@iso.org
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
...