Click here to Skip to main content
13,000,179 members (57,055 online)
Click here to Skip to main content
Add your own
alternative version

Tagged as


19 bookmarked
Posted 22 Oct 2012


, 22 Oct 2012
Rate this:
Please Sign up or sign in to vote.
A map application that is able to run custom queries on a PostGIS server.
This is an old version of the currently published article.

Editorial Note

This article is an entry in our AppInnovation Contest. Articles in this sub-section are not required to be full articles so care should be taken when voting.


This is an entry in the Ultrabook App Innovation Contest (Desktop apps). 


I thought for quite some while about apps that could truly take advantage of an ultrabooks features. I came up with several ideas that would use the sensors in some form, but I found that in fact all of the apps were better off running on a tablet, or smartphone. (Though they could also be run on an ultrabook, of course.) The point is that the (inertial/GPS/orientation) sensors are not a unique feature of an ultrabook - any smartphone or tablet has these nowadays. The unique feature of an ultrabook in my opinion is that it both is a very mobile device (with small form factor and sensors) and has ample computational power at the same time. So my app would have to use both mobile features and profit from having access to a fast SSD and a fast processor. Nowadays, one of the few classes of (non-game) applications that really put hardware to a test are geoinformation systems. For example, the full OpenStreetMap database is >300GB in size, not something you can easily handle on a smartphone or tablet. On an ultrabook however, you may run your own GIS database, e. g. using a subset of the OpenStreetMap database. See below for how to actually do this, it is not difficult. The app I propose here is essentially a map viewer, similarly to Bing Maps/Google Maps etc., with a somewhat enhanced feature set. Furthermore, you can, if you want, use it to query your GIS database and the results will be displayed on the map. So to summarize:

  • Out of the box, LocalStreetMaps is a map app, using web services, with some additional features compared to Google/Bing maps etc.
  • If you choose to have a local OpenStreetMap database (this will amount to essentially downloading and running the VMWare image of the server), then you have the following advantages: (i) You can view your maps offline. (ii) You can use it to run queries on your database that go much beyond what the typical navigation app will tell you (see below).

I understand that the second use case is not for John Doe. However, I believe that not every feature of every app has to target John Doe.

The app

Here is what the app does, for simplicity explained by a few screenshots. It displays a map:

Screenshot of Map

You can search for places:

Screenshot of Geocoding

You can get directions.

Screenshot of Directions

The app displays your position and heading etc.

Screenshot of Info page

Here I measure the north-south circumference of the earth (approximately).

Screenshot of Meter

Now to the non-standard features. Suppose you have a GIS server, then you may run a PostGIS query and get the results displayed on the map. For example, you may ask for the 10 largest (by population) cities in Great Britain.

Screenshot of GIS query

Here the author actually learnt some gegraphy, I would have bet that Manchester is among the top 5. Anyway, you may also query for renewable energy sites (Wind or hydro) in Scotland.

Or run any number of similar queries, e.g., what are the 10 cafes closest to my current location, etc. Spatial databases are really powerful in this respect, and there are uncountable possibilities. (Well..., as a mathematician I have to say that actually the number of possibilities is countable, but still quite large.) 

Points of Interest

From a coders perspective, implementing the standard features (address search, directions etc.) is a standard task, using some web services (I use Google maps). I will not say much about this, there are plenty of detailed discussions on the web. The interesting part of LocalStreetMaps is the one concerned with accessing the PostGIS database. So let me discuss only this part.


There are various database managent systems made for dealing with geographic data. I will focus on discussing PostGIS, which runs on top of PostGreSQL. In any case, special data structures have to be built in order to allow fast answering of spatial queries. The simplest such structure is a quadtree, which is used e.g., in the Navit .bin file format. PostGIS uses an R-tree, a (balanced) tree in which node is assigned a bounding box, containing all bounding boxes of its children. Maintaining this tree and keeping it balanced is not an easy task.  

PostGreSQL is an object-relational DBMS, which means for us that we can store certain classes of objects in the fields. PostGIS defines a couple of such classes, the relevant ones for us are points, linestrings and polygons. Typically, a table in PostGIS database will have one "geometry" column containing data of one of these sorts, and several other columns contaiing other data. PostGIS knows about these special (spatial) columns from entries in an additional table "geometry_columns". Here is a screenshot of pgAdmin showing the content of this table for an OpenStreetMap database.

As you see, for our purposes these geometry columns are always the "way" columns.

PostGIS provides several operators and functions to query geometric data. One important for us is the && operator, which checks for intersections between the bounding boxes of two geometric objects. An example query would look like this:

select name, way from planet_osm_point where way && ST_MakeEnvelope({0}, {3}, {2}, {1}, 900913)

This selects all objects within a certain bounding box specified by coordinates (west, south, east, north) {0}, {1}, {2}, {3}. 

The map server

Of course, to query a PostGIS database, you have to have a PostGIS database, i. e., run a server. To get mine running I followed these steps:

  1. Set up a virtual Ubuntu server in VMWare. 
  2. Install the necessary packages, see
  3. Edit the PostGIS config file to allow outside connections and change the iptable settings.

