Loading Codepoint into PostgreSQL / PostGIS

Ordnance Survey GB supply the CodePoint with Polygons dataset as a set of ESRI Shapefiles, sometimes it is useful to load them up into a PostgreSQL table for use with MapServer, GeoServer and/or QuantumGIS.

Requirements

PostgeSQL 9.3 (may work with 9.1, 9.2)
PostGIS (2.1.x)
GDAL (1.10.x)

Create a database and table

createdb ordnancesurvey
psql ordnancesurvey

CREATE TABLE codepoint
(
  ogc_fid integer NOT NULL DEFAULT nextval('fk_ogc_fid_seq'::regclass),
  postcode character varying(8),
  upp character varying(20),
  pc_area character varying(2),
  geom geometry,
  CONSTRAINT codepoint_pkey PRIMARY KEY (ogc_fid)
)
WITH (
  OIDS=FALSE
);
\q

Import the data

To get these into a single PostgreSQL PostGIS table first place all the shape files into a single folder (codepoint_oct2013 in the example).

ogr2ogr -append -f PostgreSQL "PG:dbname=ordnancesurvey" codepoint_oct2013 -lco GEOM_TYPE=geometry -lco GEOMETRY_NAME=geom -nln public.codepoint

Set Spatial Reference to British National Grid

psql ordnancesurvey

SELECT UpdateGeometrySRID('codepoint', 'geom', 27700);

\q

Add Indexes

For performance you should add indexes for the columns you are likely to query, and a spatial index if you are going to do any spatial searches.

psql ordnancesurvey

CREATE INDEX codepoint_postcode_idx ON codepoint USING btree (postcode COLLATE pg_catalog."default");

CREATE INDEX codepoint_geom_idx

  ON codepoint

  USING gist

  (geom);

\q

Apache and Upgrading Ubuntu Server LTS

Now that Ubuntu 14.04 LTS is available it's time to think about upgrading all those 12.04 servers.  For me that meant updating my home file server and development server, not exactly mission critical, so I plowed straight in to see what would get broken.

Largely the update went without fault, but before anything make sure you backup any important files.  To get it started I changed the /etc/update-manager/release-upgrades file from :-

Prompt: LTS

to :-

Prompt: normal

This step might not be necessary by now, but can be used safely if you know you are going from one LTS to another.  Once you get the the final LTS version you can set it back to Prompt: LTS

Next, update the list of available packages and install any updates.

sudo apt-get update

sudo apt-get dist-upgrade 

It's probably wise to reboot the machine at this point and check that all is well before continuing.  Once you're happy to continue start the release update.

sudo do-release-upgrade

Follow the prompts and carefully consider any prompts about replacing configs files, you can always say no, then fix the config by comparing your config with config.dpkg files.  I usually keep a list of all the files that had problems during the upgrade.

Once the upgrade completes you ought to reboot.  Repeat the do-release-upgrade process until you are at the 14.04 LTS version.

 Notable changes

- Apache Web Server, now at version 2.4.

There are some changes to the allowed config files for Apache, for me this manifested itself in a virtual host that no longer was available.  Where previously any file in /etc/apache2/sites-enabled would be parsed, it now only looks for and parses files with .conf extensions.  In addition the options to control access by ip have changes.  Instead of Order Allow, Deny and Allow from xxx.xxx.xxx.xxx/yy you need to use the Require syntax.

- PostgreSQL, from 9.1 to 9.3

As with any update to PostgreSQL you will need to run pg_upgradecluster after dropping the new default cluster with pg_dropcluster --stop 9.3 main; pg_upgradecluster 9.1 main
If you have any databases that use the postgis extension then I suggest making a backup first, dropping the database from 9.1, upgrading the cluster then restoring it to 9.3 after ensuring that postgis is installed and available.

Edit: Further investigation on my postgres migration showed that many tables were missing data.  Double check that upgradecluster completed successfully.  If in any doubt you probably should just do a full pg_backup and pg_restore.

Custom Composer Repositories

At work I've been playing around with and trying to learn Symfony2, which so far I'm hugely impressed with due to its flexibility and range of features available.

One of the coolest things that it does is use the 'Assetic' library to handle assets. What's awesome about this is that you can apply filters to the assets that it's handling allowing you to modify the output of them. It has a lot of built in filters which are very handy but require external files to work.

