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.

Replacing a hard drive

Opening up a desktop computer and replacing a hard drive is not that difficult. It’s even likely that there is still a free hard drive bay to put the new hard drive. Replacing it on a laptop… That is a different story.

MacBook

The laptop of my girlfriend is a MacBook. I believe it’s the 2008 or 2009 model that came with a hard drive of just over 100 GB and by now it needed upgrading. The website of ifixit has some really great tutorials on how to fix and replace parts (mostly on Macs).

Replacing

I went to our local computer store to get a new 2.5″ SATA hard drive. For 500 GB of storage, I had to pay just over 50€. I’m sure you can find a better deal somewhere online, but I was quite happy with this one. It was a bit of a hassle to get all the right screw drivers, but with the help of the ifixit tutorial, everything went great. Replacing the hard drive didn’t take up much time.

Reinstall

Reinstalling the system, programs and documents however did take up a great deal of time. Off course we made sure to have a up to date time machine backup, but that was not enough.
The MacBook has OS 10.6 (Snow Leopard), while my iMac runs 10.8 (Mountain Lion). When my hard drive crashed some time ago, I could still boot from the attached time machine. Apparently this is a new feature that is not included in 10.6. So doing a quick recovery by booting from time machine was a no-go. Also a no-go: booting from the Snow Leopard install DVD. We do have an official DVD, it’s just that the DVD drive on the MacBook is broken.

Target mode

Luckily Macs come with a ‘target mode’ where you connect two Macs together over firewire and one Mac doesn’t boot, but sits as a FireWire external hard drive to e other Mac (I believe this can be a PC too). I tried to connect the MacBook in target mode, the timemachine as external USB and copy the contents of the latest backup to the Mackbook over the iMac. All seemed to get fine, until I restarted the MacBook. kernel panic.

The solution

One of the mistakes I made was not using the 10.6 DVD to restore from timemachine (I either used the 10.7 DVD or running 10.8). My solution in the end was to use my iMac as a external DVD drive for the MacBook:

  1. insert the 10.6 DVD into the iMac
  2. boot the iMac in target mode
  3. connect timeMachine to MacBook
  4. boot MacBook over FireWire from the 10.6 DVD in the iMac
  5. restore MacBook from timeMachine

Debian on beaglebone

When it comes down to linux distributions, debian is my favourite. I believe it is one of the oldest out there and it has proven its worth. It is also said that debian is rocksolid. I really like that you can start with a minimal setup and add what you need instead of remove what you don’t need.

How to get debian on the beaglebone? Normally you would make a bootdisc and continue over netinstall. The beaglebone doesn’t come with a CD drive, but it does have the microSD slot, which you can use to insert a bootable disk. Unfortunately there is a bug somewhere (I don’t know if it is a hardware issue, a software issue with the installer or whatever), which prevents you from using the ethernet with a netinstall. I was warned about this, but I was stubborn and tried anyway… and failed. So no clean netinstall…

Demo image

Luckily there are more people who wanted debian on their beaglebone and among those people are also some better skilled linux people. I followed the instructions under the “Demo image” on this website to get it working. In the final step on preparing the microSD card I had to make a small change to get it working:

sudo ./setup_sccard.sh --mmc /dev/sdX --uboot bone

instead of bone/bone_dtb. Or perhaps I misunderstood and I should have put in bone_dtb, but I’m not going to change it now anymore.

Previously I mentioned that what I liked debian is that you can add what you need. We now arrived to the adding part. The image already came with a bit of a setup (no need to install apache or sudo), but I did want to add a couple of things.

Update and upgrade

With the image ready, the ethernet connection works again. Login with the root account  The first thing we want to do is set the system clock and see if there are some debian upgrades waiting for us:

ntpdate pool.ntp.org
apt-get update
apt-get upgrade

The first line sets the date and time. I’m not sure if this is more of a convenience of if it is really necesarry. It doens’t hurt anyway to get the right date and time settings. The apt-get update command does not update your system. It updates the list with resources, packages, applications and so on. The third command (apt-get upgrade) checks this list of resources and if there are any new upgrades available, it’ll install them.

