, , LE TOUR DU MONDE EN 10 (+1) FONCTIONS GEOSPATIALES

Larguer les amarres

Cet article est une suite de l’article de Pierre-Louis BERTHOIN qui présente les fonctions géospatiales intégrées à DB2.

Ce nouvel article présente quelques fonctions géospatiales exploitables facilement.

Embarquement imminent

Pour constituer nos fichiers, nous avons téléchargé différents fichiers de type geojson FeatureCollection respectant la spec GeoCodeJSON.

Les liens suivants nous permettent de charger des fichiers JSON que nous utiliserons ans la constitution de nos fichier :

Concernant les fichiers JSON, on observe plusieurs types de géométries, principalement POLYGON et MULTIPOLYGON. C’est pourquoi il nous faut définir dans nos fichiers, une colonne qui puisse englober plusieurs types de géométries. Pour ce faire, le document Database Geospatial Analytics nous fournit quelques informations …

Nous choisirons donc, pour nos fichiers, une colonne basée sur la fonction ST_GEOMETRY, qui nous permet d’englober les deux type nommés ci-dessus. Voici donc comment nous constituerons nos tables.

-- Table des états américains
CREATE TABLE GGEOLOC.US_STATES (        
    STATE_ID CHAR(2) PRIMARY KEY,  
    STATE_FULL_NAME VARCHAR(50),   
    STATE_GEO QSYS2.ST_GEOMETRY); 

-- Table des pays
CREATE TABLE GGEOLOC.COUNTRIES (           
    CODE_ISO VARCHAR(3) PRIMARY KEY,  
    NAME VARCHAR(50),                 
    CNTRY_GEO QSYS2.ST_GEOMETRY);    

-- Table des villes 
CREATE TABLE GGEOLOC.MYCITIES (     
    CTY_NAME VARCHAR(50) ,     
    CTY_GEO QSYS2.ST_GEOMETRY);

Cet article étant dédié aux fonctions géospatiales, nous n’expliciterons pas la récupération des données.

Bienvenue à bord

ST_ISSIMPLE & ST_GEOMETRYTYPE …

… attachez vos ceintures

ST_ISSIMPLE nous permet de savoir si la géométrie de la figure sélectionnée est simple (valeur 1) ou bon (valeur 0).

SELECT STATE_FULL_NAME,
  CASE QSYS2.ST_ISSIMPLE(STATE_GEO)
    WHEN 0 THEN 'Geometry is not simple'
    WHEN 1 THEN 'Geometry is simple'
  END
FROM GGEOLOC.US_STATES where STATE_ID in ('WI', 'IL', 'IN', 'HI', 'AK');
AlaskaGeometry is not simple
HawaiiGeometry is simple
IllinoisGeometry is simple
IndianaGeometry is simple
WisconsinGeometry is simple

ST_GEOMETRYTYPE nous permet de savoir de quel type de géométrie nous parlons, et nous pouvons donc constater que la simplicité de la géométrie n’a pas de lien avec le caractère « MULTI » de la figure.

SELECT STATE_FULL_NAME, QSYS2.ST_GEOMETRYTYPE(STATE_GEO)
FROM GGEOLOC.US_STATES where STATE_ID in ('WI', 'IL', 'IN', 'HI', 'AK');
AlaskaST_MULTIPOLYGON
HawaiiST_MULTIPOLYGON
IllinoisST_POLYGON
IndianaST_POLYGON
WisconsinST_POLYGON

ST_ASTEXT & ST_ASBINARY …

… briefing avant décollage

Si nous exécutons une extraction brute de nos données, on ne comprend pas immédiatement

