Why Spatial Indexes are REALLY Important

Why is a spatial index important?
Indexes are what make using a spatial database for large data sets possible. Without indexing, any search for a feature would require a “sequential scan” of every record in the database


I am not afraid to say that I failed…I compiled all of the Ordnance Survey OpenMap data into a UK vector coverage, just like I did with the Vectormap, Boundaryline & Strategi data, into a PostGIS database on a [Windows 2012] server. Unlike the other Ordnance Survey products, the OpenMap kept crashing my QGIS [2.8]….not something which is easily done!

There was a great incentive, the British Cartographic Society was holding an OS OpenData award & I had a great idea about recreating the old school 1:25000 scale maps using entirely open source data – Everytime I loaded the data, I would go to bring up the composer to create my work of art, QGIS would just crash….and not a little, this was epic. No matter what I did, reducing the drawing quality, reduce the rendering or even clip the output to the frame….NOTHING would work, it just crashed.


I’m a little new to PostGIS, I’ve worked with Oracle RDBMS, ESRI Geodatabase & a plethora of vector file formats but PostGIS is relatively new, so I took a few shortcuts in loading the ESRI Shapefile data to the database. Rather than loading the shapefiles using SPiT [in QGIS] I decided to use the OpenGeo Suite add-in (which is the dogs danglies)…..this allows me to not only load up the data but also styling & is GUI enough for me not to feel like I’ve gone back to being a database administrator. Unfortunately, as awesome as the add-in is,


Before indexing...

Why is a spatial index important?

Indexes are what make using a spatial database for large data sets possible. Without indexing, any search for a feature would require a “sequential scan” of every record in the database. Indexing speeds up searching by organizing the data into a search tree which can be quickly traversed to find a particular record. PostgreSQL supports three kinds of indexes by default: B-Tree indexes, R-Tree indexes, and GiST indexes.

