Variables d'environnement

Pour utiliser ogr2ogr, psql... Si QGIS est installé en stand-alone :

 

GDAL_DATA = C:\QGIS[version]\share\gdal
GDAL_DRIVER_PATH = C:\QGIS[version]\bin <em>ou </em>C:\QGIS[version]\bin\gdalplugins

Éventuellement :

PROJ_LIB = C:\Program Files\QGIS[version]\share\proj

Dans la variable système PATH :

C:\Program Files\QGIS 3.20.3\bin
C:\Program Files\MySQL\MySQL Server 5.6\bin
C:\Program Files\PostgreSQL\13\bin

 

Si QGIS est installé via OSGeo (mais j'ai constaté des bugs, préférez l'installation classique de QGIS en stand-alone) :

GDAL_DATA = C:\OSGeo4W\share\gdal
GDAL_DRIVER_PATH = C:\OSGeo4W\bin\gdalplugins

 


Installer GDAL

Sous Windows 10.

https://sandbox.idre.ucla.edu/sandbox/tutorials/installing-gdal-for-windows

Ensuite je l'utilise via PowerShell (depuis un dossier, Alt+f+r).

Vérifier que GDAL est correctement installée

ogr2ogr.exe --version[/code]

 


OGR2OGR

Convertir un GeoJson en Shape

Dans le cas d'un GeoJson bien propre d'un seul type de géométrie :

ogr2ogr -f "ESRI Shapefile" "OSMSportFR.shp" "OSMSportFR.geojson" -skip-failures[/code]

Convertir un Shape en WKT, tout en filtrant les données (SQL)

ogr2ogr -f CSV "OSMSportFR.csv" "OSMSportFR.shp" -sql "SELECT id, name, shop FROM OSMSportFR WHERE name LIKE '%Decathlon%'" -lco GEOMETRY=AS_WKT

Convertir un TAB (MapInfo) en WKT (CSV)

PowerShell

ogr2ogr -f CSV "PLU_57019_Amnéville_PLU_Nathalie.csv" "PLU_57019_Amnéville_PLU_Nathalie.tab" -lco GEOMETRY=AS_WKT

Convertir un TAB (MapInfo) en WKT (CSV) et le placer dans un dossier à côté

ogr2ogr -f csv NouveauDossier global.tab -lco separator=tab -lco geometry=as_wkt

Si on veut renommer directement le fichier en TXT, toujours depuis PowerShell :

rename-item "global.csv" "global.txt"

Obtenir des infos sur une couche shape

ogrinfo mon_shape.shp -so

Obtenir des infos sur une couche TAB (MapInfo)

ogrinfo mon_tab.tab -so mon_tab

 

 


Changer la projections

Convertir un TAB (MapInfo) en WKT (CSV) en changeant la projection

ogr2ogr -f CSV "NommezMonCSV.csv" "MonTAB.tab" -lco GEOMETRY=AS_WKT -s_srs EPSG:4326 -t_srs EPSG:2154

Convertir un TAB (MapInfo) en Shape en changeant la projection

ogr2ogr -f "ESRI Shapefile" "NommezMonShape.shp" "MonTAB.tab" -s_srs EPSG:4326 -t_srs EPSG:2154

 


Avec Postgres

Importer des shapes dans Postgres

Tout d'abord, en local, j'ai modifié mon fichier de configuration (C:\Program Files \ PostgreSQL \ 13\data \ pg_hba.conf) en ajoutant trust (au lieu de scram-sha-256) :

...
# IPv6 local connections:
host all all ::1/128 trust
# Allow replication connections from localhost, by a user with the
...

Car j'avais un problème d'authentification... Ensuite :

shp2pgsql -s 4326 "PLU_57019_Amnéville_PLU_Nathalie" public.plu_test | psql -h localhost -p 5432 -d test -U postgres

