Address Search OS OpenNames with PostGIS, SQLAlchemy and Python – PART 2

Part 1 of this post outlined how to configure a PostGIS database to allow us to run Full Text searches against the OS OpenNames dataset.

In Part 2 we look at writing a simple Python 3 CLI app that will show you how easy it is to integrate this powerful functionality into your apps and APIs. Other than Python the only dependency we need is the SQLAlchemy ORM to let our app communicate with Postgres.

address-search

Installing SQLAlchemy

SQLAlchemy can be installed using pip. It is dependent on psycopg2, which you may struggle to install on Mac without Postgres present, which is frustrating (however solutions can be found on Stack Overflow).

A simple address search CLI

Let me draw your attention to…

Hopefully this script is fairly easy to follow, but there are a couple of lines to draw your attention to

  • Line 4 – Note we have to tell SQLAlchemy we’re using the Postgres dialect so it understands TSVECTOR.
  • Lines 8 – 12 is simply SQLAlchemy boiler plate that sets up our connection and session for the app. You’ll need to swap out the connection details for your own.
  • Lines 17-20 I’ve chosen to map only 3 columns, you’ll probably want to map more.
  • Line 25 – is very important, here we append the OR operator to every word the user has supplied, meaning we’re returning addresses. You could extend this to allow the user to specify on exact match operator and change this to an & search.
  • Line 26 – Finally note we ask SQLAlchemy to match our search, and importantly we must supply the postgresql_reconfig param to say we’re searching in English. This is vital or you wont get the matches you expect.

Running our app

We can run our app from the command line simply by entering the following command:

python address_search.py 'forth street'

And we see our app print out all matching addresses that contain either Forth or Street 🙂

Ends

Hopefully you can see how easy it would be take the above code and integrate it into your apps and APIs. I hope you’ve found these tutorials useful. Happy text searching!

My work placement week @ thinkWhere

My name is Yacouba Traore. I am currently studying my second year BSc (Hons) Information Technology at Teeside University School of Computing.

I have had a great week placement at thinkWhere. During my week placement I was presented with a variety of opportunities. I had a great chance to meet everyone from the various parts of the business including CEO, Portfolio Manager, Business Managers, Accounts Managers, Developers, Service Desk Consultants and Office Administrators.

I have learned how the agile development systems work such as Scrum, as well as the differences between Agile and Waterfall. I have taken part in the different ceremonies including daily stand up, backlog refinement, demo and sprint planning.

Attending the Scrum daily-stand up

I have also learned about GIS (Geographical Information Systems) and the theMapCloud. I have also created my own map using QGIS where I am from in Côte d’Ivoire (Ivory Coast).

My first QGIS map of Côte d’Ivoire

The thinkWhere development team also worked with me to develop my coding skills including help with JavaScript, HTML and CSS using PyCharm. I used these skills to script a page showing a graph of theMapCloud usage metrics.

The role has allowed me to learn key skills and competencies of IT and business systems. On top of this I delivered 30 minutes of presentation about myself and a project I am working on at university.

One of the best things about the company is the people. They were very friendly, approachable and well organised. I have also made a great network of colleagues and made new friends.

I have really enjoyed my work placement. I have learnt a lot and have gained skills that I will take forward with me. I have also been given many opportunities and many new experiences. I’ve gained a deeper understanding of how large IT projects work, which is going to help me in the future.

Developing my JavaScript, HTML & CSS skills using PyCharm.

Being part of this placement has also helped me to develop my interview skills and my job prospects. It is also very valuable experience for my CV.

I hope to be back at thinkWhere again one day!