Renaming Raster Table

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

Renaming Raster Table

Osahon Oduware
Hi All,

I used the raster2pgsql tool to load a raster with overviews into a PostGIS database using the following format:
raster2pgsql -s <SRID> -d -Y -e -I -C -F -M -l 2,4,8,16 /path/to/raster/file <table_name> | psql -h <host_address> -U postgres -p 5432 -d <database>

Now, I want to rename the raster table and I would like to know the affected tables and the required changes to be done without affecting the functionality of the raster/overviews.

_______________________________________________
postgis-users mailing list
[hidden email]
https://lists.osgeo.org/mailman/listinfo/postgis-users
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Renaming Raster Table

Regina Obe-2

Osahon,

 

I think the only place where the name of table is explicitly referenced is in the constraint called enforce_overview_rast that is on each of the overview tables.

The raster_overviews view uses the information in this constraint to populate the parent table.

 

I forget why we decided not to go by name instead of table oid.  If we did, then renaming the tables would be sufficient.

 

Anyway to rename the tables, do the following:

 

1)      Rename the tables as you would normally with

ALTER TABLE ned RENAME TO ned_2017;

ALTER TABLE o_3_ned RENAME TO o_3_ned_2017;

 

Etc.

2)      Drop the constraint on each over view table and read it.

So for example if your table is in schema public, and you renamed it to o_3_ned_2017

 

You'd do:

 

ALTER TABLE public.o_3_ned_2017 DROP CONSTRAINT enforce_overview_rast;

 

ALTER TABLE public.o_3_nj_ned_2017

  ADD CONSTRAINT enforce_overview_rast CHECK (_overview_constraint(rast, 3, 'public'::name, 'ned_2017'::name, 'rast'::name));

 

 

If you have a lot of these, it's fairly easy to script them by querying the raster_overview table and executing the outputs of this query.  NOT tested so you might need to fiddle with it.

 

SELECT  'ALTER TABLE ' || quote_ident(o_table_schema)  || '.' || quote_ident(o_table_name) || ' DROP CONSTRAINT enforce_overview_rast;

ALTER TABLE ' ||  quote_ident(o_table_schema)  || '.' || quote_ident(o_table_name) || ' ADD CONSTRAINT enforce_overview_rast

CHECK (_overview_constraint(rast, ' || overview_factor || ', ' || quote_literal(r_table_schema) || '::name, ' || quote_literal('mynewtablename') || ', ' || quote_literal(o_raster_column) || '::name)); '  AS sql FROM  raster_overviews;

 

 

Hope that helps,

Regina

http://postgis.us

 

 

From: postgis-users [mailto:[hidden email]] On Behalf Of Osahon Oduware
Sent: Friday, July 07, 2017 3:55 AM
To: PostGIS Users Discussion <[hidden email]>
Subject: [postgis-users] Renaming Raster Table

 

Hi All,

 

I used the raster2pgsql tool to load a raster with overviews into a PostGIS database using the following format:

raster2pgsql -s <SRID> -d -Y -e -I -C -F -M -l 2,4,8,16 /path/to/raster/file <table_name> | psql -h <host_address> -U postgres -p 5432 -d <database>

 

Now, I want to rename the raster table and I would like to know the affected tables and the required changes to be done without affecting the functionality of the raster/overviews.


_______________________________________________
postgis-users mailing list
[hidden email]
https://lists.osgeo.org/mailman/listinfo/postgis-users
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Renaming Raster Table

Osahon Oduware
Hi Regina,

Sorry for my late response, I have been away on another task. I would try out what you suggested and give you feedback. Thanks a lot.

On Fri, Jul 7, 2017 at 9:20 PM, Regina Obe <[hidden email]> wrote:

Osahon,

 

I think the only place where the name of table is explicitly referenced is in the constraint called enforce_overview_rast that is on each of the overview tables.

The raster_overviews view uses the information in this constraint to populate the parent table.

 

I forget why we decided not to go by name instead of table oid.  If we did, then renaming the tables would be sufficient.

 

Anyway to rename the tables, do the following:

 

1)      Rename the tables as you would normally with

ALTER TABLE ned RENAME TO ned_2017;

ALTER TABLE o_3_ned RENAME TO o_3_ned_2017;

 

Etc.

2)      Drop the constraint on each over view table and read it.

So for example if your table is in schema public, and you renamed it to o_3_ned_2017

 

You'd do:

 

ALTER TABLE public.o_3_ned_2017 DROP CONSTRAINT enforce_overview_rast;

 

ALTER TABLE public.o_3_nj_ned_2017

  ADD CONSTRAINT enforce_overview_rast CHECK (_overview_constraint(rast, 3, 'public'::name, 'ned_2017'::name, 'rast'::name));

 

 

If you have a lot of these, it's fairly easy to script them by querying the raster_overview table and executing the outputs of this query.  NOT tested so you might need to fiddle with it.

 

SELECT  'ALTER TABLE ' || quote_ident(o_table_schema)  || '.' || quote_ident(o_table_name) || ' DROP CONSTRAINT enforce_overview_rast;

ALTER TABLE ' ||  quote_ident(o_table_schema)  || '.' || quote_ident(o_table_name) || ' ADD CONSTRAINT enforce_overview_rast

CHECK (_overview_constraint(rast, ' || overview_factor || ', ' || quote_literal(r_table_schema) || '::name, ' || quote_literal('mynewtablename') || ', ' || quote_literal(o_raster_column) || '::name)); '  AS sql FROM  raster_overviews;

 

 

Hope that helps,

Regina

http://postgis.us

 

 

From: postgis-users [mailto:[hidden email]] On Behalf Of Osahon Oduware
Sent: Friday, July 07, 2017 3:55 AM
To: PostGIS Users Discussion <[hidden email]>
Subject: [postgis-users] Renaming Raster Table

 

Hi All,

 

I used the raster2pgsql tool to load a raster with overviews into a PostGIS database using the following format:

raster2pgsql -s <SRID> -d -Y -e -I -C -F -M -l 2,4,8,16 /path/to/raster/file <table_name> | psql -h <host_address> -U postgres -p 5432 -d <database>

 

Now, I want to rename the raster table and I would like to know the affected tables and the required changes to be done without affecting the functionality of the raster/overviews.


_______________________________________________
postgis-users mailing list
[hidden email]
https://lists.osgeo.org/mailman/listinfo/postgis-users


_______________________________________________
postgis-users mailing list
[hidden email]
https://lists.osgeo.org/mailman/listinfo/postgis-users
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Renaming Raster Table

Osahon Oduware
In reply to this post by Regina Obe-2
Hi Regina,

I have followed the steps outlined in your post and I could alter the raster and overview table successfully. However, when I tried to load the renamed raster in QGIS it gives this error:
"Cannot get GDAL raster band:"

Please, how do I go about fixing this error?

On Fri, Jul 7, 2017 at 9:20 PM, Regina Obe <[hidden email]> wrote:

Osahon,

 

I think the only place where the name of table is explicitly referenced is in the constraint called enforce_overview_rast that is on each of the overview tables.

The raster_overviews view uses the information in this constraint to populate the parent table.

 

I forget why we decided not to go by name instead of table oid.  If we did, then renaming the tables would be sufficient.

 

Anyway to rename the tables, do the following:

 

1)      Rename the tables as you would normally with

ALTER TABLE ned RENAME TO ned_2017;

ALTER TABLE o_3_ned RENAME TO o_3_ned_2017;

 

Etc.

2)      Drop the constraint on each over view table and read it.

So for example if your table is in schema public, and you renamed it to o_3_ned_2017

 

You'd do:

 

ALTER TABLE public.o_3_ned_2017 DROP CONSTRAINT enforce_overview_rast;

 

ALTER TABLE public.o_3_nj_ned_2017

  ADD CONSTRAINT enforce_overview_rast CHECK (_overview_constraint(rast, 3, 'public'::name, 'ned_2017'::name, 'rast'::name));

 

 

If you have a lot of these, it's fairly easy to script them by querying the raster_overview table and executing the outputs of this query.  NOT tested so you might need to fiddle with it.

 

SELECT  'ALTER TABLE ' || quote_ident(o_table_schema)  || '.' || quote_ident(o_table_name) || ' DROP CONSTRAINT enforce_overview_rast;

ALTER TABLE ' ||  quote_ident(o_table_schema)  || '.' || quote_ident(o_table_name) || ' ADD CONSTRAINT enforce_overview_rast

CHECK (_overview_constraint(rast, ' || overview_factor || ', ' || quote_literal(r_table_schema) || '::name, ' || quote_literal('mynewtablename') || ', ' || quote_literal(o_raster_column) || '::name)); '  AS sql FROM  raster_overviews;

 

 

Hope that helps,

Regina

http://postgis.us

 

 

From: postgis-users [mailto:[hidden email]] On Behalf Of Osahon Oduware
Sent: Friday, July 07, 2017 3:55 AM
To: PostGIS Users Discussion <[hidden email]>
Subject: [postgis-users] Renaming Raster Table

 

Hi All,

 

I used the raster2pgsql tool to load a raster with overviews into a PostGIS database using the following format:

raster2pgsql -s <SRID> -d -Y -e -I -C -F -M -l 2,4,8,16 /path/to/raster/file <table_name> | psql -h <host_address> -U postgres -p 5432 -d <database>

 

Now, I want to rename the raster table and I would like to know the affected tables and the required changes to be done without affecting the functionality of the raster/overviews.


_______________________________________________
postgis-users mailing list
[hidden email]
https://lists.osgeo.org/mailman/listinfo/postgis-users


_______________________________________________
postgis-users mailing list
[hidden email]
https://lists.osgeo.org/mailman/listinfo/postgis-users
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Renaming Raster Table

Osahon Oduware
Hi Regina,

I needed a way to effectively rename a raster table (and associated overviews), because, I was finding it difficult to use the raster2pgsql tool to create a raster table with upper-case characters as described/resolved in the link below:
Raster Table Name With Upper-Case Characters

Maybe I would have to reload the raster from scratch since I now know how to create raster tables with upper-case characters using the raster2pgsql tool (see link above). The raster is quite large and took a very long time to create, hence, I would have prepared a faster and efficient way of renaming the already existing table/overviews.

On Wed, Jul 12, 2017 at 6:03 PM, Osahon Oduware <[hidden email]> wrote:
Hi Regina,

I have followed the steps outlined in your post and I could alter the raster and overview table successfully. However, when I tried to load the renamed raster in QGIS it gives this error:
"Cannot get GDAL raster band:"

Please, how do I go about fixing this error?

On Fri, Jul 7, 2017 at 9:20 PM, Regina Obe <[hidden email]> wrote:

Osahon,

 

I think the only place where the name of table is explicitly referenced is in the constraint called enforce_overview_rast that is on each of the overview tables.

The raster_overviews view uses the information in this constraint to populate the parent table.

 

I forget why we decided not to go by name instead of table oid.  If we did, then renaming the tables would be sufficient.

 

Anyway to rename the tables, do the following:

 

1)      Rename the tables as you would normally with

ALTER TABLE ned RENAME TO ned_2017;

ALTER TABLE o_3_ned RENAME TO o_3_ned_2017;

 

Etc.

2)      Drop the constraint on each over view table and read it.

So for example if your table is in schema public, and you renamed it to o_3_ned_2017

 

You'd do:

 

ALTER TABLE public.o_3_ned_2017 DROP CONSTRAINT enforce_overview_rast;

 

ALTER TABLE public.o_3_nj_ned_2017

  ADD CONSTRAINT enforce_overview_rast CHECK (_overview_constraint(rast, 3, 'public'::name, 'ned_2017'::name, 'rast'::name));

 

 

If you have a lot of these, it's fairly easy to script them by querying the raster_overview table and executing the outputs of this query.  NOT tested so you might need to fiddle with it.

 

SELECT  'ALTER TABLE ' || quote_ident(o_table_schema)  || '.' || quote_ident(o_table_name) || ' DROP CONSTRAINT enforce_overview_rast;

ALTER TABLE ' ||  quote_ident(o_table_schema)  || '.' || quote_ident(o_table_name) || ' ADD CONSTRAINT enforce_overview_rast

CHECK (_overview_constraint(rast, ' || overview_factor || ', ' || quote_literal(r_table_schema) || '::name, ' || quote_literal('mynewtablename') || ', ' || quote_literal(o_raster_column) || '::name)); '  AS sql FROM  raster_overviews;

 

 

Hope that helps,

Regina

http://postgis.us

 

 

From: postgis-users [mailto:[hidden email]] On Behalf Of Osahon Oduware
Sent: Friday, July 07, 2017 3:55 AM
To: PostGIS Users Discussion <[hidden email]>
Subject: [postgis-users] Renaming Raster Table

 

Hi All,

 

I used the raster2pgsql tool to load a raster with overviews into a PostGIS database using the following format:

raster2pgsql -s <SRID> -d -Y -e -I -C -F -M -l 2,4,8,16 /path/to/raster/file <table_name> | psql -h <host_address> -U postgres -p 5432 -d <database>

 

Now, I want to rename the raster table and I would like to know the affected tables and the required changes to be done without affecting the functionality of the raster/overviews.


_______________________________________________
postgis-users mailing list
[hidden email]
https://lists.osgeo.org/mailman/listinfo/postgis-users



