A few months back I published an article about the large scale deployment of QGIS and FOSSGIS at the state administration of Vorarlberg, Austria. Shortly after I published the article, they asked for the opportunity to update the article with more details. The article that follows below is that amended version. In 2011, the State... Read more »

This weekend I implemented a new feature for my ‘just for fun’ project osm-reporter. The feature implements timeline reporting for Open Street Map contributors. Its probably easiest to explain with a screenshot:     Here is another one showing a few charts together:   I added the feature because I wanted to see how many... Read more »

In 2011, the State of Vorarlberg, Austria became a new sponsor for the QGIS project. I was quite interested in the work they were doing as they are yet another great example of QGIS and FOSSGIS being used in an enterprise level setting. I arranged with Nikolaus Batlogg at the time to check in with... Read more »

If you even visited my lovely home town of Swellendam in the Western Cape of South Africa on http://openstreetmap.org, you might have noticed that the building footprints for the town are almost non-existent. Building footprints provide a valuable way to understand impacts of flood and other natural hazards, as well as being a valuable source... Read more »

Today I had a chance to do a quick interview with Anna Mason from MapAction.     The interview is an mp3 audio file recorded on my phone. My apologies if the sound quality isn’t the best. Click on the link below to download the mp3 file. Anna Mason MapAction Interview 23 May 2012  ... Read more »

During the Zurich QGIS hackfest we had some extended discussions about migrating our documentation away from LaTeX to sphinx because the latter offers a more approachable syntax for casual documentation writers and has good support for internationalisation via gettext. This week I am going to our first 2012 QGIS hackfest (to be held in Lyon,... Read more »

A nice article by Aslam Parker, Chief Directorate: National Geo-spatial Information, South Africa was published this month in our local GIS/Surveying magazine ‘Position IT‘ that describes the South African CRS. It probably would be of general interest to those trying to understand CRS concepts too. Direct link to the PDF here.

So this weekend I was trying to get into a mysql server I co-administer and nobody knew the root password anymore. Here is a short procedure to regain admin privileges to the database. I assign myself (rather than root user) admin privileges  so that I don’t interfere with any cron jobs etc. that may have been set up to take advantage of the root account.

Edit as root the /etc/init.d/mysql script and change the listing in the ‘start’ section from this:

case "${1:-''}" in
 'start')
 sanity_checks;
 # Start daemon
 log_daemon_msg "Starting MySQL database server" "mysqld"
 if mysqld_status check_alive nowarn; then
 log_progress_msg "already running"
 log_end_msg 0
 else
 /usr/bin/mysqld_safe > /dev/null 2>&1 &

Now change the last line listed above to look like this:

/usr/bin/mysqld_safe --init-file=/tmp/mysql-pwd-reset.sql > /dev/null 2>&1 &

The contents of the above script should look something like this:

CREATE USER 'foouser'@'localhost' IDENTIFIED BY 'somepassword';
GRANT ALL PRIVILEGES ON *.* TO 'foouser'@'localhost' WITH GRANT OPTION;
FLUSH PRIVILEGES;

Next start mysql again:

sudo /etc/init.d/mysql start

You should now have root access to the database:

mysqlshow -u foouser -p

Finally shutdown mysql, remove your changes in /etc/init.d/mysql and then restart the database.

Here is a really nice project for you FOSSGIS fans out there: ‘Godzi‘. Created by Pelicanmapping.com (the creators or osgEarth), Godzi is a javascript and webGL implementation of a 3D browsable earth. Take a look at their demo (screenshot below).

Godzi in action

Of course my QGIS self is now thinking ‘Oh cool we can use this to publish maps to a 3D web globe using QGIS Mapserver!’. Something to add to the todo list for the fledgling QGIS web client project.

In November 2010, I posted some charts showing some  QGIS download stats. Today I made some updates to these charts (click on them for full image) – enjoy!

Cumulative download counts

Download decay chart

