Pither.com / Simon
Development, systems administration, parenting and business

Journal, ISO week 2018-W32

last week

PaTMa updates

I got some updates to the design of the buy to let profit calculator live so now it's even easier to use with clearer results. As part of improving the PDFs that the calculator can produce I moved the generation of them from Reportlab to Weasyprint. Having previously avoided PDF rendering tools that convert from HTML - because of external tool requirements like headless browsers and performance problems - I've been pleasantly surprised by how well Weasyprint works. There are no headless browsers and the (admittedly short) PDFs generate very quickly indeed.


I spent a little while looking at improving my use of SCSS in Django and will be giving Django SASS processor a go next week.

I've been plotting a new (tiny) application/product for a while and think I might try out a "modern" Javascript app framework. Coincidentally a new Vue CLI caught my eye and got me reading up on how Vue compares to things like React and Angular. I still haven't decided - I like the syntax of Vue but the all-in-one-place approach of React. They both seem to have millions of extensions and ways to accomplish everything so I might go with the one that I can find a heavily opinionated template/scaffold for - just so I don't have to make so many decisions to get something running!

Django template filter for embedding an image as a data URL

This should probably be a tag rather than a filter and definitely needs some caching, but here's my first version of a snippet to convert an image file to a data URL - handy if you'd like to include a local image in an HTML file that might be converted to a PDF.

def image_to_b64(path):
    full_path = os.path.join(settings.SITE_ROOT, path)
    data_start = b'data:image/jpg;base64,'
    if path[:-4].lower() == '.png':
        data_start = b'data:image/png;base64,'
    with open(full_path, "rb") as f:
        return data_start + base64.b64encode(f.read())

Which might be used in a template a bit like this:

{% load <your templatetag library> %}
<img src="{{ 'static/img/icon/pdf_logo.png'|image_to_b64 }}"/>

Open source (old) game

A game (Star Ruler 2 - kind of Civilisation in space, I think) whose producers are now inactive, has been released as open source. I do like this idea - rather than letting a game or product die, releasing it to the community. Unfortunately it looks far too involved for me to try now but maybe in a few years!

PostGIS data import performance

PaTMa holds quite a lot of data - for example there are over 23 million historic house purchase records published by the Land Registry. Importing that many records can take a while, which is especially tedious in development databases that sometimes get deleted.

I've previously looked a bit at speeding it up but this week I decided to spend a bit longer to see if it could be made much faster. It turns out it could.

The previous algorithm was roughly:

  • Read CSV line
  • Parse date from date/time string (turned out this was surprising CPU heavy)
  • Lookup latitude/longitude values
  • Create a PostGIS Geometry field
  • Write out to PostgreSQL (in big batches)

My improved (taking the import on my main workstation from several hours to less than one) process is:

  • Read CSV line
  • Truncate the date/time string to just be a date
  • Lookup latitude/longitude values
  • Write (slightly tweaked) line out to a new CSV
  • After the above is finished and all 23+ million rows have been rewritten (in < 5 minutes)...
  • Use COPY to get PostgreSQL to directly import the new CSV (actually using copy_expert in psycopg2).
  • Ask PostgreSQL to generate the geometry (actually Geography) values from the latitude/longitude figures:
    UPDATE price_paid SET location = ST_GeogFromText('SRID=4326;POINT(' || lon || ' ' || lat || ')');"

Certainly not an intuitive improvement as there are more steps and the data is effectively being processed three times. But that processing is obviously much more efficient.

Rental scams

I came across a story about rental scams which sounded quite scary, there are definitely some people out there who are best avoided!

Hidden London

On Sunday Liz and I took a trip 100 feet underground to visit a WWII deep level shelter. It was a fascinating tour with excellent guides.

After that I accidentally spent a while reading about other tunnels under London.

Add a comment