SQL BDD

8/15/2019 SQL BDD

1/80

5 - SQL - Bases dedonnées

6 février 2015

version 2.1 A

8/15/2019 SQL BDD

2/80

Table des

matières

Ob jectif s 5

I - Notions SQL 7

A. Introduction.................................................................................................7

B. La sélection..................................................................................................9

. Les o!érateurs de com!araison et les o!érateurs lo"i#ues................................10

$. Les t%!es de données et les fonctions............................................................12

&. 'ri et a"ré"ation..........................................................................................15

(. &)tensions s!atiales.....................................................................................17

G. Présentation de $B*ana"er.........................................................................20

+. &)ercice 6 , sélections SQL avec $B*ana"er..................................................26

I. Les -ointures attriutaires.............................................................................28

/. Les -ointures s!atiales..................................................................................31

. &)ercice , e#u3tes et fonctions s!atiales....................................................32

II - Spatialite 35

A. Gérer les ases et les tales.........................................................................35

B. L4assistant de re#u3te SQL de Qs!atiaLite......................................................38

. éaliser des -ointures avec Qs!atialite...........................................................44

$. Inde)ation et o!timisation............................................................................45

&. &)ercice , re#u3tes SQL avec Qs!atiaLite....................................................49

III - PostGIS 53

Maîtrise d'oura!e " M#$$# - M#TL - M%%& Maîtrise d'(ure " #NT# %i) - #NSG Li*en*e ouerte #T%L%B

8/15/2019 SQL BDD

3/80

A. PostGIS , Im!orter des données...................................................................53

B. 7tiliser PostGIS de!uis QGIS........................................................................65

. 7tilisation de $B*ana"er avec PostGIS..........................................................68

$. Inde)ation s!atiale sous PostGIS..................................................................72

&. &)ercice 8 , PostGIS....................................................................................73

Solution des e)er*i*es 75


99

:otions SQL

Maîtrise d'oura!e " M#$$# - M#TL - M%%& Maîtrise d'(ure "

:otions SQL

8/15/2019 SQL BDD

4/80

+b,e*tis

e module va vous !ermettre de ,

onna;tre les rudiments du SQL

Savoir utiliser et "érer des tales dans une ase S!atialite

Savoir utiliser et "érer des tales dans une ase Post"is Savoir ouvrir une tale avec une liaison

8/15/2019 SQL BDD

5/80

I -Notions SQL I

Introduction

La sélection 8

Les o!érateurs de com!araison et les o!érateurs lo"i#ues 10

Les t%!es de données et les fonctions 12

'ri et a"ré"ation 15

&)tensions s!atiales 1

Présentation de $B*ana"er 20

&)ercice 6 , sélections SQL avec $B*ana"er 26

Les -ointures attriutaires 2

Les -ointures s!atiales 1

&)ercice , e#u3tes et fonctions s!atiales 2

Objectifs$é*ourir le lan!a!e de re.u/te SQL pour répondre 0 desbesoins d'anal1se spatiale dans QGIS2

%2 Introdu*tion

Introduction aux SGBDR

7n S%st=me de Gestion de Base de $onnées >SGB$? est un lo"iciel !ermettant destoc@er de la donnée dans une ase de données en "arantissant la #ualité la!érennité et la confidentialité des informations. La com!le)ité des o!érations detraitement des données ne nécessite !as d43tre totalement connue !ar lesutilisateurs. e module ne vise donc !as former des s!écialistes des SGB$. LesSGBD1 #ue nous utiliserons sont asés sur un mod=le de données relationnel>SGB$?. $ans ce mod=le la ase de données est com!osée d4un ensemle detables et cCa#ue li"ne d4une tale est un enre!istrement.

1 D Ctt!,EEfr.Fi@i!edia.or"EFi@iES%stAmeHdeH"estionHdeHaseHdeHdonnA8es'%!olo"ie


http://fr.wikipedia.org/wiki/Syst%C3%A8me_de_gestion_de_base_de_donn%C3%A9es#Typologiehttp://fr.wikipedia.org/wiki/Syst%C3%A8me_de_gestion_de_base_de_donn%C3%A9es#Typologie

8/15/2019 SQL BDD

6/80

Extrait du modèle relationnel du standard COVADIS de l'Éolien terrestre (formalismeUM!

Au sein du *inist=re de l4&colo"ie la COVADIS2 >ommission de Jalidation des$onnées !our l4Information S!atialisée? a !our mission de !roduire des mod=les dedonnées >"éostandards? !arta"és !ar tous.La conce!tion et la "estion d4une ase de données relationnelle sont un domaine ensoi #ui est Cors du !érim=tre de cette formation. Les !ersonnes désireuses d4ensavoir !lus sont invitées suivre le sta"e 4Con"e#oir et stru"turer des $ases dedonn%es &%o&rai)ues4 >*inist=re?.$ans cette formation nous n4e)!loiterons #ue des mod=les tr=s sim!les. :ousconsidérerons le SGB$ Spatialite #ui est installé avec QGIS et #ui est un SGB$ tr=slé"er. Il !eut 3tre #ualifié de 4ureauti#ue4 dans le sens oK il est !lutt orienté versun usa"e !ersonnel sur son !oste de travail. :ous traiterons é"alement du SGB$PostGIS #ui est eaucou! !lus com!let et avancé mais #ui doit !lutt 3treenvisa"é !our ce #ui est du !arta"e de données comme un com!osant du s%st=med4information d4un service avec un administrateur dédié. 7ne utilisation !ersonnellede ce SGB$ est ce!endant envisa"eale !our certains esoins d4anal%se ou de!roduction de données c4est dans ce cas é"alement un tr=s on outil.Pour suivre le module sur PostGIS il est nécessaire d4avoir acc=s une ase installée.L4or"anisateur de la formation !ourra le cas écCéant fournir les !aram=tres deconne)ion une ase.

2 D Ctt!,EEFFF.develo!!ementDdurale."ouv.frELaDstandardisationDdesDdonneesD!ar.Ctml D Ctt!,EE"eoinfo.metier.i2E!resentationDdeDlaDformationDa98.Ctml


:otions SQL


:otions SQL

http://www.developpement-durable.gouv.fr/La-standardisation-des-donnees-par.htmlhttp://geoinfo.metier.i2/presentation-de-la-formation-a349.htmlhttp://geoinfo.metier.i2/presentation-de-la-formation-a349.htmlhttp://www.developpement-durable.gouv.fr/La-standardisation-des-donnees-par.htmlhttp://geoinfo.metier.i2/presentation-de-la-formation-a349.htmlhttp://geoinfo.metier.i2/presentation-de-la-formation-a349.html

8/15/2019 SQL BDD

7/80

SQL

SQL >Structured Quer% Lan"ua"e #ui si"nifie lan"a"e de re#u3tes structuré? est unlan"a"e destiné la mani!ulation des ases de données au sein d4un SGB$.SQL est com!osé de trois sousDensemles ,

Le Lan"a"e de $éfinition de $onnées >L$$? #ui !ermet de créer et su!!rimerdes o-ets dans la ase de données et #ue nous n4aorderons !ase)!licitement dans le cadre de cette formation.

Le Lan"a"e de ontrle de $onnées >L$? !our "érer les droits sur les o-etset #ue nous n4aorderons !as non !lus.

Le Lan"a"e de *ani!ulation de $onnées >L*$? !our la recCercCe l4insertionla mise -our et la su!!ression de données et #ui sera le seul aordé!artiellement.

A noter #ue le SQL est utilisé é"alement dans QGIS dans les re#u3tes de filtra"es surles tales.

B2 La séle*tion

Syntax !"n"ra#

La re#u3te de sélection est la ase de la recCercCe de données en SQL.7ne re#u3te SQL res!ecte une s%nta)e de t%!e ,

S#L#3T >liste des attriuts? &4+M >liste des tales? 6#4# >onditions?La !artie S&L&' indi#ue le sousDensemle des attriuts >les colonnes? #ui doiventa!!ara;tre dans la ré!onse.La !artie (les tales? #ui sont utilisées dans la re#u3te. Lesattriuts de la clause S&L&' doivent a!!artenir au) tales listées dans la clause( 1000

sélectionne les enre"istrements de la tale c4est le sens de N? de la tale

8/15/2019 SQL BDD

8/80

r%sultats de la s%le"tion sur la ta$le COMMU*E

Il est !ossile de donner un nom d4alias au) attriuts en sortie avec le mot clef AS.

&) , SELECT nom_comm AS COMMUE , insee_comm AS !SEE, population FROMcommune

on !eut é"alement écrire directement >on omet le AS? ,SELECT nom_comm COMMUE , insee_comm !SEE, population FROM commune

Utilisation des alias de nom de "olonne

32 Les opérateurs de *omparaison et les opérateurslo!i.ues

L$ o%"ratur$ d co&%arai$on

La clause M+&& est définie !ar une condition #ui s4e)!rime l4aide d4o!érateurs decom!araison et d4o!érateurs lo"i#ues.Les o!érateurs de com!araison sont ,A O BA B >différent?A BA BA O B >inférieur ou é"al?

A O B >su!érieur ou é"al?A B&'M&&: B A:$ >com!ris entre B et ?


1010

:otions SQL


:otions SQL

8/15/2019 SQL BDD

9/80

A I: >B1 B2...? liste de valeurs ,e) , SELECT nom_comm, insee_comm, population FROM commune WHERE statut

!"#Commune simple#, #C$e%&lieu 'e canton#(A LI& 4cCa;ne4!ermet d4insérer des caract=res -o@ers dans l4o!ération de com!araison dési"nant0 !lusieurs caract=res #uelcon#ues H dési"nant un seul caract=re.&) , SELECT * FROM commune WHERE nom_comm L!)E #+# sélectionne toutes lescommunes dont le nom commence !ar ASELECT * FROM commune WHERE nom_comm L!)E #+S!T+# sélectionne toutes lescommunes dont le nom contient la cCa;ne 4SAI:'4

'ttntion ( Sn$i)i#it" * #a ca$$

SQL est sensile la casse >ma-uscule E minuscule? !our les constantes ainsi:

8/15/2019 SQL BDD

10/80

sim!le ou cCefDlieu de canton.Bien penser dans l'e)emple *i-dessus .ue le +4 lie deu) *onditions2 7ne

