Tag Archives: PostgreSQL

shp2pgsql

In a previous post I wrote about how to install debian on the beaglebone black. I installed the postgresql database with the postgis extension. The extension allows you to also work with geometric data. It’s powerful software that I use daily.

 MySQL vs postgresql

There are a couple of big battles in IT land: Internet explorer vs Mozilla, Mac vs windows, vim vs emacs, and there are more. MySQL vs postgresql is another one. It used to be that MySQL was fast but had less features and that postgresql was slow but had a huge list of features. In other blogs I read that nowadays there isn’t that much difference anymore: MySQL got its features extended and postgresql made some great speed improvements. I think MySQL still lacks one big feafture: spatial data. Yes, MySQL has a spatial extension, but it’s nowhere near what postgresql offers with postgis (at least… When I gave it a try some time ago).

Postgis

It takes a couple of steps to get postgis to work, but the normal install manual shouldn’t be too difficult. The normal apt-get procedure will do most of the work and then you have to load the postgis extensions to a database. There are plenty of other manuals and blogs on the internet that explain these steps, so I’m not going to repeat them. I am going to explain on how to get spatial data into your database.

Shape files

The standard file format to communicate data is with shape files. The format I was designed by ESRI years ago. The format consists of a couple of files that together make up the entire data. There is the .shp that contains the spatial data and all the table attributes are stored in the .dbf. Postgis stores everything in a single table. The spatial data is stored in a colum of type geometry and all the table attributes each has their own column.

Shp2pgsql

The easiest way to upload shape file into the database is by using shp2pgsql (although the latest update of QGIS also made it very easy). Again…you’ll find lots of tutorials on the internet, but I had to do some tweaking to get it to work on the beaglebone.

I assume you have successfully set up a postgresql database and installed the postgis extension. You can check this by logging into the database and then run:

Select postgis_full_version();

If you don’t get an error, but the installed version, then you know everything is fine.

You can now try to run the shp2pgsql tool. Perhaps you have more luck than me and it just works for you. Obtain a shape-file from somewhere and download it to your computer. Then run:

shp2pgsql __path_to_shape__ > shapeconversion.sql
less shapeconversion.sql

With less you can quickly view the file and if it worked, you’ll see the sql insert statements. If not, you might have the same issue as me. I got it to work by building the shp2pgsql tool myself (and since I was doing this anyway, I upgraded to postgis 2 at the same time.)

 

Build

 

Since postgis is open source, you can get a copy of all the source files and you can compile it yourself. Download the source from -here- (or some other mirror) and go into the directory. You can browse a bit through all the files.

Make sure you have all the right libraries installed. There must be a list somewhere which ones you need, but I got it together by trial and error: try to compile and just wait for the error messages from the compiler. If it says you need to install library x or ‘can’t find dependencies for library such and so’, then run:

sudo apt-get install __package__

 

To try and build postgis, make sure you are in the top level directory of the postgis source and run these commands:

make clean
./configure
Make
sudo make install

 Setup

If everything went well, you now have postgis2. Create a new database and install the extension (like you did with the previous postgis) and you’re done. You might want to use the command ‘locate’ to find the right sql files to insert the functions and types and so on.

Conversions

The shp2pgsql and pgaql2shp tools are in a subdirectory of the postgis source directory. You can use them from there, but it’s better to move them to /usr/bin.

Advertisements