I am going to provide a bare VMWare image with everything set up, except that no maps (or only maps of some small country) are installed for size reasons. (Actually I planned to release it together with this article, but unfortunately the Round 1 deadline is approaching and I did not finish it in time.) 

Installing additional maps is also relatively easy and done by (i) downloading the relavant map file, e.g., from, and (ii) importing it into our database by calling osm2pqsql. The import may take a while, depending on your machine. An SSD and sufficient RAM helps. I recommend starting with a smaller osm file of say 50 MB. The database layout osm2pqsql creates you can look up here: or explore it using pgAdmin. 

For us, the most important table is osm_planet_point, which contains coordinates (in the "way" column) and tags of point-like objects in our database. Tags are additional information OpenStreetMap contributors provide for the geographic location. For example, there is one column "name", one column "historic_site" or one column "power_source" (wind, hydro, fossile etc.) in the database. You may specify which tags are imported by osm2pgsql in its conf file. The imported tags become columns in osm_planet_point, and there will be many, as the following pgAdmin screenshot shows

What (the GIS part of) LocalStreetMaps does

You may enter a PostGIS query using the appropriate tab of the LocalStreetMaps app. You may use one of the special reserved keywords:

  • CURBB. This will get replaced by the bounding box of the area currently displayed on screen.
  • MAPCENTER. This is replaced by the center point of the bounding box.
  • MYPOS. This is replaced by the current location of the user (determined by GPS).

To be concrete, here are the corresponding line of code:

sql = sql.Replace("CURBB", String.Format(" ST_MakeEnvelope({0}, {3}, {2}, {1}, 900913) ", parameters.BBWest, parameters.BBNorth, parameters.BBEast, parameters.BBSouth))
         .Replace("MYPOS", String.Format(" ST_GeomFromText('POINT({0} {1})', 900913) ", myx, myy))
         .Replace("MAPCENTER", String.Format(" ST_GeomFromText('POINT({0} {1})', 900913) ", mapcenterx, mapcentery));  

Then the query is executed, and the results are displayed. It is expected that the result contains a column "way" with the geometry data, which is then rendered on screen. For example, if the way column contains a polygon, a polygon (line) line is rendered, if it is a point, a marker is shown, see the screenshots above. The relevant piece of code is the following:

if (FieldList.ContainsKey("way") && FieldList["way"] is string)
   var s = FieldList["way"] as string;
   byte[] b = new byte[s.Length/2];
   for (int i = 0; i < b.Length; i++)
      b[i] = Convert.ToByte(s.Substring(2 * i, 2), 16);
   var w = new NetTopologySuite.IO.WKBReader();
   var ww = w.Read(b);
   var c = ww.Centroid;
   double x = c.X, y = c.Y;
   CoordinateTransforms.ToGeographic(ref x, ref y);
   Location = new Location(y, x);
   if (ww.Coordinates != null && ww.Coordinates.Length > 1)
      Path = new LocationCollection();
      foreach (var l in ww.Coordinates.Select(cc => (new Location(cc.Y, cc.X)).ToGeographic()))

 Here I use the .NET Topology Suite to parse the answer to the content (string) of the way field, which is assumed to be in hexadecimal well known binary (HEXWKB) format.

 The code, and current status

The LocalStreetMaps app is mostly complete. The code will be freely available. However, I have not yet uploaded it to CodeProject since I could not yet test the touch and location features, so there may be some bugs lurking. What is not yet complete is the VMWare image for the PostGIS server. (Well, I have my own server's VMWare image, but I have already installed a few maps and it has grown to some size. So I have to set up a minimal server again.)
I do not intend to make money with this app, everything will be free and open source software.






This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)


About the Author

Thomas Willwacher
United States United States
No Biography provided

You may also be interested in...

Comments and Discussions

Discussions posted for the Published version of this article. Posting a message here will take you to the publicly available article in order to continue your conversation in public.
QuestionSource code Pin
Nuno Guerreiro31-Mar-13 7:54
memberNuno Guerreiro31-Mar-13 7:54 
AnswerRe: Source code Pin
Thomas Willwacher31-Mar-13 22:32
memberThomas Willwacher31-Mar-13 22:32 
GeneralMy vote of 5 Pin
Prasad Khandekar16-Mar-13 5:23
memberPrasad Khandekar16-Mar-13 5:23 
AnswerRe: My vote of 5 Pin
Thomas Willwacher16-Mar-13 23:14
memberThomas Willwacher16-Mar-13 23:14 
QuestionGreat article Pin
Member 972289412-Mar-13 23:25
memberMember 972289412-Mar-13 23:25 
AnswerRe: Great article Pin
Thomas Willwacher15-Mar-13 13:51
memberThomas Willwacher15-Mar-13 13:51 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

Permalink | Advertise | Privacy | Terms of Use | Mobile
Web02 | 2.8.170624.1 | Last Updated 22 Oct 2012
Article Copyright 2012 by Thomas Willwacher
Everything else Copyright © CodeProject, 1999-2017
Layout: fixed | fluid