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.
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
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
|from sqlalchemy import create_engine, Column, Integer, String|
|from sqlalchemy.ext.declarative import declarative_base|
|from sqlalchemy.dialects.postgresql import TSVECTOR|
|from sqlalchemy.orm import sessionmaker|
|# Create DB Session|
|engine = create_engine('postgresql://iain:password@localhost:5432/Real-World')|
|Session = sessionmaker(bind=engine)|
|session = Session()|
|Base = declarative_base()|
|__tablename__ = 'open_names'|
|# Map DB columns we're interested in|
|ogc_fid = Column(Integer, primary_key=True)|
|text = Column(String)|
|textsearchable = Column(TSVECTOR)|
|def search_address(self, search_for: str):|
|or_search = search_for.replace(' ', ' | ') # Append OR operator to every word searched|
|results = session.query(OpenNames.text).filter(OpenNames.textsearchable.match(or_search, postgresql_reconfig='english'))|
|for result in results:|
|if __name__ == '__main__':|
|parser = argparse.ArgumentParser()|
|parser.add_argument('address', help='Address you want to search for')|
|args = parser.parse_args()|
|open_names = OpenNames()|
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 🙂
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.