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.muel...@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@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@postgis.refractions.net > http://postgis.refractions.net/mailman/listinfo/postgis-users >
_______________________________________________ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users