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] 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