*ondition *ontient né*essairement un des opérateurs de *omparaison2 %insion ne peut é*rire

SELECT * FROM commune WHERE statut = 'Commune simple' OR 'Chef-lieu e

canton4

%N$ , !our sé!arer deu) conditions #ui doivent 3tre vérifiées simultanément.&) , SELECT * FROM commune WHERE statut - #Sous&p./%ectu.e# population > 10000

seules les sousD!réfectures de !lus de 10 000 Caitants sont sélectionnées.N+T , !ermet d4inverser une condition.&) , SELECT * %.om commune WHERE OT "statut - #Commune simple# ORstatut - #C$e%&lieu 'e canton#(

sélectionne les communes #ui ne sont ni commune sim!le ni cCef lieu de canton.

$2 Les t1pes de données et les on*tions

L$ ty%$ d donn"$

Les !rinci!au) t%!es de données en SQL sont ,+AA'& >ou 36%4 ? , valeur al!Ca de lon"ueur fi)e.+AA'& JAI:G >ou 8%436%4 ? , valeur al!Ca de lon"ueur ma)imale fi)ée.T#9T , suite lon"ue de caract=res >sans limite de taille?.N7M#4I3 >ou $&I*AL ou $&? , décimalI:'&G& >ou INT? , entier lon"4#%L , réel vir"ule flottante dont la re!résentation est inaire.B++L#%N >ou L!ointsli"nes...? #ue nous verrons !lus tard

L$ onction$

SQL !ro!ose des on*tions dont on trouvera une descri!tion !ar e)em!le i"i 6

&)aminons en #uel#ues unes...&on*tions de transt1pa!e"

*ast >e)!r as t%!e? , &st la fonction standard SQL #ui !ermet de convertir un t%!e enun autre.

9 D Ctt!,EEfr.Fi@i!edia.or"EFi@iESQLite'%!esHdeHdonn..A8es5 D Ctt!,EEdocs.!ost"res#l.frE8.Edatat%!e.Ctml6 D Ctt!,EEs#l!ro.develo!!eT.comEcoursEs#laTEfonctionsE


1212

:otions SQL


:otions SQL

http://fr.wikipedia.org/wiki/SQLite#Types_de_donn.C3.A9eshttp://docs.postgresql.fr/9.3/datatype.htmlhttp://sqlpro.developpez.com/cours/sqlaz/fonctions/http://fr.wikipedia.org/wiki/SQLite#Types_de_donn.C3.A9eshttp://docs.postgresql.fr/9.3/datatype.htmlhttp://sqlpro.developpez.com/cours/sqlaz/fonctions/

8/15/2019 SQL BDD

11/80

&)em!le ,Si )Hcommune est un cCam! de t%!e I:'&G& dans la tale commune

SELECT _commune FROM commune L!M!T 1 renvoi 985>noter l4utilisation de la clause LIMIT #ui !ermet d4indi#uer le nomre ma)imumd4enre"istrements en retour. Il est é"alement !ossile d4utiliser la clause +&&S#T!our décaler le nomre de li"nes otenire) , SELECT * FROM commune L!M!T 10 OFFSET 2 >!our renvo%er lesenre"istrements de 6 15?SELECT cast"_commune as .eal( FROM commune L!M!T 1 renvoie 985.0

SELECT cast"_commune as tet( FROM commune L!M!T 1 renvoie 49854 c4est dire une cCa;ne de caract=re !uis#ue entre 44.

Pots"reSQL !ro!ose une notation com!acte sous la forme e)!r,,t%!ee)em!le , SELECT _commune 33 .eal FROM commune

7ne o!ération de transt%!a"e est !arfois nécessaire !our otenir le résultat souCaitéen !articulier avec S!atiaLite. Prenons l4e)em!le de calcul d4un indicateur >ratio dedeu) entiers? avec S!atiaLite.&)em!le , SELECT "population4supe.%icie( S 'ensite FROM commune L!M!T10

renvoie ,

non utilisation du "ast a#e" satialite

e résultat est inattendu UIl est dV au fait #ue dans S!atiaLite le résultat de la division de deu) entiers est un

entier.Pour otenir un résultat satisfaisant il faut au minimum convertir le numérateur ou ledénominateur en flottant,SELECT cast"population as %loat(4supe.%icie S 'ensite FROM communeL!M!T 10

8/15/2019 SQL BDD

12/80

Le résultat devient ien celui attendu ,

Utilisation de la fon"tion "ast

&on*tions de *:aînes de *ara*tères "L#NGT6 , renvoie la lon"ueur d4une cCa;nee)em!le , SELECT len5t$"nom_comm( FROM commune

364 , renvoie le caract=re corres!ondant au code ASII >e)em!le +>19? renvoiW?;; , concat=ne deu) cCa;nes >on otient ce s%mole en ta!ant AL'Gr 6?e)em!le , SELECT nom_comm66 # #66insee_comm FROM commune L!M!T 1

renvoie 4SAI:'D/&A:D$&DLAD*cCa;ne !osition lon"ueur?

&)em!le , SELECT * FROM t.oncon_$7'.o5.ap$i8ue WHERE sust."!_9CRTO,1, :( - #;:

8/15/2019 SQL BDD

13/80

renvoie ,

fon"tion round

R&ar+u

sous PostGIS on écrira SELECT ".oun' "population4supe.%icie( 33 nume.ic, ;(S 'ensite FROM commune

le ,, étant une forme com!acte sous PostGIS !our réaliser le cast. Le formatnumeri#ue >numeric? étant oli"atoire !our la fonction round sous PostGIS.

o&%#"&nt ( Sit$ d r""rnc$ %our #$ onction$ SQL dan$ $%atia#it t o$tGIS

Les !rinci!ales fonctions dis!oniles sous S!atiaLite sont décrites i"i

Les fonctions de Post"reSQL 8.2 sont décrites i"i

:ous vous conseillons vivement de !arcourir une !remi=re fois ces sites et d4%revenir ré"uli=rement !ar la suite...

#2 Tri et a!ré!ation

ri

Il est !ossile de classer le résultat d4une re#u3te en a-outant le mot clef +4$#4 B<

suivi d4une liste de cCam!s.&)em!le , SELECT * FROM commune ORER 9 nom_comm

!our classer le résultat !ar nom de commune.7n tri décroissant !eutD3tre otenu en a-outant $#S3.&)em!le , SELECT * FROM commune ORER 9 nom_comm ESCSELECT nom_comm, .oun'"cast"population as %loat(4supe.%icie,;( S'ensite FROM commune ORER 9 'ensite

retourne la densité de !o!ulation !ar ordre croissant de densité.

D Ctt!,EEFFF.s#lite.or"Elan"Hcorefunc.Ctml D Ctt!,EEdocs.!ost"res#l.frE8.2Efunctions.Ctml

:otions SQL

15Maîtrise d'oura!e " M#$$# - M#TL - M%%& Maîtrise d'(ure " #NT# %i) - #NSG Li*en*e ouerte #T%L%B

http://www.sqlite.org/lang_corefunc.htmlhttp://docs.postgresql.fr/9.2/functions.htmlhttp://www.sqlite.org/lang_corefunc.htmlhttp://docs.postgresql.fr/9.2/functions.html

8/15/2019 SQL BDD

14/80

Densite de oulation tri%e

R&ar+u

Sous PostGIS on écriraSELECT nom_comm, .oun'"population4supe.%icie 33 nume.ic,;( S 'ensiteFROM commune ORER 9 'ensite(

'!r"!ation$

7ne a"ré"ation est une o!ération #ui !ermet de re"rou!er les enre"istrements de latale en sortie selon des crit=res et d4otenir des informations statisti#ues sur ces

re"rou!ements. Il faut utiliser l4e)!ression G4+7P B

8/15/2019 SQL BDD

15/80

nous donne ,

+oulation des "ommunes

la re#u3te ,SELECT nom_'ept, sum"population( S population_'ept FROM commune !RO"#$% nom_'ept

renvoie ,

A&r%&ation ar d%artement

La clause G4+7P Bici

sum>??. Les !rinci!ales fonctions d4a"ré"ation sont ,*ount=> , renvoie le nomre d4enre"istrementssum=> , renvoie la somme

:otions SQL


8/15/2019 SQL BDD

16/80

ma)=> , ma)imummin=> , minimum

a!=> , mo%enne

o&%#"&nt ( La c#au$ 'I,G

Il se !eut #ue l4on souCaite mettre un crit=re de sélection sur une colonne calculée!ar l4o!ération d4a"ré"ation. $ans l4e)em!le Xa serait le cas si on souCaite n4afficCer#ue les dé!artements de !lus de 20000 Caitants.res!ectivement S!atialite etPostGIS? !ermettant d4a-outer le stoc@a"e et la mani!ulation d4o-ets s!atiau) ena-outant des t%!es de données "éométri#ues et des fonctions s!atiales.

o&%#"&nt ( L$ $%"ciication$

La s!écification S(SQL >Simle ,eatures for S-8? définit les t%!es et les fonctions

#ui doivent 3tre dis!oniles dans une ase de données s!atiale selon l4

8/15/2019 SQL BDD

17/80

moins deu) autres tales internes su!!lémentaires #u4il maintient -our ,"eometr%Hcolumns et s!atialHrefHs%s >PostGIS 1.5?

/a$les internes OGC

