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

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
  import argparse
  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()

  class OpenNames(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):
          print(search_for)

          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:
              print(result.text)

  if __name__ == '__main__':
      parser = argparse.ArgumentParser()
      parser.add_argument('address', help='Address you want to search for')
      args = parser.parse_args()

      open_names = OpenNames()
      open_names.search_address(args.address)

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'

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.