[postgis-users] Search Engine

[Simon Greener]

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 <ricardo.bayley at gmail.com> 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: simon at spatialdbadvisor.com
   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