S4I$ est l4identifiant du s%st=me de !ro-ection. Par e)em!le 2159 !our leG(8ELamert8

on$i #( Qu#+u$ inor&ation$ uti#$ %our #$ 'd&ini$tratur$ d Donn"$ Loca#i$"$ 'DL:::

&n cas de !rol=me la fonction Po!ulateHGeometr%Hcolumns>? détecte les colonnes#ui contiennent de la "éométrie et met -our la tale interne "eometr%Hcolumns.A !artir de PostGIS 2.0 la ta$le Geometr01"olumns de#ient une #ue12

8/15/2019 SQL BDD

18/80

ST?Is8alid$etail=> , retourne en !lus un !ointeur vers la !artie non valide > !artirde PostGIS 2.0?.

ST?Ma@e8alid=> , 'ente de corri"er les "éométries invalides >PostGIS 2.0?

ST?9=> , retourne la coordonnée Y d4un !oint >et uni#uement d4un !oint?.ST? , coordonnée d4un !ointST?3entroid=> , retourne le centroide d4un !ol%"one&)em!le , ST_B"ST_Cent.oi'"?eomet.7(( retourne la coordonnée Y du centroided4un !ol%"one.S E L E C T n o m _ c o m m , S T _ B " S T _ c e n t . o i ' " ? e o m e t . 7 ( ( S B ,ST_"ST_cent.oi'"?eomet.7(( S FROM commune

Utilisation St1Centroid

ST?%rea=> retourne la surface d4un o-etST?Buer=> retourne un nouvel o-et tam!on construit autour d4un o-etST?Len!t:=> , retourne la lon"ueur d4un o-et de t%!e li"ne ou multiDli"ne>attention ne !as utiliser len"tC>? #ui retourne la lon"ueur du cCam! s!atialiteautorise aussi Glen"tC>??.ST?Perimeter=> , retourne le !érim=tre d4un o-et !ol%"one ou multiD!ol%"one

'ttntion ( rix S;

Il est recommandé de !réfi)er les commande !ar S'H >S!atial 'em!oral? !our 3treconforme au standard SQLE**. PostGIS a entamé une mi"ration vers ce standard etm3me si certaines fonctions anciennes sont tou-ours dis!oniles sans ce !réfi)e ellesdeviendront !rocCainement osol=tes. $onc autant !rendre les onnes Caitudes U


2020

:otions SQL


:otions SQL

8/15/2019 SQL BDD

19/80

G2 Présentation de $BMana!er

er"onomie !rocCe de celle de *a!Info?. Pourceu) #ui en dis!ose il est !ossile d4utiliser !'d&in III 16 #ui est le client le !lus!o!ulaire de PostGIS et #ui dis!ose de fonctionnalités intéressantes comme unassistant l4écriture de re#u3tes SQL.

Pour mettre en Zuvre le !lu"in $B *ana"er...JérifieT #u4il est ien installé ou sinon installeT le.

Gestionnaire d'extension de -GIS

Le !lu"in est alors dis!onile dans le menu 4ases de données4 de QGIS.

Menu Base de donn%es

Il est !ossile de créer une nouvelle ase de données s!atialite !artir de QGIS ene)!ortant une !remi=re coucCe >clic droit enre"istrer sous? !uis de cCoisir le formats!atialite >et !as s#lite?.

Pour se connecter la !remi=re fois une ase de données existante >s!atialite ou!ost"is? dans $B*ana"er il est nécessaire de le faire !ar l4intermédiaire de QGIS.

16 D Ctt!,EEFFF.!"admin.or"E[lan"OfrH(

:otions SQL


http://www.pgadmin.org/?lang=fr_FRhttp://www.pgadmin.org/?lang=fr_FR

8/15/2019 SQL BDD

20/80

\talir la conne)ion avec la ase de données QGISH!erfHsando)HJ2.s#lite>QGISH!erfHsando) !our les versions de QGIS antérieure la J2.0? en utilisant le

outon 4a,outer une *ou*:e spatialite4 et dés i"ner le fic Cie r

QGISH!erfHsando)HJ2.s#lite fourni dans le -eu) de données >ré!ertoire $ivers?.Puis connecter... vous deveT voir a!!ara;tre cette o;te de dialo"ue ,

Connexion sand$ox

&n lanXant $B *ana"er vous deveT maintenant !ouvoir vous connecter cette ase.: , 7ne ase de données de t%!e PostGIS !eut 3tre !roté"ée !ar mot de !assedans ce cas il faut le saisir dans la fen3tre #ui a!!ara;t !our cela.


2222

:otions SQL


:otions SQL

8/15/2019 SQL BDD

21/80

D$Mana&er

L4on"let info fournit les informations sur les tales

:otions SQL


8/15/2019 SQL BDD

22/80

DBMana&er Informations

8/15/2019 SQL BDD

23/80

&)em!le , sélectionner tous les o-ets de la tale commune ,

S%le"tion a#e" DBMana&er

o&%#"&nt ( G#i$$r > #?cr

$Bmana"er autorise le "lisserElacCer >dra"4n4dro!? !artir du navi"ateur de QGIS etinversement ou entre ases l4intérieur de $B*ana"er ou m3me !artir de toutesource valide de!uis le navi"ateur de ficCier de FindoFs c4est une autre faXon #uecelles #ue nous verrons !lus loin d4im!orter des données dans S!atiaLite ou PostGIS.Le résultat d4une re#u3te SQL !eutD3tre cCar"é comme une nouvelle coucCe dansQGIS en cocCant la case 4Car"er en tant #ue nouvelle coucCe4.Il faut !réciser la colonne avec des valeurs enti=res et uni#ues. e doit 3tre uncCam! de t%!e I:'&G& on cCoisira si on ne dis!ose de rien d4autres le cCam!P7I$ mais !ar e)em!le !our la tale QGIS2.6?.

1 D Ctt!,EEtrac.os"eo.or"E"dalEtic@etE520

:otions SQL


http://trac.osgeo.org/gdal/ticket/5270http://trac.osgeo.org/gdal/ticket/5270http://trac.osgeo.org/gdal/ticket/5270http://trac.osgeo.org/gdal/ticket/5270

8/15/2019 SQL BDD

24/80

62 #)er*i*e A " séle*tions SQL ae* $BMana!er

éaliser ses !remi=res re#u3tes SQL avec $B*ana"er sous QGISen utilisant les tales de 4QGISH!erfHsando).s#lite4 avec $B*ana"er réaliser lesre#u3tes suivantes ,

Ques t i on 1[Solution n23 45]

Q1 , sélectionner tous les IIS >tale IISHe)trait2? de la commune de LA (L&+&>colonne :omHom?.n , LA? dans la tale LA?.

Indi"e 6

Utiliser la ta$le iris1extrait47 et mettre une "ondition arès la "lause 89E:E ermettant d'indi)uer )u'on se limite ; la "ommune de la ,lè"e<

Ques t i on 2[Solution n27 45]

Q2 , sélectionner les communes du dé!artement de la SartCe de !lus de 1500Caitants en afficCant un taleau avec les noms de communes et leur !o!ulation.

Indi"e 6

Utiliser la ta$le "ommune= s%le"tionner les "ams demand%s (nom des"ommunes et oulation! dans la "lause SEEC/< Mettre deux "onditions'd%artement de la Sarte' E/ 'oulation de lus de 3>?? a$itants' dans la

"lause 89E:E

8/15/2019 SQL BDD

25/80

Ques t i on 5[Solution n2> 4]

Q5 , !artir de la tale !o!ulationcommune Esu!erficie commune? arrondie deu) décimales la !o!ulation de lacommune la !lus !eu!lée et celle de la moins !eu!lée la su!erficie mo%enne descommunes.

Indi"e 6

e r%sultat doit tre 6

exoF )uestion

on "er"e des sommes= mo0ennes=?mètres<

:otions SQL


8/15/2019 SQL BDD

26/80

Ques t i on [Solution n2 4]

Q , #uelle est la lon"ueur de la 4rivi=re le loir4 !ar t%!e de lar"eur sur ce -eu dedonnées [

Indi"e 6

Il faut artir de la re)ute r%"%dente et aJouter une "olonne )ui #a "al"uler lasomme de la lon&ueur des tronons

8/15/2019 SQL BDD

27/80

La solution est donc d4étalir un lien >une -ointure? entre les deu) tales afin #ue!our cCa#ue enre"istrement de la tale IISHe)trait2 on retrouve le nom de la

commune dans la tale

8/15/2019 SQL BDD

28/80

+rin"ie de Jointure attri$utaire

La tale résultat est une tale #ui a le m3me nomre d4enre"istrements #ue la taleIISHe)trait2 dans la#uelle on récu!=re le nom des communes de la tale FROM tale1> (O) tale;> O con'ition 'e ointu.e>

dans notre e)em!le cela donne ,SELECT epCom, om_!.is, insee_comm, nom_comm FROM i.is_et.ait; GO!commune O i.is_et.ait;DepCom - !SEE_COMM

SQL autorise eaucou! de sutilité dans les t%!es de -ointures on !ourra !are)em!le consulter e S- de A ; K sur les Jointures1.

1 D Ctt!,EEs#l!ro.develo!!eT.comEcoursEs#laTE-ointuresE


00

:otions SQL


:otions SQL

http://sqlpro.developpez.com/cours/sqlaz/jointures/http://sqlpro.developpez.com/cours/sqlaz/jointures/

8/15/2019 SQL BDD

29/80

2 Les ,ointures spatiales

Il n4est !as tou-ours !ossile de réaliser une -ointure attriutaire s4il n4% a !as decolonne commune entre deu) tales. $ans le cas de tales a%ant cCacune un cCam!"éométri#ue il est !ossile de réaliser des ,ointures spatiales.La -ointure s!atiale utilisera une fonction s!atiale >voir ciDdessous? dans la clauseM+&& d4une re#u3te SQL ,e)em!le ,SELECT * FROM tale, tale9 WHERE ST_!nte.sects"taleD5eomet.7,tale9D5eomet.7(

L$ r#ation$ $%atia#$

Les !rédicats s!atiau) de l4 retourne vrai s4il % a au moins un !oint

commun.ST?$is,oint=!eometr1 %C !eometr1 B> retourne vrai s4il n4% a aucun !ointcommun >é#uivalent n4intersecte !as ou :

8/15/2019 SQL BDD

30/80

d4utiliser !our des #uestions de !erformance li=e au) !ossiilités d4inde)ations!atiale?