By default PostGIS creates indexes as B-Tree but best results (in my opinion) are found by indexing using GiST option. (GiST (Generalized Search Trees) indexes break up data into “things to one side”, “things which overlap”, “things which are inside” and can be used on a wide range of data-types, including GIS data. PostGIS uses an R-Tree index implemented on top of GiST to index GIS data.


So, I found the problem, what was the solution?

Having looked in detail into the problem, I found that I had 100+ data layers, with over 20GB of data which were un-indexed…luckily I am not the first to have an epic geofail…cue some hasty conversations with Ross Mcdonald [Angus Council QGIS & PostGIS Ge0-God] & Matt Walker [Astun Technology PostGIS Guru & Blogger]

Normally to create indexes, you would write the SQL script:

CREATE INDEX “[Index name]” ON public.”[Tablename]” USING GIST ([geometry fieldname]);

But with over 100 lines to do, it would have be extremely time consuming trying to extract all the names & index names… so Matt provided a small script:

SELECT ‘CREATE INDEX ‘ || quote_ident(f_table_name || ‘_geom_idx’) || ‘ ON ‘ || quote_ident(f_table_schema) || ‘.’ || quote_ident(f_table_name) || ‘ USING GIST (‘ || quote_ident(f_geometry_column) || ‘);’ AS SQL
FROM geometry_columns
WHERE f_table_name IN (f_table_name);

This goes through and writes the “Create index” script for you!!

If there is ever a time that you think that creating indexes is a waste of time, look back to this article….Last week I couldn’t open My data in QGIS without it crashing, today it not only loads but is relatively quick.

I may have lost a day creating indexes but I am going to save DAYS in the long run…..

Nick D


As well as being niaive enough not to check the indexes when I was struggling to load the data but it would appear that I hadn’t updated the OpenGeo plugin for a while and was using 0.6.4 when the latest version is 0.7.2…..which, since 0.7 has supported creation of indexes by default.

As stated at the top of the article, I failed….but it is these failures which help us to be stronger and better in what we do [supposedly]

Nick D


Should we be reading maps or moving forward?

There has been a lot of press recently about an announcement from the Royal Institute of Navigation (RIN) declaring that “Society is being sedated by software” and it would appear that most of the media have jumped on board like the BBC, The Guardian, even the Western Morning News…I think that we are being a little technology phobic

This article was originally posted on xyHt

There has been a lot of press recently about an announcement from the Royal Institute of Navigation (RIN) declaring that “Society is being sedated by software” and it would appear that most of the media have jumped on board like the BBC, The Guardian, even the Western Morning News…And I have to agree with them to a certain extent, but there is a little part of it which grates on me a little though. It could be that I am a “Electric Map bloke” (not my words but those of my wife explaining my job) but the whole article seems a little backward thinking, I mean this as no disrespect to RIN, I know many of their members and I look up to all of them., but it reads as a little against technology…

Simple technology answers
Image from ©xkcd 2015

Satellite Navigation Systems (Sat Navs) are predominantly used by drivers, there is still very few users of the walking routes navigation…go on, ask your average 8/9 year old how to get to the shops and they may well start up Google Earth or some kind of online mapping system, they may even get directions but they won’t touch the navigation mode. Furthermore, how many young adults take a map with them when they are hiking or travelling nowadays? You will find that most young adults carry a spare set of batteries for their device rather than a paper map. Oh, and the arguement about signal? Ever heard of offline maps or network navigation (not as accurate but good enough if you are stuck in the middle of nowhere).

We [Geographers, Map Makers, Data Scientists etc] have pushed to develop technology which can actively support and inform people, we should embrace it and rather than complain about modernisation affecting techniques, we should make those using the technology to be more educated about it.

Did the world of geographers complain when Royal Mail gave each house a postcode, removing the need for coordinates to find a house? No, the fact is we still read maps, we still use coordinates, in fact location data is increasing in our daily life, ESRI [one of the worlds largest Geospatial software suppliers] stated that over 80% of data has a geospatial element.

Digital maps do layers

The fact is that using a digital map is more efficient. You can zoom in for more detail, obtain coordinates & even send them, there is the ability to overlay different schemes (OS, Google Earth, Bing, OSM basemaps) so that you can get a better understanding of where you are and from personal experience I must say that a mobile device won’t fly out of your hand as easily in the middle of  a windy field or go soggy and smudge in a rain storm (my device is waterproof).

Here is my proposal,

1, GIS needs to be taught in schools, this way children will NEED to handle coordinates, deal with geospatial awareness & learn the geographical interaction of everything around them.

2, Rather than teaching map reading, we should be teaching data collection to develop the next generation of cartographers, GIS experts & Data Scientists – Location data is in almost all data businesses handle, lets ensure it’s the best data!

3, Let’s give more children availability to mobile mapping and get them treasure hunting & playing location based games more.

4, We need to change the medias focus & make maps cool, have annual competitions open to younger people and also have more discussion in the media on WHY traffic issues are occuring…Using Ordnance Survey road network data (open source) childern can analyse better routes and solve navigation issues.

5, More 3D!! Lets get children thinking more about the world they live in and how it is represented. They see false 3D environments in video games, let’s bring that interest to representing the real world. Get them to make a house in Sketchup….it will take 2 minutes before they realise that it needs a location to appear in Google Earth and that 3D features need parameters like height!

RINS is kind of right, as we have developed and improved these amazing mapping systems on the internet and mobile media, we have forgotten to educate the new wave of users & developers. I think we need to take a different view on it though, location and navigation are vital parts of geography but then so is the translation. Technology is all around us and we will struggle to get someone to use a paper map, it’s a little like your dad bringing a Sinclair Spectrum 48k to a gaming day at your friends house.

Treasure map - see blog post https://dragons8mycat.wordpress.com/2013/05/29/how-to-make-a-treasure-map-using-free-gis-something-for-the-kids-part-2/

One of my fondest memories in the last year was making a treasure map with my children, 6yrs & 8yrs. This was not some school homework, no, my son said that he wanted to make a pirate map….for a change, instead of wiping teabags over paper and putting it in the oven, I opened my computer and started up QGIS. After I made a folder with some map data in, I showed them both the simple functions and left them for a couple of hours…..consequently 2 weeks later we went to London to visit Big Ben, where my son had “hidden” the treasure on the map he made.


Solving distance(s) to work in QGIS

A quick easy way to get the distances and lines to a central feature.

A couple of days ago I was asked to calculate some distances for people, from where they lived to where they worked…I scratched my head a little and then went to turn on the ArcGIS spatial join tool….but then thought of how this could be done in QGIS. The answer? The Hub Distance tool, part of the MMQGIS set of tools.

I’ll run through how to use this tool below using some arbetuary points and a pretend hospital to “calculate the distance to the hospital”

Let’s consider the image below, there are multiple houses and a hospital. The houses are on one layer and the hospital is on it’s own layer.

Figure 1

Select the “Hub Distance” tool as below:

figure 2

The houses are the SOURCE layer and the Hospital is the DESTINATION layer, there is also the option to set your options for the unit of distance (Note that you should be using a local projection for metres)

figure 3…and process!!

If everything went to plan you should have some lines now connected between the “hub” [hospital] and the houses. The distance will be within a field HUBDist – this can be used for labelling:

figure 4

And don’t forget to adjust the formatting to limit the number of decimal places shown, unless you want 7 decimal places!

figure 5Now, within a matter of minutes, you have a map showing the distances from houses to the hospital….so easy and extremely quick.

figure 6What I really like about doing this in QGIS, is that, unlike ArcGIS where I have to create a shapefile and edit etc, I can hit the “new shapefile” option in QGIS and put the point down in seconds.

Nick D

How to put Transparent Text on the map in ArcGIS

Transparent text has always eluded me in ArcGIS, to the point where I have started using QGIS more. So imagine my surprise when I found a way to do it!
I had to share it with you…

Originally posted at xyHt.com – Online blog

Wow, it’s been a while since I last posted, though if you’ve seen my Twitter feed you will understand why…yes, I am now a Fellow of the Royal Geographic Society & Istitute of British Geography.

Thats’s not why you’re reading this though, you are probably reading this, shouting at the screen and calling me a liar, I am not, I have the maps to prove it! I’ll show you through the method below.

Lets consider this map:Transparent text fig1I want to make it look like the Ordnance Survey Explorer (folded) map(s) and have the map tile names displayed but transparent as not to hide any information. The text is currently being rendered from the layer “1_50000_Tile_finder”. Once I have the text looking similar to how I want it, convert it to annotation.

Transparent text fig2Right-click on the layer and select the “convert label to annotation” option

Transparent text fig3The trick here though, and I’ve made this mistake before, is you MUST SAVE TO DATABASE – as in the image below.

Transparent text fig4There now should be an extra item in your legend

Transparent text fig5If you open the properties of this layer, you will see that you now have a “Display” tab, you can adjust the text transparency here.

Transparent text fig6And voila! We have a finished map with transparent text!Transparent text fig7This was done in ArcGIS 10.3, but has been tested in ArcGIS 10.2 and works.

Nick D