Discussion :

[vincedchart]

Bonjour, le résultat de la selection suivante (extension Postgis) :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
SELECT a.id, b.iso3
FROM "test.poi.barrest" a, "monde.admin.paystressimplifie" b
WHERE a.geometry && b.geom
AND ST_Intersects(a.geometry, b.geom)

est :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
       id        | iso3 
------------------+------
 way/401467207    | DZA
 node/482252337   | DZA
 node/508328276   | DZA
 way/745390942    | DZA
 way/1171619321   | DZA
 way/1171620274   | DZA
 way/1171624882   | DZA

J'ai besoin d'inserer le résultat, comme une nouvelle colonne nommee iso3 dans test.poi.barrest

Merci d'avance de votre aide.

[escartefigue]

Bonjour,


Depuis la norme SQL de 1992, les jointures ne se codent plus dans la restriction WHERE, mais avec l'opérateur JOIN.

De plus, stocker une valeur calculée est une erreur : si la valeur d'un des opérandes change, la valeur calculée devient fausse.
C'est pourquoi il faut calculer la valeur par requête à chaque fois que c'est nécessaire. Si le besoin est récurrent, créez une vue pour calculer cette valeur.

[vincedchart]

Bonjour, je veux bien voir comment faire évoluer mon code, j'essaye d'apprendre Join mais je vois que j'ai malgré tout obtenu un résultat positif.

Concernant cet enregistrement de la valeur à un instant T, et pas de manière dynamique, cela m'arrange.

Ma base de données d'origine est dans tous les cas une copie, un snapshot d'une autre base de données qui a évolué depuis, et qui n'est plus à jour jusqu'au moment où je déciderai de créer une nouvelle copie (annuelle).

Ma requête a pour but de qualifier : 'dans quel commune, dans quel quartier (avec un périmètre lourd en ressources calculé à chaque fois..) se situe tel point ?'

A l'échelle d'une année, ce nom de commune ou de quartier n'a pas vocation à changer, d'où la non-nécessité d'avoir une base de données dynamique à cet endroit.

J'essaye donc actuellement de comprendre les UPDATE et ALIAS pour tenter d'arriver à mes fins...

[ced]

De plus, stocker une valeur calculée est une erreur : si la valeur d'un des opérandes change, la valeur calculée devient fausse.
C'est pourquoi il faut calculer la valeur par requête à chaque fois que c'est nécessaire. Si le besoin est récurrent, créez une vue pour calculer cette valeur.

Ça, c'est la théorie, avec laquelle je suis habituellement d'accord .
Mais dans la pratique, et surtout dès lors qu'on rentre dans des croisements avec de l'information spatiale, ce qui peut à la fois être volumineux et où les temps de traitements sont souvent longs, le recours à une requête systématique n'est pas toujours acceptable... Et dans ce cas, pour des raisons de performances des requêtes, il faut recourir au stockage de l'info issue du croisement.

Bonjour, je veux bien voir comment faire évoluer mon code, j'essaye d'apprendre Join mais je vois que j'ai malgré tout obtenu un résultat positif.

Votre requête s'écrit comme ça :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
SELECT a.id, b.iso3
FROM "test.poi.barrest" a
INNER JOIN "monde.admin.paystressimplifie" b ON a.geometry && b.geom AND ST_Intersects(a.geometry, b.geom)

Pour ce qui est de la création de la colonne dans la table test.poi.barrest :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

ALTER TABLE test.poi.barrest ADD COLUMN iso3 CHAR(3);

Attention, sur votre exemple, la donnée ISO3 n'a que 3 caractères, mais peut-être existe-t-il des valeurs plus longue sur cette donnée (qu'on ne voit pas dans l'exemple). Il faudra donc adapter le type de la donnée iso3 à cette longueur maximale...

