[postgis-users] Search Engine

[Ricardo Bayley]

Uli and Simon,

Thank you very much for your quick response.
I agree in not searching table by table, and merging the results.
I will store the Full Text ts_vector data into one common table. I guess I
will use the TableOID + GID to know excatly where the indexed data came
from.

Thanks again to both of you !


Ricardo

On Wed, Aug 4, 2010 at 5:55 AM, uli mueller <uli.mueller at gmx.ch> wrote:

> 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
>
>
> _______________________________________________
> postgis-users mailing list
> postgis-users at postgis.refractions.net
> http://postgis.refractions.net/mailman/listinfo/postgis-users
>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20100804/131cfb85/attachment.html>