Ricardo, This is how I did it for SQL Server: http://www.spatialdbadvisor.com/sql_server_blog/166/new-presentation-on-active-spatial-metadata Follow this link to the presentation.
I don't know anything about the data model that informs your schema, but the approach above honours any schema as I assume that a database is not like a bunch of shapefiles - you don't normally have a high level of management access to a business's databases. So, if PostgreSQL has an equivalent to SQL Server's FULLTEXT indexing, simply index all text columns on a table in place. If you have metadata other than GEOMETRY_COLUMNS (I created an "extended" version of GEOMETRY_TABLES in SQL Server as it does not have such a table and then created an OGC "compliant" view over the top) then also use them as well. Simon On Wed, 04 Aug 2010 08:33:19 +1000, Ricardo Bayley <[email protected]> wrote:
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
-- SpatialDB Advice and Design, Solutions Architecture and Programming, Oracle Database 10g Administrator Certified Associate; Oracle Database 10g SQL Certified Professional Oracle Spatial, SQL Server, PostGIS, MySQL, ArcSDE, Manifold GIS, FME, Radius Topology and Studio Specialist. 39 Cliff View Drive, Allens Rivulet, 7150, Tasmania, Australia. Website: www.spatialdbadvisor.com Email: [email protected] Voice: +61 362 396397 Mobile: +61 418 396391 Skype: sggreener Longitude: 147.20515 (147° 12' 18" E) Latitude: -43.01530 (43° 00' 55" S) GeoHash: r22em9r98wg NAC:W80CK 7SWP3 _______________________________________________ postgis-users mailing list [email protected] http://postgis.refractions.net/mailman/listinfo/postgis-users