select STATE_ID, STATE_FULL_NAME, STATE_GEO 
from GGEOLOC.US_STATES where STATE_ID in ('OK', 'TX', 'AL', 'AR', 'CO');
ALAlabama01000000E6100000100000002100000015BCE82B483F3E4062B48EAA26804140EC6987BF261E56C0FAEE5696E83855C00000000000000000000000000000000000000000000000000000000000000000FF77C423DD383F42000000000070704001000000000000000000000000000000000000000000000000000000FF00000000000001000000000216135CFACB00C0AA9BFFF32147FFFE6982C001967EFFEF4924FFFDD3BFFFE3002DFFFC7D71FFE8AEFEC0012B85FFFF3F74C003ED88C002C203C000805EC002D768C0016BB4FFFF9507C0012B85FFFC3D42C0006AF9FFFD7E2BC003D823C000805EC00216DBC00241A5C00241A6C00140EAFFFED47BC00281D5C00095C2C002ECCDFFFC92D6C0020176C000805D00C02017699565C007857DC0042DB7FFFE541DC00402EDC0011620C00498B0FFFE7EE7C0029739C0015650C001EC12AA9BC001EC11C0029739C000EB57FFFE3EB8C0018119C000AB27C0026C6FFFFE541EC004431CFFFE3EB8C0134E15FFFC3D42C00D1EE8FFFD5362C000402FFFE541D3
ARArkansas01000000E61000001000000025000000E7B985AE4480404019C8E7EC64424240FC1A498270A757C08FE1B19FC56E56C00000000000000000000000000000000000000000000000000000000000000000E1FFD1EB441C4042000000000000724001000000000000000000000000000000000000000000000000000000180100000000000100000000022CF965FA5E718E00FFFDD3C0FFEF1E5AC002D768FFE52C6EFFFF2A0EFFFE944CC001967EC000C08DC0030232FFFF54D8C0022C40FFF7E4C10000C0069A27FFFFBFD1C02545DBC00216DCC0012B85C0032CFCFFFF2A0FC00140EAC001564FC0020176FFFD5362C004AE15C0026C6FC0026C6FC002C203C001ABE3FFFF14AAC00281D4C0032CFBC00100BBAA9CC000D5F2C002C203C0030232FFFFBFD1C000AB27C001967EC0029739C0011620C0012B86C0026C6FC001564FFFFF14AAC0006AF8C0018119C002AC9EC0018119C003AD5AFFFFBFD1C00281D4C002D768C002570AC000805DC000D5F2C00241A6C002D768C000805D00FFF62379C002D768C0026C70C001D6ACC002570AC002ECCDFFFEA9B19565FFBE0FE6
HIHawaii01000000E6100000180000003100000044E048A0C1F2324076417DCB9C3A3640C3EFA65B76F863C0E48409A3D95963C00000000000000000000000000000000000000000000000000000000000000000A30B1449B5780B420000000000007C4001000000000000000000000000000000000000000000000000000000A801000000000005000000000000003E0000006B000000B3000001130000000100000000014B149BF695D9A3FFFE541EFFFD5362AA9BFFFE13EEFFFBBCE4C0022C41C0005593C0034261C00216DCC000D5F1C00397F4FFFE541D00000001000000000143A484F6A4A46EC0002ACAFFFC1277FFFE3EB8FFFEE9E000C0038290C001967EC0018119000000010000000001532FDBF67EB36BFFFED47AFFFC1278FFFD7E2CFFFF6A3DFFFE3EB8C004D8DEC0016BB4C001C148C00241A6C000805EC001967EFFFF2A0EC0002ACAFFFE3EB900000001000000000140E281F6AAA8D1FFFE541DFFFE541EFFFE7EE7C00100BBFFFF6A3DC0029739FFFD5362C000402FFFFF9508C00100BBC0011620C00503A9C0022C40C000C08CC0026C6FFFFB11BDFFFF14AAFFFDBE5AC00216DCFFFE3EB80000000100000000012120ABF6B9336DC002D768C001D6ADC0020177C00357C5C00140EAC004C37AC002AC9EC002AC9EC0034261FFFBA77FC002017600C002570BFFFD1333C001967EFFFB3C87C0022C40FFFBBCE4FFFE2953FFFFAA6CFFFE3EB9C0011620FFFD5361FFFE7EE7FFFE13EFFFFDE925FFFA2666C002AC9EFFFC92D5FFFF7FA2FFFEA9B1C00095C3FFFEA9B1C003C2BE
OKOklahoma01000000E6100000100000002D0000004E64E60297D140404461BECB0D8442402FBE688F17C059C0809BC58B859B57C00000000000000000000000000000000000000000000000000000000000000000E00FEAF8FF3E45420000000000507540010000000000000000000000000000000000000000000000000000004D010000000000010000000002349447FA08C866AA9BFFE22A3C9565FFF16000FFF8651E0000C012229000C01BA983FFE26A6B00C0002ACAC0012B85FFFD1333C0036D2BC000805EC001D6ADFFFFD536C004D8DEFFFD1333C0011620C0002ACAC0031797FFFEA9B1C00357C6C0002ACAC0030232FFFEBF16C00140EAC0016BB4C001EC11FFFF54D8C002ECCDFFFE541DC00140EAFFFFBFD2C0022C41FFFDE924C0012B85C0020177C002AC9EFFFED47AC00397F5FFFEBF16C001564FC00095C3C001C147FFFE13EEC00140EAC0036D2BC003D824FFFE3EB8C001162000C00357C6FFFEE9E0C0032CFBFFFEA9B1C0012B85C002570AC0030232C000C08DC0034261FFFF2A0EC0018119C0018119C00397F4FFFF2A0FC000D5F2AA9BC003ED88C001564FC00100BBFFFE7EE7C003ED88FFFE3EB8C0018119FFFE541DC005D99AC01AD392C000D5F2C010E1A6FFFD2898C0079AE20000FFAC8318
TXTexas01000000E610000010000000980000003688D68A36E3394002009C54AD404240E126A3CA30A95AC0BEF73768AF6157C00000000000000000000000000000000000000000000000000000000000000000AA5048649E0A6442000000000074924001000000000000000000000000000000000000000000000000000000950400000000000100000000022CF965F9EE753200FFEDDD7000FFFF6A3DFFDA0EFDAA9BFFF08A0EFFFFAA6DFFF0B4D80000FFC9D87EFFFE7EE7FFFF9508FFFE3EB9C001C147FFFF2A0EC00241A6FFFBFD13C002AC9EFFFE6982C003D823FFFCFDCEC002C203FFFEBF16C0029739FFFC7D71C0032CFCFFFD68C7C005EEFFFFFE3EB8C001ABE3FFFD3DFDC00095C2FFFCD304C0029739FFFC1278C0002ACAFFFBA77FC0029739FFFE13EEC003AD5AFFFDFE8AC001D6ACFFFC3D41C009718FFFFDA8F6C003C2BE9565C00281D4C0030232C00216DC9565C000EB56C005190DC001ABE3C0032CFCC002C203C000805EC003AD59C001967DC000C08DFFFED47BC0036D2AC0002ACAC00402EDFFFF3F74C006AF8CFFFCA83AC002570AFFFED47BC0030232FFFEA9B1C000C08CFFFE13EEC0034261FFFDD3C0C001EC12FFFCFDCEC000AB27FFFC52A7C0020177FFFEBF15C0018119FFFB6751C001967DFFFDE924C0034261FFFD7E2CC00241A6FFFD3DFDC000D5F1FFFDD3C0C0029739FFFFBFD1C001967EFFFD9390C001EC12FFFDD3C0FFFF9507FFFD7E2CC00140EAFFFDA8F5FFFFAA6CFFFD3DFDC002C203FFFB6751C0016BB4FFFE13EEC0022C41FFFF54D9C0032CFBFFFDFE89C002570BFFFFBFD1C0031797FFFD7E2CC0041852C0002ACAC004C379FFFF3F73C00397F5FFFDFE8AC001EC11C001967EC004AE15C0029739FFFEA9B1C002ECCDFFFF9507C001967EFFFE541DC00498AFFFFF9508C00140EAFFFE944CC00241A69565C0002ACAC0012B85C00402EDC000D5F1FFFF7FA2FFFE3EB8C0020177C0006AF9FFFF9507C001ABE3C0058406C00241A5C00543D7C0038290C002D768C0036D2AC002C203C0006AF9FFFF3F74C002C203C00140EAC002ECCDC003ED88FFFBFD13C0006AF9C0011620FFFE13EEC001C148C000AB28C003C2BEFFFF14A99565C00100BCC002C203FFFF54D8C000EB57C00402EDC009315FC003C2BFC00498B0C0030231C002D768C002D768FFFFBFD1C000EB57FFFE6982C0022C40C000C08CFFFF9508C0016BB4C001C147C0020177FFFD7E2CC000805EFFFED47BC002D768C00216DBC007F0759565C00281D4C0018119FFFEA9B1C0056EA1C00397F5C002ECCDFFFED47AC001967EC0011621C00216DCFFFF7FA2C001967EC0018119C003ED88C001967EC0034260FFFFBFD1C0062F2EFFFBD249C00357C6AA9BC00357C5FFFCA83AC00FA0BC00C0081B3F00C000AB28FFFDD3C0FFFF3F73FFFCFDCEC0016BB4FFFE6982C001ABE3FFFA2666C001C148FFFE7EE7C0018119FFFC1278FFFEA9B1FFFEFF459565FFFC1278C000D5F1FFFF2A0EFFFE7EE7FFFC680CC000D5F2FFFE7EE7FFFF3F73FFFCBD9FFFFDA8F6FFFCFDCEC001564FFFFED47BC0011620FFFCD30500FFFCA83AC001C148FFFEE9E0FFFC92D5FFFC27DCC001EC12FFFEBF16FFFF6A3DFFFE3EB9C00140EAFFFEA9B1C0012B86FFFC680BFFFDFE89FFFD5362C00216DCFFFED47BC000402EFFFDD3BFC001ABE3FFFEBF16C000AB28FFFD1333FFFE944CFFFE13EFC00140EAFFFEBF16FFFFD536FFFCFDCEC001564FFFFCA83AFFFFD536FFFCE869C002ECCDFFFEE9E0C0002ACAFFFB2722FFFF7FA2FFFE2953C002ECCDFFFC92D5FFFFD536FFFED47BC01D95950000FFE4567D

