[postgis-users] Search Engine

[uli mueller]

Ricardo,

Clearly, if you want to search across different tables you need a way to
combine data in a common place. You should not do anything like search
the tables one after the other and combine the results.

Even if you merge all tables into one, you will need some explicit
mechanism (trigger!) to keep your tsvector up to date.

Using inheritance? I would not see inheritance as a real goodie with
PostgreSQL. There are some serious caveats that may cause more problems
than inheritance can solve. Check the last paragraph in the docs on
inheritance
(http://www.postgresql.org/docs/8.4/interactive/ddl-inherit.html).

A system using triggers to build the tsvector is not so hard to
maintain. Once you have written the triggers it simply runs and runs.
Any time data in any relevant table changes, a trigger updates the
tsvector, some key (gid or whatever) and maybe other data like bounding
boxes in the one and only table that will be searched. Some challenge
could arise, if it takes too long to rebuild your index on the tsvector.
But normally this is not critical.

We use the trigger approach for our search engine on
http://mapmatters.org . The hardest thing there was and still is to
optimize the way how data are combined and weighted for the tsvector (
so how you feed the "to_tsvector" function).

Uli


Am 04.08.2010 00:33, schrieb Ricardo Bayley:
> Hi fellows,
> 
> I am creating a search engine for my spatial data. 
> And I am thinking of the best approach.
> 
> My idea is to have a full text search (tsvector) coulmn for every table. 
> Instead of performing a search on every table, I have thought of a few
> options
> 
> 1. "Merge" all tables into one, regardless of their geometry type.
> 2. Use PostgreSQL goodies such as table Inheritance to split geometry
> types. (not sure if it would be of any good)
> 3. Create a table to store table oid, gid and full text search data of
> every table in my system, and query this table instead. This should be
> harder to maintain, since it should be done through triggers and rules.
> 
> Hope I explained it clearly.
> 
> By the way, at start I only have 20 tables, with not more than 500k rows
> total. So it is not much, but this should grow considerably.
> 
> 
> Do you guys have any thoughts on this ?
> 
> 
> Looking foward to hearing from you.
> 
> 
> Ricardo
> 
> 
> 
> _______________________________________________
> postgis-users mailing list
> postgis-users at postgis.refractions.net
> http://postgis.refractions.net/mailman/listinfo/postgis-users


-- 
geOps GeoInformatics
www.geOps.de
D-79098 Freiburg