The two I was most interested in was the use of 'YUI Compressor' to compress (minify) css/javascript files, and 'Google Closure' which not only compresses Javascript files, but can also improve (speed up) the code in them.

Awesome.

Now according to the symfony documentation in order to actually use them requires you to manually download the relevant 'jar' files, install them in the "/app/Resources/java/" folder and then modify your config.yml file to point the filters to the correct paths.

With Symfony we use composer to make our lives easier to download third party libraries and use them in our projects without having to commit them to version control. However, this isn't possible with those external Jar files... or is it?

As it turns out you can create custom repositories in composer meaning that anyone running a simple 'composer update' command will get the data contained within those repositories, even if they're external website's zip files. You can apply this to almost any zip file or static URL'd file that's on the internet.

The first step in getting this to work is working out what files we need to download (we require direct links to the files), and what types of file they are (jar, zip, etc).

So, in my example above for the "YUI Compressor" and "Google Closure" we end up with the following:

YUI Compressor

• http://cloud.github.com/downloads/yui/yuicompressor/yuicompressor-2.4.7.zip
• Zip File

Google Closure

• http://dl.google.com/closure-compiler/compiler-latest.zip
• Zip File

Now we know what files we need to download to get the assetic filters to work, the next step is creating the entries in the 'composer.json' file. The individual entries should be as follows:

YUI Compressor
Google Closure
Now we put them into the composer.json file, under the "repositories" section, as follows:

I've put this section in mine between the "autoload" and "require" sections, but I don't think it should make a difference where in the file it actually goes.

I'll just quickly explain the information above, for each of the entries the information is basically the same.

