SQL Server 2008 : Ma démonstration des données spatiales en plénière des TechDays le 12 février... (1/4)

Pour ceux qui étaient là et qui n'ont pas pu tout voir (cela va très vite), et pour ceux (encore plus nombreux assurément) qui n'étaient pas là, voici tous les détails.

La première partie de cette démonstration a été réalisée avec l'outil tiers Sql Spatial Query Visualizer  ( dispnible ici : https://www.sharpgis.net/)

1ère requête : une variable de type géographique

C'est tout d'abord beaucoup de chiffres (en fait les coordonnées en latitude et longitude des points formant la frontière d'un pays)...

Puis on peut faire la requête sur cette variable :

L'onglet "map" donne accès à une représentation plus visuelle du polygone qui ici représente l'Argentine. On notera à droite les informations associées à cet objet géographique qui correspondent aux  autres colonnes de la table. 

Voici la requête complète :

DECLARE @a geography;
SET @a = geography::STGeomFromText('MULTIPOLYGON (((-54.500228881835938 -68.632919311523438, -54.856121063232422 -68.515007019042969, -55.044719696044922 -66.551681518554688, -54.929168701171875 -65.353912353515625, -54.646949768066406 -65.142227172851562, -54.67279052734375 -65.696403503417969, -54.456680297851562 -66.443893432617188, -53.9102783203125 -67.573623657226562, -53.2861213684082 -68.180007934570312, -53.229450225830078 -68.543617248535156, -52.994178771972656 -68.327507019042969, -53.103069305419922 -68.2247314453125, -52.643619537353516 -68.618331909179688, -54.500228881835938 -68.632919311523438)), ((-54.905281066894531 -64.686683654785156, -54.719451904296875 -63.826950073242188, -54.719730377197266 -64.57000732421875, -54.905281066894531 -64.686683654785156)), ((-22.114450454711914 -66.345550537109375, -22.227500915527344 -66.7369384765625, -22.821559906005859 -67.183670043945312, -23.002780914306641 -67.001106262207031, -24.023899078369141 -67.339996337890625, -24.606670379638672 -68.491096496582031, -24.805280685424805 -68.571670532226562, -25.129169464111328 -68.354446411132812, -25.354999542236328 -68.5755615234375, -26.17388916015625 -68.404167175292969, -26.520559310913086 -68.579727172851562, -27.044729232788086 -68.320281982421875, -27.120559692382812 -68.811111450195312, -27.965839385986328 -69.186111450195312, -28.4052791595459 -69.656951904296875, -29.108060836791992 -69.800277709960938, -29.354999542236328 -70.035003662109375, -30.088619232177734 -69.967498779296875, -30.158889770507812 -69.827499389648438, -30.37639045715332 -69.944442749023438, -30.370840072631836 -70.16583251953125, -31.039169311523438 -70.258056640625, -31.188060760498047 -70.533332824707031, -31.566669464111328 -70.58721923828125, -31.968889236450195 -70.231948852539062, -32.051670074462891 -70.379989624023438, -32.895561218261719 -69.992767333984375, -33.172508239746094 -70.098892211914062, -33.286128997802734 -69.79833984375, -34.229721069335938 -69.81304931640625, -34.2983512878418 -70.05499267578125, -35.142501831054688 -70.361656188964844, -35.254459381103516 -70.5694580078125, -35.340839385986328 -70.4233169555664, -36.046680450439453 -70.369453430175781, -36.409160614013672 -70.7005615234375, -36.486110687255859 -71.03778076171875, -36.8488883972168 -71.186943054199219, -37.104171752929688 -71.079452514648438, -37.713340759277344 -71.1763916015625, -38.551670074462891 -70.825843811035156, -38.916110992431641 -71.397781372070312, -39.593059539794922 -71.488327026367188, -39.6372184753418 -71.710563659667969, -39.906391143798828 -71.605827331542969, -40.168338775634766 -71.807502746582031, -40.303340911865234 -71.664436340332031, -40.732769012451172 -71.9505615234375, -40.953899383544922 -71.837509155273438, -41.626388549804688 -71.918060302734375, -42.103900909423828 -71.735000610351562, -42.281940460205078 -72.131668090820312, -42.477500915527344 -72.025833129882812, -42.991390228271484 -72.139717102050781, -43.188060760498047 -71.732772827148438, -43.378059387207031 -71.922500610351562, -43.923160552978516 -71.6488265991211, -44.105289459228516 -71.852493286132812, -44.365291595458984 -71.8538818359375, -44.528060913085938 -71.110282897949219, -44.794460296630859 -71.2711181640625, -44.774730682373047 -72.081123352050781, -44.897789001464844 -72.071121215820312, -44.971389770507812 -71.591667175292969, -45.305839538574219 -71.296951293945312, -45.725559234619141 -71.791946411132812, -45.9747200012207 -71.601936340332031, -46.140289306640625 -71.897781372070312, -46.679180145263672 -71.669448852539062, -46.806110382080078 -71.933319091796875, -47.229728698730469 -71.877777099609375, -47.464450836181641 -72.357772827148438, -47.909160614013672 -72.5352783203125, -48.075569152832031 -72.32305908203125, -48.341941833496094 -72.287216186523438, -48.481410980224609 -72.6040267944336, -48.783618927001953 -72.553047180175781, -49.0655517578125 -73.0755615234375, -49.303619384765625 -73.134170532226562, -49.3113899230957 -73.4647216796875, -49.531940460205078 -73.581680297851562, -49.782501220703125 -73.444442749023438, -50.144718170166016 -73.528610229492188, -50.324131011962891 -73.277801513671875, -50.744720458984375 -73.174156188964844, -50.649730682373047 -72.294158935546875, -51.017230987548828 -72.267227172851562, -51.096389770507812 -72.391952514648438, -51.232498168945312 -72.247772216796875, -51.546119689941406 -72.438316345214844, -51.996799468994141 -71.910202026367188, -51.996391296386719 -69.998611450195312, -52.336940765380859 -68.383621215820312, -51.633060455322266 -68.979171752929688, -51.625839233398438 -69.6138916015625, -51.578060150146484 -68.9727783203125, -50.966388702392578 -69.188056945800781, -50.596389770507812 -69.103912353515625, -50.308891296386719 -68.827507019042969, -50.158351898193359 -68.3739013671875, -49.903068542480469 -68.591667175292969, -50.122219085693359 -68.243362426757812, -49.920841217041016 -67.805557250976562, -49.264720916748047 -67.603057861328125, -49.331951141357422 -67.793060302734375, -48.956668853759766 -67.477226257324219, -47.930290222167969 -65.789459228515625, -47.785011291503906 -65.96331787109375, -47.541950225830078 -65.7569580078125, -47.198341369628906 -65.772506713867188, -47.031951904296875 -66.735000610351562, -46.466949462890625 -67.498062133789062, -46.003898620605469 -67.586669921875, -45.281391143798828 -66.990837097167969, -44.9627799987793 -66.161956787109375, -45.016399383544922 -65.604736328125, -44.900001525878906 -65.531417846679688, -44.776119232177734 -65.71917724609375, -44.346950531005859 -65.2327880859375, -43.667789459228516 -65.330291748046875, -43.304458618164062 -65.0283432006836, -42.991958618164062 -64.2952880859375, -42.768890380859375 -65.013633728027344, -42.675010681152344 -64.967216491699219, -42.506961822509766 -64.4586181640625, -42.890289306640625 -64.087226867675781, -42.761959075927734 -63.6261100769043, -42.335289001464844 -63.586940765380859, -42.095291137695312 -63.735561370849609, -42.233608245849609 -64.329452514648438, -42.266399383544922 -64.0836181640625, -42.4344596862793 -64.119171142578125, -42.437229156494141 -64.585563659667969, -42.268329620361328 -64.459457397460938, -42.028900146484375 -65.050003051757812, -40.8477897644043 -65.134452819824219, -40.729450225830078 -64.7800064086914, -40.814739227294922 -64.9122314453125, -41.164730072021484 -63.396389007568359, -40.8880615234375 -62.3638916015625, -40.636669158935547 -62.185009002685547, -40.678340911865234 -62.3266716003418, -40.560009002685547 -62.2599983215332, -40.311111450195312 -62.4888916015625, -39.847240447998047 -62.259170532226562, -39.844459533691406 -62.127780914306641, -39.465839385986328 -62.051948547363281, -39.312229156494141 -62.281391143798828, -39.378059387207031 -62.020000457763672, -39.172500610351562 -62.348060607910156, -38.791400909423828 -62.365280151367188, -38.986118316650391 -61.818061828613281, -38.9969482421875 -61.124729156494141, -38.5341682434082 -58.4988899230957, -38.096950531005859 -57.53778076171875, -37.835010528564453 -57.487499237060547, -36.923618316650391 -56.678340911865234, -36.326961517333984 -56.736110687255859, -36.2883415222168 -57.100009918212891, -35.930278778076172 -57.3849983215332, -35.416950225830078 -57.124729156494141, -35.177780151367188 -57.313060760498047, -34.653068542480469 -58.331668853759766, -34.296119689941406 -58.565559387207031, -34.188060760498047 -58.377498626708984, -33.753898620605469 -58.540840148925781, -33.101398468017578 -58.430561065673828, -33.075290679931641 -58.161949157714844, -31.854169845581055 -58.178890228271484, -31.78639030456543 -58.0372200012207, -31.4566707611084 -58.07611083984375, -30.876949310302734 -57.795841217041016, -30.504449844360352 -57.874721527099609, -28.380830764770508 -55.9002799987793, -28.408889770507812 -55.692501068115234, -28.219999313354492 -55.756671905517578, -27.853889465332031 -55.029170989990234, -27.5302791595459 -54.806110382080078, -27.171689987182617 -53.838901519775391, -26.889450073242188 -53.6986083984375, -26.199729919433594 -53.6602783203125, -25.629169464111328 -53.894718170166016, -25.49860954284668 -54.095829010009766, -25.684999465942383 -54.426109313964844, -25.574869155883789 -54.596111297607422, -26.659450531005859 -54.803611755371094, -26.944999694824219 -55.1261100769043, -26.971670150756836 -55.396110534667969, -27.434490203857422 -55.785381317138672, -27.312229156494141 -56.148609161376953, -27.58612060546875 -56.392230987548828, -27.44805908203125 -56.585830688476562, -27.294179916381836 -58.6026496887207, -27.137229919433594 -58.6361198425293, -26.658620834350586 -58.185268402099609, -26.27277946472168 -58.1722297668457, -25.57305908203125 -57.575569152832031, -25.087779998779297 -57.870548248291016, -24.726390838623047 -58.88555908203125, -24.009729385375977 -60.038059234619141, -23.806110382080078 -61.015010833740234, -22.994720458984375 -61.999168395996094, -21.996389389038086 -62.809169769287109, -22.000839233398438 -63.941108703613281, -22.873619079589844 -64.32501220703125, -22.171119689941406 -64.670272827148438, -22.102230072021484 -65.76806640625, -21.787509918212891 -66.2316665649414, -22.114450454711914 -66.345550537109375)))',4326);

-- select @a

SELECT @a AS Forme, @a.STNumPoints() AS 'Nb de points',
@a.STLength()/1000 AS 'Longueur de la frontière (km)',
@a.STArea()/1000000 AS 'Surface (km²)',
'Quel est ce pays ?' AS Nom, 'Green' AS FillColor, 'Blue' AS LineColor;