Conseil pour une requête SQL

invitea1400dd5 · 21/08/2011, 17h51

Bonjour,

J'ai récupéré une base de donnée avec les Cp, villes et position Gps.
Comme vous pouvez le voir si dessous, j'ai différents Cp pour la même ville et sur surtout la même position Gps.

Comment faire pour garder que l'officiel, ici par exemple :
31000 : Toulouse (43.6043,1.4437)
Et supprimer les autres de la base ?

Merci d'avance de aide,

Code:

31000 : Toulouse (43.6043,1.4437)
31001 : Toulouse (43.6043,1.4437)
31002 : Toulouse (43.6043,1.4437)
31003 : Toulouse (43.6043,1.4437)
31004 : Toulouse (43.6043,1.4437)
31005 : Toulouse (43.6043,1.4437)
31006 : Toulouse (43.6043,1.4437)
31007 : Toulouse (43.6043,1.4437)
31008 : Toulouse (43.6043,1.4437)
31009 : Toulouse (43.6043,1.4437)
31010 : Toulouse (43.6043,1.4437)
31011 : Toulouse (43.6043,1.4437)
31012 : Toulouse (43.6043,1.4437)
31013 : Toulouse (43.6043,1.4437)
31014 : Toulouse (43.6043,1.4437)
31015 : Toulouse (43.6043,1.4437)
31016 : Toulouse (43.6043,1.4437)
31017 : Toulouse (43.6043,1.4437)
31018 : Toulouse (43.6043,1.4437)
31019 : Toulouse (43.6043,1.4437)
31020 : Toulouse (43.6043,1.4437)
31021 : Toulouse (43.6043,1.4437)
31022 : Toulouse (43.6043,1.4437)
31023 : Toulouse (43.6043,1.4437)
31024 : Toulouse (43.6043,1.4437)
31025 : Toulouse (43.6043,1.4437)
31026 : Toulouse (43.6043,1.4437)
31027 : Toulouse (43.6043,1.4437)
31028 : Toulouse (43.6043,1.4437)
31029 : Toulouse (43.6043,1.4437)
31030 : Toulouse (43.6043,1.4437)
31031 : Toulouse (43.6043,1.4437)
31032 : Toulouse (43.6043,1.4437)
31033 : Toulouse (43.6043,1.4437)

invitea0ecda6e · 21/08/2011, 22h31

Code:

DELETE FROM ma_table WHERE code_postal != '31000' AND coordonnees != '(43.6043,1.4437)'

Il y a de fortes chances pour que le code ci-dessus soit faux mais il faut que tu nous donnes des infos sur ta table si tu veux quelque chose de plus précis.

invitea1400dd5 · 21/08/2011, 23h09

Merci de ta réponse encore une fois Tlaloc.
Je suis d'accord avec toi sur le principe de ta requette, mais le souci c'est que qu'il y a 300 000 lignes de code postal ...
Comment faire un "truc" automatique qui ne garderait que le code postal du genre : 31000 ou 24000 ou 12000 ... et supprimerait tous les autres 12011, 12012, 12014, ... qui ont les mêmes coordonnées gps.

Pour la table c'est celle utilisé ici :
http://x-zolezzi.developpez.com/tuto...utocompletion/

Code:

CREATE TABLE IF NOT EXISTS `cp_autocomplete` (
  `CODEPAYS` char(2) NOT NULL,
  `CP` varchar(10) NOT NULL,
  `VILLE` varchar(180) NOT NULL,
  `NOMADMIN1` varchar(100) NOT NULL,
  `CODEADMIN1` varchar(20) NOT NULL,
  `NOMADMIN2` varchar(100) NOT NULL,
  `CODEADMIN2` varchar(20) NOT NULL,
  `NOMADMIN3` varchar(100) NOT NULL,
  `CODEADMIN3` varchar(20) NOT NULL,
  `LATITUDE` double NOT NULL,
  `LONGITUDE` double NOT NULL,
  `ACURANCY` int(1) NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

invitea0ecda6e · 21/08/2011, 23h14

Si tu veux supprimer tous les codes postaux qui ne finissent pas par 000 (c'est bien ça ?), il me semble qu'il te suffit de faire :

Code:

DELETE FROM cp_autocomplete WHERE CP NOT LIKE '%000'

A voir en vidéo sur Futura · Aujourd'hui

invitea1400dd5 · 21/08/2011, 23h29

Non pas exactement, car certains code postaux comme 31000 et 31420 ont des coordonnées gps différentes. Je voudrai juste supprimer les codes postaux qui ont les mêmes coordonnées gps et ne garder que celui qui est l'officiel comme ici :
Garder que le 31000 (et le 31420 puisque coord. gps différents)

Code:

31000 : Toulouse (43.6043,1.4437)
31001 : Toulouse (43.6043,1.4437)
31002 : Toulouse (43.6043,1.4437)
31003 : Toulouse (43.6043,1.4437)
31004 : Toulouse (43.6043,1.4437)
31005 : Toulouse (43.6043,1.4437)
31006 : Toulouse (43.6043,1.4437)
31007 : Toulouse (43.6043,1.4437)

invitea0ecda6e · 21/08/2011, 23h42

Et pour deux codes postaux et coordonnes identiques, comment est-ce que tu detecte l'officiel? Le plus petit code postal?

invitea1400dd5 · 21/08/2011, 23h46

"L'officiel" c'est celui qui est du style xx000 si il existe sinon effectivement ça sera le plus petit ...

invitea0ecda6e · 22/08/2011, 04h46

Parmi les différentes entrées avec ville+latitude+longitude identiques, pour ne garder que celle qui a le plus petit code postal:

Code:

-- Création de la table temporaire
create table cp_autocomplete_copy like cp_autocomplete;
insert into cp_autocomplete_copy (select * from cp_autocomplete);

-- Suppression des valeurs
delete from cp_autocomplete where (cp, ville, latitude, longitude) not in (
    select min(cp), ville, latitude, longitude from cp_autocomplete_copy group by ville, latitude, longitude
);

-- Suppression de la table temporaire
drop table cp_autocomplete_copy;

Il y a surement plus simple mais bon, je ne suis pas très inspiré et ça fait le travail

**danyvio** · 22/08/2011, 08h22

Envoyé par Tlaloc

Code:

DELETE FROM ma_table WHERE code_postal != '31000' AND coordonnees != '(43.6043,1.4437)'

Il y a de fortes chances pour que le code ci-dessus soit faux mais il faut que tu nous donnes des infos sur ta table si tu veux quelque chose de plus précis.

En effet la requête ci dessus est violente, car ensuite il ne restera plus que 31000 et 43.6043,1,4437. Donc adieu Paris, Lyon et TOUTES les autres villes... sauf Toulouse...

invitea1400dd5 · 22/08/2011, 11h24

Impeccable Tlaloc c'est exactement ça en effet.
Seul "hic" lors de la requette la plus importante celle qui supprime les lignes des cp ...
La requette prend apparement trop de temps :

Code:

Query execution was interrupted

J'ose pas faire :

Code:

delete from cp_autocomplete where (cp < "40000", ville, latitude, longitude) not in (
    select min(cp), ville, latitude, longitude from cp_autocomplete_copy group by ville, latitude, longitude);

Et ainsi de suite ... en changent cp < "xxx" jusqu'au dernier cp

**danyvio** · 22/08/2011, 19h26

Perso, je créerais une table temporaire, en plusieurs étapes si nécessaire, contenant exactement ce qu'il faut supprimer, puis faire une requête du style :
delete from autocomplete where (cp etc... in select * from temporaire)
Ci dessus syntaxe très approximative.

invitea0ecda6e · 22/08/2011, 22h29

Code:

delete from cp_autocomplete where (cp < "40000", ville, latitude, longitude) not in (
    select min(cp), ville, latitude, longitude from cp_autocomplete_copy group by ville, latitude, longitude);

Ca ne marchera pas... Essaye ça:

Code:

delete from cp_autocomplete where cp < '40000' and (cp, ville, latitude, longitude) not in (
    select min(cp), ville, latitude, longitude from cp_autocomplete_copy group by ville, latitude, longitude);

PS: Evite les double quotes dans les requêtes sql

invitea1400dd5 · 23/08/2011, 19h28

Bon j'ai fais pas mal d'essais du style :

Code:

delete from cp_autocomplete where cp < '02000' and cp > '01001' and (cp, ville, latitude, longitude) not in (
    select min(cp), ville, latitude, longitude from cp_autocomplete_copy group by ville, latitude, longitude);

Impossible que la requete se termine correctement !
Réponse : #1317 - Query execution was interrupted

J'aime bien ton idée "danyvio" de créer un table qui contient exactement ce qui doit être supprimé, je teste ...

**danyvio** · 23/08/2011, 20h06

Il se peut que tes tentatives de requêtes se plantent because en faisant un croisement de deux tables identiques de grande taille T tu crées en mémoire un produit cartésien de taille = T² insupportable par ton système.

Il est d'ailleurs inutile de croiser une table et sa copie intégrale....

invitea1400dd5 · 23/08/2011, 20h11

Je viens de tester la requete suivante :

Code:

insert into cp_autocomplete_sup (select * from cp_autocomplete where 
(cp, ville, latitude, longitude) not in (
    select min(cp), ville, latitude, longitude from cp_autocomplete group by ville, latitude, longitude)
);

Après avoir bien entendu créer la base cp_autocomplete_sup de structure identique à cp_autocomplete.

Et forcement j'ai un retour d'erreur ...
#1317 - Query execution was interrupted

Comment procéder autrement ?
Merci d'avance

invitea1400dd5 · 23/08/2011, 20h17

Dsl danyvio j'avais pas vue ta réponse ...
Comment je pourrais faire pour alléger la requete ? Quitte faire le travail en plusieurs étapes.
Pour info je réalise mes requetes en ligne sur le serveur de ovh.

**danyvio** · 24/08/2011, 13h31

Envoyé par squal31

"L'officiel" c'est celui qui est du style xx000 si il existe sinon effectivement ça sera le plus petit ...

Le "xx000" s'il existe sera obligatoirement le plus petit...

invitea1400dd5 · 24/08/2011, 14h09

Effectivement, ... oui ...

**polo974** · 24/08/2011, 15h11

Envoyé par danyvio

Le "xx000" s'il existe sera obligatoirement le plus petit...

pas obligatoirement:
les dom sont sur 3 chiffres pour le département...

export de la base avec un order by cp
vidage de la base, ajout d'une contrainte de clé sur latitude, longitude
injection de l'export (avec plein d'erreur...)

éventuellement suppression de la clé.

autre solution:
export:
select distinct on (latitude,longitude,cp) ...(liste des champs)... from cp_autocomplete order by cp;

vidage, réinjection sans erreurs...

sinon, c'est:
delete from cp_autocomplete where not (latitude,longitude,cp) in select distinct on (cp) latitude,longitude,cp from cp_autocomplete order by latitude,longitude,cp);
mais c'est monstrueux...

