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
Current Stage
6060 - International Standard published
Start Date
01-Sep-2021
Completion Date
01-Sep-2021
Ref Project

RELATIONS

Buy Standard

Standard
ISO/IEC 19075-8:2021 - Information technology -- Guidance for the use of database language SQL
English language
59 pages
sale 15% off
Preview
sale 15% off
Preview
Draft
ISO/IEC PRF 19075-8:Version 10-jul-2021 - Information technology -- Guidance for the use of database language SQL
English language
59 pages
sale 15% off
Preview
sale 15% off
Preview

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 19075-8:2021(E)
ISO/IEC 2021
---------------------- Page: 1 ----------------------
ISO/IEC 19075-8:2021(E)
COPYRIGHT PROTECTED DOCUMENT
© 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
---------------------- Page: 2 ----------------------
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
---------------------- Page: 3 ----------------------
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
---------------------- Page: 4 ----------------------
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
---------------------- Page: 5 ----------------------
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
---------------------- Page: 6 ----------------------
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
---------------------- Page: 7 ----------------------
ISO/IEC19075-8:2021(E)
AlistofallpartsintheISO/IEC19075seriescanbefoundontheISOandIECwebsites.

Anyfeedbackorquestionsonthisdocumentshouldbedirectedtotheuser’snationalstandardsbody.A

completelistingofthesebodiescanbefoundatwww.iso.org/members.htmlandwww.iec.ch/-
national-committees.
viii ©ISO/IEC2021–Allrightsreserved
---------------------- Page: 8 ----------------------
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
---------------------- Page: 9 ----------------------
ISO/IEC19075-8:2021(E)
x ©ISO/IEC2021–Allrightsreserved
---------------------- Page: 10 ----------------------
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
---------------------- Page: 11 ----------------------
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
---------------------- Page: 12 ----------------------
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
---------------------- Page: 13 ----------------------
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
---------------------- Page: 14 ----------------------
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
---------------------- Page: 15 ----------------------
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
---------------------- Page: 16 ----------------------
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
---------------------- Page: 17 ----------------------
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
---------------------- Page: 18 ----------------------
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
---------------------- Page: 19 ----------------------
ISO/IEC19075-8:2021(E)
5.3 MD-arraytypedefinition

Allinall,anySQLdatatypeisallowedtobeanelementtypeofanMD-array,exceptforcollection-con-

tainingtypes.AdatatypeTYiscollection-containingifexactlyoneofthefollowingconditionsistrue:

— TYisacollectiontype.
— TYisarowtype,andthedeclaredtypeofsomefieldofTYisaco
...

INTERNATIONAL ISO/IEC
STANDARD 19075-8
First edition
Information technology — Guidance
for the use of database language
SQL —
Part 8:
Multidimensional arrays
PROOF/ÉPREUVE
Reference number
ISO/IEC 19075-8:2021(E)
ISO/IEC 2021
---------------------- Page: 1 ----------------------
ISO/IEC 19075-8:2021(E)
COPYRIGHT PROTECTED DOCUMENT
© 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
---------------------- Page: 2 ----------------------
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
---------------------- Page: 3 ----------------------
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
---------------------- Page: 4 ----------------------
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
---------------------- Page: 5 ----------------------
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
---------------------- Page: 6 ----------------------
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
---------------------- Page: 7 ----------------------
ISO/IEC19075-8:2021(E)
AlistofallpartsintheISO/IEC19075seriescanbefoundontheISOandIECwebsites.

Anyfeedbackorquestionsonthisdocumentshouldbedirectedtotheuser’snationalstandardsbody.A

completelistingofthesebodiescanbefoundatwww.iso.org/members.htmlandwww.iec.ch/-
national-committees.
viii ©ISO/IEC2021–Allrightsreserved
---------------------- Page: 8 ----------------------
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
---------------------- Page: 9 ----------------------
ISO/IEC19075-8:2021(E)
x ©ISO/IEC2021–Allrightsreserved
---------------------- Page: 10 ----------------------
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
---------------------- Page: 11 ----------------------
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
---------------------- Page: 12 ----------------------
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
---------------------- Page: 13 ----------------------
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
---------------------- Page: 14 ----------------------
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
---------------------- Page: 15 ----------------------
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
---------------------- Page: 16 ----------------------
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
---------------------- Page: 17 ----------------------
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
---------------------- Page: 18 ----------------------
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
---------------------- Page: 19 ----------------------
ISO/IEC19075-8:2021(E)
5.3 MD-arraytypedefinition

Allinall,anySQLdatatypeisallowedtobeanelementtypeofanMD-array,exceptforcollection-con-

tainingtypes.AdatatypeTYiscollection-containingifexactlyoneofthefollowingconditionsistrue:

— TYisacollectiontype.
— TYisarowtype,andthedeclaredtypeofsomefieldofTYisacollection-containingtype.
— TYisdistincttype,andthesourcetypeofTYisacollection-containingtype.
— TYisastructuredtypeandthedeclaredtypeofsome
...

Questions, Comments and Discussion

Ask us and Technical Secretary will try to provide an answer. You can facilitate discussion about the standard in here.