Enfin, pour mettre à jour cette nouvelle colonne avec le résultat du croisement spatial, ça peut être fait comme ça par exemple (d'autres syntaxes sont possibles, mais je trouve celle-là plus compréhensible) :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
WITH croisement AS (
    SELECT a.id, b.iso3
    FROM "test.poi.barrest" a
    INNER JOIN "monde.admin.paystressimplifie" b ON a.geometry && b.geom AND ST_Intersects(a.geometry, b.geom)
)
UPDATE "test.poi.barrest" tpb
SET iso3 = c.iso3
FROM croisement c
WHERE tpb.id = c.id;

[vincedchart]

Merci beaucoup. Effectivement je veux limiter le ponctionnement de ressources réseau. Ces deux codes sont intéressants et fonctionnent ! merci.

Aucun souci pour CHAR3 ou varchar254 d'ailleurs, pour créer une colonne que je fais souvent.

[vincedchart]

Je voudrais aller plus loin et dans l'exemple suivant, grâce à votre aide j'ai déterminé et enregistré dans la base de données, dans quelle commune se situe tel centre commercial.

Il s'agit d'inserer pour centre commercial l'information du quartier dans lequel il se situe, étant donné que la division en quartiers n'est pas une science exacte mais une affaire de sensibilités, et ainsi un objet pourrait finalement être dans plusieurs quartiers s'il est à la limite, ou bien en même tempsdes microquartiers de voisinage et des quartiers à l'échelle plus large.

J'aimerai plusieurs résultats dans la cellule, séparés par une virgule (les concaténer)

voici un select qui a marché :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

SELECT a.name AS namecc, a.communes AS comm, b.name AS nameq FROM "idf.poi.ccial" a INNER JOIN "fr.local.osmquartiers" b ON ST_DWithin(a.geom,b.geom,0.006)

ce qui donne des objets représentés par un rayon d'environ 1 km (mesuré par 0,006 degrés en mesure de la Terre).

pourriez-vous me dire comment cumuler ces resultats avant de faire un update ?

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
               namecc                |         comm         |            nameq             
-------------------------------------+----------------------+------------------------------
 Italie 2 (Printemps)                | Paris                | 13e Arrondissement
 Italie 2 (Printemps)                | Paris                | Butte aux Cailles
 Carrousel du Louvre (Printemps)     | Paris                | 1er Arrondissement
 Terre-Ciel (Carrefour)              | Chelles              | Villeneuve
 Les 3 Fontaines (Auchan)            | Cergy                | Cergy Préfecture
 Bobigny 2                           | Bobigny              | Cité Paul Vaillant Couturier
 Bobigny 2                           | Bobigny              | Cité Pablo Picasso
 Bobigny 2                           | Bobigny              | Cité Hector Berlioz
 Bobigny 2                           | Bobigny              | Cité Paul Éluard
 Westfield Forum des Halles          | Paris                | Les Halles
 Italie 2 (Printemps)                | Paris                | Les Gobelins
 Passy Plaza                         | Paris                | Passy
 Westfield Forum des Halles          | Paris                | Beaubourg
 Évry 2 (Carrefour)                  | Évry                 | Droits de l'Homme
 Évry 2 (Carrefour)                  | Évry                 | Le Canal
 Val d'Yerres 2 (Cora)               | Boussy Saint Antoine | Les Vingt Cinq Arpents
 Avenir (Carrefour)                  | Drancy               | Avenir Parisien
 Plein Air (E.Leclerc)               | Le Blanc Mesnil      | Cité Marcel Alizard
 Westfield Forum des Halles          | Paris                | Quartier des Halles
 Issy 3 Moulins (Auchan)             | Issy les Moulineaux  | Les Pucelles
 Issy 3 Moulins (Auchan)             | Issy les Moulineaux  | Les Brillants
 Westfield Les Quatre Temps (Auchan) | Puteaux              | La Défense
 CNIT (Fnac)                         | Puteaux              | La Défense
 Bay 1 Loisirs                       | Torcy                | Le Segrais
 Les 3 Fontaines (Auchan)            | Cergy                | Le Chemin de Cergy
 Les 3 Fontaines (Auchan)            | Cergy                | Les Clos Tripiers
 Les 3 Fontaines (Auchan)            | Cergy                | Les Arrangeons
 Les 3 Fontaines (Auchan)            | Cergy                | Vc Qua Nouvelle Prefecture
 Les 3 Fontaines (Auchan)            | Cergy                | Res des Bourgognes
 Les 3 Fontaines (Auchan)            | Cergy                | Les Bourgognes
 Les 3 Fontaines (Auchan)            | Cergy                | Les Marjoberts
 Les 3 Fontaines (Auchan)            | Cergy                | Les Maheux
 Les 3 Fontaines (Auchan)            | Cergy                | Les Plants Orange
 Les 3 Fontaines (Auchan)            | Cergy                | Les Plants Bruns
 Les 3 Fontaines (Auchan)            | Cergy                | Le Chemin Dupuis Brun
 Quai des Marques Franconville       | Franconville         | Bois de Saint-Marc
 Quai des Marques Franconville       | Franconville         | La Borne de Marbre
 St-Lazare Paris                     | Paris                | Quartier de l'Europe
 Westfield Les Quatre Temps (Auchan) | Puteaux              | La Défense 8
 CNIT (Fnac)                         | Puteaux              | La Défense 8
 Westfield Les Quatre Temps (Auchan) | Puteaux              | La Défense 6
 CNIT (Fnac)                         | Puteaux              | La Défense 6
 Charras                             | Courbevoie           | La Défense 5
 Westfield Les Quatre Temps (Auchan) | Puteaux              | La Défense 12
 CNIT (Fnac)                         | Puteaux              | La Défense 12
 CNIT (Fnac)                         | Puteaux              | La Défense 3
 Westfield Les Quatre Temps (Auchan) | Puteaux              | La Défense 4
 CNIT (Fnac)                         | Puteaux              | La Défense 4
 Charras                             | Courbevoie           | La Défense 2
 Westfield Les Quatre Temps (Auchan) | Puteaux              | La Défense 9
 CNIT (Fnac)                         | Puteaux              | La Défense 9
 Marques Avenue L'Île-Saint-Denis    | L'Île Saint Denis    | Vieux Saint-Ouen
 Charras                             | Courbevoie           | La Défense 1
 Thiais Village (Ikea)               | Thiais               | Green Valley
 Les Arcades (Carrefour)             | Noisy le Grand       | Maille-Horizon
 Beau Sevran (Carrefour)             | Sevran               | Les Beaudottes

[ced]

Pour le coup, je vous le déconseille. Si vous faites ça, vous enfreignez la première forme normale, avec plein de conséquences possibles en termes de performances et de recherches d'informations désastreuses.
Par exemple, si vous concaténez les quartiers, comment faire une recherche efficace sur un quartier particulier ?

ced

[vincedchart]

je n'avais pas encore prévu une selection postgresql définitive,

mais je pense a quelque chose comme

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

SELECT * from "idf.poi.ccial" WHERE commune = 'Paris' AND quartier LIKE 'Butte aux Cailles'

Like, ou contains...

[SQLpro]

Votre LIKE est parfaitement inutile.... En effet :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

SELECT * from "idf.poi.ccial" WHERE commune = 'Paris' AND quartier LIKE 'Butte aux Cailles'

Est strictement équivalent à :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

SELECT * from "idf.poi.ccial" WHERE commune = 'Paris' AND quartier = 'Butte aux Cailles'

Pour que le LIKE soit pertinent il faut y ajouter des jokers ("_", "%") et éventuellement le caractères d'échappement.
À me lire : https://sqlpro.developpez.com/cours/sqlaz/select/#L3-4

Par exemple :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

SELECT * from "idf.poi.ccial" WHERE commune = 'Paris' AND quartier = '%Butte%Caille%'

A +

[vincedchart]

ok. merci. je ne peux pas tester cette solution pour le moment, car je n'ai pas encore les cases concatenées.

j'aimerai bien tenter de toute manière car je n'ai pas trop d'autres idées pour le moment... et voir ce que ça donne

comment concaténer, comme ça ?

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
WITH croisement AS (
    SELECT a.id, b.iso3
    FROM "test.poi.barrest" a
    INNER JOIN "monde.admin.paystressimplifie" b ON a.geometry && b.geom AND ST_Intersects(a.geometry, b.geom)
)
UPDATE "test.poi.barrest" tpb
SET iso3 = iso3 + ',' + c.iso3 
FROM croisement c
WHERE tpb.id = c.id;

[escartefigue]

Attention aussi à la collation sensible ou pas à la casse, selon le cas, le résultat de quartier LIKE 'Butte aux Caill%' sera différent.

Exemple :

Code SQL :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
create table T1
      (  ID1   integer primary key
       , CH4   char(4)  
               character set latin1
               collate latin1_general_cs
               not null
      )
;
insert into T1(ID1, CH4) 
values (1, 'toto')
     , (2, 'TOTO')
     , (3, 'TOto')
;

Cette requête

Code SQL :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
 
select * from T1
where CH4 like 'TO%'
;

donne ce résultat



Alors que cette requête

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
select * from T1
where CH4 collate latin1_general_ci like 'TO%'
;

donne cet autre résultat

[vincedchart]

Visiblement je vais avoir ma liste

Pensez-vous que les LIKE avec les joker seront bien utiles concernant les noms de quartiers comme ceux de la liste, séparés par des virgules ?

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

SELECT a.name AS namecc, a.communes AS comm, string_agg(b.name, ', ') as nameq FROM "idf.poi.ccial" a INNER JOIN "fr.local.osmquartiers" b ON ST_DWithin(a.geom,b.geom,0.006) GROUP BY a.name, a.communes

retour :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
               namecc                |         comm         |                                                                  
                                            nameq                                                                              
 
-------------------------------------+----------------------+------------------------------------------------------------------
-------------------------------------------------------------------------------------------------------------------------------
--------------------------------
 Avenir (Carrefour)                  | Drancy               | Avenir Parisien
 Bay 1 Loisirs                       | Torcy                | Le Segrais
 Beau Sevran (Carrefour)             | Sevran               | Les Beaudottes
 Bobigny 2                           | Bobigny              | Cité Hector Berlioz, Cité Paul Éluard, Cité Paul Vaillant Couturi
er, Cité Pablo Picasso
 Carrousel du Louvre (Printemps)     | Paris                | 1er Arrondissement
 Charras                             | Courbevoie           | La Défense 2, La Défense 1, La Défense 5
 CNIT (Fnac)                         | Puteaux              | La Défense 3, La Défense 12, La Défense 9, La Défense 8, La Défen
se 6, La Défense, La Défense 4
 Évry 2 (Carrefour)                  | Évry                 | Le Canal, Droits de l'Homme
 Issy 3 Moulins (Auchan)             | Issy les Moulineaux  | Les Pucelles, Les Brillants
 Italie 2 (Printemps)                | Paris                | Butte aux Cailles, 13e Arrondissement, Les Gobelins
 Les 3 Fontaines (Auchan)            | Cergy                | Les Clos Tripiers, Les Arrangeons, Vc Qua Nouvelle Prefecture, Re
s des Bourgognes, Les Bourgognes, Les Marjoberts, Les Maheux, Les Plants Orange, Les Plants Bruns, Le Chemin Dupuis Brun, Cergy
 Préfecture, Le Chemin de Cergy
 Les Arcades (Carrefour)             | Noisy le Grand       | Maille-Horizon
 Marques Avenue L'Île-Saint-Denis    | L'Île Saint Denis    | Vieux Saint-Ouen
 Passy Plaza                         | Paris                | Passy
 Plein Air (E.Leclerc)               | Le Blanc Mesnil      | Cité Marcel Alizard
 Quai des Marques Franconville       | Franconville         | La Borne de Marbre, Bois de Saint-Marc
 St-Lazare Paris                     | Paris                | Quartier de l'Europe
 Terre-Ciel (Carrefour)              | Chelles              | Villeneuve
 Thiais Village (Ikea)               | Thiais               | Green Valley
 Val d'Yerres 2 (Cora)               | Boussy Saint Antoine | Les Vingt Cinq Arpents
 Westfield Forum des Halles          | Paris                | Beaubourg, Les Halles, Quartier des Halles
 Westfield Les Quatre Temps (Auchan) | Puteaux              | La Défense 8, La Défense, La Défense 4, La Défense 12, La Défense
 6, La Défense 9