**polo974** · 24/08/2011, 15h35

oups, les 2 dernières pas bonnes, il y a cp en trop dans le distinct et sans, ça ne passe pas...

donc reste la clé et les erreurs....

invitea1400dd5 · 24/08/2011, 15h37

Bon la je suis perdu ...
Comment tu ajoute une contrainte de clé sur latitude, longitude par rapport au Cp ?

**polo974** · 25/08/2011, 07h12

un truc du genre:

Code:

ALTER TABLE ONLY cp_autocomplete ADD CONSTRAINT meme_coord UNIQUE (latitude, longitude);

invitea1400dd5 · 29/08/2011, 20h16

Bonsoir,

Je me permet de relancer mon post par rapport à ma requette sql ...
Tous mes essais sont des échecs ...

Si quelqu'un a une idée, je suis grandement preneur !
Merci d'avance,

invitea0ecda6e · 29/08/2011, 22h37

Le plus simple serait peut-être de mettre tout ce que tu veux récupérer dans une nouvelle table:

insert into nouvelle_table (select * from cp_autocomplete where blah blah ...)

Je te laisse chercher un peu

invitea1400dd5 · 30/08/2011, 16h48

Encore merci pour ton aide Tlaloc,

Je re-fais signe dès que j'ai la solution ...

invitea1400dd5 · 30/08/2011, 19h30