• The 'name' should match the format "vendor/packagename"
• The 'version' should match the version number of the package you want where possible (in Google Closure's case I don't know the version number so i've gone with 1.0 as default.. and if we know it's updated we can increment this and composer should uninstall the old version and put the new one in its place)
• The 'url' should be self explainatory
• The 'type' should match the type of file you're downloading, 'zip' will automatically unzip to the vendor folder, if you want to straight copy a file (in the case of downloading a raw 'jar' file) then just use the type 'file'
• The 'reference' I believe should match up with the 'version' number above (not 100% sure on this, but it seems to work on the copy I've got

That's pretty much all there is to setting the repositories up, you can add as many as you want to this and just follow the same pattern to make them available for everyone.

To get them to install in symfony, you need to continue to modify the 'composer.json' file, and add them into the "require" section, as follows:

From:

To:

Make sure the version numbers you specify match up with the ones you've set in the "repositories" section, and the name section need to match again with what you've previously specified in the "repositories" section.

Now if you run "composer update" you should find that it creates the folders "google/closure" and "yui/compressor" in your "vendors" directory of your symfony2 install.

The final step in getting this all to work is to enable the assetic filters in the "app/config/config.yml" file, scroll down to the "assetic" section, and find the sub-section labeled "filters", you might find the 'yui_css' and 'closure' ones already in there but commented out with # symbols.

You'll need to either uncomment or add them in yourself and specify the paths to the jar files, as follows:

You'll see that I start the path in the %kernel.root_dir% folder, this is your /app/ folder, as the vendor folder is the same level as that we go back up one level '../' then we can access the vendor folder, now its simply a case of adding the paths to the jar files.

Hopefully if you've followed this then you just need to add the filters onto your {% stylesheet %} and {% javascript %} blocks to use them to minify your front end code.

Any questions or thoughts on how this could be improved just drop me a comment below

Pathfinding

If you've read the previous post then you'll know that I'm building an RTS game in my spare time, the area I've chosen to initial focus on is one that will unfortunately probably be replaced when we finally make the move to Unity Pro as it has this feature built in. This however is a good starting point as its an interesting subject that is far more difficult than you'd first expect it to be.

I chose to start with basic pathfinding, we'd already decided that the easiest way to allow placement / movement of units and buildings around the map area would be to use a grid system, after some research into how pathfinding should work we discovered the generally accepted way of doing this was using an algorithm called "A*" (Pronounced "A Star").

I found a fantastic website (written in 2005!) on how the algorithm works by Patrick Lester, I'll paraphrase a lot of what he's put here so you can get the jist of it, however if you're interested in learning about it in-depth then I'd highly recommend you give his page a read.

The basic theory of the A* Algorithm is that we score the potential moves from the initial location in relation to the direction we need to move. Essentially this process repeats over and over (totalling up the score), until we finally reach the point we're looking to reach. This may take several passes sometimes but the lowest total scored path found should be shortest route. The scoring works by the score being significantly higher if you're moving in the opposite direction to the point you're trying to reach, and as the algorithm will look for the lowest scored node to move to from its current position you should be getting closer to the target point each move you make, however if you encounter a bottleneck in the map you might have to double back on yourself. The algorithm will take this into consideration though, and won't include the incorrect portion of this path in the final value.

As you might imagine this particular search is quite intensive on the CPU due to finding multiple paths at any one time, so should be done as infrequently as possible. If you need to move lots of units at once, then the best way to deal with this is to queue up the path finding into different frames. If your game is running at 30-60FPS a 1-2 frame delay in calculating the paths for various units shouldn't matter too much in the grand scheme of things. Another way of coping with this is storing the calculated paths in memory once done, then if another unit can re-use the same path (or extend it, or even use just part of it) to get to the point they're going to, that should save on processor time significantly.

Writing the actual A* algorithm in c# wasn't too bad, however to sort the chosen paths we need to use what is called a 'binary heap', this allows us to very quickly get the lowest value in a particular group of values. While you might wish to use arrays / collections to achieve the same result you'll find that once you're running lots of these path finding algorithms at once it'll get very slow, hence why the binary heap method is prefered. Again theres a brilliant article by Patrick Lester on binary heaps and why they're so useful in the A* algorithm which is a bit in depth, but well worth a read if you're interested in implementing this yourself.

Once I'd got the binary heap class working along with the A* class then tests of the pathfinding function worked as expected, it was very fast to search through the map grid, and work out the most efficient route to a particular square. To make something impassable (such as when a building is placed) we just remove it from the usable grid squares, meaning that units walk 'around' the buildings on the map.

So while this implementation is basic and will eventually (probably) be removed when we upgrade to unity pro, it will work as a stop gap, and currently seems to work quite well. At least now we have basic unit movement when we implement units in the game.

Game Engine Design

Well, this is the first proper post on the subject of coding which essentially is what this blog is all about. Currently in my spare time I'm using Unity to create an RTS game, I don't want to give away much more than that at the moment on the actual subject of the game, however the actual process of building a full 3d game in a modern game engine is completely new to me. This means that I'll be documenting my process being various parts of the system along with my thoughts and reasoning behind certain decisions, while I don't claim these are all going to be correct or necessarily make it into the finished game, hopefully they'll give you an insight into what I'm doing and why.

The first steps I've taken is working out the things an RTS would (or should) have the ability to do, I believe this to be:

• Resource management
• Unit creation
• Building creation
• Using your units/buildings to overcome some form of task or difficulty
• Achieving an end goal for the level you're playing
• You should be able to navigate around the map
• Areas of the map should be dulled to it (usually with a fog of war, or cloud cover)
• Occasionally on some levels events will occur at set points or when particular criteria are met
• You (the player) should have a clear mind of what you need to achieve to complete the level
• You should be able to easily select a unit and see it's current health
• Units should be able to navigate from their current location to one you tell them to, navigating around objects such as buildings, mountains etc
• You should be able to see what your current resource levels are at any time
• Occasionally we may want fully scripted "cut scenes" to happen, demonstrating something such as a "hero unit" killing a particular enemy or being captured as part of the story
• Buildings should be upgradable
• Units should be able to receive promotions to improve their stats,

There are far more things that I've not mentioned I'm sure, however I think it's best to take it one step at a time, I'll do another post explaining exactly what I chose to focus on first from that list.

Welcome to the site!

Hello and welcome to the Piston Coding website, hopefully you'll find the posts contained within either educational, interesting or at least somewhat entertaining.

You should hear from the various member of the Piston Coding team, which consists of myself (John), James and Tom. We will endeavour to bring you interesting articles on the coding that we do in both our work lives (where legally possible to let you know what we're doing that is!) and also in our personal lives with the various projects that we have going on.

So with that.. welcome to the site!