Désagréger un WKT multi-type et multi-partie en ligne de commande

  • Suppose une table PostGIS de type doc_graph_objets_0_import en 4326, dans une base test.
  • S'exécute dans CMD (pas PowerShell).
  • Vérifier chemins et noms de fichiers lignes 2 et 6.
  • Ne pas oublier d'exécuter aussi la dernière ligne (CMD...).
  • Vérifiez les noms de champs, qui doivent être présents dans le fichier d'import.
  1. psql -h localhost -p 5432 -d test -U postgres -c "DELETE FROM doc_graph_objets_0_import;"
  2. psql -h localhost -p 5432 -d test -U postgres -c "\copy doc_graph_objets_0_import (wkt, id_commune, id_doc_graph, desc1, desc2, code_couleur, legende, legende_ordre, idrow) FROM 'C:/_GH/0/global.txt' delimiter E'\t' csv header ENCODING 'WIN1252'"
  3. psql -h localhost -p 5432 -d test -U postgres -c "UPDATE doc_graph_objets_0_import SET geom = ST_GeomFromText(wkt);"
  4. psql -h localhost -p 5432 -d test -U postgres -c "DROP TABLE IF EXISTS doc_graph_objets_0_import_singlelized; CREATE TABLE doc_graph_objets_0_import_singlelized AS SELECT wkt, id_commune, id_doc_graph, desc1, desc2, code_couleur, legende, legende_ordre, idrow, contour_l, contour_c, (st_dump(geom)).geom AS geom FROM doc_graph_objets_0_import;"
  5. psql -h localhost -p 5432 -d test -U postgres -c "UPDATE doc_graph_objets_0_import_singlelized SET wkt = ST_AsText(geom);"
  6. psql -h localhost -p 5432 -d test -U postgres -c "\copy (SELECT wkt, id_commune, id_doc_graph, desc1, desc2, code_couleur, legende, legende_ordre, idrow FROM doc_graph_objets_0_import_singlelized) TO 'C:/_GH/0/global_singlelized.txt' (FORMAT CSV, HEADER TRUE, DELIMITER E'\t', ENCODING 'WIN1252');"
  • 1ère ligne : vide la table "doc_graph_objets_0_import" de Postgres
  • 2ème ligne : importe le fichier texte contenant le champ wkt dans Postgres table "doc_graph_objets_0_import" (attention encodage WIN1252 pour ANSI)
  • 3ème ligne : crée la géométrie dans le champ "geom" à partir de "wkt"
  • 4ème ligne : crée une table désagrégée "doc_graph_objets_0_import_singlelized"
  • 5ème ligne : met à jour le wkt dans le champ "wkt" à partir de "geom", dans la nouvelle table désagrégée
  • 6ème ligne : export

Créer un champ et l'indexer

psql -h localhost -p 5432 -d bdtopo -U postgres -c "ALTER TABLE batiment ADD COLUMN id_commune INTEGER;"
psql -h localhost -p 5432 -d bdtopo -U postgres -c "create index idx_commune on batiment (id_commune);"

Importer shape des communes dans Postgres

shp2pgsql -s 4326 "communes" public.communes | psql -h localhost -p 5432 -d bdtopo -U postgres

Exporter un dump sur une seule table

cd C:\Program Files\PostgreSQL\13
pg_dump -h localhost -p 5432 -U postgres -d MaBase --table public.MaTable > C:\FromPostgres.sql

Exporter un dump d'une seule table avec uniquement des INSERT (compatible MySQL)

cd C:\Program Files\PostgreSQL\13
pg_dump -h localhost -p 5432 -U postgres -d MaBase --column-inserts --data-only --table public.MaTable > C:\FromPostgres.sql

Ensuite pour le rendre compatible avec MySQL, il n'y aura qu'à surveiller le début et la fin du fichier, et à remplacer " public." :

powershell -Command "(gc 'FromPostgres.sql' -encoding "Default") -replace ' public.', '' | Out-File -encoding "Default" 'FromPostgres.sql'"

Et attention à l'encodage !

 


Avec MySQL

Vérifier connexion avec OGR2OGR

ogrinfo MYSQL:"cadastre_202010,user=root,port=3308"

Exporter en shape en filtrant avec SQL