The GeoNames dataset provides a list of global placenames, their location and some additional information such as population and so on. It is published under the Creative Commons Attribution 3.0 License, which allows you to use the data for your own purposes. You can download the data by country, or the entire global dataset. In this article, I will walk you though how I downloaded the entire dataset, loaded it into PostgreSQL and added a geometry column so that I could view it in QGIS. Note that you can substitute these instructions for a specific country’s data easily.

First up, lets get the data from the geonames download page!

wget -c http://download.geonames.org/export/dump/allCountries.zip

Note the download is around 196mb so if you live in an internet backwater like I do, expect it to take a little while. If the download gets disconnected, just rerun the same command again – the ‘-c’ option tells wget to continue where it left off last time.

Once the data is downloaded, unzip it:

unzip allCountries.zip

You should now have a text file called allCountries.txt weighing in at just under 900mb. Next we can load it into PostgreSQL using a variation of this article. I highly recommend the use of schemas to partition your database into logical units. In the code listings that follow, it is assumed you have a schema called ‘world’. If you need to create it, simply do:

create schema world;

From the psql prompt. Since I am only interested in the geoname table at the moment I simply do this in my database.

create table world.geoname (
         geonameid       int,
         name            varchar(200),
         asciiname        varchar(200),
         alternatenames  varchar(8000),
         latitude        float,
         longitude       float,
         fclass  char(1),
         fcode   varchar(10),
         country varchar(2),
         cc2 varchar(60),
         admin1  varchar(20),
         admin2  varchar(80),
         admin3  varchar(20),
         admin4  varchar(20),
         population      bigint,
         elevation       int,
         gtopo30         int,
         timezone varchar(40),
         moddate         date
 );

You will notice that I extended the alternatenames field size from the original tutorial’s 4000 characters to 8000 characters in order to accommodate some longer entries that were causing my imports to fail with 4000 chars.

Next we can import the data (also from the psql prompt):

copy world.geoname (geonameid,name,asciiname,alternatenames,
latitude,longitude,fclass,fcode,country,cc2,
admin1,admin2,admin3,admin4,population,elevation,gtopo30,
timezone,moddate) from '/home/web/allCountries.txt' null as '';

Once again this is similar to the import line used by the original article I used, except I have used a full path to my allCountries.txt file. The import may take a little while depending on the speed of your computer.

When it is completed, you should have a bunch of data (~7.5 million records) in your table:

gis=# select count(*) from world.geoname ;
  count
---------
 7664712
(1 row)

It is going to be useful to have a unique id for every record – QGIS for one would like to have it, so lets add one (in addition to the geonameid field):

alter table world.geoname add column id serial not null;
CREATE UNIQUE INDEX idx_geoname_id ON world.geoname (id);

Because I know I will be using some other fields as basis for subset queries etc., I added some indexes on those too:

CREATE INDEX idx_geoname_population ON world.geoname (population);
CREATE INDEX idx_geoname_fcode ON world.geoname(fcode);

Ok thats all great, but there is no geometry column yet so we can’t view this in QGIS easily. So lets GIS enable the table! First we add a new geometry column:

alter table world.geoname add column the_geom geometry;

Now we populate the geometry column:

update world.geoname set the_geom = st_makepoint(longitude,latitude);

Next we add a constraint to ensure that the column always contains a point record or is null

alter table world.geoname add constraint enforce_geotype_the_geom
CHECK (geometrytype(the_geom) = 'POINT'::text OR the_geom IS NULL);

Finally lets index the table on the geometry field:

CREATE INDEX idx_geoname_the_geom ON world.geoname USING gist(the_geom);

Ok next we can connect to the database using QGIS and view our data! Note that you may want to filter the data or set up scale dependent visibility so that QGIS doesn’t try to render all 7.5 million points when you zoom out.

I added a query filter like this to the layer properties -> general tab -> subset:

"population" >= 10000 AND fcode != 'PPLA' and fcode != 'PCLI' AND fcode != 'ADM1'

You should experiment and read the geonames documentation in order to define a filter that is useful for your purposes.

Geonames layer loaded in QGIS

The Geonames dataset is a wonderful asset to the GIS community, particularly to those of us who value Free Software and Free Data.

Update 6 Aprl 2011:

I encountered one issue with the above procedure: the SRID for the imported points is -1 when loaded instead of 4326. This cause problems e.g. in mapserver you get an error like this:

ERROR:  Operation on two geometries with different SRIDs

To fix the issue I ran the following update query to assign all points a proper SRID:

update world.geoname set the_geom = ST_SETSRID(the_geom,4326);

Note that it takes quite a while to run.  When it completes, you can verify that the points are all in the correct CRS by running this little query:

gis=# select distinct ST_SRID(the_geom) from world.geoname;

Which should produce something like this:

 st_srid
---------
 4326
(1 row)

For good measure, I also added the geoname table to my geometry columns table:

insert into geometry_columns (f_table_catalog,f_table_schema,f_table_name,f_geometry_column,
coord_dimension,srid,type) values ('','world','geoname','the_geom',2,4326,'POINT');

Lastly I also gave select permissions to my readonly user (which I use when publishing in a web environment):

grant usage on schema world to readonly;
grant select on world to readonly;

I have also fixed the formatting of some of the SQL snippets above for those who struggled to read it within the width of this page.

I thought I would take a moment to run through the tools in my digital toolbox that I use to develop GIS enabled web sites. I try to pick the best of breed in each area rather than learn multiple tools that do the same thing – life is short and there isn’t enough time for me to do that.  I would be interested to read in the comments if anyone has suggestions of better alternatives:

Operating system: Debian or Ubuntu Server Edition. Really don’t run your web site on Windows, its just a silly waste of time and money.

Web server – Apache 2. It’s a no brainer really, its fast, robust and infinitely configurable while easy to get running in a default configuration.

Programmers editor: Vim (or emacs if you prefer). Being able to use the same editor on both local development machines and remote servers is indispesible. Also if you hand write your html it tends to have less gumpf clogging up the works compared to the Frontpage etc. generated sites I have seen out there. If you must use a GUI editor there are a few good choices under Linux, but VIM does it for me.

Web Application framework: (Geo)Django. With its support for spatial extensions,  intuitive MVC architecture and wealth of 3rd party add-ons, Django is a great choice for building your web applications with Python.

Backend Database: PostgreSQL/PostGIS. It does everything you need including store geospatial data.  Some people suggest SpatialLite as an alternative, but thus far I have haven’t used it in a production system so I don’t know what limitations, if any, it has.

Javascript Framework: Jquery. I know there are some good competitors but JQuery and JQuery-UI are so great I haven’t felt a need to go and discover their competitors. There are a huge number of add ons for JQuery and plenty of helpful people out there if you get stuck.

CSS Framework: Blueprint CSS. I think CSS frameworks are still a relatively new concept. They take a lot of the pain out of layout and make your site look good with minimal effort. Blueprint implements the 960 grid and the results are pretty much always pleasing on the eye.

CSS Compressor: http://www.csscompressor.com/. This web site will take your CSS and squish it by removing comments, white space etc. It typically makes my  CSS files around 25% smaller.

Javascript Compressor: Google Closure. This web service / web app will squash your javascript down nice and small using a variety of techniques.  With simple optimisations, I typically get around 40% reduction in code size. It also optimises your code for good measure.  They provide lots of detailed documentation to get you on your way.

Web Mapping Control: OpenLayers. It’s a bit of a no-brainer. I haven’t really looked that hard but is there anything that really competes with it (other than the Google Maps API)?

Web Mapping Server: UMN Mapserver. One day soon I am going to replace this entry with QGIS’ own mapserver implementation by Marco Hugentobler, but for now Mapserver is the best thing since sliced bread – it’s  a doddle to install, incredible flexible and you can generate basic mapfiles using QGIS so it’s easy to get started with. Also worth a mention is Mapnik which can produce gorgeous maps.

Web Mapping Cache: TileCache. I keep meaning to try out MapProxy since it supports region delimitation using shapefiles rather than bounding boxes – which can drastically reduce the size of your seeded cache. But for now, TileCache is really easy to set up and you should have it running in just a few minutes.

Did I leave anything out? If you take the above tools, software and web services, you will have everything you need to produce some great web mapping software.