Archive for the ‘Database’ Category

GIS in Everyday Life

October 15, 2010

If you take a look, where you can find a kind of a GIS in companies, the readers that have to do with GIS, will easily find a lot of spots. After you successfully integrated a GIS into your IT-structure, it can provide it’s data to several other applications, from spreadsheets to external databases.

But how about your everyday life? I’m sure, everyone of you had to do with GIS there in different ways. Sometimes active, somtimes passive. Let’s have a look. You’ll find yourselfe in one or the other situation, maybe without beeing aware by now.

Over the last few months I noticed that I’ve been asked for my zip-code several times during paying at the check-out. One of the most simple way to get basic data for geomarketing. Anonymous data provides a basic information to evaluate the catchment area of a discounter, so they can more precisely address their advertising. The results for every single discounter may vary. They can check the data with the portion they wanted to achieve. By precise advertising in areas with a lower portion they can try to reach more customers than before. If the discounter has access to demographic data, they could use it to reach regions with more spending capacity. You can refine these analysis to nearly every level of detail. The last one is an example for the passive contact with GIS.


OSM-Data in PostGIS and QGIS

June 18, 2010

Some time ago I had the idea of importing some of the data of OpenStreetMap (OSM) into a database — as they’ve become much better quality over the last few years — and find special objects systematically. This information could populate a databasetable and could be used by our sales department. 


As I use to work with Linux (Ubuntu), my description is related to such an environment. On my notebook I have a basic Ubuntu installed (with recent version Lucid, and a Gnome desktop).  I added PostgreSQL 8.4, PostGIS 1.4, OSM2PGSQL and QGIS 1.4 (from the standard repository of Ubuntu). To work in PostgreSQL with a GUI, I installed PGAdminIII in addition to this.  After installing the software you just have to tweak some things and soon you have a useable GIS with OSM-data. But let’s do it step by step …

Preparing the Database

After installing PostgreSQL and PostGIS, you have already activated the spatial extension for the databaseserver. In my case, I could use psql (textbased client for PostgreSQL) in a terminal immediately to connect to PostgreSQL, but not via PGAdminIII. In PGAdminIII they expect a password, which you have to set in psql at first.  So you go and open a terminal window in Linux (e.g. bash) and create a new databaseuser using createuser gisadmin (as far as you only test this case on a local PC, you can answer the question about the superuser with YES). After that you create a fresh database with the command createdb mygis. To avoid problems with PGAdminIII you should set the password for the user gisadmin right now. To do so, you connect to PostgreSQL by psql -d meingis and use the command \password gisadmin. Than you have to enter the new password twice (you’ll be asked to). Now you can disconnet from the server by \q.
Back in the terminal, you have to activate the procedural language in PostgreSQL by createlang plpgsql mygis. Now you have to find the files postgis.sql and spatial_ref_sys.sql in the filesystem. In my case they were found in /usr/share/postgresql/8.4/contrib. To really find them, it’s important not to have the packet postgis-common installed, but the one that is bundled by Ubuntu! The necessary tables for really using PostGIS in the database mygis will be created by pgsql -d mygis -f /usr/share/postgresql/8.4/contrib/postgis.sql and pgsql -d mygis -f /usr/share/postgresql/8.4/contrib/spatial_ref_sys.sql.

Detailed information about installing of PostGIS can be found here…

So, where’s the OSM-data …?