ST?3rosses>"eometr% A "eometr% B? retourne vrai si le résultat de l4intersectiondes "éométries est de dimension immédiatement inférieure la !lus "rande desdimensions des o-ets >e) , si A est un !ol%"one et B une li"ne la dimension del4intersection doit 3tre une li"ne? &' #ue le résultat de l4intersection est l4intérieurdes deu) "éométries.ST?+erlaps=!eometr1 %C !eometr1 B> retourne vrai si les deu) "éométries sontde m3me dimension et #ue l4intersection est de m3me dimension mais de "éométriedifférente >renvoi fau) si les deu) "éométries sont identi#ues?.ST?Tou*:es=!eometr1 %C !eometr1 B> retourne vrai si les contours s4intersectentou si un seul des intérieurs intersecte le contour de l4autre.ST?it:in=!eometr1 %C !eometr1 B> retourne vrai si le !remier o-et estcom!létement dans le deu)i=me.ST?3ontains=!eometr1 %C !eometr1 B> retourne vrai si le deu)i=me o-et estcom!létement dans le !remier.Les fonctions suivantes sont é"alement intéressantes ,ST?$Dit:in=!eometr1 %C !eometr1 BC distan*e> #ui retourne vrai si la distancela !lus courte entre A et B est inférieure ou é"ale distance.ST?$istan*e=!eometr1 %C !eometr1 B> #ui calcule la distance la !lus courte entredeu) "éométries.

'ttntion ( o&%ort&nt di"rnt a=c i"i 18?

E2 #)er*i*e F " 4e.u/tes et on*tions spatiales

éaliser des re#u3tes et fonctions s!atiales ]

Q1 , #uels sont les !onctuels C%dro"ra!Ci#ues de la commune de La (l=cCe [

Indi"e 6

On utilisera les ta$les +O*C/UE19LD:OG:A+9I-UE et COMMU*E

8/15/2019 SQL BDD

31/80

Ques t i on 2[Solution n23? 4> ]

Q2 , #uelle est la lon"ueur de la 4rivi=re le loir4 dans cCacune des communesintersectées !ar le cours d4eau [résultat otenir ,

r%sultat exo4 )uestion 7

Indi"e 6

Ca)ue "ommune eut "ontenir lusieurs tronons= il faut don" "al"uler la sommedes lon&ueurs des tronons our "a)ue "ommune

8/15/2019 SQL BDD

32/80

Une re)ute de t0eSELECT nom_comm, srid(Geometry) AS SRID, MakePoint(X_CMM!"E,

#_CMM!"E, srid(Geometry)) AS Geometry $RM comm%ne &'ERE comm%nenom_comm *LA $LEC'E*

retourne des oints au "entroide "al"ul% ; artir des "oordonn%es 1COMMU*E= L COMMU*E<


99

:otions SQL


:otions SQL

8/15/2019 SQL BDD

33/80

II -Spatialite II

Gérer les ases et les tales 5

L4assistant de re#u3te SQL de Qs!atiaLite

éaliser des -ointures avec Qs!atialite 99

Inde)ation et o!timisation 95

&)ercice , re#u3tes SQL avec Qs!atiaLite 98

Objectifs

$é*ourir et mettre en prati.ue le SGB$4 SpatiaLite ae*QGIS

%2 Gérer les bases et les tables

ti#i$r S%atia#it $ou$ QGIS

S!atiaLite est l4e)tension s!atiale de S#lite. Il est conforme la norme OGCS,S20

>

R&ar+u

Le !lu"Din 4%dition ors "onnexion21

4 !ermet de "érer la s%ncCronisation avec unease S!atiaLite >offline.s#liter? emar#uée.Il est donc !ossile d4envisa"er des utilisations avec saisie terrain sous S!atiaLite!uis s%ncCronisation au retour avec une ase !arta"ée centrale >sous PostGIS !are)em!le?.

:ous allons utiliser s!atialite sous QGIS avec le !lu"in QspatiaLite réalisé !aromain IJI&& >mais ceu) #ui !réf=rent !euvent utiliser $BMana!er #ui est !lusuniversel mais #ui ne dis!ose !as encore >QGIS 2.2? d4assistant SQL?.Jérifier #ue Qs!atiaLite est ien installé ,

20 D Ctt!,EEFFF.o!en"eos!atial.or"EstandardsEsfs21 D Ctt!,EEdocs.#"is.or"E2.2EfrEdocsEuserHmanualE!lu"insE!lu"insHofflineHeditin".Ctml


5

http://www.opengeospatial.org/standards/sfshttp://docs.qgis.org/2.2/fr/docs/user_manual/plugins/plugins_offline_editing.htmlhttp://www.opengeospatial.org/standards/sfshttp://docs.qgis.org/2.2/fr/docs/user_manual/plugins/plugins_offline_editing.html

8/15/2019 SQL BDD

34/80

-satialite installation

Le menu ase de données de QGIS doit !ro!oser un item 4S!atiaLite4Son lancement doit afficCer la o;te de dialo"ue suivante ,

-satialite interfa"e rin"iale

C o i s i r ! a r e ) e m ! l e l a a s e Q G I S H ! e r f H s a n d o ) H v 2 . s # l i t e .>QGISH!erfHsando).s#lite !our une version de QGIS 2.0?

a**um database , !ermet de netto%er la ase de données des

enre"istrements vides >é#uivalent 4com!acter tale4 sous *a!Info?.


66

S!atialite


S!atialite

8/15/2019 SQL BDD

35/80

+pen3reate neD $ataBase , !ermet de créer une nouvelle ase SQLite ou

de récréer le lien vers une ase e)istante. Le ficCier d4e)tension . s#lite ainsi créé estcom!létement autonome et !eutD3tre diffusé un utilisateur distant >comme !are)em!le le ficCier #"isH!erfHsando)HJ2.s#lite fourni dans le -eu de données de laformation?.

4emoe , !ermet de su!!rimer la ase de données en cours de la liste des

ases référencées sous Qs!atiaLite. La ase n4est !as su!!rimée !C%si#uement. PourreDréférencer une ase e)istante il faut utiliser 4:eF $B4 et sélectionner la asee)istante. 7ne fen3tre demandant si on veut rem!lacer la ase a!!ara;t mais la asen4est !as écrasée et nouveau référencée.

Pour cCa#ue 'ale listée ,l4icne re!résente le t%!e de tale >!oint li"ne !ol%"ones données vues...? enfonction du t%!e de tale un clic droit afficCe un menu déroulant #ui donne acc=s au)commandes suivantes ,4#&4#S6 T4##, réactualise l4arre décrivant les tales.N# T%BL# , !ermet de créer une nouvelle tale directement sous Qs!atiaLiteN# 8I# , afficCe dans l4éditeur SQL l4ordre > com!léter? !ermettant de créerune 4vue4 >le conce!t de vue est un !eu similaire au) tales re#u3tes sous *a!Info.Joir !ar e)em!le i"i 22?. L4intér3t est #u4elle est remise -our si les données des tales

sources de la re#u3te #ui a "énéré la vue sont modifiées. 7ne vue s4utilise commeune tale >avec #uel#ues restrictions?.S6+ M#$%T% , afficCe les métadonnées stoc@ées dans 4"eomHcolsHrefHs%s4 de latale en cours.$4+P T%BL# , !ermet de su!!rimer la taleL+%$ IN QGIS , !ermet de cCar"er la tale dans QGISS6+ S%MPL# , montre un écCantillon des données de la taleS6+ %LL , montre les données de la taleS6+ 3+L7MNS , liste des colonnes de la tale avec leurs caractéristi#ues.N# IN$#Y , !ermet d4afficCer dans la o;te SQL la s%nta)e de création d4un inde).La notion d4inde) sous S#lite est !lus !uissante #ue sous ma!Info car elle !eutre!oser sur !lusieurs colonnes #oir ar exemle2.N# 3+L7MN , !ermet d4afficCer dans la o;te SQL la s%nta)e de création d4unenouvelle colonneN# T4IGG#4 , !ermet d4afficCer dans la o;te SQL la s%nta)e de création d4untri""er. :ous ne verrons !as dans ce cours les tri""ers voir !ar e)em!le i"i 29 !our ensavoir !lus.34#%T# SP%TI%L IN$#9 , !ermet de créer un inde) s!atial >tree? sur le cCam!"eometr% >!our accélérer les re#u3tes sur les "rosses tales?. :ous en re!arlerons...

%dan*e SQL #ditor , donne acc=s une oite de dialo"ue !ermettant de

construire les re#u3te SQL en mode interactif. :ous allons l4étudier un !eu !lus loin...

22 D Ctt!,EEs#l!ro.develo!!eT.comEcoursEs#laTEddlE[!a"eO!artie2L2 D Ctt!,EEs#l!ro.develo!!eT.comEcoursEs#laTEddlE[!a"eO!artie2L1029 D Ctt!,EEfr.Fi@i!edia.or"EFi@iE$éclencCeur

S!atialite


http://sqlpro.developpez.com/cours/sqlaz/ddl/?page=partie2#L8http://sqlpro.developpez.com/cours/sqlaz/ddl/?page=partie2#L10http://fr.wikipedia.org/wiki/D%C3%A9clencheurhttp://sqlpro.developpez.com/cours/sqlaz/ddl/?page=partie2#L8http://sqlpro.developpez.com/cours/sqlaz/ddl/?page=partie2#L10http://fr.wikipedia.org/wiki/D%C3%A9clencheur

8/15/2019 SQL BDD

36/80

ces outons !ermettent de navi"uer dans l':istori.ue des commandes

SQL. Import des *ou*:es QGIS. fi)er le SI$ est !rudent. Si c4est du

G(8ELamert 8 on indi#uera 2159 comme code E+SG25.

Import de i*:iers +G4 . !ermet d4im!orter directement dans s!atialite des

ficCiers dans les formats reconnus !ar

8/15/2019 SQL BDD

37/80

Ad#an"e S-

ette o;te de dialo"ue !ermet de construire la re#u3te SQL. La démarcCe estd4aord de sélectionner la ou les tales sur les#uelles on souCaite travailler >e) ,44A&

8/15/2019 SQL BDD

38/80

Exemle S- a#e" le re)uteur a#an"%

R&ar+u

Il n4% a !as N !ar défaut >comme sous *a!Info? dans le cCam! 4olumns4 dans lao;te de dialo"ue !our sélectionner tous les cCam!son !eut écrire O ou OO comme o!érateur d4é"alité.on ne dis!ose !as de O A: >utiliser I:?`` → est l4o!érateur de concaténation >ne !as utiliser comme sous *a!Info?

o&%#"&nt

GL+B , est similaire l4o!érateur LI& >O 0 n caract=res HO 1 caract=re? maisutilise les -o@ers uni) >N O 0 n caract=res [O 1 caract=re ? et est sensile lacasse.

B#T##N n4est !as dis!onile dans les menus déroulants mais est utilisale.M%T36 , !ermet de com!arer un ensemle de valeurs de li"ne un ensemle deli"nes retourné !ar une sousDre#u3te >usa"e rare?. Joir i" i 26 !our en savoir !lus.4#G#9P , !ermet d4utiliser les e)!ressions ré"uli=res ou rationnelles >voir i"i 2?. Ledocumentation !récise toutefois #u4il faut se définir sa !ro!re fonction re"e)!>? car iln4% en !as !ar défaut. L4utilisation de cet o!érateur sans définir de fonction "én=re unmessa"e d4erreur. 'r=s !eu utile !our les esoins des services.

'ttntion

SQLite est la)iste sur le contrle de t%!e des cCam!s !lus !récisément SQLite utiliseu n t0a&e d0nami)ue2. L oK *a!Info afficCera un messa"e du t%!e 4:onD