Afin de connaitre la forme géographique, nous pouvons utiliser deux fonctions :

ST_ASTEXT permet de traduire ces données en WKT (well known text), compréhensibles pour une personne

select STATE_ID, STATE_FULL_NAME, QSYS2.ST_ASTEXT(STATE_GEO)
from GGEOLOC.US_STATES where STATE_ID in ('OK', 'TX', 'AL', 'AR', 'CO');
ALAlabamaPOLYGON ((-87.359296 35.00118, -88.202745 34.995703, -88.098683 34.891641, -88.241084 33.796253, -88.471115 31.895754, -88.394438 30.367687999999998, -88.137022 30.318396, -88.10416 30.499135, -88.01105199999999 30.685350999999997, -87.934375 30.657966, -87.90699 30.411503999999997, -87.655051 30.247194999999998, -87.51812799999999 30.280057, -87.37025 30.427933999999997, -87.446927 30.510088, -87.40858899999999 30.674397, -87.63314299999999 30.86609, -87.60028199999999 30.997536, -85.497137 30.997536, -85.004212 31.003013, -85.113751 31.27686, -85.042551 31.539752999999997, -85.141136 31.840985, -85.05350399999999 32.01077, -85.058981 32.136739999999996, -84.889196 32.262709, -85.004212 32.322956, -84.960397 32.421541, -85.069935 32.580372, -85.184951 32.859696, -85.43141299999999 34.124869, -85.606675 34.984749, -87.359296 35.00118))
ARArkansasPOLYGON ((-94.47384199999999 36.501861, -94.616242 36.501861, -94.430026 35.395519, -94.48479599999999 33.637420999999996, -94.38073399999999 33.544312999999995, -94.18356399999999 33.593606, -94.041164 33.54979, -94.041164 33.018527, -93.608485 33.018527, -91.16577 33.002096, -91.08909299999999 33.139019999999995, -91.143862 33.347144, -91.056231 33.429297999999996, -91.231493 33.560744, -91.072662 33.867453, -90.89192299999999 34.026284, -90.952169 34.135822999999995, -90.744046 34.300131, -90.749522 34.365854, -90.568783 34.420624, -90.585214 34.617793999999996, -90.481152 34.661609, -90.40995199999999 34.831393999999996, -90.251121 34.908072, -90.31136699999999 34.995703, -90.21278199999999 35.023087, -90.11419699999999 35.198349, -90.130628 35.439335, -89.944412 35.603643, -89.911551 35.756997, -89.763673 35.811766999999996, -89.730812 35.997983, -90.377091 35.997983, -90.21825899999999 36.184199, -90.064905 36.304691, -90.152536 36.496384, -94.47384199999999 36.501861))
HIHawaiiMULTIPOLYGON (((-156.982162 21.210244, -157.239579 21.221197999999998, -157.310779 21.106182, -157.080747 21.106182, -156.982162 21.210244)), ((-156.587823 21.029505, -156.697361 20.919966, -156.63163799999998 20.821381, -156.461853 20.783042, -156.44542199999998 20.607779999999998, -156.379699 20.580396, -156.05108199999998 20.651595999999998, -156.00179 20.793996, -156.324929 20.952827, -156.472807 20.892581, -156.587823 21.029505)), ((-157.951581 21.697691, -158.12684299999998 21.588153, -158.252813 21.582676, -158.110412 21.303352, -157.89681099999999 21.325259, -157.842042 21.462183, -157.951581 21.697691)), ((-155.63483499999998 18.948266999999998, -155.514342 19.134483, -155.295265 19.26593, -154.983079 19.348084, -154.807817 19.523346, -155.092618 19.736947, -155.092618 19.868392999999998, -155.284311 20.021748, -155.596496 20.125809999999998, -155.87582 20.26821, -155.897728 20.147717, -155.826528 20.032702, -155.92511299999998 19.857439, -156.062036 19.731469999999998, -155.886774 19.348084, -155.919636 19.123528999999998, -155.881297 19.035898, -155.63483499999998 18.948266999999998)), ((-159.468693 22.228955, -159.72610899999998 22.152276999999998, -159.764448 21.987969, -159.446786 21.872953, -159.33177 21.966061, -159.29890799999998 22.113939, -159.353678 22.218000999999997, -159.468693 22.228955)))
OKOklahomaPOLYGON ((-100.087706 37.000263, -102.042974 36.994786, -103.001438 37.000263, -103.001438 36.501861, -101.81294199999999 36.501861, -100.000075 36.501861, -100.000075 34.563024, -99.92339799999999 34.573978, -99.698843 34.382284999999996, -99.57835 34.415147, -99.260688 34.404193, -99.189488 34.2125, -98.986841 34.223454, -98.767763 34.135822999999995, -98.570593 34.146777, -98.488439 34.064623, -98.36247 34.157731, -98.170777 34.113915, -98.088623 34.004376, -97.94622199999999 33.987946, -97.869545 33.851022, -97.694283 33.982469, -97.45877399999999 33.905791, -97.37114299999999 33.823637, -97.25612799999999 33.861976, -97.173974 33.736005999999996, -96.922034 33.960561, -96.85083399999999 33.845545, -96.631756 33.845545, -96.423633 33.774345, -96.34695599999999 33.686713999999995, -96.14978599999999 33.840067999999995, -95.936185 33.889361, -95.8376 33.834590999999996, -95.602092 33.933175999999996, -95.547322 33.878406999999996, -95.289906 33.87293, -95.224183 33.960561, -94.96676699999999 33.861976, -94.86818199999999 33.74696, -94.48479599999999 33.637420999999996, -94.430026 35.395519, -94.616242 36.501861, -94.616242 37.000263, -100.087706 37.000263))
TXTexasPOLYGON ((-101.81294199999999 36.501861, -103.001438 36.501861, -103.039777 36.501861, -103.045254 34.01533, -103.067161 33.002096, -103.067161 31.999816, -106.616219 31.999816, -106.643603 31.901231, -106.528588 31.786216, -106.38071 31.731446, -106.20544799999999 31.468553, -105.953509 31.364490999999997, -105.77277 31.167320999999998, -105.60298499999999 31.085167, -105.39486099999999 30.855135999999998, -105.00599799999999 30.685350999999997, -104.896459 30.570335, -104.858121 30.389595999999997, -104.68833599999999 30.181472, -104.677382 29.924056, -104.50759699999999 29.639255, -104.266611 29.513285, -104.146119 29.381839, -103.52722399999999 29.135375999999997, -103.280762 28.982021999999997, -103.11645399999999 28.987499, -102.97953 29.184669, -102.91928399999999 29.190146, -102.80974499999999 29.524238999999998, -102.62900599999999 29.732363, -102.388021 29.765224999999997, -102.33872799999999 29.869286, -102.11417399999999 29.792609, -101.851281 29.803562999999997, -101.413125 29.754271, -101.259771 29.535193, -101.062601 29.458516, -101.01330899999999 29.370884999999998, -100.799708 29.244915, -100.673738 29.102515, -100.62992299999999 28.905344999999997, -100.498476 28.66436, -100.399891 28.582205, -100.29583 28.280973999999997, -100.082229 28.14405, -99.93435099999999 27.979741999999998, -99.879582 27.799003, -99.709797 27.656603, -99.605735 27.640172, -99.479765 27.48134, -99.50715 27.338939999999997, -99.424996 27.174632, -99.44690399999999 27.021276999999998, -99.266165 26.840538, -99.173057 26.539306999999997, -99.030656 26.413337, -98.82253299999999 26.369522, -98.669178 26.238075, -98.46653099999999 26.221643999999998, -98.198161 26.057336, -97.885976 26.068289999999998, -97.65046699999999 26.018997, -97.524498 25.887551, -97.217789 25.991612999999997, -97.30542 26.161398, -97.332805 26.353091, -97.44234399999999 26.457152999999998, -97.46972799999999 26.758384, -97.56283599999999 26.840538, -97.55735899999999 26.988415999999997, -97.480682 26.99937, -97.425913 27.262262999999997, -97.54092899999999 27.229401, -97.513544 27.360847999999997, -97.404005 27.333463, -97.25612799999999 27.694941, -97.026096 28.039987999999997, -96.801542 28.226204, -96.774157 28.406943, -96.593418 28.357650999999997, -96.401725 28.439805, -96.66461799999999 28.697221, -96.593418 28.724605999999998, -96.478402 28.598636, -96.23194 28.642452, -96.226463 28.582205, -96.04572399999999 28.647928999999998, -95.98547699999999 28.604112999999998, -95.38301399999999 28.867006, -95.08178199999999 29.113469, -94.895566 29.310637999999997, -94.911997 29.496854, -95.016059 29.557101, -94.96676699999999 29.699500999999998, -94.87365899999999 29.672117, -94.742212 29.787132, -94.70935 29.622823999999998, -94.523134 29.546146999999998, -94.002825 29.683069999999997, -93.838517 29.688547, -93.926148 29.787132, -93.69063899999999 30.143133, -93.76731699999999 30.334826, -93.69611599999999 30.438888, -93.728978 30.575812, -93.630393 30.679873999999998, -93.526331 30.937289999999997, -93.542762 31.150889999999997, -93.816609 31.556184, -93.822086 31.775261999999998, -94.041164 31.994339, -94.041164 33.018527, -94.041164 33.54979, -94.18356399999999 33.593606, -94.38073399999999 33.544312999999995, -94.48479599999999 33.637420999999996, -94.86818199999999 33.74696, -94.96676699999999 33.861976, -95.224183 33.960561, -95.289906 33.87293, -95.547322 33.878406999999996, -95.602092 33.933175999999996, -95.8376 33.834590999999996, -95.936185 33.889361, -96.14978599999999 33.840067999999995, -96.34695599999999 33.686713999999995, -96.423633 33.774345, -96.631756 33.845545, -96.85083399999999 33.845545, -96.922034 33.960561, -97.173974 33.736005999999996, -97.25612799999999 33.861976, -97.37114299999999 33.823637, -97.45877399999999 33.905791, -97.694283 33.982469, -97.869545 33.851022, -97.94622199999999 33.987946, -98.088623 34.004376, -98.170777 34.113915, -98.36247 34.157731, -98.488439 34.064623, -98.570593 34.146777, -98.767763 34.135822999999995, -98.986841 34.223454, -99.189488 34.2125, -99.260688 34.404193, -99.57835 34.415147, -99.698843 34.382284999999996, -99.92339799999999 34.573978, -100.000075 34.563024, -100.000075 36.501861, -101.81294199999999 36.501861))

