More

Extension

PostgreSQL contains a lot of extensions. During this section, we will review some of them, showing how you can use them with Spgeed.

Soundex

The soundex system is a mechanism which allows you to index names according to phonetics. This can be really powerful since it allows you to perform very rich search, based on how the words sound !

To enable the soundex extension, you need to add it in your schema creation script:

CREATE EXTENSION IF NOT EXISTS "fuzzystrmatch";

Example: perform a soundex search

@Select(sql = "SELECT * FROM journey WHERE soundex(name) = soundex(${name})");
Journey[] searchByName(String name);

You can find more information about this on PostgreSQL.

Earth

The earth extension provides algorithms to compute distances on the surface of the Earth. For example, you can use that feature to compute the distance between two geographical points on earth.

To enable the earth extension, you need to add it in your schema creation script:

CREATE EXTENSION IF NOT EXISTS "cube";
CREATE EXTENSION IF NOT EXISTS "earthdistance";

Exemple: let's find all available journeys around a certain point.

@Select(sql = "SELECT * FROM journey " +
        "WHERE POINT(latitude, longitude) <@> POINT(${latitude}, ${longitude}) < 30");
Journey[] getJourneysNear(float latitude, float longitude);

You can find more information about this on PostgreSQL.

Full-text search

PostgreSQL also offers us a way to perform full-text search.

Exemple: let's perform a full-text search on journeys names and descriptions

@Select(sql = "SELECT * FROM journey WHERE name || ' ' || description @@ ${query}");
Journey[] search(String query);

name || ' ' || description means that journeys names and journeys description will be concatenated. The full-text search will be performed on the result of that concatenation.

Warning

For performance purposes, you should create a column of tsvector type on which you add a gin index. The full-text search should then be performed on that column. According to the previous example, we should then create a tsvector column in which we put the concatenation of journeys names and descriptions (name || ' ' || description) and do the search on that column.

You can find more information about this on PostgreSQL.