Re: [postgis-users] how to keep geometry_columns in sync with tables and views
G'day Brent, I'm forever creating tables as subsets of existing tables so it is a truly useful function, however, I've suffered the same concerns - perhaps it is worth pursuing the name being changed? I've also never really understood the distinction between the populate_ and the probe_ functions? the probe_ one appears to be a 'lite' version, but it may have some other purpose that I don't understand? cheers Ben On 19/05/2011, at 9:02 AM, pcr...@pcreso.com wrote: > I foubd this an unfortunately ambiguous name. > > it doesn't populate geometry columns so much as update the geometry_columns > table. > > But irrespective of the name, it is nice to have :-) > > > Cheers > > Brent Wood > > --- On Thu, 5/19/11, Ben Madin wrote: > > From: Ben Madin > Subject: Re: [postgis-users] how to keep geometry_columns in sync with tables > and views > To: "PostGIS Users Discussion" > Date: Thursday, May 19, 2011, 12:50 PM > > Ge, > > Try > > SELECT Populate_Geometry_Columns(); > > http://postgis.refractions.net/docs/Populate_Geometry_Columns.html > > which promises to truncate the geometry columns table first, then rebuild it. > > cheers > > Ben > > > > On 18/05/2011, at 8:05 PM, G. van Es wrote: > >> Hi Edward, >> >> This will not work because this function doesn't do anything with views. >> Also stale records aren't removed. >> >> Ge >> >> --- On Wed, 5/18/11, Edward Mac Gillavry wrote: >> >> From: Edward Mac Gillavry >> Subject: Re: [postgis-users] how to keep geometry_columns in sync with >> tables and views >> To: postgis-users@postgis.refractions.net >> Date: Wednesday, May 18, 2011, 4:57 AM >> >> Hi Ge, >> >> You may want to check Probe_Geometry_Columns >> (http://postgis.refractions.net/docs/Probe_Geometry_Columns.html). >> >> Kind regards, >> >> Edward >> >> >> >> Date: Wed, 18 May 2011 04:38:51 -0700 >> From: gves2...@yahoo.com >> To: postgis-users@postgis.refractions.net >> Subject: [postgis-users] how to keep geometry_columns in sync with tables >> and views >> >> Hi All, >> >> We have a lot of tables and views updated, or better said, replaced on a >> daily basis. We have seen that under certain conditions (which are unclear) >> entries of the geometry_columns table are removed. So a mismatch occurs so >> now and then resulting in showing either no data or being very slow when an >> application has to do a table scan to obtain the geometry type. >> >> What I like to have is a procedure which checks all tables and views against >> the geometry_columns table and makes if necessary the right corrections. >> >> Before inventing the wheel again, does anyone know if this procedure already >> exist or knows perhaps another/better way to achieve this? >> >> Thanks in advance, >> >> Ge >> >> >> >> >> >> ___ postgis-users mailing list >> postgis-users@postgis.refractions.net >> http://postgis.refractions.net/mailman/listinfo/postgis-users >> >> -Inline Attachment Follows- >> >> ___ >> 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 > > > -Inline Attachment Follows- > > ___ > 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 mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] how to keep geometry_columns in sync with tables and views
I foubd this an unfortunately ambiguous name. it doesn't populate geometry columns so much as update the geometry_columns table. But irrespective of the name, it is nice to have :-) Cheers Brent Wood --- On Thu, 5/19/11, Ben Madin wrote: From: Ben Madin Subject: Re: [postgis-users] how to keep geometry_columns in sync with tables and views To: "PostGIS Users Discussion" Date: Thursday, May 19, 2011, 12:50 PM Ge, Try SELECT Populate_Geometry_Columns(); http://postgis.refractions.net/docs/Populate_Geometry_Columns.html which promises to truncate the geometry columns table first, then rebuild it. cheers Ben On 18/05/2011, at 8:05 PM, G. van Es wrote: Hi Edward, This will not work because this function doesn't do anything with views. Also stale records aren't removed. Ge --- On Wed, 5/18/11, Edward Mac Gillavry wrote: From: Edward Mac Gillavry Subject: Re: [postgis-users] how to keep geometry_columns in sync with tables and views To: postgis-users@postgis.refractions.net Date: Wednesday, May 18, 2011, 4:57 AM Hi Ge, You may want to check Probe_Geometry_Columns (http://postgis.refractions.net/docs/Probe_Geometry_Columns.html). Kind regards, Edward Date: Wed, 18 May 2011 04:38:51 -0700 From: gves2...@yahoo.com To: postgis-users@postgis.refractions.net Subject: [postgis-users] how to keep geometry_columns in sync with tables and views Hi All, We have a lot of tables and views updated, or better said, replaced on a daily basis. We have seen that under certain conditions (which are unclear) entries of the geometry_columns table are removed. So a mismatch occurs so now and then resulting in showing either no data or being very slow when an application has to do a table scan to obtain the geometry type. What I like to have is a procedure which checks all tables and views against the geometry_columns table and makes if necessary the right corrections. Before inventing the wheel again, does anyone know if this procedure already exist or knows perhaps another/better way to achieve this? Thanks in advance, Ge ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users -Inline Attachment Follows- ___ 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 -Inline Attachment Follows- ___ 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 keep geometry_columns in sync with tables and views
Ge, Try SELECT Populate_Geometry_Columns(); http://postgis.refractions.net/docs/Populate_Geometry_Columns.html which promises to truncate the geometry columns table first, then rebuild it. cheers Ben On 18/05/2011, at 8:05 PM, G. van Es wrote: > Hi Edward, > > This will not work because this function doesn't do anything with views. Also > stale records aren't removed. > > Ge > > --- On Wed, 5/18/11, Edward Mac Gillavry wrote: > > From: Edward Mac Gillavry > Subject: Re: [postgis-users] how to keep geometry_columns in sync with tables > and views > To: postgis-users@postgis.refractions.net > Date: Wednesday, May 18, 2011, 4:57 AM > > Hi Ge, > > You may want to check Probe_Geometry_Columns > (http://postgis.refractions.net/docs/Probe_Geometry_Columns.html). > > Kind regards, > > Edward > > > > Date: Wed, 18 May 2011 04:38:51 -0700 > From: gves2...@yahoo.com > To: postgis-users@postgis.refractions.net > Subject: [postgis-users] how to keep geometry_columns in sync with tables > and views > > Hi All, > > We have a lot of tables and views updated, or better said, replaced on a > daily basis. We have seen that under certain conditions (which are unclear) > entries of the geometry_columns table are removed. So a mismatch occurs so > now and then resulting in showing either no data or being very slow when an > application has to do a table scan to obtain the geometry type. > > What I like to have is a procedure which checks all tables and views against > the geometry_columns table and makes if necessary the right corrections. > > Before inventing the wheel again, does anyone know if this procedure already > exist or knows perhaps another/better way to achieve this? > > Thanks in advance, > > Ge > > > > > > ___ postgis-users mailing list > postgis-users@postgis.refractions.net > http://postgis.refractions.net/mailman/listinfo/postgis-users > > -Inline Attachment Follows- > > ___ > 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 mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] Invisible Table
The case was the problem. Thanks Mark Bob From: MarkW Sent: Wednesday, May 18, 2011 12:05 PM To: PostGIS Users Discussion Subject: Re: [postgis-users] Invisible Table Do you mean that the SQL Pane shows this: CREATE TABLE public."MyTestTable" ( etc... ) Case-sensitivity may be the issue - does your simple sql query include quotes around the table name? If it is a case-sensitivity issue and you want to avoid it, the Shapefile and Dbf gui loader plugin for PGAdmin (as one example of another way to do it) will change table and column names to lowercase for you. Mark On Wed, May 18, 2011 at 2:39 PM, Bob Pawley wrote: Hi I imported a shape file using the QGis shapefile to PostgreSQL tool. The resulting table appears as normal on the PGAdmin Object Browser and in QGis as spatial geometry. However the table is not existing according to a simple sql query. Other tables are seen with no problem. The only difference I can detect is when the table name in the Object Browser is highlighted the SQL Pane shows quotes around the table name in the Create Table statement. The other tables, self created not imported, do not have the quotes around the table name. Would these quotes be the cause of an sql query not being able to see the table?? Bob ___ 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 mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] Invisible Table
Do you mean that the SQL Pane shows this: CREATE TABLE public."MyTestTable" ( etc... ) Case-sensitivity may be the issue - does your simple sql query include quotes around the table name? If it is a case-sensitivity issue and you want to avoid it, the Shapefile and Dbf gui loader plugin for PGAdmin (as one example of another way to do it) will change table and column names to lowercase for you. Mark On Wed, May 18, 2011 at 2:39 PM, Bob Pawley wrote: > Hi > > I imported a shape file using the QGis shapefile to PostgreSQL tool. > > The resulting table appears as normal on the PGAdmin Object Browser and in > QGis as spatial geometry. > > However the table is not existing according to a simple sql query. Other > tables are seen with no problem. > > The only difference I can detect is when the table name in the Object > Browser is highlighted the SQL Pane shows quotes around the table name in > the Create Table statement. > > The other tables, self created not imported, do not have the quotes around > the table name. > > Would these quotes be the cause of an sql query not being able to see the > table?? > > Bob > > ___ > 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] Invisible Table
Hi I imported a shape file using the QGis shapefile to PostgreSQL tool. The resulting table appears as normal on the PGAdmin Object Browser and in QGis as spatial geometry. However the table is not existing according to a simple sql query. Other tables are seen with no problem. The only difference I can detect is when the table name in the Object Browser is highlighted the SQL Pane shows quotes around the table name in the Create Table statement. The other tables, self created not imported, do not have the quotes around the table name. Would these quotes be the cause of an sql query not being able to see the table?? Bob___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] how to keep geometry_columns in sync with tables and views
Hi Edward, This will not work because this function doesn't do anything with views. Also stale records aren't removed. Ge --- On Wed, 5/18/11, Edward Mac Gillavry wrote: From: Edward Mac Gillavry Subject: Re: [postgis-users] how to keep geometry_columns in sync with tables and views To: postgis-users@postgis.refractions.net Date: Wednesday, May 18, 2011, 4:57 AM Hi Ge, You may want to check Probe_Geometry_Columns (http://postgis.refractions.net/docs/Probe_Geometry_Columns.html). Kind regards, Edward Date: Wed, 18 May 2011 04:38:51 -0700 From: gves2...@yahoo.com To: postgis-users@postgis.refractions.net Subject: [postgis-users] how to keep geometry_columns in sync with tables and views Hi All, We have a lot of tables and views updated, or better said, replaced on a daily basis. We have seen that under certain conditions (which are unclear) entries of the geometry_columns table are removed. So a mismatch occurs so now and then resulting in showing either no data or being very slow when an application has to do a table scan to obtain the geometry type. What I like to have is a procedure which checks all tables and views against the geometry_columns table and makes if necessary the right corrections. Before inventing the wheel again, does anyone know if this procedure already exist or knows perhaps another/better way to achieve this? Thanks in advance, Ge ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users -Inline Attachment Follows- ___ 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 keep geometry_columns in sync with tables and views
Hi Ge, You may want to check Probe_Geometry_Columns (http://postgis.refractions.net/docs/Probe_Geometry_Columns.html). Kind regards, Edward Date: Wed, 18 May 2011 04:38:51 -0700 From: gves2...@yahoo.com To: postgis-users@postgis.refractions.net Subject: [postgis-users] how to keep geometry_columns in sync with tables and views Hi All, We have a lot of tables and views updated, or better said, replaced on a daily basis. We have seen that under certain conditions (which are unclear) entries of the geometry_columns table are removed. So a mismatch occurs so now and then resulting in showing either no data or being very slow when an application has to do a table scan to obtain the geometry type. What I like to have is a procedure which checks all tables and views against the geometry_columns table and makes if necessary the right corrections. Before inventing the wheel again, does anyone know if this procedure already exist or knows perhaps another/better way to achieve this? Thanks in advance, Ge ___ 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] how to keep geometry_columns in sync with tables and views
Hi All, We have a lot of tables and views updated, or better said, replaced on a daily basis. We have seen that under certain conditions (which are unclear) entries of the geometry_columns table are removed. So a mismatch occurs so now and then resulting in showing either no data or being very slow when an application has to do a table scan to obtain the geometry type. What I like to have is a procedure which checks all tables and views against the geometry_columns table and makes if necessary the right corrections. Before inventing the wheel again, does anyone know if this procedure already exist or knows perhaps another/better way to achieve this? Thanks in advance, Ge ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
