Le portail francophone de la géomatique

Theos2000 · Mon 20 January 2020 10:14

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

Fichier(s) joint(s) :
Pour accéder aux fichiers vous devez vous inscrire.

Nicolas Ribot · Mon 20 January 2020 10:37

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

Djperou · Mon 20 January 2020 10:50

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,

Nicolas Ribot · Mon 20 January 2020 10:50

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

Nicolas Ribot · Mon 20 January 2020 10:53

Nicolas Ribot a écrit:

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

tumasgiu · Mon 20 January 2020 10:54

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)

tumasgiu · Mon 20 January 2020 11:06

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

Nicolas Ribot · Mon 20 January 2020 11:09

tumasgiu a écrit:

Un autre moyen sale :

Code:

SELECT 
    ('POINT' || replace(geoloc_etablissement, ',', ' '))::geometry('POINT', 4626)
FROM
    sirene
;

Ah oui joli

Nicolas Ribot · Mon 20 January 2020 11:10

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 )

tumasgiu · Mon 20 January 2020 11:14

Nicolas Ribot a écrit:

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/

Theos2000 · Mon 20 January 2020 12:06

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)

Nicolas Ribot · Mon 20 January 2020 12:13

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

Djperou · Mon 20 January 2020 14:30

Nicolas Ribot a écrit:

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.

Theos2000 · Mon 20 January 2020 15:17

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

Communauté	Ressources	L'emploi	Le marché	L'association	Fils RSS
Forum Blogs Wiki Planet Sites amis	Agenda Biblio Fiches AMO-CNIG Paris PCGIS Téléchargements	Offres d'emploi Banque de CV Formations Démarche Métiers	Geo-Entreprises Geo-Communiqué Appels d'offres	L'association Qui sommes-nous ? Nous aider Bienfaiteurs Partenaires Historique Statuts	Tout le site Job Géomatique Services GeoBlogs ...	Faire un don Mentions légales

Le portail francophone de la géomatique

Annonce

#1 Mon 20 January 2020 10:14

Sirene V3 open data soft extraire colonne localisation

#2 Mon 20 January 2020 10:37

Re: Sirene V3 open data soft extraire colonne localisation

Code:

#3 Mon 20 January 2020 10:50

Re: Sirene V3 open data soft extraire colonne localisation

Code:

#4 Mon 20 January 2020 10:50

Re: Sirene V3 open data soft extraire colonne localisation

Code:

#5 Mon 20 January 2020 10:53

Re: Sirene V3 open data soft extraire colonne localisation

Code:

Code:

#6 Mon 20 January 2020 10:54

Re: Sirene V3 open data soft extraire colonne localisation

Code:

#7 Mon 20 January 2020 11:06

Re: Sirene V3 open data soft extraire colonne localisation

Code:

#8 Mon 20 January 2020 11:09

Re: Sirene V3 open data soft extraire colonne localisation

Code:

#9 Mon 20 January 2020 11:10

Re: Sirene V3 open data soft extraire colonne localisation

Code:

#10 Mon 20 January 2020 11:14

Re: Sirene V3 open data soft extraire colonne localisation

Code:

#11 Mon 20 January 2020 12:06

Re: Sirene V3 open data soft extraire colonne localisation

#12 Mon 20 January 2020 12:13

Re: Sirene V3 open data soft extraire colonne localisation

#13 Mon 20 January 2020 14:30

Re: Sirene V3 open data soft extraire colonne localisation

Code:

#14 Mon 20 January 2020 15:17

Re: Sirene V3 open data soft extraire colonne localisation

Pied de page des forums