SIG Et @

SIG & Aménagement du Territoire & internet

Import DBF/CSV to PostGIS using ogr2ogr

Recently, I read a post from Nathaniel Kelso about some behaviours of PostGIS he want to be improved. Some days after, Paul Ramsey, one of the core PostGIS commiters made an answer about this.

Both of them, are using for importing data from DBF/CSV with location information a manual table creation, a csv import and a geometry creation similar to

I use another way I wanted to share.

I rely on ogr2ogr, an utility from the library ogr/gdal and the virtual table format (a way to wrap other format in order to make some custom things)

Be careful, we’re using version 1.9.1 the latest stable release of ogr/gdal, some old binaries will not stand all options of our example.

The CSV example


  • a myfile.csv file
48.1,0.25,"First point"
49.2,1.1,"Second point"
47.5,0.75,"Third point"
  • a myfile.vrt file
    <OGRVRTLayer name="myfile">
        <GeometryField encoding="PointFromColumns" x="Longitude" y="Latitude"/>
  • and ogr2ogr command line (we custom it look like shp2pgsql default id to gid and geometry to the_geom)
ogr2ogr -f "PostgreSQL" -lco GEOMETRY_NAME=the_geom -lco FID=gid PG:"host=localhost user=postgres dbname=mydb password=mypassword" myfile.vrt -nln mytable

This below mean import from myfile.vrt (this file point to your mycsvfile.csv) into a PostgresSQL table named mytable using a geometry name set to the_geom and the id set to gid. The PostgreSQL connection is defined into PG:"…"

The DBF example

It’s the same instructions except:

  • you create a DBF file using same structure (simple as a cut & paste of csv into LibreOffice or OpenOffice and a save as dbf)
  • you replace your myfile.vrt file with
    <OGRVRTLayer name="myfile">
        <GeometryField encoding="PointFromColumns" x="Longitude" y="Latitude"/>
  • reload the same command line changing only option for overwriting previous csv table already loaded
ogr2ogr -f "PostgreSQL" -overwrite -lco GEOMETRY_NAME=the_geom -lco FID=gid PG:"host=localhost user=postgres dbname=mydb password=mypassword" myfile.vrt -nln mytable

Small goodies

ogr2ogr related

The way we import is raw, the input is default to string but with VRT, you can custom input type for columns (default is string) See the fields part of the vrt doc and the example "Example: Renaming attributes" Be cautious, I haven’t try with DBF to set custom type

In our example, the ogr2ogr option "-nln mytable" can be forgotten if you don’t need to custom the output table name

Another great ressource (outside of the official doc) if you want to dive into ogr2ogr further is BostonGIS ogr_cheatsheet

For continuing DBF example, I borrow them this example

ogr2ogr -f "PostgreSQL" PG:"host=myserver user=myusername dbname=mydbname password=mypassword" sometable.dbf -nln "sometable"

shp2pgsql related

Following another Kelso question concerning make default some options in shp2pgsql, another tip is to use functions in bash (only available in Unix-like systems)

So put in ~/.bashrc

shp2pgsql_im () {
  shp2pgsql -dID $@

reload ~/.bashrc

source ~/.bashrc

Now shp2pgsql_im the new command with default options added to shp2pgsql

Hope you enjoy this small contribution

Orthophotos de Nantes pour OSM

Récemment, nous avons vu passer une annonce sympathique de Libertic qui annonçait qu’un contributeur (Tangui Morlier de l’April pour ne pas le citer) avait récupéré les url des raster des orthophotos de la ville de Nantes qui datent de 2005. Au final, de fil en aiguille, cela nous a amené à créer un WMS pour permettre aux contributeurs nantais d’OSM d’améliorer la couverture des données sur Nantes. Suivez le guide même si tout n’est pas détaillé :)

Installer NodeJS sous Ubuntu

Installer Node sous Ubuntu

sudo apt-get install python-software-properties
sudo add-apt-repository ppa:chris-lea/node.js
sudo apt-get update
sudo apt-get install nodejs

Cela permet d’installer la version courante de Node JS sur la version stable d’Ubuntu.

Si vous souhaitez pouvoir compiler des modules C++ Node, n’oubliez pas d’installer les paquets de dev avec:

sudo apt-get install nodejs-dev

Installation d’Octopress

Octopress est un framework utilisant Jekyll, le générateur de blog statique utilisé sur les pages Githubs Il vous évite d’avoir un hébergement compliqué. Le dynamisme est obtenu côté client. Vous pouvez rajouter la gestion de Twitter, de Github, de StackOverflow. Pour les commentaires ,vous passez par Disqus, un service tiers ou par une application comme Juvia. Ce post vous permettra d’aborder rapidement l’installation d’Octopress.