26 D Ctt!,EEs#l!ro.develo!!eT.comEcoursEs#laTEsousre#uetesEL1.2 D Ctt!,EEfr.Fi@i!edia.or"EFi@iE&)!ressionHrationnelle2 D Ctt!,EEfr.Fi@i!edia.or"EFi@iESQLite'%!esHdeHdonn..A8es


9090

S!atialite


S!atialite

http://sqlpro.developpez.com/cours/sqlaz/sousrequetes/#L1.3http://users/michel/Desktop/QGIS_PERF_AUTOFORM_PUBLI_2015-02-06/M05_SQL_BDD_papier.odt/%20http://fr.wikipedia.org/wiki/Expression_rationnellehttp://fr.wikipedia.org/wiki/SQLite#Types_de_donn.C3.A9eshttp://sqlpro.developpez.com/cours/sqlaz/sousrequetes/#L1.3http://users/michel/Desktop/QGIS_PERF_AUTOFORM_PUBLI_2015-02-06/M05_SQL_BDD_papier.odt/%20http://fr.wikipedia.org/wiki/Expression_rationnellehttp://fr.wikipedia.org/wiki/SQLite#Types_de_donn.C3.A9es

8/15/2019 SQL BDD

39/80

concordance de t%!e de données dans l4e)!ression4 Qs!atiaLite n4afficCera rien maisle résultat sera 4&m!t% résultat4 >résultat vide?. a!!elons la fonction *ast !our faire

des com!araisons avec cCan"ement de t%!e.

'ica! d tout$ #$ co&&un$ a=c ca#cu# dCun ca&% $u%%#"&ntair d dn$it"

-satialite= editeur S- a#an"%= &estion des "ams en sortie

Le cCoi) d4une colonne dans la liste déroulante 4columns4 a-oute le nom du cCam!!récédé d4une vir"ule. e n4est !as tou-ours souCaitale comme dans le cas oK l4onfait une division de deu) cCam!s. Il faut alors su!!rimer la vir"ule. Par défaut le nomde la tale est a-outé devant le nom de la colonne.L4a!!el au) fonctions comme round !our arrondir #ue l4on !eut cCoisir dans la listedéroulante 4matC4 n4afficCe !as la s%nta)e de la fonction. Pour une aide sur lesfonctions s!atiale il est conseillé d4utiliser une des"rition en li&ne28. >il est !ossile

d4otenir la version S!atiaLite dans le menu 4a !ro!os4 de QGIS?. Pour QGIS 2.2 laversion de s!atialite est la 9.1.1Pour une aide sur les fonctions de s#lite on !ourra consulter ce site0.La validation !ar

8/15/2019 SQL BDD

40/80

on!#t o%tion

on&let otion

Il !ermet de fi)er la destination du résultat ,

!ar défaut dans une nouvelle tale !rovisoire 4s#lesult4.uni#uement attriutaire? du nomindi#ué dans 'ale :ame dans S!atialite et cCar"er cette tale dans QGIS.3reate Spatial Table Load in QGIS , créer une tale s!atiale >avec la"éométrie? et la cCar"er dans QGIS.3reate 8ieD Load in QGIS , créer une Jue et la cCar"er dans QGIS.3reate spatial 8ieD Load in QGIS , créer une Jue s!atiale et la cCar"er dansQGIS.7ne ue est une tale virtuelle. Seule la re#u3te est mémorisée et nouveaue)écutée cCa#ue fois #ue l4on utilise la vue comme une tale. Le conce!t est tout fait similaire au) tales re#u3tes de *a!Info.

on$i #( G$tion d #a !"o&"tri dan$ #$ a!r"!ation$

Il est !ossile de faire des a"ré"ations et des tris en utilisant les cCam!s "ou! B%olumns et

8/15/2019 SQL BDD

41/80

Grou $0 a#e" &%om%trie

contient la colonne Geometr% >la transformation en te)te de la "éométrie au formatM' est demandée titre illustratif?e!endant un afficCa"e sous QGIS montre #ue la "éométrie est uni#uement celle delermontDréans >!remi=re commune dans la tale ré!ondant au crit=re 4communesim!le4 et non la fusion des "éométries de toutes les communes de Statut 4commune

sim!le4.$4une faXon "énérale si on utilise un Gver sion antér ieu re la 2.0? on !our ra util ise r@opulate_?eomet.7_Columns"( ou @.oe_?eomet.7_Columns"(

La tale devient "ra!Ci#ue et !eutD3tre cCar"ée sous QGIS !our vérification.A !artir de la version 2.0 de PostGIS il n4est !lus nécessaire d4utiliser les fonctions demise -our des tales internes. Le t%!e "eometr% étant un t%!e !art enti=re on!eut écrire ,CRETE T9LE EBEM@LE S

SELECT statut, st_multi"ST_Union"?eom(( 33 ?eomet.7"MULT!@OL?O, ;12J(as 5eom, sum"supe.%icie( as supe.%icie, sum"population( as population

FROM commune

?ROU@ 9 communeDstatutORER 9 communeDstatut

S!atialite


8/15/2019 SQL BDD

42/80

La conversion en t%!e 4multi!ol%"on4 avec le modificateur de t%!e Geometr%>? met -our automati#uement la vue 4"eometr%Hcolumns4.

>Le résultat de S'Hunion étant soit un 4!ol%"on4 soit un 4multi!ol%"on4 on utilise lafonction st?multi=> !our convertir tous les résultats en 4multi!ol%"on4?.n , Pour les li"nes il est !ossile de su!!rimer les discontinuités et éviter laconstructions de multili"nes avec la fonction st?linemer!e=>e) , S E L E C T t o p o n 7 m e , . o = _ n u m e . " ( o I e . " ( a s i ' ,st_lineme.5e"st_union"?eom(( as 5eom %.om t.oncon_$7'.o5.ap$i8ue =$e.etopon7me > ## 5.oup 7 topon7me

$B*ana"er demandant un identifiant de t%!e entier uni#ue !our cCar"er les coucCessous QGIS il est créé avec roD?number=> oer=>.eci !ermet de récu!érer le numéro de li"ne #ui est alors utilisé comme identifiant.Pour ceu) #ui sont intéressés cette s%nta)e utilise les !ossiilités avancés de SQLsur le fentra&e1.

32 4éaliser des ,ointures ae* Qspatialite

Les -ointures attriutaires !euvent 3tre réalisées avec l4assistant de re#u3te encCoisissant >au moins? deu) tales.

Nointures a#e" -satialite

$ans l4e)em!le ciDdessus le !aramétra"e réalise la commande SQL ,SELECT !R!S_et.ait;D#epCom#,

1 D Ctt!,EEs#l!ro.develo!!eT.comEarticleEola!DclauseDFindoFE


9999

S!atialite


S!atialite

http://sqlpro.developpez.com/article/olap-clause-window/http://sqlpro.developpez.com/article/olap-clause-window/

8/15/2019 SQL BDD

43/80

!R!S_et.ait;D#om_!.is#,

!R!S_et.ait;D#om_Com#

FROM !R!S_et.ait;, COMMUEWHERE !R!S_et.ait;D#epCom# --COMMUED#!SEE_COMM#

$e m3me on !eut réaliser des -ointures s!atiales

-satialite Jointures satiales

:oter #ue cette re#u3te s4e)écute en 202 ms >indi#ué en as droite dans l4on"let4result4?.

R&ar+u

Les o!érateurs s!atiau) sont des fonctions la s%nta)e est donc différente de celle de*a!Info.Si on souCaite utiliser le résultat comme une tale s!atiale il est nécessaire decCoisir une des colonnes de "éométrie en sortie si on indi#ue N il % a aura deu)colonnes de "éométrie dans la tale résultante. Il faudra donc !réciser la#uelle onconsid=re comme la source de "éométrie >cCam!s 4"eometr% field? lors de l4e)!ort deQs!tiaLite vers QGIS.

$2 Inde)ation et optimisation

$ans le cas d4une "rosse ase de données les re#u3tes S#l !euvent 3tre coVteusesen tem!s de calcul a fortiori les re#u3tes s!atiales #ui utilisent la "éométrie des

o-ets.réer des inde) >s!atiau) ou non? !eut !ermettre d4améliorer les tem!s detraitement. e n4est ce!endant !as une recette miracle. $ans S!atiaLite un inde)

S!atialite


8/15/2019 SQL BDD

44/80

s!atial ne !eut accélérer les calculs #ue dans le cas oK le résultat a!!artient une!etite !ortion du -eu de données. Quand les résultats incluent une "rande !artie du

-eu de données l4inde) s!atial ne !ermet !as de "ains de !erformance.$ans un SGB$ élaoré comme PostGIS le !lanificateur de re#u3te cCoisit de faXonada!tée d4utiliser ou non les inde) et l4inde) s!atial >de t%!e Gist #ue nous verrons!lus tard? est primordial. La seule restriction d4utilisation est celle des tales avecde tr=s "ros o-ets en !etit nomre >e) , tacCe uraine dé!artementale ré!artie en10 !ériodes soit 10 enre"istrements?.

8/15/2019 SQL BDD

45/80

Cr%ation index satial

$ans les anciennes versions de s!atialite on !ouvait utiliser des fonctions tree>treeIntersects 'reeMitCin treeontain?.e sont ces fonctions #ui sont im!lémentées dans l4assistant SQL de Qs!atialite>version .0.2 D AoVt 2019? ,

