GIN/GiST Full Text searches through openlayers, geoserver and postgres

Question

I have a a web app that automatically maps recent tweets and allows you to search for certain keywords. it uses Postgres for the database, geoserver as the server, and openlayers as the cartographic library. Right now tweet keywords are done using the ILIKE query.

This works pretty well if the keyword i'm looking for is a commonly used word, like 'love' , 'friend', 'OMG', etc. but less common words like "geospatial" need to search through a lot more data in order to find the last n instances of the word. This is dead slow.

To combat my slowness I'm building a GIN index on my tweet field in postgres. Two days later and i'm still waiting for the index to complete. Once it's built though, I'll try a few queries using SQL view parameters and hopefully this will dramatically speed things up.

Even if this approach works though, it's not going to be all that useful if the index takes so long to create -- the maintenance of it will not be able to keep up will the incoming flood of data. I think I will try out the GiST index next and see how long it takes as I understand it is much faster to this build index.

Beyond this, what can I do next? Do options like Solr work with Geoserver?? Are there any common approaches to this type of problem and data size/rate?

Answer 1

It sounds like you could do it with with Solr. However, I would use ElasticSearch its easier to get started with. Since you are working with points it seems like a good fit.

You would use it standalone as a datasource to Openlayers, it doesn't act act as a "store" to geoserver.

Answer 2

You could also try the Full Text Search PostgreSQL capabilities and that would still match your architecture (OL, GeoServer and PostgreSQL).

Here you have a very nice resource on PostgreSQL FTS: http://blog.lostpropertyhq.com/postgres-full-text-search-is-good-enough/ According to it, you should build a GiST (and not a GIN) index because it works better with dynamic data.

By the way, avoid LIKE/ILIKE queries if you want good search performance, they are one of the SQL Anti-patterns! See p.135: http://www.scribd.com/doc/2670985/SQL-Antipatterns Better use PostgreSQL FTS!

Answer 3

You could use GeoAlchemy + Full Text Search (FTS). It can probably increase the speed of your queries. However, it seems that FTS needs to be adapted to do such work in GA. I put below some links that may give you some more insights on both tools:

GeoAlchemy:

1. http://frankpurcell.com/code/tutorial
2. http://www.geoalchemy.org/_sources/tutorial.txt
3. https://github.com/geoalchemy/geoalchemy/tree/master/examples
4. https://geoalchemy-2.readthedocs.org/en/latest/orm_tutorial.html

FTS (with SqlAlchemy, but it's already adapted to SA as a patch):

1. http://nibrahim.net.in/2013/11/29/sqlalchemy_and_full_text_searching_in_postgresql.html