ISO/IEC PRF 19075-8

Information technology -- Guidance for the use of database language SQL

Titre manque

General Information

• Status: Published
• ICS: 35.060 - Languages used in information technology
• Technical Committee: ISO/IEC JTC 1/SC 32 - Data management and interchange
• Drafting Committee: ISO/IEC JTC 1/SC 32/WG 3 - Database language

RELATIONS

• Revised: ISO/IEC TR 19075-8:2019 - Information technology database languages -- SQL technical reports
• Effective Date: 23-Apr-2020

Standards Content (sample)

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: [phone]
Email: [e-mail]
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
...