Index satial

&n a!!u%ant sur 'use Spatial Inde)4 Qs!atiale ra-oute automati#uement unes%nta)e dans la clause FCere.@OCTUEL_HRO?R@H!KUEDROW! ! "

SELECT pi' FROM i'_@OCTUEL_HRO?R@H!KUE_?eomet.7 WHERE pi' MTCHRT.ee!nte.sects"

M9RminB"TROCO_HRO?R@H!KUED#?eomet.7#(,M9Rmin"TROCO_HRO?R@H!KUED#?eomet.7#(,M9RmaB"TROCO_HRO?R@H!KUED#?eomet.7#(,M9Rma"TROCO_HRO?R@H!KUED#?eomet.7#(((

3ette s1nta)e n'est plus re*ommandée dans les derni=res versions de s!atialite

> 9.0.1? !our la#uelle il % a eu des tra#aux d'otimisation6

.

6 D Ctt!s,EEFFF."aiaD"is.itEfossilElis!atialiteEFi@i[nameOs!eedDo!timiTation

S!atialite


https://www.gaia-gis.it/fossil/libspatialite/wiki?name=speed-optimizationhttps://www.gaia-gis.it/fossil/libspatialite/wiki?name=speed-optimization

8/15/2019 SQL BDD

46/80

&lle !eut conduire au messa"e d4erreur suivant si le module "eocallac@ n4est !as!résent ,

#no suc$ %unction 3 Rt.ee!nte.sects# "mo'ule 5eocallacs non p./sent($ésormais !our utiliser e)!licitement les inde) s!atiau) on !eut construire unere#u3te co!iant la s%nta)e indi#uée i"i ,SELECT pD#?eomet.7# S ?eomet.7,

pD#T@E#,

pD#TURE#,

pD#TO@OME#,

pD#COTE#,

tD#TO@OME# as COUR__EU

FROM #@OCTUEL_HRO?R@H!KUE# p, #TROCO_HRO?R@H!KUE# t

WHERE st_inte.sects"pD#?eomet.7#,tD#?eomet.7#( an' pD#ROW!# in " select

.o=i' %.om spatial!n'e =$e.e %_tale_name - #@OCTUEL_HRO?R@H!KUE# sea.c$_%.ame - tD#?eomet.7#(

n ,

8/15/2019 SQL BDD

47/80

Quel#ues e)em!les de "ains sont données i" i

o&%#"&nt Les !ossiilités de mani!ulation s!atiale sont tr=s "randes... voici #uel#uesréférences su!!lémentaires ,-uel)ues exer"i"es et astu"es "lassi)ues8

le li#re de "uisine 90

:e !as CésiteT consulter !artir de "ette a&e 91le 4S!atial SQL functions reference"uide4 #ui est la liste de référence des fonctions dis!oniles dans la derni=re versionde s!atialite >attention ce n4est !as forcement celle de votre version de QGIS?. Pouraller !lus loin on !ourra en !articulier re"arder avec intér3t les fonctions 4G&Lamert 8?.réer une nouvelle coucCe BA'IHI:$7S'I&L10 et la cCar"er dans QGIS ensélectionnant dans la tale BA'IHI:$7S'I&L les 4Btiment industriel4 >attention lama-usculeU? dont la Cauteur est d4au moins 10 m

Indi"e 6 a ta$le r%sultat doit "ontenir enre&istrements<

Ques t i on 2[Solution n23 44 ]

Q2 , AfficCer dans la fen3tre 4esult4 de Qs!atiaLite les 4(or3t fermée de conif=res4 dela commune de La (l=cCe. :e !as oulieT de mettre une condition de -ointure entreles deu) coucCes... #ui devra 3tre ici s!atiale.

D Ctt!s,EEFFF."aiaD"is.itEfossilElis!atialiteEFi@i[nameOs!eedDo!timiTation

8 D Ctt!s,EEsites."oo"le.comEsiteEs"ds!atialiteEe)ercicesDastuces90 D Ctt!,EEFFF."aiaD"is.itEs!atialiteD2.9.0D9Es!atialiteDcoo@oo@DfrEinde).Ctml91 D Ctt!s,EEFFF."aiaD"is.itEfossilElis!atialiteEinde)92 D Ctt!,EEFFF.1@e%data.comEfrEs#lE9 D Ctt!,EEs#l!ro.develo!!eT.comE

S!atialite


https://www.gaia-gis.it/fossil/libspatialite/wiki?name=speed-optimizationhttps://sites.google.com/site/sgbdspatialite/exercices-astuceshttp://www.gaia-gis.it/spatialite-2.4.0-4/spatialite-cookbook-fr/index.htmlhttps://www.gaia-gis.it/fossil/libspatialite/indexhttp://www.1keydata.com/fr/sql/http://sqlpro.developpez.com/https://www.gaia-gis.it/fossil/libspatialite/wiki?name=speed-optimizationhttps://sites.google.com/site/sgbdspatialite/exercices-astuceshttp://www.gaia-gis.it/spatialite-2.4.0-4/spatialite-cookbook-fr/index.htmlhttps://www.gaia-gis.it/fossil/libspatialite/indexhttp://www.1keydata.com/fr/sql/http://sqlpro.developpez.com/

8/15/2019 SQL BDD

48/80

La tale résultat doit contenir 55 enre"istrements.

Ques t i on [Solution n23> 44 ]

Q , Plus difficile...alculer la somme des surfaces des 4(or3t fermée de feuillus4 de la commune de la(l=cCe en Ca >1Ca O 10 000 m2? en faisant attention ne !rendre en com!te #ueles !arties de surfaces des !ol%"ones réellement situées l4intérieur de la commune.

attention aux limites des ol0&ones

Ainsi dans l4e)em!le ciDdessus il ne faut !rendre en com!te #ue la !artie du !ol%"one -aune #ui est l4intérieur de la commune de la (l=cCe... on !ourra !enser lafonction stHIntersection>? #ui retourne un o-et "éométri#ue intersection de deu)o-ets...le résultat est 565 Ca

Ques t i on 9[Solution n23F 44 ]

Q9 , onstruire une nouvelle coucCe dans QGIS >non "ra!Ci#ue? de nom&'ABLISHPL7SHP

8/15/2019 SQL BDD

49/80

Le résultat est ,

exo - r%sultat

Indi"es 6

Il est "onseill% de d%"omoser un ro$lème "omlexe en ro$lèmes lus simles our arri#er ; la solution

8/15/2019 SQL BDD

50/80

SEEC/


5252

S!atialite


S!atialite

8/15/2019 SQL BDD

51/80

III -PostGIS III

PostGIS , Im!orter des données 5

7tiliser PostGIS de!uis QGIS 65

7tilisation de $B*ana"er avec PostGIS 6

Inde)ation s!atiale sous PostGIS 2

&)ercice 8 , PostGIS

Objectifs

$é*ourir et mettre en prati.ue PostGIS ae* QGIS

%2 PostGIS " Importer des données

PostGIS est eaucou! !lus com!let #ue S!atiaLite. &n com!araison il !ro!ose ,

une !lus "rande ricCesse dans les fonctions s!atiales >e) , fonction deconversion de !ro-ection $ routin"...? administration de ase >"estion desdroits outils d4administration et d4o!timisation...?

une meilleure "estion des transactions > ACID95?.

l4utilisation automati#ue des inde) s!atiau) "rce l4o!timiseur dere#u3tes.

*ais en contre!artie d4une !lus "rande com!le)ité. L4utilisation de PostGIS n4est doncrecommandée #ue s4il e)iste dé- une ase installée dans votre service "érée !ar

une é#ui!e s!écialisée ou si vous aveT les com!étences >et l4autorisation? !our lefaire installer sur votre !oste de travail dans un ut d4anal%se s!atiale !lus #ue de!arta"e de données.Pour utiliser ce module il faut avoir acc=s une ase PostGIS. Le cas écCéant les!aram=tres de conne)ion doivent 3tre fournis !ar l4or"anisateur de la formation.Pour im!orter et e)!orter des données dans PostGIS il e)iste !lusieurs solutionsavec cCacune leurs avanta"es et inconvénients.

ti#i$ation d #Cxtn$ion SI outi# dCi&%ortation d $a%i#$dan$ o$tGIS

Pour im!orter des ficCiers sCa!e et .df dans Post"reSQL directement de!uis QGIS ile)iste une e)tension a!tisée SI A.

95 D Ctt!,EEfr.Fi@i!edia.or"EFi@iEPro!riétésHAI$96 D Ctt!,EEdocs.#"is.or"E1.EfrEdocsEuserHmanualE!lu"insE!lu"insHs!it.Ctml


5

http://fr.wikipedia.org/wiki/Propri%C3%A9t%C3%A9s_ACIDhttp://docs.qgis.org/1.8/fr/docs/user_manual/plugins/plugins_spit.htmlhttp://fr.wikipedia.org/wiki/Propri%C3%A9t%C3%A9s_ACIDhttp://docs.qgis.org/1.8/fr/docs/user_manual/plugins/plugins_spit.html

8/15/2019 SQL BDD

52/80

ette e)tension doit 3tre activée l4aide du "estionnaire d4e)tensions de QGIS.7ne fois l4activation effectuée il faut cli#uer sur l4icne de l4e)tension SPI' dans la

arre d4outils de QGIS.

Imorter des Saes dans +ost&reS-

ette e)tension est é"alement accessile de!uis le menu Base de données.La fen3tre suivante a!!ara;t.

imortation de Saefile

Il faut créer une nouvelle conne)ion ou utiliser une conne)ion dé- créée.7ne fois #ue la conne)ion est étalie cli#uer sur A-outer.La fen3tre suivante a!!ara;t.


5959

PostGIS


PostGIS

8/15/2019 SQL BDD

53/80

Coix du fi"ier S9+

Coisir le ou les ficCiers sCa!e im!orter dans PostGis >les cCoisir les uns a!r=s lesautres?.li#uer sur

PostGIS


8/15/2019 SQL BDD

54/80

La fen3tre suivante a!!ara;t ,

"onnexion +ost&reS-

