Re: [postgis-users] Search Engine
+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 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 > -- == 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 postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] Backup and Restore of a PostGis database
Hi Luis, With option 2, all the errors are about a failure to create objects (postgis functions & spatial_ref_sys entries I assume). Why is this a problem for you? They already exist so do not need to be reinstalled, the rest of your database should be installed, so you should have a fully functional copy. If you ignore the error warnings about trying to reinstall postgis objects, is there a reason the resulting database is not what you wanted to create? Cheers, Brent Wood --- On Wed, 8/4/10, Luís de Sousa wrote: > From: Luís de Sousa > Subject: [postgis-users] Backup and Restore of a PostGis database > To: "PostGIS Users Discussion" > Date: Wednesday, August 4, 2010, 11:15 PM > Dear all, > > I'm having a hard time porting a postgis database to > another server > (let's call server A the one where the original database > resides and > server B the one where I need to replicate the database). > I'm trying > it in two different ways: > > 1. Creating a fresh empty database at server B and then > restoring. > With this process PostGres complains of not having > permissions to > create basic types. > > 2. Creating a fresh database from the PostGis template. > This way > PostGres complains of objects that already exist. > > At the root of this is the fact that when dumping a PostGis > database, > all PostGis objects are created, functions, types, etc. Is > there a way > of making a dump excluding the creation of PostGis > objects? > > Thank you, > > Luís > ___ > 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
Re: [postgis-users] Search Engine
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 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
Re: [postgis-users] Update to current shapelib
Mark Cave-Ayland wrote Subject: Re: [postgis-users] Update to current shapelib Date: 04.08.2010 11:40 >Peter Hopfgartner wrote: > >> Hi >> >> I tried to upgrade the shapelib version in PostGIS to the current one, >as found in the current shapelib CVS repository. >> >> What I did was: >> * copy safileio.c shapefil.h shpopen.c dbfopen.c from shapelib >> * add support for dates and include the functions DBFReadSetup and >DBFReadDeleted from the current PostGIS shapelib version. >> * adapt Makefile.in >> >> This was done with 1.5, adapting this to trunk should be easy. >> >> Regards, >> >> Peter > >Hi Peter, > >Thanks for this. Was this for a particular bug that you needed to work >around? Please feel free to create a ticket in the bugtracker and attach >a patch in diff -u format against SVN trunk. > > >ATB, > >Mark. > Hi Mark, We are currently doing some analysis on some issues we have with shape import and export in our applications. One of the problems we have is related to code pages, which was fixed in the official shapelib. I've created http://trac.osgeo.org/postgis/ticket/554 . Regards, Peter ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] ST_Azimuth on the spheroid
Indeed, the code is there, it just needs to be exposed to SQL. Do you build your own PostGIS Francis? If so you can open a ticket for the feature and I'll provide a patch there you can apply. If not, it could be a long wait for PostGIS 2.0 and new features. (several months) P On Wed, Aug 4, 2010 at 4:10 AM, Francis Markham wrote: > Is there, or are we likely to see a way of calculating ST_Azimuth on the > spheroid? > > Is an implementation, overloading ST_Azimuth to accept the GEOGRAPHY type to > be expected? > > I suspect the code has already been written, given the existence of > ST_Distance_Spheroid which presumably uses Vincenty's Inverse formula (which > also calculates azimuth). > > Cheers, > > Francis Markham > > ___ > 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
Re: [postgis-users] How to setup a versioned datastore?
I'm sorry, I don't know german. Can't help you with that. For what I could understand is that the relation > uberschwemmungsgebiete existiert nicht doest not exist. Are you sure the table exists? It should work, my current version works. If you can translate the error would be nice :P I'll run other tests here. Please let me know. George On Wed, Aug 4, 2010 at 4:46 AM, Dominik Wiedner wrote: > Hi, > > I also downloaded the extension and managed to install it within the target > database. But when I attempt to enable the target-table, I get the following > error: > > HINWEIS: CREATE TABLE erstellt implizit eine Sequenz > »uberschwemmungsgebiete_h > istory_history_id_seq« für die »serial«-Spalte > »uberschwemmungsgebiete_hist > ory.history_id« > > KONTEXT: SQL-Anweisung »CREATE TABLE > public.Uberschwemmungsgebiete_history(history_id serial not null,date_added > timestamp not null default now(),date_deleted > timestamp default null,last_operation varchar(30) not null,active_user > varchar(90) not null default CURRENT_USER,current_version text not null,like > public.Uberschwemmungsgebiete,CONSTRAINT Uberschwemmungsgebiete_history_pk > primary key(history_id));« > > PL/pgSQL function "postgis_enable_history" line 66 at EXECUTE-Anweisung > FEHLER: Relation »public.uberschwemmungsgebiete« existiert nicht > This is the error I mean, since the table has the schema "public". > ZEILE 1: ...t CURRENT_USER,current_version text not null,like public.Ube... > ^ > ANFRAGE: CREATE TABLE public.Uberschwemmungsgebiete_history(history_id > serial not null,date_added timestamp not null default now(),date_deleted > timestamp default null,last_operation varchar(30) not null,active_user > varchar(90) not null default CURRENT_USER,current_version text not null,like > public.Uberschwemmungsgebiete,CONSTRAINT Uberschwemmungsgebiete_history_pk > primary key(history_id)); > > KONTEXT: PL/pgSQL function "postgis_enable_history" line 66 at > EXECUTE-Anweisung > > Any hints? > > Regards, > Dominik > ___ > postgis-users mailing list > > postgis-users@postgis.refractions.net > http://postgis.refractions.net/mailman/listinfo/postgis-users > -- George R. C. Silva Desenvolvimento em GIS http://blog.geoprocessamento.net ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] Backup and Restore of a PostGis database
Hi, Am Mittwoch, 4. August 2010 13:15:04 schrieb Luís de Sousa: > I'm having a hard time porting a postgis database to another server I suggest to follow the steps as described in the PostGIS manual for hard upgrades [1]. Regards, Frank [1] http://postgis.org/documentation/manual-1.5/ch02.html#upgrading -- Frank Koormann | ++49-541-335083-0 | http://www.intevation.de/ Intevation GmbH, Neuer Graben 17, 49074 Osnabrück | AG Osnabrück, HR B 18998 Geschäftsführer: Frank Koormann, Bernhard Reiter, Dr. Jan-Oliver Wagner ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] Backup and Restore of a PostGis database
Hi Luís, you may need to do first a "backup globals" of the entire database cluster A. (to import users, etc..) A good practice (I think) is to avoid public schema for data... Then, for option B, you can backup only data schemas (and use postgis function to populate geometry_columns at the end) Fred. 2010/8/4 Luís de Sousa > Dear all, > > I'm having a hard time porting a postgis database to another server > (let's call server A the one where the original database resides and > server B the one where I need to replicate the database). I'm trying > it in two different ways: > > 1. Creating a fresh empty database at server B and then restoring. > With this process PostGres complains of not having permissions to > create basic types. > > 2. Creating a fresh database from the PostGis template. This way > PostGres complains of objects that already exist. > > At the root of this is the fact that when dumping a PostGis database, > all PostGis objects are created, functions, types, etc. Is there a way > of making a dump excluding the creation of PostGis objects? > > Thank you, > > Luís > ___ > 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
Re: [postgis-users] Backup and Restore of a PostGis database
El Wednesday 04 August 2010, Luís de Sousa escribió: > 1. Creating a fresh empty database at server B and then restoring. > With this process PostGres complains of not having permissions to > create basic types. Are you executing that restore with a user who has permissions to create basic types? User 'postgres' perhaps? Probably this can be the easiest solution. Anyway, pg_dump has a lot of options, you can try to look for a better combination of them. -- María Arias de Reyna Domínguez Área de Operaciones Emergya Consultoría Tfno: +34 954 51 75 77 / +34 607 43 74 27 Fax: +34 954 51 64 73 www.emergya.es ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] Backup and Restore of a PostGis database
You could try the newest version of pgadmin, 1.12.0 beta3. It lets you do partial dumps of a database. Jan On 08/04/10 13:15, Luís de Sousa wrote: Dear all, I'm having a hard time porting a postgis database to another server (let's call server A the one where the original database resides and server B the one where I need to replicate the database). I'm trying it in two different ways: 1. Creating a fresh empty database at server B and then restoring. With this process PostGres complains of not having permissions to create basic types. 2. Creating a fresh database from the PostGis template. This way PostGres complains of objects that already exist. At the root of this is the fact that when dumping a PostGis database, all PostGis objects are created, functions, types, etc. Is there a way of making a dump excluding the creation of PostGis objects? Thank you, Luís ___ 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
[postgis-users] Backup and Restore of a PostGis database
Dear all, I'm having a hard time porting a postgis database to another server (let's call server A the one where the original database resides and server B the one where I need to replicate the database). I'm trying it in two different ways: 1. Creating a fresh empty database at server B and then restoring. With this process PostGres complains of not having permissions to create basic types. 2. Creating a fresh database from the PostGis template. This way PostGres complains of objects that already exist. At the root of this is the fact that when dumping a PostGis database, all PostGis objects are created, functions, types, etc. Is there a way of making a dump excluding the creation of PostGis objects? Thank you, Luís ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
[postgis-users] ST_Azimuth on the spheroid
Is there, or are we likely to see a way of calculating ST_Azimuth on the spheroid? Is an implementation, overloading ST_Azimuth to accept the GEOGRAPHY type to be expected? I suspect the code has already been written, given the existence of ST_Distance_Spheroid which presumably uses Vincenty's Inverse formula (which also calculates azimuth). Cheers, Francis Markham ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] Update to current shapelib
Peter Hopfgartner wrote: Hi I tried to upgrade the shapelib version in PostGIS to the current one, as found in the current shapelib CVS repository. What I did was: * copy safileio.c shapefil.h shpopen.c dbfopen.c from shapelib * add support for dates and include the functions DBFReadSetup and DBFReadDeleted from the current PostGIS shapelib version. * adapt Makefile.in This was done with 1.5, adapting this to trunk should be easy. Regards, Peter Hi Peter, Thanks for this. Was this for a particular bug that you needed to work around? Please feel free to create a ticket in the bugtracker and attach a patch in diff -u format against SVN trunk. ATB, Mark. -- Mark Cave-Ayland - Senior Technical Architect PostgreSQL - PostGIS Sirius Corporation plc - control through freedom http://www.siriusit.co.uk t: +44 870 608 0063 Sirius Labs: http://www.siriusit.co.uk/labs ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] Search Engine
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] How to setup a versioned datastore?
Hi, I also downloaded the extension and managed to install it within the target database. But when I attempt to enable the target-table, I get the following error: HINWEIS: CREATE TABLE erstellt implizit eine Sequenz »uberschwemmungsgebiete_h istory_history_id_seq« für die »serial«-Spalte »uberschwemmungsgebiete_hist ory.history_id« KONTEXT: SQL-Anweisung »CREATE TABLE public.Uberschwemmungsgebiete_history(history_id serial not null,date_added timestamp not null default now(),date_deleted timestamp default null,last_operation varchar(30) not null,active_user varchar(90) not null default CURRENT_USER,current_version text not null,like public.Uberschwemmungsgebiete,CONSTRAINT Uberschwemmungsgebiete_history_pk primary key(history_id));« PL/pgSQL function "postgis_enable_history" line 66 at EXECUTE-Anweisung FEHLER: Relation »public.uberschwemmungsgebiete« existiert nicht This is the error I mean, since the table has the schema "public". ZEILE 1: ...t CURRENT_USER,current_version text not null,like public.Ube... ^ ANFRAGE: CREATE TABLE public.Uberschwemmungsgebiete_history(history_id serial not null,date_added timestamp not null default now(),date_deleted timestamp default null,last_operation varchar(30) not null,active_user varchar(90) not null default CURRENT_USER,current_version text not null,like public.Uberschwemmungsgebiete,CONSTRAINT Uberschwemmungsgebiete_history_pk primary key(history_id)); KONTEXT: PL/pgSQL function "postgis_enable_history" line 66 at EXECUTE-Anweisung Any hints? Regards, Dominik ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