Well, at this point we have a fresh and clean GIS database, but without any data. Unfortunately you cannot use the webinterface of OpenStreetMap (OSM) ( to extract the data at once. You have to use the so called XAPI of OSM. In the beginning, I though about the XAPI as something very complicated just to get some data. But fortunately, this was completely wrong.  The only energy you have to invest is to define the area you want to extract and the objects you want to see in your database. Let me give you an example of an area in Oberhausen (Germany) where we first simply extract all data unfiltered. For a more complexe query you should have a look at this description.

I assume, that you don’t have the geographic coordinates (Lon/Lat) of the area you want to extract at hand. You need the lower left corner and and the upper right corner. To get them, you can open the webinterface of OpenStreetMap and navigate to the area you’d like to extract. Place the centre of the map to the lower left corner of the area and point the mouse to the right left corner of the map, wher you find the link ”Permalink”. At the bottomline of your browser you can see the link (permalink) with the value for Lon and Lat. Write down the complete number. Repeat this with the right upper corner.

Next step is to really get our data. You can either put this line into your browser and safe the returned data into data.osm,LAT-LU,LON-RO,LAT-RO. Or you can use the following command in your terminal window  wget,LAT-LU,LON-RO,LAT-RO -O data.osm. You certainly have to change the values after bbox= with the ones you have identyfied in your case. The resulting file can range from a few MB to a half GB, according to the area you’ve selected. If your file is empty — 0 kb — than you have to check the coordinates. This problem appears regulary …

Preparing the OSM-data

At this stage you still cannot use the OSM-data — but we will change this right now. We transfer the OSM-data (the file data.osm) into PostGIS using OSM2PGSQL. If you use the command for the first time you should use it like this:  osm2pgsql -c -d mygis -U gisadmin -W -H localhost data.osm. The parameter -c stands for creating the new tables (create) or to clean the old ones before adding the new data. This is followed by the name of the database, the name of the user, an option for a passwordrequest, the hostname for the databaseserver and the name of the OSM-datafile, which we want to transfer.
If this executes without an error — which usually are errors in the part in front of the filename — you can find your OSM-data in the mygis database. Besides OSM2PGSQL you could use OSMOSIS. I won’t describe this here, but you can find information about this via

Connecting PostGIS with QGIS

One or the other reader may find it useful, to analyse the OSM-data directly in the database with SQL. But the real strength compared to textbased datacollections is the visualization of the data in a map. As PostGIS finally is just a datacollection with a huge package of geospatial functions you cannot use it directly to display the data in a map. Certainly there is a great number of GIS that you could connect to PostGIS, but I usually care about QGIS. And ou can easily connect PostGIS with PostGIS.
Within the buttons at the top of QGIS you can find a blue barrel-like symbol with a ”plus” to add a PostGIS-layer. After clicking the symbol, a dialogue pops up. In this dialogue you have to add a database-connection if you use it for the first time. Click on the button ”New” and enter the required information (mygis, gisadmin, hostname aso.). After entering the information you should go and click on ”test connection”.  If this test is successful you can leave the dialogue with the ”OK”-button. If this test fails, you have to check the entered data. So, if you successfully created the new connection, you have to activate it by clicking on ”Connect”. In the lower area of the dialogue you will see the available tables with their names. There should be at least four tables with names beginning with planet_osm_… You can find lines (…_line), points (…_point), areas (…polygon) and roads (…roads). In my case, most of the roads were inserted into the table containing the lines and not among the roads. That’s just a note I wanted to add. After this you should click on all the tables in the list, and finally click on ”Add” to add them to QGIS.

Nice lines …

… don’t make an appealing map. If you haven’t used QGIS before you should maybe go through a tutorial to learn about the basic functions and configuration of the objects. As a quick howto, I can tell you this: click on a layername with the right mouse-button (e.g. planet_osm_line) and the properties of the layer will open. Besides the appearence of the object you can change the caption. If you activate the checkbox ”Show caption” you should select the field, which contains the info you want to display. I guess it is named ”name”, if you haven’t put your info into an extraordinary field. You should play around with textheight and colour. If you want a special layer to be placed over another layer you can easily drag and drop it in the list.

What’s next?

If you successfully went through all this, there will surely come some questions about special usecases in your mind. Let me tell you this: the OSM-data is much better quality than you think. But please don’t fool me, if there’s no data in the area you wanted to analyse: why haven’t YOU already collected the data?  OpenStreetMap is open to everyone, who can enrich OSM with his data. You can also simply place your question via email in my contact form (contactform). Maybe I can add a new entry in this blog to describe and solve your problem or I can assist you personally.

PostGIS (I) – central datapool

August 1, 2006

Well, I know from my GIS at work, that just viewing objects will not satisfy people for a long time. For that we should create our environment in a lasting way. If data should be available in a company network why not doing it with all the data for our objects and why not ‘really central’. PostGIS is a free extension for PostgreSQL which includes geospatial objecttypes and functions related to them. PostgreSQL is as free as PostGIS. At the same time it is very powerful database system that can keep up with the great commercial products (e. g. Oracle). That means, you can use PostgreSQL as a datapool for all listings and other kind of information in form of a listing, that different people should access at the same time. Centralising information is always a good idea. And if you didn’t knew it before: that is the idea behind a buisness warehouse!

You can install the PostgreSQL software on a Linux- or Windows-machine. I prefer Linux, because you can than reactivate a much more older machine, that you maybe have sort out before. Even for a good database performance you don’t have to use a dual-core pentium somewhat. A machine with 1GB or more memory and a processor with about 1GHz will do. Maybe in your special case less would also do. Try it! The harddisk can be chosen by the amount of information you will store in the database plus the operation system and some clearance.

Finally we start! First of allyou should fetch the actual PostgreSQL sourcecode from their homepage. Depending on your operation system you should follow the according installation instructions. The sourcecode is needed to install PostGIS. So please don’t use binary packages. They won’t do. If all requirements are fullfilled, compiling and installing PostgreSQL should be no problem. After that we do the same with PostGIS. During the compilation I had a problem with a shared object (‘’) that couldn’t be found. I searched for it and copied it into the PostgreSQL-lib-directory. After that it worked … If PostGIS is compiled, the SQL-statements to extend PostgreSQL would also be ready and you can run than like the installationinstructions tell you. Before you should have created a database in PostgreSQL, because you don’t extend the whole databse system, but a single instance of it: your dataspace. Now there is nothing more standing in your way, holding you from using your fresh created geospatial extension of PostgreSQL.

After that I had done the tests of the PostGIS FAQs to be sure that my database has got the extension and was amazed how simple you can access the geospatial data and functions. As a ‘help programmer’ there are a lot of SQL-statements coming into my mind, which I will test next time with my demo data.

An advice at the end: if you’d like to comfortably access PostgreSQL from a Windows-based machine, you should install PgAdmin III . It’s a kind of gui and admintool in one. And for all that cannot be devorced from MS Access there should be an ODBC-driver installed on their machines which can establish the neede connection to the PostgreSQL-backend.

And now you should do some first steps in QGIS connecting with PostGIS.