Bon je viens de réussir, mais pas par la voie du langage SQL mais en php/Mysql.
Pour certains ça va être des kms de ligne, alors qu'on aurait pu le faire en 2 lignes sql certainement ...
En tout cas ça à fonctionné ...

Merci encore à tous ceux qui m'ont aidé et mis sur la bonne voie
(Surtout pour la requête : group by)

Pour ceux qui serait dans le même cas que moi voici le script :

Code:

// Sélection des Cp unique en fct de leur position Gps
// ---------------------------------------------------
	
	// Création d'une table SQL temporaire
	$rq = 'create table cp_autocomplete_copy like cp_autocomplete';
	$result1 = mysql_query($rq) or die (mysql_error());

	
	// Sélection des Cp unique par leur position Gps
	$rq = '	select CODEPAYS, min(CP), VILLE, NOMADMIN1, NOMADMIN2, CODEADMIN2, LATITUDE, LONGITUDE , ACURANCY
			from cp_autocomplete 
			group by VILLE, LATITUDE, LONGITUDE';
	
	$result1 = mysql_query($rq) or die (mysql_error());
	
	while ($data = mysql_fetch_array($result1))
		{
			extract($data);
			
			$fr=$data['CODEPAYS'];
			
			$cp=$data['min(CP)'];
			$ville=$data['VILLE'];
			
			$nom1=$data['NOMADMIN1'];
			$nom2=$data['NOMADMIN2'];
			$cd=$data['CODEADMIN2'];
			
			$latt=$data['LATITUDE'];
			$long=$data['LONGITUDE'];
			
			$ac=$data['ACURANCY'];
			
			// Affichage un par un
			echo ''.$fr.' -- '.$cp.' : '.$ville.' - '.$nom1.' / '.$nom2.' / '.$cd.' -('.$latt.','.$long.') - '.$ac.'<br />';
			
			// Insertion dans la table temporaire chaque Cp unique en fct de la position Gps
			$rq2 = 'INSERT INTO cp_autocomplete_copy
       				VALUES ("'.$fr.'", "'.$cp.'", "'.$ville.'", "'.$nom1.'", "'.$nom2.'", "'.$cd.'", "'.$latt.'", "'.$long.'", "'.$ac.'")';
			$result2 = mysql_query($rq2) or die (mysql_error());
			
		}
		
		
	// Vidage de la table cp_autocomplete
	$rq = 'TRUNCATE cp_autocomplete';
	$result1 = mysql_query($rq) or die (mysql_error());
	

	// Copiage de la table Sql temporaire dans la cp_autocomplete
	$rq = 'insert into cp_autocomplete (select * from cp_autocomplete_copy)';
	$result1 = mysql_query($rq) or die (mysql_error());
	
	
	// Suppression de la table Sql temporaire
	$rq = 'drop table cp_autocomplete_copy';
	$result1 = mysql_query($rq) or die (mysql_error());

Conseil pour une requête SQL

Conseil pour une requête SQL

Re : Conseil pour une requête SQL

Re : Conseil pour une requête SQL

Re : Conseil pour une requête SQL

Re : Conseil pour une requête SQL

Re : Conseil pour une requête SQL

Re : Conseil pour une requête SQL

Re : Conseil pour une requête SQL

Re : Conseil pour une requête SQL

Re : Conseil pour une requête SQL

Re : Conseil pour une requête SQL

Re : Conseil pour une requête SQL

Re : Conseil pour une requête SQL

Re : Conseil pour une requête SQL

Re : Conseil pour une requête SQL

Re : Conseil pour une requête SQL

Re : Conseil pour une requête SQL

Re : Conseil pour une requête SQL

Re : Conseil pour une requête SQL

Re : Conseil pour une requête SQL

Re : Conseil pour une requête SQL

Re : Conseil pour une requête SQL

Re : Conseil pour une requête SQL

Re : Conseil pour une requête SQL

Re : Conseil pour une requête SQL

Re : Conseil pour une requête SQL

Discussions similaires

Requete SQL

synthaxe requète sql

requête SQL

logiciel et requete sql

Requete SQL