ST_ASBINARY permet de traduire ces données en WKB (well known binary), utilisables dans un programme

select STATE_ID, STATE_FULL_NAME, QSYS2.ST_ASBINARY(STATE_GEO)
from GGEOLOC.US_STATES where STATE_ID in ('OK', 'TX', 'AL', 'AR', 'CO');
ALAlabama00000000030000000100000021C055D6FEB4A6655A40418026AA8EB463C0560CF9C62A1B5C40417F733226C3B9C0560650D2806AF4404172214AD362E9C0560F6DEB95E5B04040E5EB9E492BC3C0561E26BF8769EC403FE550225742DDC056193E78E1932D403E5E20CCFF21B3C05608C4F7EC3548403E518266772D5EC05606AA8EB46349403E7FC74FB549F9C05600B51372A38B403EAF7329C347E8C055FBCCCCCCCCCD403EA87075B3E143C055FA0C1FC8F323403E6958537E2C55C055E9EC5B078D93403E3F482BE8BC16C055E1290257C914403E47B1D0C80410C055D7B22D0E5604403E6D8D1526D8B1C055DC9A73B42CC3403E8295208E1501C055DA26527A2057403EACA548238540C055E8856A37AC3E403EDDB813016484C055E66B05319828403EFF5E84F09529C0555FD117B5286B403EFF5E84F09529C05540450268900C403F00C575C0767DC0554747B24638C9403F46E04C059210C05542B927D45A60403F8A2D40AAEAFAC05549085F4A1273403FD74ACAFF6D33C055436C9C0A8D2140400160E94EE393C05543C6583E857740401180B242070BC05538E89656EEFA404021A072D1AEB4C05540450268900C404029569F490603C0553D7724FA8B4C404035F50E347629C0554479D0A6762140404A49A133C1CEC0554BD63CB8173340406E0A84BE4042C0555B9C4545846E40410FFBB517A461C05566D3C361134040417E0C4156E265C055D6FEB4A6655A40418026AA8EB463
ARArkansas00000000030000000100000025C0579E536D655E284042403CFB3311A5C057A77082491AFC4042403CFB3311A5C0579B858BC59B804041B2A05DD8F92BC0579F06E5CD4ED24040D19702E6644DC057985DF2239E6A4040C5AC0C62E4D1C0578BBF83382E444040CBFB480A5ACDC05782A26E5471714040C65F84CAD57CC05782A26E5471714040825F17BD8BE7C05766F16B11C6D24040825F17BD8BE7C056CA9BF9C62A1B40408044AE85B9E9C056C5B3B320535C404091CB6848BEB5C056C93508F648C74040AC6F36EF8056C056C39949E8815E4040B6F33CA31E7DC056CED0C80410304040C7C6759AB6D0C056C4A67E846A5D4040EF08B32CE896C056B9154434E3364041035D462C343BC056BCF0563ED0F640411162A5E785B5C056AF9E731D2E0E4041266AB14EC205C056AFF82B1F687B40412ED44DCA8E2EC056A466F0CFE154404135D701D9F4D3C056A574256BCA5340414F13DFB0D51FC0569ECB31C219EB404154AF9A8CDEA0C0569A3CA7503B8140416A6B1E5C0B99C05690125DD095AF4041743BB40B34E7C05693ED6FDA836E40417F733226C3B9C0568D9E3864CB5B404182F483CAFB3BC056874F00EF134840419963800218DFC056885C358AFC484041B83C21187E7CC0567C713F077CCC4041CD442C7FBACBC0567A56DA0168B64041E0E5471715C6C05670E004B7F5A54041E7E7FB267C6BC0566EC59FB1E18F4041FFBDE82D7B63C0569822424A276B4041FFBDE82D7B63C0568DF7F498C3B040421793D5347A5BC0568427674D1633404227001D5C3159C05689C3265ADD9C40423F8982CB20FBC0579E536D655E284042403CFB3311A5
HIHawaii00000000060000000500000000030000000100000005C0639F6DDF00ABF7403535D28D002E23C063A7AAA19439DE403538A06E9FF0CBC063A9F1E6CD291340351B2EBE596C83C063A2957ABB880140351B2EBE596C83C0639F6DDF00ABF7403535D28D002E230000000003000000010000000BC06392CF722E1AC54035078DA3C21188C0639650C80410304034EB82E44B6E93C063943660E51D254034D24606748E47C0638EC77FF151E74034C87570C564F9C0638E40E5A35D6740349B97785729B2C0638C267E846A5D40349494D50EBAAEC06381A276B7ED414034A6CEFED63454C063800EA9E6EEB74034CB43526527A2C0638A65D18090B44034F3EC7863BEECC0638F213C254A3C4034E480303C07EEC06392CF722E1AC54035078DA3C2118800000000030000000100000007C063BE7359FF4FD74035B29BE09BB6AAC063C40F190D173F4035969131EC0B56C063C8170B49E01E4035952A411C2A02C063C3887EBF22C040354DA87A072D1BC063BCB2ACFB762D403553442C7FBACBC063BAF202107B7840357651A005C465C063BE7359FF4FD74035B29BE09BB6AA00000000030000000100000012C0637450917D6B654032F2C1A048E043C06370757D5A9EB24033226D7A56DE33C0636972CF95D4E940334413FD0D0679C0635F7562174C4D4033591C087442C8C06359D9A30984E4403385FA00E27E0FC06362F6BA0620AB4033BCA88EFFE2A4C06362F6BA0620AB4033DE4F00EF1348C06369191361DC944034059146E4C0DFC06373167EC7863C40342035158B827FC0637C06B7AA25D9403444A9691A75CDC0637CBA30121683403425D0C8041030C0637A72EAD9274E4034085F28848388C0637D9A869403574033DB811F4F50A0C06381FC32EBE5974033BB419E30014FC0637C6073DE1E2E4033591C087442C8C0637D6DA87A072D40331F9F98B71B8AC0637C3395C42203403309309C7FFDE7C0637450917D6B654032F2C1A048E04300000000030000000100000008C063EEFF88765BA740363A9CCB7D4174C063F73C48F10A99403626FBA01EEED8C063F8765BA6EFC34035FCEB8950763AC063EE4C122749F14035DF79D909F1F1C063EA9DDC1E79684035F74FC610F0E9C063E990A782906840361D2B1B36BD2BC063EB5154866A12403637CEE9DD7ECBC063EEFF88765BA740363A9CCB7D4174
OKOklahoma0000000003000000010000002DC059059CF9A06A6E404280089E34330DC05982C01605250340427F5525CC4263C059C0178F68BE2F404280089E34330DC059C0178F68BE2F4042403CFB3311A5C05974073DE1E2DE4042403CFB3311A5C05900013A92A3054042403CFB3311A5C05900013A92A305404148112BA16E7AC058FB18F3ECCC46404149781C714FCEC058ECB9D7FD8277404130EEB702602CC058E503AFB7E9104041352389720429C058D0AF1CB89D6B404133BC98A222D5C058CC209246BF0140411B3333333333C058BF286727568640411C9A24031487C058B123076C050C40411162A5E785B5C058A484988094E6404112C996B7670AC0589F4295A6C5D2404108459103C8E2C0589732B55EF1FE404114308787485EC0588AEE02A77A2D40410E94C447C30DC05885ABFFCDAB194041008F648C7193C0587C8EE6B8305D4040FE7503B81B65C05877A6A012599F4040ECEE49F51698C0586C6F21F6CACD4040FDC18B502ABBC0585D5C8D9F90534040F3F0F5A1016DC05857C0CE91C8EA4040E96CEFED6345C058506466B1E5C04040EE553AC4F7ECC0584B2263D816AD4040DE3571D1D473C0583B029AE4F3344040FAF3A9B06812C0583674107314CA4040EC3AD18D25EEC058286EB0B7C3504040EC3AD18D25EEC0581B1CCD9620684040E31DBCA9691AC058163486F049A94040D7E63E8DDA48C05809961804D9834040EB8759253543C057FBEA747D805E4040F1D694CCAB3FC057F59B3D07C84B4040EAD3E0BD4499C057E688ACE24BBA4040F7724FA8B4BFC057E30752DA98674040F06FA3FCC9EAC057D28DD1E53A824040EFBC2B94D940C057CE5903A7546D4040FAF3A9B06812C057BDDF82B1F6874040EE553AC4F7ECC057B7904B3C3E744040DF9C62A1B5C8C0579F06E5CD4ED24040D19702E6644DC0579B858BC59B804041B2A05DD8F92BC057A77082491AFC4042403CFB3311A5C057A77082491AFC404280089E34330DC059059CF9A06A6E404280089E34330D
TXTexas00000000030000000100000098C05974073DE1E2DE4042403CFB3311A5C059C0178F68BE2F4042403CFB3311A5C059C28BB4D488834042403CFB3311A5C059C2E5710880D8404101F6555C52E7C059C44C5DA6A44440408044AE85B9E9C059C44C5DA6A444403FFFF3F0FE047DC05AA77021D10B20403FFFF3F0FE047DC05AA930CAA326E1403FE6B713272431C05AA1D462C343B7403FC94573A79789C05A985D8D79D0A6403FBB400B88CA3EC05A8D260F5E41D4403F77F316E37154C05A7D064A9CDC44403F5D4F483CAFB3C05A7175104D551D403F2AD58C8EEF1BC05A66974E65BEA0403F15CD8127B2CCC05A594567125DD0403EDAEA3161A1DBC05A4062456F75D9403EAF7329C347E8C05A395F9591CD1C403E9201797CC3A0C05A36EB7457C0B1403E63BC903EA704C05A2C0DB2702A34403E2E74F2F123C4C05A2B5A3A08398A403DEC8EEF1BAC2EC05A207C7820A30D403DA3A63736CDF2C05A11102795703F403D8366A5508701C05A095A038194C0403D61C03361565CC059E1BE09BB6AA4403D22A80064A9CDC059D1F8012DFD69403CFB65CB35F3D7C059C773FB7A5F41403CFCCCBC05D52CC059BEB09E98DCDB403D2F4677B395C4C059BAD58C8EEF1B403D30AD68837718C059B3D2DCB1465E403D863486F049A9C059A841A261BF37403DBB7C243DCCEAC05998D556084A51403DC3E5C91D14E3C05995ADB8348F53403DDE8986FCDEE3C059874EA077036C403DCAE86C6583E8C059767B634DAD32403DCDB64E054690C0595A70A3D70A3D403DC117E77D523BC05950A0168B5CC0403D890268900C52C0594401A79FEC9A403D75614DF8B157C05940DA0DFDEF84403D5EF251C193B3C059332E6A76965F403D3EB2BFDB4CC2C0592B1E85FD04A3403D1A3E6C4C5975C0592850A88EFFE2403CE7C4B09E98DCC0591FE707E175D1403CAA137F38C543C0591997D06BBDBE403C950B630A9153C05912EEE0F3CB3E403C47EDE97D06BBC05905433D6C7219403C24E075F6FD22C058FBCC6822FF08403BFAD05F288483C058F84B124D099E403BCC8B75EA67E8C058ED6D50657321403BA817225B749BC058E6C45CBBC2B9403BA3E24FEBD09EC058DEB4784230FD403B7B39192641B3C058E07525460AA6403B56C4C5974E65C058DB33226C3B92403B2CB4AEC8D5C7C058DC9A133C1CE6403B057268D32830C058D108D8EC95C0403AD72D7F950B95C058CB135DAAD602403A8A10060780FDC058C1F644955B46403A69D074213A0CC058B4A46173B85E403A5E98FE69270BC058AAD3CFF64CF9403A3CF27BB2FEC5C0589DDBA4D6E47D403A38BDA9435AC8C0588CAEAB7995EE403A0EAD9274E22AC05878B3D4AE429E403A117B7414A4D2C05869A140570824403A04DCFCC5B8DCC0586191600F34504039E3368AD68837C0584DF041461B6D4039FDDA597D49D7C058538C0053E2D6403A2951611BA3CAC058554CAD57BC7F403A5A642BF9830EC0585C4F5D35653C403A7507FAA044AEC0585E10060780FD403AC225742DCF46C0586405814940BB403AD72D7F950B95C05863ABC5154866403AFD08D4BAD7D7C0585EC37E6F71A8403AFFD6B65A9A80C0585B4228997C3D403B4323AAFFF36AC058629E94AB1D4F403B3ABA0620AB71C05860DDE7A743A6403B5C6088D6D3B6C05859DB37C99AE9403B555DD4C76D11C058506466B1E5C0403BB1E7A743A648C05841AB8E8EA39C403C0A3CA7503B81C058334C76D117B5403C39E8815E3971C058318BC9CD3E0C403C682D6A9C560CC05825FA8F7DB6E5403C5B8F041461B6C05819B5DCC63F14403C70970F7B9E06C0582A8919EF954E403CB27D1351159CC05825FA8F7DB6E5403CB97FC7607C41C0581E9E236C15D3403C9940357A3550C0580ED81ADEA897403CA477BBF93FF2C0580E7E5EAAB042403C950B630A9153C05802ED245B291B403CA5DEACC92146C057FF120E1F7D73403C9AA7264A16A4C057D8834D26FA3F403CDDF41AEF6F8FC057C53BEA91D9B1403D1D0C4DEC1C1DC057B950F40E5A36403D4F85F8D2E514C057BA5E28AA4335403D7F31D2E0E304C057C1071C53F39D403D8E9E2BCF91A3C057BDDF82B1F687403DB3127F5E84F0C057B7EA077036C9403DAC0FDC1615ECC057AF8066C2ACB8403DC9817B95A294C057AD65FD8ADABA403D9F7164C729F5C057A17B07075B3E403D8BD04A2FCEFAC057802E48E8A71E403DAEDDACEEE0F3C05775AA433508F6403DB0449DBEC248C0577B460242D05F403DC9817B95A294C0576C336DEB95E5403E24A45D41FA76C057711BB8C32A8C403E55B7281FD9BAC0576C8D2A1F8E3A403E705AF6C69B5AC0576EA793576039403E93686A4CA4F4C05768585BE1A826403EAE0C38F36694C05761AF6837F7BE403EEFF23CC8DE2AC05762BC9CD3E0BD403F26A0BA1F4B1EC0577443526527A2403F8E62131A8EF7C057749D0E991FF7403FC6779207D4E0C05782A26E547171403FFE8D002E2329C05782A26E5471714040825F17BD8BE7C05782A26E5471714040C65F84CAD57CC0578BBF83382E444040CBFB480A5ACDC057985DF2239E6A4040C5AC0C62E4D1C0579F06E5CD4ED24040D19702E6644DC057B7904B3C3E744040DF9C62A1B5C8C057BDDF82B1F6874040EE553AC4F7ECC057CE5903A7546D4040FAF3A9B06812C057D28DD1E53A824040EFBC2B94D940C057E30752DA98674040F06FA3FCC9EAC057E688ACE24BBA4040F7724FA8B4BFC057F59B3D07C84B4040EAD3E0BD4499C057FBEA747D805E4040F1D694CCAB3FC05809961804D9834040EB8759253543C058163486F049A94040D7E63E8DDA48C0581B1CCD9620684040E31DBCA9691AC058286EB0B7C3504040EC3AD18D25EEC0583674107314CA4040EC3AD18D25EEC0583B029AE4F3344040FAF3A9B06812C0584B2263D816AD4040DE3571D1D473C058506466B1E5C04040EE553AC4F7ECC05857C0CE91C8EA4040E96CEFED6345C0585D5C8D9F90534040F3F0F5A1016DC0586C6F21F6CACD4040FDC18B502ABBC05877A6A012599F4040ECEE49F51698C0587C8EE6B8305D4040FE7503B81B65C05885ABFFCDAB194041008F648C7193C0588AEE02A77A2D40410E94C447C30DC0589732B55EF1FE404114308787485EC0589F4295A6C5D2404108459103C8E2C058A484988094E6404112C996B7670AC058B123076C050C40411162A5E785B5C058BF286727568640411C9A24031487C058CC209246BF0140411B3333333333C058D0AF1CB89D6B404133BC98A222D5C058E503AFB7E9104041352389720429C058ECB9D7FD8277404130EEB702602CC058FB18F3ECCC46404149781C714FCEC05900013A92A305404148112BA16E7AC05900013A92A3054042403CFB3311A5C05974073DE1E2DE4042403CFB3311A5