Add user

The demo image comes already with two users: root and debian. It is good practice not to log in with root for ‘normal’ things: you use your own account for that. Since my name is not debian, I make a new account:

useradd rob
passwd rob
mkdir /home/rob
chown rob:rob /home/rob
adduser rob sudo

The ‘useradd’ command does what is says: add an user named ‘rob’.  With the second line you add a password to rob. The third command is an abbriviation for ‘make directory’ with the directory name of ‘rob’ and it should be placed in ‘/home’. The ‘home’ directory is the place where all the users home directories go. Because we run this command as root, root gets set as owner of the directory. Since it is the home directory of rob, we need to transfer the ownership to that user by using ‘chown’ (change owner). There are two sets of arguments to this command. The first is ‘rob:rob’. The first ‘rob’ is the user and after the colon is the ‘group’. There is no need to worry much more about users and groups at this time (you can really fill up entire blogs about users, groups and permissions). The second argument for ‘chown’ is the directory path (or file) from which the ownership needs to be transferred. The last command adds the user rob to the sudo group. This allows the user rob to run a command as root. You’ll find this very userful as you want to control the GPIO on the beaglebone (which only works running it as root).

More installs

There are a couple more programs you’ll want to install:

apt-get install apache2
apt-get install ssh
apt-get install vim
apt-get install locate
apt-get install postgresql-9.1 postgresql-client postgresql-contrib
apt-get install postgis postgresql-9.1-postgis
apt-get install zip
apt-get install gcc
apt-get install build-essential

I believe apache already came installed on the demo image, so probably apt-get doesn’t do much with the first command. At some point you’ll find ‘ssh’ (secure shell) to be very useful to communicate with other computers. My favourite terminal text editor program is ‘vim’. It has a very steep learning curve, but it’s great once you learned it. With ‘locate’ you’ll have a nice tool to quickly search files and folders on your hard drive. After you installed it, run ‘updatedb’ to start the indexing. Afterwards you can find files and directories with ‘locate’ (e.g.: ‘locate www’ will show you all the files connected to ‘www’). Some people prefer mysql, but I rather work with ‘postgresql’. It is a database engine and I use it a lot at work, especially in combination with ‘postgis’. If you have never heard of SQL then it’s probably best to skip this and install it once you actually need it. I’m going to write another post on how to set up postgresql en postgis. You’ll need to install ‘zip’ to deal with zipfiles. It gives you the commandos to ‘zip’ and ‘unzip’ files. The last two commands are necessary to compile source code (once you run into ‘make’ and ‘make install’).

Firewall

You’ll probably want to setup a firewall as well. I’m totally not the expert here. I followed this blog to set it up: https://kura.io/2010/02/11/howto-iptables-on-debian-ubuntu/ The demo-image that I used already had iptables installed. I only added port 5432 for postgresql.

Desktop environment

So far we’ve been doing terminal stuff. Now comes the desktop environment that most people are familiar with. With linux there is a lot of choice (and there is a lot of fighting over which one is the best) in desktop environments. For the beaglebone I choose a lightweight window manager: lxde.

apt-get install task-lxde-desktop

With this command you’ll install the lxde desktop. Run this command and wait for a bit… well… actually quite a bit.

Once the install finishes, it is probably best to reboot:

reboot

The system goes down and when it comes back up: it’ll start a login screen where you log in to the graphical interface. You’ll see that with the install also the debiant version of ‘firefox’ was installed and that you now have ‘libreoffice’. Libreoffice is an office package to assist you with writing, spreadsheets, presentations etc.

Good to go?

Almost. I have changed the wallpaper to something nicer than the standard one. To change the wallpaper for the login screen, you’ll have to change this file:

/etc/lightdm/lightdm-gtk-greater.conf

There is a line somewhere that says: background=.... Fill in the file name of the image that you wnat to use as wallpaper and you’re set.