Le ou les ficCiers sCa!e im!ortés dans PostGis a!!araissent dans la liste.$ans le cCam! :om de la colonne de "éométrie laisser vide ou saisir le nom de lacolonne de "éométrie du ficCier sCa!e ou cocCer la case 7tiliser le nom de colonne"éométri#ue !ar défaut >tCeH"eom?.$ans le cCam! SI$ saisir la valeur 2159 #ui corres!ond au s%st=me decoordonnées de référence >S? G(8ELamert 8.li#uer sur référentiel E données métiers E données !roduites ou données taulaires E donnéesvectorielles !ar e)em!le?. L4or"anisation en scCémas multi!les !ermet de sim!lifier la"estion des droits d4utilisation mais le !rinci!al avanta"e reste de faciliter la mise

-our de PostGIS ainsi #ue la restauration ou l4écCan"e !lus efficaces de données.

La définition d4une structure d4e)!loitation de la ase !arta"ée !ar tous est duressort de l'administrateur de la $ase9.

9 D Ctt!s,EEfr.Fi@i!edia.or"EFi@iEAdministrateurHdeHasesHdeHdonnA8es


5656

PostGIS


PostGIS

https://fr.wikipedia.org/wiki/Administrateur_de_bases_de_donn%C3%A9eshttps://fr.wikipedia.org/wiki/Administrateur_de_bases_de_donn%C3%A9es

8/15/2019 SQL BDD

55/80

o&%#"&nt ( '=anta!$ > Incon="nint$ d SI

Avanta"es , *ulti!lateforme >MindoFs Linu) 7ni) *ac

8/15/2019 SQL BDD

56/80

+ostGIS Sae,ile Imort.Exort Mana&er

li#uer sur JieF connection details...Saisir les informations demandées !our ,

7ser :ame , >ici !ost"res?

PassFord , > cCoisir lors de l4installation de PostGIS?

Server +ost , localCost Port , >ici 592?

$ataase , >ici !ost"is?


55

PostGIS


PostGIS

8/15/2019 SQL BDD

57/80

d%tails "onnexion

li#uer sur

8/15/2019 SQL BDD

58/80

Conne"tin&

$ans la fen3tre de conne)ion >Lo" MindoF? un messa"e indi#ue si la conne)ion estétalie et ra!!elle les informations la concernant.Pour a-outer des ficCiers im!orter cli#uer sur Add (ile

8/15/2019 SQL BDD

59/80

imort S9+

'ttntion

Il faut veiller ce #ue les cCemins et les noms des ficCiers ne com!ortent ni es!aceslancs ni accents.

Lors#ue le ou les ficCiers im!orter dans PostGis ont été cCoisis cli#uer sur

PostGIS


8/15/2019 SQL BDD

60/80

La fen3tre suivante a!!ara;t ,

Otions imort

Selon le t%!e de données im!orter il faut cocCer les cases corres!ondantes.Puis cli#uer sur 2159 !our les données en G(8ELamert 8?.Les différents modes sont ,3reate , réé la tale et la rem!lit.%ppend , A-oute les enre"istrement la tale >si elle e)iste dé-?.$elete , Su!!rime la tale et la recrée avec les enre"istrements.Prepare , réé la tale mais ne la rem!lit !as avec les enre"istrements.Pour afficCer la fen3tre des o!tions de l4im!ortation cli#uer sur +ptions...Les o!tions les !lus im!ortantes retenir sont les suivantes ,

Presere *ase o *olumn names , conserve la casse >ma-usculeEminuscule?des colonnes du ficCier im!orté >Les noms de colonnes sont entourés de RRdans PostGIS?.

3reate spatial inde) automati*all1 ater load , crée automati#uement uninde) s!atial a!r=s le cCar"ement du ficCier im!orté. onseillé si vous necCar"eT !as d4autres données dans cette tale.

Load onl1 attribute =db> data , !ermet de ne cCar"er #ue les donnéesattriutaires dans une tale au format .df

Load into G#+G4%P6< *olumn , cCar"e la "éométrie des données dansune colonne de t%!e G&!lutt #ue G&

8/15/2019 SQL BDD

61/80

n , au su-et de l4encoda"e des ficCiers S+P voir i"i 98 ainsi #ue #uel#ues informationscom!lémentaires i" i 50.

li#uer sur Lo" FindoF? fait a!!ara;tre un ou des messa"es sur la!rocédure d4im!ortation des ficCiers cCoisis.Si l4im!ortation se déroule correctement le messa"e suivant a!!ara;t dans la fen3trede conne)ion.

lo& QindoQ

La conne)ion et l4im!ortation de données dans PostGIS étant faite on !eut !asserdans QGIS !our afficCer des données !rovenant de PostGIS.

ti#i$ation d DB&ana!r

$B*ana"er !ermet de cCar"er des ficCiers dans une ase PostGIS connectée sousQGIS !ar "lisser E lcCé de ficCiers !artir du navi"ateur QGIS ou !ar le menu4'ale Im!orter une coucCe ou un ficCier4 !our les coucCes dé- cCar"ée dans

QGIS. e dernier menu est é"alement accessile !ar le outon

n , $B*ana"er !ermet é"alement de faire des écCan"es de données avec les asesde données s!atialite !ar "lissé E lcCé.

98 D Ctt!,EEFFF."eoinformations.develo!!ementDdurale."ouv.frE#"isD2D2Dencoda"eDdesDficCiersDsC!Da280.Ctml50 D Ctt!,[email protected]

PostGIS


http://www.geoinformations.developpement-durable.gouv.fr/qgis-2-2-encodage-des-fichiers-shp-a2908.htmlhttp://chinook.memoris.fr/http://www.geoinformations.developpement-durable.gouv.fr/qgis-2-2-encodage-des-fichiers-shp-a2908.htmlhttp://chinook.memoris.fr/

8/15/2019 SQL BDD

62/80

La fen3tre d4im!ort !ermet de saisir les !aram=tres nécessaires ,

D$Mana&er aramètres d'imort

o&%#"&nt ( @!r2o!r

Les s!écialistes !réf=rent !arfois utiliser o&r7o&r 51 !our contrler l4im!ort desdonnées dans PostGIS. :ous ne détaillerons !as ici cet outil #ui est !lutt réserver l4administrateur de la ase.Avanta"es ,

Prend en cCar"e une multitude de format 52.

Possiilité de cCan"ement de !ro-ection Possiilité de sélection SQL des données en im!ort.

Inconvénient ,

S%nta)e com!le)e cause des nomreuses o!tions.n , Pour les s!écialistes l4o!tion RD confi" PGH7S&H

8/15/2019 SQL BDD

63/80

faXon drasti#ue le cCar"ement de "ros ficCiers avec o"r2o"r.$ans la version QGIS 2. devrait a!!ara;tre un al"oritCme d4im!ort dans le menu

traitement >Processin"? 4Im!ort vector into PostGIS dataase4 #ui utilisera de faXontrans!arente o"r2o"r.

B2 7tiliser PostGIS depuis QGIS

Il e)iste deu) modalités différentes !our lancer PostGIS de!uis QGIS.

'outr un couc o$tGIS

li#uer sur l4icne A-outer une coucCe PostGIS dans la arre d4outils de QGIS.

ou !artir du menu oucCe !uis A-outer une coucCe PostGIS >trlSCift$?.La fen3tre suivante a!!ara;t.

AJouter "ou"es +ostGIS

li#uer sur :ouveau !our étalir une nouvelle conne)ion avec une ase PostGIS.

PostGIS


8/15/2019 SQL BDD

64/80

La fen3tre suivante a!!ara;t.

Information de "onnexion

7ne fois les !aram=tres corrects rensei"nés et les cases cocCées tester maconne)ion !uis cli#uer sur

8/15/2019 SQL BDD

65/80

iste des ta$les et s"%mas

on$i#

$ans le cas de tales volumineuses >comme !ar e)em!le la B$Parcellaireg? il estconseillé d4utiliser le constructeur de re#u3tes >outon 4(iltrer4? !our filtrer lesdonnées afficCer >!ar e)em!le sur une seule commune?.

Coisir la tale #u4on souCaite afficCer dans QGis et cli#uer sur A-outer.La coucCe des données de PostGIS a!!ara;t dans la liste des coucCes de QGIS ets4afficCe dans la fen3tre carto"ra!Ci#ue si on cocCe son afficCa"e.L4infoDulle dans le contrle des coucCes afficCe les informations sur l4ori"ine de lacoucCe >ase Sando)...?

#isualisation dans -GIS

'outr d$ donn"$ %ar G#i$$" > L?c" d%ui$ DB

8/15/2019 SQL BDD

66/80

32 7tilisation de $BMana!er ae* PostGIS

omme nous l4avons vu dans le cours sur S!atiaLite il est !ossile d4utiliser le !lu"in$bMana!er.Si la ase de données Post"reSQLEPostGIS est !roté"ée !ar un mot de !asse il fautle saisir dans la fen3tre #ui a!!ara;t !our cela.:ous ra!!elons ici les !rinci!ales éta!es...La fen3tre suivante a!!ara;t indi#uant #ue la conne)ion avec la ase de donnesPost"reSQLEPostGIS est effective.n , A noter #ue cCa#ue utilisateur dis!ose de Priilè!es #ui sont indi#ués. 4estl4administrateur de la ase de données #ui r="les les droits et rles.7n utilisateur doit !ourvoir au minimum 4accéder au) o-ets4 un !roducteur doit

!ouvoir 4créer de nouveau) o-ets4.

D$Mana&er

li#uer sur le scCéma utilisé ici !ulic !uis sur le nom du ficCier souCaité , ici routeH)% La fen3tre suivante a!!ara;t indi#uant tous les cCam!s attriutaires de cette talePost"reSQLEPostGIS.


66

PostGIS


PostGIS

8/15/2019 SQL BDD

67/80

ta$le

8/15/2019 SQL BDD

68/80

r%sultat

Il faut dési"ner oli"atoirement la colonne avec des valeurs entières et uni#ues>identifiant? et la colonne "éométri#ue.