ST_AREA & ST_BUFFER …

… dans les aires

ST_AREA nous donne la surface en m² d’une aire géographique (POLYGON ou MULTIPOLYGON)

on ajoute une colonne ici pour avoir une idée de l’aire en km²

select STATE_ID, STATE_FULL_NAME, QSYS2.ST_AREA(STATE_GEO), integer(QSYS2.ST_AREA(STATE_GEO)/1000000)
from GGEOLOC.US_STATES 
where STATE_ID in ('OK', 'TX', 'AL', 'AR', 'HI');
ALAlabama1.3409800288446873E11134098
ARArkansas1.3838751120399905E11138387
HIHawaii1.4748657954505682E1014748
OKOklahoma1.8250255202012402E11182502
TXTexas6.886199875225208E11688619

ST_BUFFER nous donne les coordonnées d’une surface élargie du nombre de mètres voulus

voici un exemple de calcul de surfaces en élargissant de 1000 m les frontières de deux états

select STATE_ID, STATE_FULL_NAME, integer(QSYS2.ST_AREA(STATE_GEO)/1000000), integer(QSYS2.ST_AREA(QSYS2.ST_BUFFER(STATE_GEO, 1000))/1000000)
from GGEOLOC.US_STATES 
where STATE_ID in ('OK', 'AL');
ALAlabama134098135822
OKOklahoma182502184806

