OSGEO – Page 25 – GISforThought

Natural Earth provides some of the best data for large scale mapping. It is clean, accurate, extensive, at a number of different scales, and best of all free.

To load the data it into PostGIS (PostgreSQL) we will use the vector tools provided in GDAL. Mainly ogr2ogr.

After downloading the data. I went for all of the vector data in ShapeFile format. First I need to generate a list of datasets and their respective file paths. This will be put into a spreadsheet and the command to load the data will be applied to each line, and finally it will be run using a shell script. Setting up a PostGIS database is covered in my previous post.

My Natural Earth data consisted of:

28 directories, 1472 files

So a little automation is needed. Interestingly there were also a few .gdb files “ne_10m_admin_1_label_points.gdb”. Those we can look into at a later date.

To begin:

ls > my_contents.txt

Produced a decent result, but not quite what I was looking for.

sudo apt-get install tree

tree > natural_earth.txt

Was much better, although with a bit more tuning I’m sure ls would have achieved a better result.

After a bit of work in the spreadsheet, I had what I wanted. Perhaps not the most elegant solution, but certainly enough for my purposes.

Now for the ogr2ogr command:

ogr2ogr -nlt PROMOTE_TO_MULTI -progress -skipfailures -overwrite -lco PRECISION=no -f PostgreSQL PG:"dbname='natural_earth' host='localhost' port='5432' user='natural_earth' password='natural_earth'" 10m_cultural/ne_10m_admin_0_antarctic_claim_limit_lines.shp

Ogr2ogr is a file converter, which does so much more. In this case we are converting the ShapeFiles into tables in a PostGIS database. Essentially you want to copy the beginning part of the command in front of the files you want to load, changing only: “10m_cultural/ne_10m_admin_0_antarctic_claim_limit_lines.shp” .

Our settings:

-nlt PROMOTE_TO_MULTI | Loads all out files as if they were multi-part polygons. This means that a multi-part polygon wont fail the loading. This is a PostGIS requirement.

-progress | Shows a progress bar.

-skipfailures | Will not stop for each failure.

-overwrite | Overwrites a table if there is one with the same name. Our tables will be called whatever the ShaeFile is called since we are not specifying a name.

-lco PRECISION=no | Helps keep numbers manageable, especially with this data where precision is not important.

-f PostgreSQL PG:”dbname=’DatabaseName’ host=’IpAddressOfHost’ port=’5432′ user=’Username’ password=’Password'” | Details of the database where we are connecting to.

Now we are ready to run the commands. While ogr2ogr commands can be pasted straight into the terminal, for this task that is not really feasible. So we can create a simple shell script.

Copy the commands into a file and then:

sh your_commands

Finally there was one final error, with ne_10m_populated_places.shp. This was due to encoding. The encoding for the ogr2ogr tool can be changed from UFT8 to LATIN1 using:

export PGCLIENTENCODING=LATIN1;

After which the file loaded swimmingly.

Now for some mapping.

Thanks to:

http://unix.stackexchange.com/questions/92387/pasting-into-terminal-deteriorating?noredirect=1#comment139520_92387

http://lists.osgeo.org/pipermail/gdal-dev/2009-May/020771.html

I have just ordered a new home server to store all of my GIS data, so I have put together a quick guide on setting up PostGIS on Linux Mint, setting it up on Ubuntu will be very similar.

PostgreSQL come pre-installed on most Linux distros, so we don’t have to worry about installing it. However we do need to change the password for the default ‘postgres’ user. This can be done with the following:

sudo -u postgres psql

     postgres=> alter user postgres password 'apassword';

     postgres=> create user yourusername createdb createuser password 'somepass';

     postgres=> \q

The command sets the password for the root user ‘postgres’ and sets up your own user account with privileges to create databases and users.

If you get a response of:

sudo: unknown user: postgres

sudo: unable to initialise policy plug-in

Then PostgreSQL is not installed, but can be done using:

sudo apt-get install postgresql

To create a database we then simply run:

createdb nameofdatabase

In my case it was natural_earth. PostGIS is an add-on to a PostgreSQL database that that makes it spatially enabled. To install PostGIS we can launch pgAdmin, which is used to administer PostgreSQL database. As we can see we have the default ‘postgres’ databse and the one just created ‘natural_earth’.

Adding PostGIS to the database we created should be as easy as running the following SQL command in the ‘natural_earth’ database:

CREATE EXTENSION postgis;

Unfortunately while PostgreSQL is automatically installed, PostGIS is not. So it needs to be installed through the Software Manager:

If you cannot find PostGIS it may be due to a missing personal repository, UbuntuGIS. It is a PPA (Personal Package Archive). In Software Manager it can be added from Edit> Software Sources> PPAs> Add a new PPA> ppa:ubuntugis/ppa.

With PostGIS installed we will try the command again: