Re: [postgis-users] usage of AddGeometryColumn etc.

classic Classic list List threaded Threaded
6 messages Options
Reply | Threaded
Open this post in threaded view
|

Re: [postgis-users] usage of AddGeometryColumn etc.

Sandro Santilli-2
On Tue, Jun 01, 2004 at 01:19:47PM +0200, Hubert Fröhlich wrote:

> Hi list,
>
> I have a table with a geometry column.
>
>
>
> test=# create table demo(col1 text);
> test=#  select AddGeometryColumn('', 'demo',
> 'pgpoly','31494','MULTIPOLYGON',2);
>                                      
> addgeometrycolumn                                      
> ----------------------------------------------------------------------------------------------
> Geometry column pgpoly added to table public.demo WITH a SRID of 31494
> and type MULTIPOLYGON
> (1 row)
>
> When I drop the table without dropping the geometry column first
>
> test=# drop table demo;
> DROP TABLE
> test=# select * from geometry_columns;
> f_table_catalog | f_table_schema | f_table_name | f_geometry_column |
> coord_dimension | srid  |     type     | attrelid | varattnum | stats
> -----------------+----------------+--------------+-------------------+-----------------+-------+--------------+----------+-----------+-------
>                 | public         | demo         | pgpoly            
> |               2 | 31494 | MULTIPOLYGON |  5391696 |         3 |
> (1 row)
>
>
> I learn that DROP TABLE did not remove my geometry fully - I have to
> - either delete the entry in geometry_columns.
> - or execute a DropGeometryColumn(....) before DROPing the table.
>
> a) It would be quite comfortable just DROPping tables ... Is it intended
> or possible to improve the functions this way?

Not intended.
Postgresql guys does not approve triggers on system catalogues.

Maybe, the next AddGeoemtryColumn call could 'detect' those
spurious records in geometry_columns and get rid of them, or
just always replace records there.

What users think `bout this ?

---strk;

> b) If this is not possible, some hint in the documentation would be
> useful .
>
> Regards,
>
> Hubert
>
> --
> -------------------------------------------------------------------------------
> Dr.-Ing. Hubert Fröhlich
> Bezirksfinanzdirektion München
> Alexandrastr. 3, D-80538 München, GERMANY
> Tel. :+49 (0)89 / 2190 - 2980
> Fax  :+49 (0)89 / 2190 - 2997
> hubert dot froehlich at bvv dot bayern dot de
>
>
> _______________________________________________
> postgis-users mailing list
> [hidden email]
> http://postgis.refractions.net/mailman/listinfo/postgis-users

Reply | Threaded
Open this post in threaded view
|

Re: [postgis-users] usage of AddGeometryColumn etc.

Carl Anderson-2-2
strk wrote:

> On Tue, Jun 01, 2004 at 01:19:47PM +0200, Hubert Fröhlich wrote:
>
>>Hi list,
>>
>>I have a table with a geometry column.
>>
>>
>>
>>test=# create table demo(col1 text);
>>test=#  select AddGeometryColumn('', 'demo',
>>'pgpoly','31494','MULTIPOLYGON',2);
>>                                    
>>addgeometrycolumn                                      
>>----------------------------------------------------------------------------------------------
>>Geometry column pgpoly added to table public.demo WITH a SRID of 31494
>>and type MULTIPOLYGON
>>(1 row)
>>
>>When I drop the table without dropping the geometry column first
>>
>>test=# drop table demo;
>>DROP TABLE
>>test=# select * from geometry_columns;
>>f_table_catalog | f_table_schema | f_table_name | f_geometry_column |
>>coord_dimension | srid  |     type     | attrelid | varattnum | stats
>>-----------------+----------------+--------------+-------------------+-----------------+-------+--------------+----------+-----------+-------
>>                | public         | demo         | pgpoly            
>>|               2 | 31494 | MULTIPOLYGON |  5391696 |         3 |
>>(1 row)
>>
>>
>>I learn that DROP TABLE did not remove my geometry fully - I have to
>>- either delete the entry in geometry_columns.
>>- or execute a DropGeometryColumn(....) before DROPing the table.
>>
>>a) It would be quite comfortable just DROPping tables ... Is it intended
>>or possible to improve the functions this way?
>
>
> Not intended.
> Postgresql guys does not approve triggers on system catalogues.
>
> Maybe, the next AddGeoemtryColumn call could 'detect' those
> spurious records in geometry_columns and get rid of them, or
> just always replace records there.
>
> What users think `bout this ?
>

My last post to [postgis-devel] was an update to update_geometry_stats()
that NULLed attrelid before repopulating it. (among other things)

As a side effect of that activity after update_geometry_stats() any
entry in geometry_coulmns with a NULL attrelid is an unreachable table
and could be deleted.  Not that I think its a good idea to automate the
delete though.





--
Carl Anderson
GIS Manager, Fulton County E&CD
404.730.8026
[hidden email]


Reply | Threaded
Open this post in threaded view
|

Re: [postgis-users] usage of AddGeometryColumn etc.

Sandro Santilli-2
On Tue, Jun 01, 2004 at 09:01:01AM -0400, Carl Anderson wrote:

> strk wrote:
> >On Tue, Jun 01, 2004 at 01:19:47PM +0200, Hubert Fröhlich wrote:
> >
> >>Hi list,
> >>
> >>I have a table with a geometry column.
> >>
> >>
> >>
> >>test=# create table demo(col1 text);
> >>test=#  select AddGeometryColumn('', 'demo',
> >>'pgpoly','31494','MULTIPOLYGON',2);
> >>                                    
> >>addgeometrycolumn                                      
> >>----------------------------------------------------------------------------------------------
> >>Geometry column pgpoly added to table public.demo WITH a SRID of 31494
> >>and type MULTIPOLYGON
> >>(1 row)
> >>
> >>When I drop the table without dropping the geometry column first
> >>
> >>test=# drop table demo;
> >>DROP TABLE
> >>test=# select * from geometry_columns;
> >>f_table_catalog | f_table_schema | f_table_name | f_geometry_column |
> >>coord_dimension | srid  |     type     | attrelid | varattnum | stats
> >>-----------------+----------------+--------------+-------------------+-----------------+-------+--------------+----------+-----------+-------
> >>               | public         | demo         | pgpoly            
> >>|               2 | 31494 | MULTIPOLYGON |  5391696 |         3 |
> >>(1 row)
> >>
> >>
> >>I learn that DROP TABLE did not remove my geometry fully - I have to
> >>- either delete the entry in geometry_columns.
> >>- or execute a DropGeometryColumn(....) before DROPing the table.
> >>
> >>a) It would be quite comfortable just DROPping tables ... Is it intended
> >>or possible to improve the functions this way?
> >
> >
> >Not intended.
> >Postgresql guys does not approve triggers on system catalogues.
> >
> >Maybe, the next AddGeoemtryColumn call could 'detect' those
> >spurious records in geometry_columns and get rid of them, or
> >just always replace records there.
> >
> >What users think `bout this ?
> >
>
> My last post to [postgis-devel] was an update to update_geometry_stats()
> that NULLed attrelid before repopulating it. (among other things)
>
> As a side effect of that activity after update_geometry_stats() any
> entry in geometry_coulmns with a NULL attrelid is an unreachable table
> and could be deleted.  Not that I think its a good idea to automate the
> delete though.

Again, triggers on system tables are not seen with simpaty.
If you guys write down every occurrence of the stale record problem
we can find out an ad-hoc solution (like adding check in AddGeometryColumn
which I think is the one function making noise about it).

--strk;

PS: I've just committed changes in the postgis.sql.in file to make
    find_extent and build_histogram2d (and update_geometry_stats)
     better support schema in the way you suggested.

>
>
>
>
>
> --
> Carl Anderson
> GIS Manager, Fulton County E&CD
> 404.730.8026
> [hidden email]
>
> _______________________________________________
> postgis-devel mailing list
> [hidden email]
> http://postgis.refractions.net/mailman/listinfo/postgis-devel

Reply | Threaded
Open this post in threaded view
|

Re: [postgis-users] usage of AddGeometryColumn etc.

David Blasby-3
strk wrote:

> Again, triggers on system tables are not seen with simpaty.
> If you guys write down every occurrence of the stale record problem
> we can find out an ad-hoc solution (like adding check in AddGeometryColumn
> which I think is the one function making noise about it).
>
> --strk;
>
> PS: I've just committed changes in the postgis.sql.in file to make
>     find_extent and build_histogram2d (and update_geometry_stats)
>      better support schema in the way you suggested.

One way would be to make a geometry_columns_meta table with all the
information in it (like we have now).  Then have geometry_columns be a
view that crosses the system table with the geometry_columns_meta table.
  This will hide any "stale" rows.

Alternately, you could have AddGeometryColumn() behave smarter - it
should automagically delete any stale columns in the current
geometry_columns table (or the future geometry_columns_meta table).

dave

Reply | Threaded
Open this post in threaded view
|

Re: [postgis-users] usage of AddGeometryColumn etc.

Sandro Santilli-2
On Tue, Jun 01, 2004 at 09:12:04AM -0700, David Blasby wrote:

> strk wrote:
>
> >Again, triggers on system tables are not seen with simpaty.
> >If you guys write down every occurrence of the stale record problem
> >we can find out an ad-hoc solution (like adding check in AddGeometryColumn
> >which I think is the one function making noise about it).
> >
> >--strk;
> >
> >PS: I've just committed changes in the postgis.sql.in file to make
> >    find_extent and build_histogram2d (and update_geometry_stats)
> >     better support schema in the way you suggested.
>
> One way would be to make a geometry_columns_meta table with all the
> information in it (like we have now).  Then have geometry_columns be a
> view that crosses the system table with the geometry_columns_meta table.
>  This will hide any "stale" rows.
>
> Alternately, you could have AddGeometryColumn() behave smarter - it
> should automagically delete any stale columns in the current
> geometry_columns table (or the future geometry_columns_meta table).

I've modified AddGeoemtryColumn() to delete any existing record
with the same catalogue,schema,table,column values as the
ones being added. This will avoid primary key constraint
from being violated.

--strk;

>
> dave
> _______________________________________________
> postgis-devel mailing list
> [hidden email]
> http://postgis.refractions.net/mailman/listinfo/postgis-devel

Reply | Threaded
Open this post in threaded view
|

DropGeometryTable

Sandro Santilli-2
In reply to this post by Sandro Santilli-2
On Tue, Jun 01, 2004 at 12:05:56PM -0700, Wood Brent wrote:
>
[..]
> I have a stored query called drop_geo_table which does a clean removal of the
> geometry_column entry & then the actual table.
>
> I think a similar built in function, such as DropGeometryTable() (which would
> call DropGeometryColmn(), then drop the table) or an argument to
> DropGeometryColumn() to drop the table after removing the record would be  a
> simple way to provide the functionality.

Current CVS has DropGeometryTable([<schema>], <table>).
It sounded as a good idea.

--strk;