ST_DISJOINT & ST_WITHIN …

… garder le cap

ST_DISJOINT retourne 1 si deux figures n’ont rien en commun.

select CTY_NAME, CODE_ISO
from GGEOLOC.MYCITIES, GGEOLOC.COUNTRIES
where QSYS2.ST_DISJOINT(CTY_GEO, CNTRY_GEO) = 0 ;

HELSINKIFIN
TEGUCIGALPAHND
NAIROBIKEN
GUADALAJARAMEX
COPENHAGENDNK
LYONFRA
NANTESFRA
OSLONOR
ROCHESTERUSA

ST_WITHIN retourne 1 si la première figure est complètement dans la seconde.

Exemple : Une ville est-elle contenue dans un pays ? Un pays est-il contenu dans une ville ?

select CTY_NAME, CODE_ISO, QSYS2.ST_WITHIN(CTY_GEO, CNTRY_GEO), QSYS2.ST_WITHIN(CNTRY_GEO, CTY_GEO)
from GGEOLOC.MYCITIES, GGEOLOC.COUNTRIES
where CTY_NAME in ('LYON', 'ROCHESTER') and CODE_ISO in ('FRA', 'USA') ;
LYONFRA10
ROCHESTERFRA00
LYONUSA00
ROCHESTERUSA10

