+1 for triggers. It's always the best way to solve these situations, be it GIS or not.
Bèrto On 4 August 2010 11:55, uli mueller <[email protected]> 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 > > [email protected] > > http://postgis.refractions.net/mailman/listinfo/postgis-users > > > -- > geOps GeoInformatics > www.geOps.de > D-79098 Freiburg > > > _______________________________________________ > postgis-users mailing list > [email protected] > http://postgis.refractions.net/mailman/listinfo/postgis-users > -- ============================== Constitution du 24 juin 1793 - Article 35. - Quand le gouvernement viole les droits du peuple, l'insurrection est, pour le peuple et pour chaque portion du peuple, le plus sacré des droits et le plus indispensable des devoirs.
_______________________________________________ postgis-users mailing list [email protected] http://postgis.refractions.net/mailman/listinfo/postgis-users
