ISO/IEC 19075-8:2021
(Main)Information technology — Guidance for the use of database language SQL — Part 8: Multidimensional arrays
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
Relations
Buy Standard
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-
2
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-
2
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.