ST_INTERSECTS & ST_INTERSECTION …

… passer la frontière

ST_INTERSECTS nous permet de savoir si deux figures ont une intersection (la fonction retourne 1 si tel est le cas)

Dans l’exemple suivant, on cherche parmi une liste d’états, à savoir si ceux-ci sont directement voisins du Michigan

select t1.STATE_FULL_NAME, t2.STATE_FULL_NAME, 
CASE WHEN QSYS2.ST_INTERSECTS(t1.STATE_GEO, t2.STATE_GEO) = 1 
    THEN 'Etats Voisins'
    ELSE 'Etats éloignés'
END as config
from GGEOLOC.US_STATES t1, GGEOLOC.US_STATES t2
where t1.STATE_ID = 'MI'
and t2.STATE_ID in('WI', 'IL', 'IN', 'OH', 'PA', 'MN') ;
MichiganIllinoisEtats éloignés
MichiganIndianaEtats Voisins
MichiganMinnesotaEtats éloignés
MichiganOhioEtats Voisins
MichiganPennsylvaniaEtats éloignés
MichiganWisconsinEtats Voisins

Il suffisait de voir la carte pour s’en rendre compte !! Heureusement, ST_INTERSECTION nous en dit beaucoup plus puisqu’elle nous indique la forme de l’intersection entre deux figures géométriques.