_______________________________________________
postgis-users mailing list
[hidden email]
https://lists.osgeo.org/mailman/listinfo/postgis-users
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Renaming Raster Table

Osahon Oduware
Hi Regina,

I needed a way to effectively rename a raster table (and associated overviews), because, I was finding it difficult to use the raster2pgsql tool to create a raster table with upper-case characters as described/resolved in the link below:
Raster Table Name With Upper-Case Characters

Maybe I would have to reload the raster from scratch since I now know how to create raster tables with upper-case characters using the raster2pgsql tool (see link above). The raster is quite large and took a very long time to create, hence, I would have preferred a faster and efficient way of renaming the already existing table/overviews.

On Fri, Jul 14, 2017 at 1:40 PM, Osahon Oduware <[hidden email]> wrote:
Hi Regina,

I needed a way to effectively rename a raster table (and associated overviews), because, I was finding it difficult to use the raster2pgsql tool to create a raster table with upper-case characters as described/resolved in the link below:
Raster Table Name With Upper-Case Characters

Maybe I would have to reload the raster from scratch since I now know how to create raster tables with upper-case characters using the raster2pgsql tool (see link above). The raster is quite large and took a very long time to create, hence, I would have prepared a faster and efficient way of renaming the already existing table/overviews.

On Wed, Jul 12, 2017 at 6:03 PM, Osahon Oduware <[hidden email]> wrote:
Hi Regina,

I have followed the steps outlined in your post and I could alter the raster and overview table successfully. However, when I tried to load the renamed raster in QGIS it gives this error:
"Cannot get GDAL raster band:"

Please, how do I go about fixing this error?

On Fri, Jul 7, 2017 at 9:20 PM, Regina Obe <[hidden email]> wrote:

Osahon,

 

I think the only place where the name of table is explicitly referenced is in the constraint called enforce_overview_rast that is on each of the overview tables.

The raster_overviews view uses the information in this constraint to populate the parent table.

 

I forget why we decided not to go by name instead of table oid.  If we did, then renaming the tables would be sufficient.

 

Anyway to rename the tables, do the following:

 

1)      Rename the tables as you would normally with

ALTER TABLE ned RENAME TO ned_2017;

ALTER TABLE o_3_ned RENAME TO o_3_ned_2017;

 

Etc.

2)      Drop the constraint on each over view table and read it.

So for example if your table is in schema public, and you renamed it to o_3_ned_2017

 

You'd do:

 

ALTER TABLE public.o_3_ned_2017 DROP CONSTRAINT enforce_overview_rast;

 

ALTER TABLE public.o_3_nj_ned_2017

  ADD CONSTRAINT enforce_overview_rast CHECK (_overview_constraint(rast, 3, 'public'::name, 'ned_2017'::name, 'rast'::name));

 

 

If you have a lot of these, it's fairly easy to script them by querying the raster_overview table and executing the outputs of this query.  NOT tested so you might need to fiddle with it.

 

SELECT  'ALTER TABLE ' || quote_ident(o_table_schema)  || '.' || quote_ident(o_table_name) || ' DROP CONSTRAINT enforce_overview_rast;

ALTER TABLE ' ||  quote_ident(o_table_schema)  || '.' || quote_ident(o_table_name) || ' ADD CONSTRAINT enforce_overview_rast

CHECK (_overview_constraint(rast, ' || overview_factor || ', ' || quote_literal(r_table_schema) || '::name, ' || quote_literal('mynewtablename') || ', ' || quote_literal(o_raster_column) || '::name)); '  AS sql FROM  raster_overviews;

 

 

Hope that helps,

Regina

http://postgis.us

 

 

From: postgis-users [mailto:[hidden email]] On Behalf Of Osahon Oduware
Sent: Friday, July 07, 2017 3:55 AM
To: PostGIS Users Discussion <[hidden email]>
Subject: [postgis-users] Renaming Raster Table

 

Hi All,

 

I used the raster2pgsql tool to load a raster with overviews into a PostGIS database using the following format:

raster2pgsql -s <SRID> -d -Y -e -I -C -F -M -l 2,4,8,16 /path/to/raster/file <table_name> | psql -h <host_address> -U postgres -p 5432 -d <database>

 

Now, I want to rename the raster table and I would like to know the affected tables and the required changes to be done without affecting the functionality of the raster/overviews.


_______________________________________________
postgis-users mailing list
[hidden email]
https://lists.osgeo.org/mailman/listinfo/postgis-users




_______________________________________________
postgis-users mailing list
[hidden email]
https://lists.osgeo.org/mailman/listinfo/postgis-users
Loading...