La colonne Géométri#ue !eut 3tre trouvée dans l4on"let info de la tale sous$B*ana"er ou l4on voit #ue le cCam! est de t%!e "eometr%>LineStrin" 2159?. Lacolonne avec des 4valeurs enti=res et uni#ues4 est donnée !ar la ruri#ue 3onstraintdans le m3me on"let il s4a"it de la colonne !id.Si nous n4avions !as dis!osé d4un identifiant de t%!e I:'&G& on aurait !u le créeren modifiant la re#u3te SQL ,SELECT +o,num.e+& o/e+& as ient, * FROM ROUTE_B =$e.e ume.o -#10J#

L4infoDulle de la coucCe résultante dans QGIS a!r=s cCar"ement indi#ue la source ,tale OR>S&L&' N (

8/15/2019 SQL BDD

69/80

*ais les inde) a-outent aussi une surcCar"e au s%st=me de ase de données dansson ensemle si ien #u4ils doivent 3tre utilisés avec discernement.

L4inde)ation s!atiale sous PostGIS utilise l4inde) GiST >GeneraliTed SearcC 'ree?.Lors#ue une tale de PostGIS est cCar"ée avec l4e)tension !"SCa!eLoader celuiDcicrée automati#uement un inde) s!atial a!!elé taleHtCeH"eomH"ist.Il est !ossile de su!!rimer cet inde).RO@ !EB tale_t$e_5eom_5ist

Si l4inde) n4e)iste !as il est !ossile de le créer !ar la commande suivante ,CRETE !EB tale_t$e_5eom_5ist O tale US!? ?!ST "t$e_5eom(

Plus la tale est "rande !lus le tem!s d4e)écution d4une re#u3te utilisant les inde)diminue !ar ra!!ort la m3me re#u3te e)écutée sur une tale sans inde) s!atial.

LCo%ti&i$ur d r+uEt$ $ou$ o$tGIS

PostGIS !oss=de un !lanificateurEo!timiseur de re#u3te #ui utilise une fonctiond4estimation des coVts des différentes straté"ies afin de trouver le cCemin le moinscoVteu) et étalir un !lan d4e)écution o!timal.contrairement S!atiaLite? l4e)ce!tion notale des fonctions ST?$is,oint et ST?4elate.7n ordre SQL de t%!e #9PL%IN %N%L

8/15/2019 SQL BDD

70/80

cCoisir mettre en !remier la condition la !lus Rra!ideR ou la !lus discriminante!ermet d4o!timiser les re#u3tes.

!enser utiliser les sousre)utes56

, dans certains cas l4utilisation de sousre#u3tes est !lus efficace #ue les multi!les conditions et -ointures. Il faut donc!enser tester l4utilisation de sousDre#u3tes d4autant !lus #u4elles!ermettent de décom!oser un !rol=me com!le)e en !lusieurs !rol=mes!lus sim!les. L4utilisation de sousDre#u3te !eut ce!endant !ara;tre com!le)elors#u4on déute en SQL... n4CésiteT !as vous faire aider !our mettre au!oint une re#u3te devant traiter de "rosses tales et faire des essais!réalales sur des e)traits U

Ce site5 donne é"alement #uel#ues e)!lications sur le fonctionnement del4o!timiseur >!lanner?.

#2 #)er*i*e " PostGIS

7tilisation de PostGISo-ectif ,Im!orter des données dans PostGIS et visualiser une !artie des donnéesdans QGIS en réalisant une re#u3te de filtra"e.

Q u e s t i o n[Solution n234 4]

Im!orter le ficCier sCa!e

8/15/2019 SQL BDD

71/80

Solution des

e)er*i*es

Solution nJ (exer"i"e

8/15/2019 SQL BDD

72/80

SELECT nom_'ept, sum"population( S population_'ept,

. o u n ' " a I 5 " c a s t " p o p u l a t i o n a s % l o a t ( 4 s u p e . % i c i e ( , ; ( S'ensite_mo7_communes,

ma"population( S pop_ma_commune, min"population( S pop_min_commune,.oun'"aI5"supe.%icie(,;( S su.%ace_mo7_commune FROM commune

?ROU@ 9 nom_'ept

n , le cast n4est !as utile si on utilise PostGIS au lieu de s!atialite.

Solution nA (exer"i"e ? n4est !as dis!onile dans la version de s!atialitefournie avec QGIS 1. on utilise alors Glen"tC>?. &lle est dis!onile dans lesderni=res versions >QGIS 2.2?.sous !ostGIS on écrira ,SELECT nom_comm, .oun'""st_a.ea"5eom(41000000( 33 nume.ic,;( SSURFCE_m;, .oun'""ST_pe.imete."5eom(41000( 33 nume.ic,;( S@ER!METRE_m FROM commune WHERE nom_'ept - #SRTHE#

8/15/2019 SQL BDD

73/80

Solution n (exer"i"e

8/15/2019 SQL BDD

74/80

Solution nJJ (exer"i"e < 55 !

SELECT * FROM ponctuel_$7'.o5.ap$i8ue, etalissement WHEREst_'istance"ponctuel_$7'.o5.ap$i8ueD?eomet.7, etalissement D?eomet.7( 2000

ouSELECT * FROM ponctuel_$7'.o5.ap$i8ue, etalissement WHEREs t _ c o n t a i n s " 9 u % % e . " e t a l i s s e m e n t D ? e o m e t . 7 , 2 0 0 0 ( ,ponctuel_$7'.o5.ap$i8ueD?eomet.7(

sous PostGIS on !ourrait utiliser la fonction stH$FitCin>?SELECT * %.om ponctuel_$7'.o5.ap$i8ue, etalissement WHEREST_Wit$in"ponctuel_$7'.o5.ap$i8ueD?eomet.7, etalissement D?eomet.7,2000(

8/15/2019 SQL BDD

75/80

solution

Solution nJ (exer"i"e < H !

SELECT *

FROM POE_AE?ETT!O, COMMUE

WHERE POE_AE?ETT!OD#TURE# - #Fo.Qt %e.m/e 'e coni%.es# an' C O M M U E D # O M # - # L a F l c $ e # a n '

st_inte.sects"POE_AE?ETT!OD#?eomet.7#, COMMUED#?eomet.7#(

Solution nJ5 (exer"i"e < >? !

SELECT.oun'"sum"st_a.ea"st_inte.section"POE_AE?ETT!OD#?eomet.7#,COMMUED#?eomet.7#((( 4 10000( as su.%ace_$a

FROM POE_AE?ETT!O, COMMUE

WHERE st_inte.sects"POE_AE?ETT!OD#?eomet.7#,COMMUED#?eomet.7#(an' POE_AE?ETT!OD#TURE# - #Fo.Qt %e.m/e 'e %euillus# an'COMMUED#OM# - #La Flc$e#

Solution nJA (exer"i"e < >? !

La solution est ,SELECT @!_STED!, 9T!_!USTR!ELD!, st_'istance"@!_STED?eomet.7,9T!_!USTR!ELD?eomet.7( S 'istance FROM @!_STE,9T!_!USTR!ELWHERE 'istance ! "SELECT min"st_'istance"@!_STED?eomet.7,9 T ! _ ! U S T R ! E L D ? e o m e t . 7 ( ( S ' i s t a n c e _ m i n F R O M@!_STE,9T!_!USTR!EL ?ROU@ 9 @!_STED@)U!(

Pour cCar"er la tale dans QGIS on utilisera un 4reate 'ale Load in QGIS4>!uis#ue ce n4est !as un tale s!atiale?.

Solution des e)ercices


8/15/2019 SQL BDD

76/80

exo - r%sultat

o&%#"&nt

Le ut de l4e)ercice est de montrer l4intér3t et la s%nta)e d4une re#u3te com!le)e.e!endant !our ré!ondre la #uestion !osée nous aurions !u utiliser l4outil JecteurD

8/15/2019 SQL BDD

77/80

Coisir l'en"oda&e du S9+

Le ou les ficCiers sCa!e im!ortés dans PostGIS a!!araissent dans la liste.$ans le cCam! :om de la colonne de "éométrie laisser vide ou saisir le nom de lacolonne de "éométrie du ficCier sCa!e ou cocCer la case 7tiliser le nom de colonne"éométri#ue !ar défaut >tCeH"eom?.$ans le cCam! SI$ saisir la valeur 2159 #ui corres!ond au s%st=me decoordonnées de référence >S? G(8ELamert 8.Si S@!T .enIoi un messa5e 'u t7pe ERROR 3 e.o&len5t$ 'elimite'i'enti%ie. at o. nea.

L!E 1 3 CRETE T9LE pulicDROUTE_B" SER!L @R!MR )E, !'DDD

Alors ta!eT >ou reta!eT? e)!licitement un nom dans le cCam! 4:om de la clé!rimaire4 >ce !eutD3tre 4"id4 !ar e)em!le mais ce ne !eutD3tre un des cCam!s du

ficCier S+P?.li#uer sur

8/15/2019 SQL BDD

78/80

"onstru"teur de re)ute

$ouleDcli#uer sur le cCam! numéro. e cCam! doit a!!ara;tre entre RR dans lafen3tre lause SQL M+&& située en as de la fen3tre du constructeur de re#u3tes.li#uer sur l4o!érateur O. eluiDci doit a!!ara;tre a!r=s le cCam! numero dans lafen3tre lause SQL M+&&.li#uer sur le outon 'out. La liste des valeurs du cCam! numéro doit a!!ara;tre

dans la !artie droite de la fen3tre.Coisir la valeur $2. ette valeur doit a!!ara;tre entre 44 dans la fen3tre lauseSQL M+&& a!r=s l4o!érateur O.li#uer sur

8/15/2019 SQL BDD

79/80

-GIS

&n cli#uant sur la coucCe on !eut afficCer la tale attriutaire et vérifier #ue lestronXons afficCés ont ien la valeur $2 dans le cCam! numero .

R&ar+u ( D$ &$$a!$ dCrrur %o$$i)#

si vous rencontreT le messa"e d4erreur ,

erreur ost&is

Jous n4aveT !as indi#ué une clef !rimaire correcte.



8/15/2019 SQL BDD

80/80

Si vous rencontreT le messa"e suivant ,

erreur +ostGIS

Jous n4aveT !as indi#ué le on encoda"e de caract=res >7'(D en "énéral?.Pour le "lissé E lcCé de!uis le navi"ateur ,

Gliss% . lR"% a#e" DBmana&er



SQL BDD

Documents

Transcript of SQL BDD