ogr2ogr -f "ESRI Shapefile" "NommerMonShape.shp" MYSQL:MaBase,host=localhost,user=root,password='',port=3308 -sql "id_cadastre, surface, tag_voirie FROM cadastre where id_commune=14243" -s_srs EPSG:4326 -t_srs EPSG:4326

Exécuter un fichier SQL

cd C:\Program Files\MySQL\MySQL Server 5.6\bin
mysql -u root -p --port=3308 test < D:\Work\Imports\BDTOPO\ExportImport_ToMysql.sql

 

 

 

Pandas logoPandas is an excellent Python librarie to handle data. Barely started using it, it already solves some of my recurring needs. Matplotlib allows to create advanced charts and Openpyxl is very usefull to manage Excel files, read or generate them for example.

So here some tips for my memory and maybe for you.

Let's go learn data analysis with this 3 beautiful tools. First install or check Python and Pip, then the libraries.

pip install pandas
pip install matplotlib
pip install openpyxl

Liste de commandes non-exhaustive bien sûr... Mémo surtout propre à mon usage personnel sur MySQL et Postgres, attention dans vos manipulations.

De nombreuses commandes SQL sont valables sur les 2 systèmes, testez, lisez les messages d'erreur renvoyés et n'hésitez pas à faire des recherches textuelles sur l'intégralité de l'article.


Postgres/PostGIS

Connaître l'encodage d'un serveur Postgres

SHOW SERVER_ENCODING

Connaître l'encodage du client Postgres

SHOW CLIENT_ENCODING

Connaître le SRID des géométries

SELECT DISTINCT st_srid(geom) FROM TABLE

Calculer une surface en km2 en arrondissant  à l'entier

SELECT round(area(GEOM)/1000000) AS area FROM TABLE

Lister des noms de fichiers et répertoires

En PowerShell (Alt+F+R) dans le répertoire contenant les objets à lister :

get-childitem | select-object -expandproperty name

Et pour en exporter un fichier texte contenant la liste :

get-childitem | select-object -expandproperty name > liste.txt

Renommer un fichier

PowerShell

rename-item "NomOrigine.txt" "NouveauNom.txt"

Renommer un répertoire

CMD

rename Igo Igo2

Déplacer un répertoire

PowerShell

move Igo2 la

Supprimer un répertoire et son contenu/un fichier

PowerShell

Remove-Item Igo2

Écrire du texte dans un nouveau fichier texte

echo "Bonjour à tous les gavas !!!" > "Bonjour.txt"

 

 

Les expressions régulières (regex) sur Notepad++ sont très pratiques pour standardiser un fichier de données avant import par exemple, récupérer des données, corriger des chaînes particulières, avec des conditions, etc...

Ici quelques astuces propres à mon usage personnel.

MS Access is enought supported, so I would list only specific cases which took me more than 1 minute to solve.

Get the first letter from each word in upper case in a query (Title Case)

Get the first letter from each word in upper case and the remaining in lower case is easy with Excel (=PROPER), but with Access I searched for a long time ...

StrConv([field],3)

Get HTML from plain text in a query

Recover plain text from rich text in an Access query is easy (PlainText([field]), but the opposite is not!

Here an example to get the main HTML chars (not all!) from a field named First name:

firstname: Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace([First name],"è","&egrave;"),"ú","&uacute;"),"í","&iacute;"),"ć","&cacute;"),"á","&aacute;"),"é","&eacute;"),"ö","&ouml;"),"ç","&ccedil;"),"ş","&scedil;"),"ü","&uuml;"),"ó","&oacute;"),"ñ","&ntilde;"),"ë","&euml;"),"æ","&aelig;"),"à","&agrave;"),"ã","&atilde;"),"'","&apos;"),"ô","&ocirc;")

I know, I should use VBA ... Later!

Get the date today

Full time (13/09/2021 12:05:15)

today: Now()

Day only (13/09/2021)

today: Date()

Some formatting (2021 09 13 or 2021-09-13 12:13:16)

today: Format(Date(),"aaaa mm jj")
today: Format(Now(),"aaaa-mm-jj hh:nn:ss")