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 :
- Etats Américains https://github.com/PublicaMundi/MappingAPI/blob/master/data/geojson/us-states.json
- Pays https://github.com/datasets/geo-countries/blob/master/data/countries.geojson
- Villes du monde https://github.com/drei01/geojson-world-cities/blob/master/cities.geojson (ce dernier fichier étant extrêmement fourni, nous en avons extrait quelques villes pour nos tests)
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');
| Alaska | Geometry is not simple |
| Hawaii | Geometry is simple |
| Illinois | Geometry is simple |
| Indiana | Geometry is simple |
| Wisconsin | Geometry 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');
| Alaska | ST_MULTIPOLYGON |
| Hawaii | ST_MULTIPOLYGON |
| Illinois | ST_POLYGON |
| Indiana | ST_POLYGON |
| Wisconsin | ST_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');
| AL | Alabama | 01000000E6100000100000002100000015BCE82B483F3E4062B48EAA26804140EC6987BF261E56C0FAEE5696E83855C00000000000000000000000000000000000000000000000000000000000000000FF77C423DD383F42000000000070704001000000000000000000000000000000000000000000000000000000FF00000000000001000000000216135CFACB00C0AA9BFFF32147FFFE6982C001967EFFEF4924FFFDD3BFFFE3002DFFFC7D71FFE8AEFEC0012B85FFFF3F74C003ED88C002C203C000805EC002D768C0016BB4FFFF9507C0012B85FFFC3D42C0006AF9FFFD7E2BC003D823C000805EC00216DBC00241A5C00241A6C00140EAFFFED47BC00281D5C00095C2C002ECCDFFFC92D6C0020176C000805D00C02017699565C007857DC0042DB7FFFE541DC00402EDC0011620C00498B0FFFE7EE7C0029739C0015650C001EC12AA9BC001EC11C0029739C000EB57FFFE3EB8C0018119C000AB27C0026C6FFFFE541EC004431CFFFE3EB8C0134E15FFFC3D42C00D1EE8FFFD5362C000402FFFE541D3 |
| AR | Arkansas | 01000000E61000001000000025000000E7B985AE4480404019C8E7EC64424240FC1A498270A757C08FE1B19FC56E56C00000000000000000000000000000000000000000000000000000000000000000E1FFD1EB441C4042000000000000724001000000000000000000000000000000000000000000000000000000180100000000000100000000022CF965FA5E718E00FFFDD3C0FFEF1E5AC002D768FFE52C6EFFFF2A0EFFFE944CC001967EC000C08DC0030232FFFF54D8C0022C40FFF7E4C10000C0069A27FFFFBFD1C02545DBC00216DCC0012B85C0032CFCFFFF2A0FC00140EAC001564FC0020176FFFD5362C004AE15C0026C6FC0026C6FC002C203C001ABE3FFFF14AAC00281D4C0032CFBC00100BBAA9CC000D5F2C002C203C0030232FFFFBFD1C000AB27C001967EC0029739C0011620C0012B86C0026C6FC001564FFFFF14AAC0006AF8C0018119C002AC9EC0018119C003AD5AFFFFBFD1C00281D4C002D768C002570AC000805DC000D5F2C00241A6C002D768C000805D00FFF62379C002D768C0026C70C001D6ACC002570AC002ECCDFFFEA9B19565FFBE0FE6 |
| HI | Hawaii | 01000000E6100000180000003100000044E048A0C1F2324076417DCB9C3A3640C3EFA65B76F863C0E48409A3D95963C00000000000000000000000000000000000000000000000000000000000000000A30B1449B5780B420000000000007C4001000000000000000000000000000000000000000000000000000000A801000000000005000000000000003E0000006B000000B3000001130000000100000000014B149BF695D9A3FFFE541EFFFD5362AA9BFFFE13EEFFFBBCE4C0022C41C0005593C0034261C00216DCC000D5F1C00397F4FFFE541D00000001000000000143A484F6A4A46EC0002ACAFFFC1277FFFE3EB8FFFEE9E000C0038290C001967EC0018119000000010000000001532FDBF67EB36BFFFED47AFFFC1278FFFD7E2CFFFF6A3DFFFE3EB8C004D8DEC0016BB4C001C148C00241A6C000805EC001967EFFFF2A0EC0002ACAFFFE3EB900000001000000000140E281F6AAA8D1FFFE541DFFFE541EFFFE7EE7C00100BBFFFF6A3DC0029739FFFD5362C000402FFFFF9508C00100BBC0011620C00503A9C0022C40C000C08CC0026C6FFFFB11BDFFFF14AAFFFDBE5AC00216DCFFFE3EB80000000100000000012120ABF6B9336DC002D768C001D6ADC0020177C00357C5C00140EAC004C37AC002AC9EC002AC9EC0034261FFFBA77FC002017600C002570BFFFD1333C001967EFFFB3C87C0022C40FFFBBCE4FFFE2953FFFFAA6CFFFE3EB9C0011620FFFD5361FFFE7EE7FFFE13EFFFFDE925FFFA2666C002AC9EFFFC92D5FFFF7FA2FFFEA9B1C00095C3FFFEA9B1C003C2BE |
| OK | Oklahoma | 01000000E6100000100000002D0000004E64E60297D140404461BECB0D8442402FBE688F17C059C0809BC58B859B57C00000000000000000000000000000000000000000000000000000000000000000E00FEAF8FF3E45420000000000507540010000000000000000000000000000000000000000000000000000004D010000000000010000000002349447FA08C866AA9BFFE22A3C9565FFF16000FFF8651E0000C012229000C01BA983FFE26A6B00C0002ACAC0012B85FFFD1333C0036D2BC000805EC001D6ADFFFFD536C004D8DEFFFD1333C0011620C0002ACAC0031797FFFEA9B1C00357C6C0002ACAC0030232FFFEBF16C00140EAC0016BB4C001EC11FFFF54D8C002ECCDFFFE541DC00140EAFFFFBFD2C0022C41FFFDE924C0012B85C0020177C002AC9EFFFED47AC00397F5FFFEBF16C001564FC00095C3C001C147FFFE13EEC00140EAC0036D2BC003D824FFFE3EB8C001162000C00357C6FFFEE9E0C0032CFBFFFEA9B1C0012B85C002570AC0030232C000C08DC0034261FFFF2A0EC0018119C0018119C00397F4FFFF2A0FC000D5F2AA9BC003ED88C001564FC00100BBFFFE7EE7C003ED88FFFE3EB8C0018119FFFE541DC005D99AC01AD392C000D5F2C010E1A6FFFD2898C0079AE20000FFAC8318 |
| TX | Texas | 01000000E610000010000000980000003688D68A36E3394002009C54AD404240E126A3CA30A95AC0BEF73768AF6157C00000000000000000000000000000000000000000000000000000000000000000AA5048649E0A6442000000000074924001000000000000000000000000000000000000000000000000000000950400000000000100000000022CF965F9EE753200FFEDDD7000FFFF6A3DFFDA0EFDAA9BFFF08A0EFFFFAA6DFFF0B4D80000FFC9D87EFFFE7EE7FFFF9508FFFE3EB9C001C147FFFF2A0EC00241A6FFFBFD13C002AC9EFFFE6982C003D823FFFCFDCEC002C203FFFEBF16C0029739FFFC7D71C0032CFCFFFD68C7C005EEFFFFFE3EB8C001ABE3FFFD3DFDC00095C2FFFCD304C0029739FFFC1278C0002ACAFFFBA77FC0029739FFFE13EEC003AD5AFFFDFE8AC001D6ACFFFC3D41C009718FFFFDA8F6C003C2BE9565C00281D4C0030232C00216DC9565C000EB56C005190DC001ABE3C0032CFCC002C203C000805EC003AD59C001967DC000C08DFFFED47BC0036D2AC0002ACAC00402EDFFFF3F74C006AF8CFFFCA83AC002570AFFFED47BC0030232FFFEA9B1C000C08CFFFE13EEC0034261FFFDD3C0C001EC12FFFCFDCEC000AB27FFFC52A7C0020177FFFEBF15C0018119FFFB6751C001967DFFFDE924C0034261FFFD7E2CC00241A6FFFD3DFDC000D5F1FFFDD3C0C0029739FFFFBFD1C001967EFFFD9390C001EC12FFFDD3C0FFFF9507FFFD7E2CC00140EAFFFDA8F5FFFFAA6CFFFD3DFDC002C203FFFB6751C0016BB4FFFE13EEC0022C41FFFF54D9C0032CFBFFFDFE89C002570BFFFFBFD1C0031797FFFD7E2CC0041852C0002ACAC004C379FFFF3F73C00397F5FFFDFE8AC001EC11C001967EC004AE15C0029739FFFEA9B1C002ECCDFFFF9507C001967EFFFE541DC00498AFFFFF9508C00140EAFFFE944CC00241A69565C0002ACAC0012B85C00402EDC000D5F1FFFF7FA2FFFE3EB8C0020177C0006AF9FFFF9507C001ABE3C0058406C00241A5C00543D7C0038290C002D768C0036D2AC002C203C0006AF9FFFF3F74C002C203C00140EAC002ECCDC003ED88FFFBFD13C0006AF9C0011620FFFE13EEC001C148C000AB28C003C2BEFFFF14A99565C00100BCC002C203FFFF54D8C000EB57C00402EDC009315FC003C2BFC00498B0C0030231C002D768C002D768FFFFBFD1C000EB57FFFE6982C0022C40C000C08CFFFF9508C0016BB4C001C147C0020177FFFD7E2CC000805EFFFED47BC002D768C00216DBC007F0759565C00281D4C0018119FFFEA9B1C0056EA1C00397F5C002ECCDFFFED47AC001967EC0011621C00216DCFFFF7FA2C001967EC0018119C003ED88C001967EC0034260FFFFBFD1C0062F2EFFFBD249C00357C6AA9BC00357C5FFFCA83AC00FA0BC00C0081B3F00C000AB28FFFDD3C0FFFF3F73FFFCFDCEC0016BB4FFFE6982C001ABE3FFFA2666C001C148FFFE7EE7C0018119FFFC1278FFFEA9B1FFFEFF459565FFFC1278C000D5F1FFFF2A0EFFFE7EE7FFFC680CC000D5F2FFFE7EE7FFFF3F73FFFCBD9FFFFDA8F6FFFCFDCEC001564FFFFED47BC0011620FFFCD30500FFFCA83AC001C148FFFEE9E0FFFC92D5FFFC27DCC001EC12FFFEBF16FFFF6A3DFFFE3EB9C00140EAFFFEA9B1C0012B86FFFC680BFFFDFE89FFFD5362C00216DCFFFED47BC000402EFFFDD3BFC001ABE3FFFEBF16C000AB28FFFD1333FFFE944CFFFE13EFC00140EAFFFEBF16FFFFD536FFFCFDCEC001564FFFFCA83AFFFFD536FFFCE869C002ECCDFFFEE9E0C0002ACAFFFB2722FFFF7FA2FFFE2953C002ECCDFFFC92D5FFFFD536FFFED47BC01D95950000FFE4567D |
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');
| AL | Alabama | POLYGON ((-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)) |
| AR | Arkansas | POLYGON ((-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)) |
| HI | Hawaii | MULTIPOLYGON (((-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))) |
| OK | Oklahoma | POLYGON ((-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)) |
| TX | Texas | POLYGON ((-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');
| AL | Alabama | 00000000030000000100000021C055D6FEB4A6655A40418026AA8EB463C0560CF9C62A1B5C40417F733226C3B9C0560650D2806AF4404172214AD362E9C0560F6DEB95E5B04040E5EB9E492BC3C0561E26BF8769EC403FE550225742DDC056193E78E1932D403E5E20CCFF21B3C05608C4F7EC3548403E518266772D5EC05606AA8EB46349403E7FC74FB549F9C05600B51372A38B403EAF7329C347E8C055FBCCCCCCCCCD403EA87075B3E143C055FA0C1FC8F323403E6958537E2C55C055E9EC5B078D93403E3F482BE8BC16C055E1290257C914403E47B1D0C80410C055D7B22D0E5604403E6D8D1526D8B1C055DC9A73B42CC3403E8295208E1501C055DA26527A2057403EACA548238540C055E8856A37AC3E403EDDB813016484C055E66B05319828403EFF5E84F09529C0555FD117B5286B403EFF5E84F09529C05540450268900C403F00C575C0767DC0554747B24638C9403F46E04C059210C05542B927D45A60403F8A2D40AAEAFAC05549085F4A1273403FD74ACAFF6D33C055436C9C0A8D2140400160E94EE393C05543C6583E857740401180B242070BC05538E89656EEFA404021A072D1AEB4C05540450268900C404029569F490603C0553D7724FA8B4C404035F50E347629C0554479D0A6762140404A49A133C1CEC0554BD63CB8173340406E0A84BE4042C0555B9C4545846E40410FFBB517A461C05566D3C361134040417E0C4156E265C055D6FEB4A6655A40418026AA8EB463 |
| AR | Arkansas | 00000000030000000100000025C0579E536D655E284042403CFB3311A5C057A77082491AFC4042403CFB3311A5C0579B858BC59B804041B2A05DD8F92BC0579F06E5CD4ED24040D19702E6644DC057985DF2239E6A4040C5AC0C62E4D1C0578BBF83382E444040CBFB480A5ACDC05782A26E5471714040C65F84CAD57CC05782A26E5471714040825F17BD8BE7C05766F16B11C6D24040825F17BD8BE7C056CA9BF9C62A1B40408044AE85B9E9C056C5B3B320535C404091CB6848BEB5C056C93508F648C74040AC6F36EF8056C056C39949E8815E4040B6F33CA31E7DC056CED0C80410304040C7C6759AB6D0C056C4A67E846A5D4040EF08B32CE896C056B9154434E3364041035D462C343BC056BCF0563ED0F640411162A5E785B5C056AF9E731D2E0E4041266AB14EC205C056AFF82B1F687B40412ED44DCA8E2EC056A466F0CFE154404135D701D9F4D3C056A574256BCA5340414F13DFB0D51FC0569ECB31C219EB404154AF9A8CDEA0C0569A3CA7503B8140416A6B1E5C0B99C05690125DD095AF4041743BB40B34E7C05693ED6FDA836E40417F733226C3B9C0568D9E3864CB5B404182F483CAFB3BC056874F00EF134840419963800218DFC056885C358AFC484041B83C21187E7CC0567C713F077CCC4041CD442C7FBACBC0567A56DA0168B64041E0E5471715C6C05670E004B7F5A54041E7E7FB267C6BC0566EC59FB1E18F4041FFBDE82D7B63C0569822424A276B4041FFBDE82D7B63C0568DF7F498C3B040421793D5347A5BC0568427674D1633404227001D5C3159C05689C3265ADD9C40423F8982CB20FBC0579E536D655E284042403CFB3311A5 |
| HI | Hawaii | 00000000060000000500000000030000000100000005C0639F6DDF00ABF7403535D28D002E23C063A7AAA19439DE403538A06E9FF0CBC063A9F1E6CD291340351B2EBE596C83C063A2957ABB880140351B2EBE596C83C0639F6DDF00ABF7403535D28D002E230000000003000000010000000BC06392CF722E1AC54035078DA3C21188C0639650C80410304034EB82E44B6E93C063943660E51D254034D24606748E47C0638EC77FF151E74034C87570C564F9C0638E40E5A35D6740349B97785729B2C0638C267E846A5D40349494D50EBAAEC06381A276B7ED414034A6CEFED63454C063800EA9E6EEB74034CB43526527A2C0638A65D18090B44034F3EC7863BEECC0638F213C254A3C4034E480303C07EEC06392CF722E1AC54035078DA3C2118800000000030000000100000007C063BE7359FF4FD74035B29BE09BB6AAC063C40F190D173F4035969131EC0B56C063C8170B49E01E4035952A411C2A02C063C3887EBF22C040354DA87A072D1BC063BCB2ACFB762D403553442C7FBACBC063BAF202107B7840357651A005C465C063BE7359FF4FD74035B29BE09BB6AA00000000030000000100000012C0637450917D6B654032F2C1A048E043C06370757D5A9EB24033226D7A56DE33C0636972CF95D4E940334413FD0D0679C0635F7562174C4D4033591C087442C8C06359D9A30984E4403385FA00E27E0FC06362F6BA0620AB4033BCA88EFFE2A4C06362F6BA0620AB4033DE4F00EF1348C06369191361DC944034059146E4C0DFC06373167EC7863C40342035158B827FC0637C06B7AA25D9403444A9691A75CDC0637CBA30121683403425D0C8041030C0637A72EAD9274E4034085F28848388C0637D9A869403574033DB811F4F50A0C06381FC32EBE5974033BB419E30014FC0637C6073DE1E2E4033591C087442C8C0637D6DA87A072D40331F9F98B71B8AC0637C3395C42203403309309C7FFDE7C0637450917D6B654032F2C1A048E04300000000030000000100000008C063EEFF88765BA740363A9CCB7D4174C063F73C48F10A99403626FBA01EEED8C063F8765BA6EFC34035FCEB8950763AC063EE4C122749F14035DF79D909F1F1C063EA9DDC1E79684035F74FC610F0E9C063E990A782906840361D2B1B36BD2BC063EB5154866A12403637CEE9DD7ECBC063EEFF88765BA740363A9CCB7D4174 |
| OK | Oklahoma | 0000000003000000010000002DC059059CF9A06A6E404280089E34330DC05982C01605250340427F5525CC4263C059C0178F68BE2F404280089E34330DC059C0178F68BE2F4042403CFB3311A5C05974073DE1E2DE4042403CFB3311A5C05900013A92A3054042403CFB3311A5C05900013A92A305404148112BA16E7AC058FB18F3ECCC46404149781C714FCEC058ECB9D7FD8277404130EEB702602CC058E503AFB7E9104041352389720429C058D0AF1CB89D6B404133BC98A222D5C058CC209246BF0140411B3333333333C058BF286727568640411C9A24031487C058B123076C050C40411162A5E785B5C058A484988094E6404112C996B7670AC0589F4295A6C5D2404108459103C8E2C0589732B55EF1FE404114308787485EC0588AEE02A77A2D40410E94C447C30DC05885ABFFCDAB194041008F648C7193C0587C8EE6B8305D4040FE7503B81B65C05877A6A012599F4040ECEE49F51698C0586C6F21F6CACD4040FDC18B502ABBC0585D5C8D9F90534040F3F0F5A1016DC05857C0CE91C8EA4040E96CEFED6345C058506466B1E5C04040EE553AC4F7ECC0584B2263D816AD4040DE3571D1D473C0583B029AE4F3344040FAF3A9B06812C0583674107314CA4040EC3AD18D25EEC058286EB0B7C3504040EC3AD18D25EEC0581B1CCD9620684040E31DBCA9691AC058163486F049A94040D7E63E8DDA48C05809961804D9834040EB8759253543C057FBEA747D805E4040F1D694CCAB3FC057F59B3D07C84B4040EAD3E0BD4499C057E688ACE24BBA4040F7724FA8B4BFC057E30752DA98674040F06FA3FCC9EAC057D28DD1E53A824040EFBC2B94D940C057CE5903A7546D4040FAF3A9B06812C057BDDF82B1F6874040EE553AC4F7ECC057B7904B3C3E744040DF9C62A1B5C8C0579F06E5CD4ED24040D19702E6644DC0579B858BC59B804041B2A05DD8F92BC057A77082491AFC4042403CFB3311A5C057A77082491AFC404280089E34330DC059059CF9A06A6E404280089E34330D |
| TX | Texas | 00000000030000000100000098C05974073DE1E2DE4042403CFB3311A5C059C0178F68BE2F4042403CFB3311A5C059C28BB4D488834042403CFB3311A5C059C2E5710880D8404101F6555C52E7C059C44C5DA6A44440408044AE85B9E9C059C44C5DA6A444403FFFF3F0FE047DC05AA77021D10B20403FFFF3F0FE047DC05AA930CAA326E1403FE6B713272431C05AA1D462C343B7403FC94573A79789C05A985D8D79D0A6403FBB400B88CA3EC05A8D260F5E41D4403F77F316E37154C05A7D064A9CDC44403F5D4F483CAFB3C05A7175104D551D403F2AD58C8EEF1BC05A66974E65BEA0403F15CD8127B2CCC05A594567125DD0403EDAEA3161A1DBC05A4062456F75D9403EAF7329C347E8C05A395F9591CD1C403E9201797CC3A0C05A36EB7457C0B1403E63BC903EA704C05A2C0DB2702A34403E2E74F2F123C4C05A2B5A3A08398A403DEC8EEF1BAC2EC05A207C7820A30D403DA3A63736CDF2C05A11102795703F403D8366A5508701C05A095A038194C0403D61C03361565CC059E1BE09BB6AA4403D22A80064A9CDC059D1F8012DFD69403CFB65CB35F3D7C059C773FB7A5F41403CFCCCBC05D52CC059BEB09E98DCDB403D2F4677B395C4C059BAD58C8EEF1B403D30AD68837718C059B3D2DCB1465E403D863486F049A9C059A841A261BF37403DBB7C243DCCEAC05998D556084A51403DC3E5C91D14E3C05995ADB8348F53403DDE8986FCDEE3C059874EA077036C403DCAE86C6583E8C059767B634DAD32403DCDB64E054690C0595A70A3D70A3D403DC117E77D523BC05950A0168B5CC0403D890268900C52C0594401A79FEC9A403D75614DF8B157C05940DA0DFDEF84403D5EF251C193B3C059332E6A76965F403D3EB2BFDB4CC2C0592B1E85FD04A3403D1A3E6C4C5975C0592850A88EFFE2403CE7C4B09E98DCC0591FE707E175D1403CAA137F38C543C0591997D06BBDBE403C950B630A9153C05912EEE0F3CB3E403C47EDE97D06BBC05905433D6C7219403C24E075F6FD22C058FBCC6822FF08403BFAD05F288483C058F84B124D099E403BCC8B75EA67E8C058ED6D50657321403BA817225B749BC058E6C45CBBC2B9403BA3E24FEBD09EC058DEB4784230FD403B7B39192641B3C058E07525460AA6403B56C4C5974E65C058DB33226C3B92403B2CB4AEC8D5C7C058DC9A133C1CE6403B057268D32830C058D108D8EC95C0403AD72D7F950B95C058CB135DAAD602403A8A10060780FDC058C1F644955B46403A69D074213A0CC058B4A46173B85E403A5E98FE69270BC058AAD3CFF64CF9403A3CF27BB2FEC5C0589DDBA4D6E47D403A38BDA9435AC8C0588CAEAB7995EE403A0EAD9274E22AC05878B3D4AE429E403A117B7414A4D2C05869A140570824403A04DCFCC5B8DCC0586191600F34504039E3368AD68837C0584DF041461B6D4039FDDA597D49D7C058538C0053E2D6403A2951611BA3CAC058554CAD57BC7F403A5A642BF9830EC0585C4F5D35653C403A7507FAA044AEC0585E10060780FD403AC225742DCF46C0586405814940BB403AD72D7F950B95C05863ABC5154866403AFD08D4BAD7D7C0585EC37E6F71A8403AFFD6B65A9A80C0585B4228997C3D403B4323AAFFF36AC058629E94AB1D4F403B3ABA0620AB71C05860DDE7A743A6403B5C6088D6D3B6C05859DB37C99AE9403B555DD4C76D11C058506466B1E5C0403BB1E7A743A648C05841AB8E8EA39C403C0A3CA7503B81C058334C76D117B5403C39E8815E3971C058318BC9CD3E0C403C682D6A9C560CC05825FA8F7DB6E5403C5B8F041461B6C05819B5DCC63F14403C70970F7B9E06C0582A8919EF954E403CB27D1351159CC05825FA8F7DB6E5403CB97FC7607C41C0581E9E236C15D3403C9940357A3550C0580ED81ADEA897403CA477BBF93FF2C0580E7E5EAAB042403C950B630A9153C05802ED245B291B403CA5DEACC92146C057FF120E1F7D73403C9AA7264A16A4C057D8834D26FA3F403CDDF41AEF6F8FC057C53BEA91D9B1403D1D0C4DEC1C1DC057B950F40E5A36403D4F85F8D2E514C057BA5E28AA4335403D7F31D2E0E304C057C1071C53F39D403D8E9E2BCF91A3C057BDDF82B1F687403DB3127F5E84F0C057B7EA077036C9403DAC0FDC1615ECC057AF8066C2ACB8403DC9817B95A294C057AD65FD8ADABA403D9F7164C729F5C057A17B07075B3E403D8BD04A2FCEFAC057802E48E8A71E403DAEDDACEEE0F3C05775AA433508F6403DB0449DBEC248C0577B460242D05F403DC9817B95A294C0576C336DEB95E5403E24A45D41FA76C057711BB8C32A8C403E55B7281FD9BAC0576C8D2A1F8E3A403E705AF6C69B5AC0576EA793576039403E93686A4CA4F4C05768585BE1A826403EAE0C38F36694C05761AF6837F7BE403EEFF23CC8DE2AC05762BC9CD3E0BD403F26A0BA1F4B1EC0577443526527A2403F8E62131A8EF7C057749D0E991FF7403FC6779207D4E0C05782A26E547171403FFE8D002E2329C05782A26E5471714040825F17BD8BE7C05782A26E5471714040C65F84CAD57CC0578BBF83382E444040CBFB480A5ACDC057985DF2239E6A4040C5AC0C62E4D1C0579F06E5CD4ED24040D19702E6644DC057B7904B3C3E744040DF9C62A1B5C8C057BDDF82B1F6874040EE553AC4F7ECC057CE5903A7546D4040FAF3A9B06812C057D28DD1E53A824040EFBC2B94D940C057E30752DA98674040F06FA3FCC9EAC057E688ACE24BBA4040F7724FA8B4BFC057F59B3D07C84B4040EAD3E0BD4499C057FBEA747D805E4040F1D694CCAB3FC05809961804D9834040EB8759253543C058163486F049A94040D7E63E8DDA48C0581B1CCD9620684040E31DBCA9691AC058286EB0B7C3504040EC3AD18D25EEC0583674107314CA4040EC3AD18D25EEC0583B029AE4F3344040FAF3A9B06812C0584B2263D816AD4040DE3571D1D473C058506466B1E5C04040EE553AC4F7ECC05857C0CE91C8EA4040E96CEFED6345C0585D5C8D9F90534040F3F0F5A1016DC0586C6F21F6CACD4040FDC18B502ABBC05877A6A012599F4040ECEE49F51698C0587C8EE6B8305D4040FE7503B81B65C05885ABFFCDAB194041008F648C7193C0588AEE02A77A2D40410E94C447C30DC0589732B55EF1FE404114308787485EC0589F4295A6C5D2404108459103C8E2C058A484988094E6404112C996B7670AC058B123076C050C40411162A5E785B5C058BF286727568640411C9A24031487C058CC209246BF0140411B3333333333C058D0AF1CB89D6B404133BC98A222D5C058E503AFB7E9104041352389720429C058ECB9D7FD8277404130EEB702602CC058FB18F3ECCC46404149781C714FCEC05900013A92A305404148112BA16E7AC05900013A92A3054042403CFB3311A5C05974073DE1E2DE4042403CFB3311A5 |
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');
| AL | Alabama | 1.3409800288446873E11 | 134098 |
| AR | Arkansas | 1.3838751120399905E11 | 138387 |
| HI | Hawaii | 1.4748657954505682E10 | 14748 |
| OK | Oklahoma | 1.8250255202012402E11 | 182502 |
| TX | Texas | 6.886199875225208E11 | 688619 |
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');
| AL | Alabama | 134098 | 135822 |
| OK | Oklahoma | 182502 | 184806 |
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 ;
| HELSINKI | FIN |
| TEGUCIGALPA | HND |
| NAIROBI | KEN |
| GUADALAJARA | MEX |
| COPENHAGEN | DNK |
| LYON | FRA |
| NANTES | FRA |
| OSLO | NOR |
| ROCHESTER | USA |
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') ;
| LYON | FRA | 1 | 0 |
| ROCHESTER | FRA | 0 | 0 |
| LYON | USA | 0 | 0 |
| ROCHESTER | USA | 1 | 0 |
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') ;
| Michigan | Illinois | Etats éloignés |
| Michigan | Indiana | Etats Voisins |
| Michigan | Minnesota | Etats éloignés |
| Michigan | Ohio | Etats Voisins |
| Michigan | Pennsylvania | Etats éloignés |
| Michigan | Wisconsin | Etats 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');
| Michigan | Illinois | POINT EMPTY | Etats éloignés |
| Michigan | Indiana | MULTILINESTRING ((-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 |
| Michigan | Minnesota | POINT EMPTY | Etats éloignés |
| Michigan | Ohio | LINESTRING (-83.45423799999999 41.732338999999996, -84.807042 41.694001) | Etats Voisins |
| Michigan | Pennsylvania | POINT EMPTY | Etats éloignés |
| Michigan | Wisconsin | MULTILINESTRING ((-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');
| Michigan | Illinois | 58.493941547601004 |
| Michigan | Indiana | 0.0 |
| Michigan | Minnesota | 33.60195301382611 |
| Michigan | Ohio | 0.0 |
| Michigan | Pennsylvania | 179.1488383130458 |
| Michigan | Wisconsin | 0.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à !