select t1.STATE_FULL_NAME, t2.STATE_FULL_NAME, 
QSYS2.ST_ASTEXT(QSYS2.ST_INTERSECTION(t1.STATE_GEO, t2.STATE_GEO)),
CASE WHEN QSYS2.ST_INTERSECTS(t1.STATE_GEO, t2.STATE_GEO) = 1 
    THEN 'Etats Voisins'
    ELSE 'Etats éloignés'
END as config
from GGEOLOC.US_STATES t1, GGEOLOC.US_STATES t2
where t1.STATE_ID = 'MI'
and t2.STATE_ID in('WI', 'IL', 'IN', 'OH', 'PA', 'MN');
MichiganIllinoisPOINT EMPTYEtats éloignés
MichiganIndianaMULTILINESTRING ((-84.807042 41.759724, -85.990061 41.759724), (-84.807042 41.694001, -84.807042 41.759724), (-85.990061 41.759724, -86.82255599999999 41.759724))Etats Voisins
MichiganMinnesotaPOINT EMPTYEtats éloignés
MichiganOhioLINESTRING (-83.45423799999999 41.732338999999996, -84.807042 41.694001)Etats Voisins
MichiganPennsylvaniaPOINT EMPTYEtats éloignés
MichiganWisconsinMULTILINESTRING ((-87.791975 45.500474, -87.781021 45.675736), (-89.09000999999999 46.135799, -90.11967399999999 46.338446), (-87.885083 45.363551, -87.791975 45.500474), (-87.742682 45.199242999999996, -87.649574 45.341643), (-87.989145 45.796229, -88.10416 45.922199), (-87.589328 45.095181, -87.742682 45.199242999999996), (-87.781021 45.675736, -87.989145 45.796229), (-90.229213 46.508230999999995, -90.41542899999999 46.568478), (-88.662808 45.987922, -89.09000999999999 46.135799), (-90.11967399999999 46.338446, -90.229213 46.508230999999995), (-88.10416 45.922199, -88.531362 46.020784), (-88.531362 46.020784, -88.662808 45.987922), (-87.649574 45.341643, -87.885083 45.363551))Etats Voisins

ST_DISTANCE …

… une dernière pour la route

ST_DISTANCE va retourner la distance entre deux points, mais il est intéressant de l’utiliser sur des figures de type POLYGON …

select t1.STATE_FULL_NAME, t2.STATE_FULL_NAME, 
QSYS2.ST_DISTANCE(t1.STATE_GEO, t2.STATE_GEO)/1000
CASE WHEN QSYS2.ST_INTERSECTS(t1.STATE_GEO, t2.STATE_GEO) = 1 
    THEN 'Etats Voisins'
    ELSE 'Etats éloignés'
END as config
from GGEOLOC.US_STATES t1, GGEOLOC.US_STATES t2
where t1.STATE_ID = 'MI'
and t2.STATE_ID in('WI', 'IL', 'IN', 'OH', 'PA', 'MN');
MichiganIllinois58.493941547601004
MichiganIndiana0.0
MichiganMinnesota33.60195301382611
MichiganOhio0.0
MichiganPennsylvania179.1488383130458
MichiganWisconsin0.0

… pour lesquelles on se rend compte que la fonction retourne la distance (ramenées en km ici) entre les points les plus proches des deux figures comparées.

Atterrissage

Nous n’avons exploré ici qu’une partie des fonctions géospatiales disponibles. Il en existe bien d’autres fonctions pour savoir si une figure recouvre complètement une autre, si une figure est contenue dans une autre si une figure en traverse une autre, … Il existe également des fonctions de manipulation des GEOHASHES (système de géocodage basé sur la division d’une zone géographique en cellules).

Bref, tout une panoplie de fonctions que l’on peut combiner à l’infini et au-delà !