When you’re logged into the desktop environment and want to return to a terminal, you normally use a terminal emulator. In lxde that is: ‘Lxterminal’. You can change the settings of the appearance of the terminal, but in my case these changes weren’t saved after I quit the terminal. To make it save your changes, you’ll have to change the user rights of the configuration file:

chmod 777 ~/.config/lxterminal/lxterminal.conf

Now it saves your changes. Remember that the terminal emulator starts in a ‘shell’, not in a ‘bash shell’. To start a bash shell, type bash into the terminal. In another blogpost I will explain on how to use bash and how to set preferences for bash.

As a finishing touch, I also changed the hostname to BBB. You have to change two files:

/etc/hostname
/etc/hosts

In the hostname file, there is just one line with the hostname. I’ve changed this to BBB. The second file contains two lines, from which you’ll have to edit the last one. Replace the current hostname with the hostname you like. My file now looks like this:

127.0.0.1 localhost
127.0.1.1 BBB

GPIO

On of the reasons to get a beaglebone is to use the GPIO to control electronics. Adafruit made a really nice python library for it. Get it from http://learn.adafruit.com/setting-up-io-python-library-on-beaglebone-black. Follow the instructions for Ubuntu. At the last item in the tutorial I ran into a small problem, but that was easily fixed by running:

sudo easy_install -U distribute

and then redo the last step (sudo pip install Adafruit_BBIO).

The Adafruit library unfortunately doesn’t support the user LEDS on the beaglebone. So I wrote a python library myself. You can find it here: https://github.com/borrob/beaglebone_userled/. I’ll write a blogpost about this later as well.

Beaglebone arrives

Along with some other stuff I ordererd, my Beaglebone arrived a couple of days ago.  🙂

The thing that amazed me straight away is that is actually IS credit card sized. It is not just a marketing trick. I had a small tin box laying around (one of those mint boxes) and the beaglebone fits right in.

I hooked it up over USB to my computer, installed the drivers and I got started with cloud9 and bonescript. Runing the beaglebone over USB is not what I really plan to do that much, but it is good to know it is there when needed. My plan is to run the beaglebone more as miniPC, connected to the television in the living room. Therefore I also bought a 5V adapter and a miniHDMI to HDMI cable.  The power adapter in my case is strong enough to power the beaglebone, an USB hub, keyboard and mouse. I was afraid I should use a powered USB adapter, but luckily it turns out I don’t have to.

Our television is a ‘smart TV’ .The smart functionality actually came as an extra: we picked this TV for some other reasons and it happend to be a smart TV. I am actually not so much impressed by the smart capabilities of the TV, so I don’t feel bad taking its ethernet connection and giving it to the beaglebone.

I’m sure the developers of the Beaglebone have plenty of reasons to pick Angstrom as the main OS, but I prefer debian. Next up: installing debian.

startup

It’s time to blog.

Just the other day I have bought myself a beaglebone black (link). Why? Mostly for the fun of it. It is a €45,- mini computer, capable of running a full linux distribution and you can more or less use it as a home computer.  But that is not the main goal for me. I wanted a small and cheap computer like that to tinker with. (Please note: I’m a Mac-users for years now and the sad thing about Macs is that they just work: there is nothing to tinker with).

In the past I have done a couple of DIY electronics projects (not that fancy though) and I have done my share of programming. In my work as a noise specialist (link), I do a lot of data analysis, scripting and programming. Off course it happens that you’re getting stuck on some issue that you can’t fix straight away and then you’ll start googling for an answer. It is quite likely that you stumble upon a blog that offers the solution, or at least a direction to continue your quest. In short: I am grateful to all the other people out there that run a blog and thereby help me to fix my problem. This blog is my return gift to the community. I don’t expect my blog to offer a solution to all the people all of the time, but I would be happy if it helps some people some of the time.

 

The content of this blog will mainly govern technical stuff, ranging from beagleboard stuff, DIY electronics, programming and scripting, general computer things and everything else that has my interest. Don’t hold back on dropping me a line or leave a comment. Thank!

 

Rob.