#1 Mon 20 January 2020 10:14
- Theos2000
- Participant assidu
- Date d'inscription: 15 Jun 2015
- Messages: 221
Sirene V3 open data soft extraire colonne localisation
Bonjour a tout le monde et une bonne santé a tous les membres.
Je cherche dans la base sirene V3 issu de open data soft a construire un point a partir du x et Y qui sont dans la même colonne "geolocalisation de établissement" (48.539364,2.653041). Je cherche a construire 2 champs distincts par un substring qui s'arreterait a la ',' car le nombre de caractères change a chaque fois...auriez vous une idée sous postgres.
Merci a vous
Hors ligne
#2 Mon 20 January 2020 10:37
- Nicolas Ribot
- Membre
- Lieu: Toulouse
- Date d'inscription: 9 Sep 2005
- Messages: 1554
Re: Sirene V3 open data soft extraire colonne localisation
Bonjour,
Un moyen bien crad' (virer les parenthèses et fabriquer un tableau de valeurs en prenant ',' comme séparateur):
Code:
with tmp as ( select string_to_array(replace(replace(geoloc_etablissement, '(', ''), ')', ''), ',') as coords from sirene ) select coords[1] as latitude, coords[2] as longitude;
Pour fabriquer un point, il suffira de faire: st_setSRID(st_makePoint(coords[2], coords[1]), 4326)
Ca serait plus propre avec une expression régulière mais je n'y arrive pas
Nicolas
Hors ligne
#3 Mon 20 January 2020 10:50
- Djperou
- Juste Inscrit !
- Date d'inscription: 11 Mar 2014
- Messages: 6
Re: Sirene V3 open data soft extraire colonne localisation
Bonjour,
J'imagine que la colonne est en texte. Du coup tu peux utiliser split_part(<texte>,<délimiteur>,<colonne à renvoyer>) pour retrouver tes deux colonnes :
Code:
SELECT split_part( "geolocalisation de établissement" , ',' , 1 ) as latitude, split_part( "geolocalisation de établissement" , ',' , 2 ) as longitude FROM sirene_v3
Je ne sais pas comment tu veux utiliser cette info mais tu devras peut être utiliser la fonction CAST pour déclarer en real tes datas, ou encore utiliser ST_MakePoint() si tu veux les géométries PostGis de ces points.
J'espère que ça t'aidera
Bonne journée,
Hors ligne
#4 Mon 20 January 2020 10:50
- Nicolas Ribot
- Membre
- Lieu: Toulouse
- Date d'inscription: 9 Sep 2005
- Messages: 1554
Re: Sirene V3 open data soft extraire colonne localisation
Avec une regexp:
Code:
with tmp as ( select regexp_matches(geoloc_etablissement, '[-+]?[0-9]*\.?[0-9]+', 'g') as coords from sirene ) select coords[1] as latitude, coords[2] as longitude;
(je ne comprends rien à cette regexp, je l'ai trouvée sur le net )
Nicolas
Hors ligne
#5 Mon 20 January 2020 10:53
- Nicolas Ribot
- Membre
- Lieu: Toulouse
- Date d'inscription: 9 Sep 2005
- Messages: 1554
Re: Sirene V3 open data soft extraire colonne localisation
Bonjour,
Un moyen bien crad' (virer les parenthèses et fabriquer un tableau de valeurs en prenant ',' comme séparateur):Code:
with tmp as ( select string_to_array(replace(replace(geoloc_etablissement, '(', ''), ')', ''), ',') as coords from sirene ) select coords[1] as latitude, coords[2] as longitude;Pour fabriquer un point, il suffira de faire: st_setSRID(st_makePoint(coords[2], coords[1]), 4326)
Petite erreur: faut effectivement caster en float:
Code:
st_setSRID(st_makePoint((coords[2])::float, (coords[1])::float), 4326)
Nico
Hors ligne
#6 Mon 20 January 2020 10:54
- tumasgiu
- Membre
- Lieu: Ajaccio
- Date d'inscription: 5 Jul 2010
- Messages: 1160
Re: Sirene V3 open data soft extraire colonne localisation
Un autre moyen sale :
Code:
SELECT ( 'SRID=4326;POINT' || replace(geoloc_etablissement, ',', ' ') )::geometry('POINT', 4326) FROM sirene ;
Dernière modification par tumasgiu (Mon 20 January 2020 11:10)
Hors ligne
#7 Mon 20 January 2020 11:06
- tumasgiu
- Membre
- Lieu: Ajaccio
- Date d'inscription: 5 Jul 2010
- Messages: 1160
Re: Sirene V3 open data soft extraire colonne localisation
Avec une regexp:
Code:
with tmp as ( select regexp_matches(geoloc_etablissement, '[-+]?[0-9]*\.?[0-9]+', 'g') as coords from sirene ) select coords[1] as latitude, coords[2] as longitude;(je ne comprends rien à cette regexp, je l'ai trouvée sur le net )
Nicolas
[-+]? : zero ou une fois le symbole - ou +
[0-9]* : zero ou plusieurs symbole numérique
\.? : zero ou un symbole '.' (\ pour échapper ., qui signifie normalement : n'importe quel symbole)
[0-9]+ : au moins un symbole numérique
Hors ligne
#8 Mon 20 January 2020 11:09
- Nicolas Ribot
- Membre
- Lieu: Toulouse
- Date d'inscription: 9 Sep 2005
- Messages: 1554
Re: Sirene V3 open data soft extraire colonne localisation
Un autre moyen sale :
Code:
SELECT ('POINT' || replace(geoloc_etablissement, ',', ' '))::geometry('POINT', 4626) FROM sirene ;
Ah oui joli
Hors ligne
#9 Mon 20 January 2020 11:10
- Nicolas Ribot
- Membre
- Lieu: Toulouse
- Date d'inscription: 9 Sep 2005
- Messages: 1554
Re: Sirene V3 open data soft extraire colonne localisation
Nicolas Ribot a écrit:Avec une regexp:
Code:
with tmp as ( select regexp_matches(geoloc_etablissement, '[-+]?[0-9]*\.?[0-9]+', 'g') as coords from sirene ) select coords[1] as latitude, coords[2] as longitude;(je ne comprends rien à cette regexp, je l'ai trouvée sur le net )
Nicolas
[-+]? : zero ou une fois le symbole - ou +
[0-9]* : zero ou plusieurs symbole numérique
\.? : zero ou un symbole '.' (\ pour échapper ., qui signifie normalement : n'importe quel symbole)
[0-9]+ : au moins un symbole numérique
Merci.
chaque partie, ca va a peu près, mais de là à pouvoir l'écrire sans google, j'ai encore du taf )
Hors ligne
#10 Mon 20 January 2020 11:14
- tumasgiu
- Membre
- Lieu: Ajaccio
- Date d'inscription: 5 Jul 2010
- Messages: 1160
Re: Sirene V3 open data soft extraire colonne localisation
tumasgiu a écrit:Nicolas Ribot a écrit:Avec une regexp:
Code:
with tmp as ( select regexp_matches(geoloc_etablissement, '[-+]?[0-9]*\.?[0-9]+', 'g') as coords from sirene ) select coords[1] as latitude, coords[2] as longitude;(je ne comprends rien à cette regexp, je l'ai trouvée sur le net )
Nicolas
[-+]? : zero ou une fois le symbole - ou +
[0-9]* : zero ou plusieurs symbole numérique
\.? : zero ou un symbole '.' (\ pour échapper ., qui signifie normalement : n'importe quel symbole)
[0-9]+ : au moins un symbole numérique
Merci.
chaque partie, ca va a peu près, mais de là à pouvoir l'écrire sans google, j'ai encore du taf )
Si tu veux t'entrainer : https://regexcrossword.com/
Hors ligne
#11 Mon 20 January 2020 12:06
- Theos2000
- Participant assidu
- Date d'inscription: 15 Jun 2015
- Messages: 221
Re: Sirene V3 open data soft extraire colonne localisation
En fait la solution se trouve dans la fonction de postgres split_part, ça marche assez bien.
Aprés dans la suite du processus je n'arrive pas a transformer mes champs, ca me fait le message suivant syntaxe en entrée invalide pour le type numeric : « »
Le fait de caster des élement de coordonnées de texte en numérique qui bloque, et je ne trouve pas le moyen de les transformer (::real, ::numeric)
Hors ligne
#12 Mon 20 January 2020 12:13
- Nicolas Ribot
- Membre
- Lieu: Toulouse
- Date d'inscription: 9 Sep 2005
- Messages: 1554
Re: Sirene V3 open data soft extraire colonne localisation
En fait la solution se trouve dans la fonction de postgres split_part, ça marche assez bien.
Aprés dans la suite du processus je n'arrive pas a transformer mes champs, ca me fait le message suivant syntaxe en entrée invalide pour le type numeric : « »
Le fait de caster des élement de coordonnées de texte en numérique qui bloque, et je ne trouve pas le moyen de les transformer (::real, ::numeric)
Les différentes solutions marchent également, c'est juste un choix de votre part.
Le message d'erreur indique qu'une des valeurs est une chaine vide, non convertible en nombre.
Utilisez trim(), ou nullif() ou autre pour nettoyer les valeurs avant le cast.
Nicolas
Hors ligne
#13 Mon 20 January 2020 14:30
- Djperou
- Juste Inscrit !
- Date d'inscription: 11 Mar 2014
- Messages: 6
Re: Sirene V3 open data soft extraire colonne localisation
Theos2000 a écrit:En fait la solution se trouve dans la fonction de postgres split_part, ça marche assez bien.
Aprés dans la suite du processus je n'arrive pas a transformer mes champs, ca me fait le message suivant syntaxe en entrée invalide pour le type numeric : « »
Le fait de caster des élement de coordonnées de texte en numérique qui bloque, et je ne trouve pas le moyen de les transformer (::real, ::numeric)
Les différentes solutions marchent également, c'est juste un choix de votre part.
Le message d'erreur indique qu'une des valeurs est une chaine vide, non convertible en nombre.
Utilisez trim(), ou nullif() ou autre pour nettoyer les valeurs avant le cast.
Nicolas
Exactement, soit tu attribues des coordonnées arbitraires aux lignes comme le dit Nicolas, soit si tu veux tu peux les retirer de tes traitements en y ajoutant une condition comme
Code:
WHERE geolocalisation is not null
Ça dépend si tu as besoin de l'intégralité de la base où si tu peux te permettre d'exclure les établissements qui n'ont pas été géolocalisés.
Hors ligne
#14 Mon 20 January 2020 15:17
- Theos2000
- Participant assidu
- Date d'inscription: 15 Jun 2015
- Messages: 221
Re: Sirene V3 open data soft extraire colonne localisation
Merci pour tout ces éléments de réponse qui m'on bien aidé ! Je ne comprend pas pourquoi on a une disparité importante entre les les tables sirene qui proviennent de différents opérateurs, mais cela est un autre sujet ! Merci encore pour ces éléments
Hors ligne