9

I want to rename one of my postGIS tables. However, when I do this by applying a simple

ALTER TABLE old_name RENAME TO new_name;

The spatial data, as I understand it, isn't updated with the new name. First, can these references be fixed by running the function:

SELECT probe_geometry_columns()

Is the table itself OK after this? Or should other measures be taken also? Second, how do I update GeoServer so that it finds this new table? As of now, if I run the above commands, and edit the layer info on the GeoServer to point to the new name... I still get an error (GeoServer still tries to find it under the old name). Exactly, where should the layer name be changed on the GeoServer?

Improve this question
4

In order to rename the table you will have to change the name in the metadata of two places. First on PostGIS, then on the Geoserver config.

To change the PostGIS one, you can update the geometry_columns directly. Although probe_geometries will have the effect of inserting that entry into geometry columns, it will also have the effect of DESCRIBING the schema of all tables an inserting any missing geometry columns entries. If you have a lot of tables this can get expensive, so why not just update the geometry_columns table directly?

For updating geoserver you have various options. Certainly you can do it manually through the GUI, but I have a feeling you want to automate it. Writing a shell script that edits the config files in the geoserver installation folder and restarting geoserver, but that is also the long way of doing things. I would recommend you use the geoserver REST config API which allows you to manipulate the layer configs through RESTful calls.

Update:

In case the "update geometry columns" is not obvious do the following:

UPDATE geometry_columns set f_table_name = 'my_new_table_name' where f_table_name = 'my_old_table_name';

Update 8 years(!!!) later (2018):

Like some people mentioned below, after PostGIS 2.0, the metadata tables get updated automatically after the ALTER statement.

Improve this answer
  • Pardon my ignorance, but as I'm very new to PostGIS. How exactly do I update geometry_columns? Did I understand you right that running the probe_ function (without any parameters) does the same thing? – Mimo Oct 12 '11 at 8:07
  • 1
    Look at the implementation from probe_geometry_columns trac.osgeo.org/postgis/changeset/7548 It is going through every single table in the db, finding if the schema has the string "geometrytype" and inserting a new record if the required entry does not exist in geometry_columns. Nevertheless, orphan records are left as they are. So why not just do: UPDATE geometry_columns SET f_table_name = 'new_table_name' where f_table_name = 'old_table_name' – Ragi Yaser Burhum Oct 12 '11 at 14:37
  • this shows Not Found The requested URL /latest/en/user/restconfig/rest-config-api.html was not found on this server. – Scaramouche Jan 26 '18 at 17:35
  • you want to look at this now: docs.geoserver.org/stable/en/user/rest/api/index.html – Ragi Yaser Burhum Jan 29 '18 at 19:50
7

Note that in PostGIS 2.0+, all you have to do is the normal

ALTER TABLE old_name RENAME TO new_name;

The entry in the geometry_columns view (or geography_columns) will also be updated.

Improve this answer
3
SELECT Probe_Geometry_Columns();

is a handy utility.

First, when we append a geometry column to an existing table with

SELECT AddGeometryColumn('my_table', 'geo_column', 1234, 'MULTIPOINT', 2);

we're feeding the function everything it needs to tack the column of type geometry (geo_column) to the specified table (my_table) and write the important details like SRID (1234), geometry type (MULTIPOINT) and number of dimensions (2) to the geometry_columns table. In essence, it is an ALTER and three UPDATES.

Creating geometry columns by other means (loaded from shapefile, selected in a CREATE TABLE AS, etc.) can lead to spatial tables that are invisible to external applications, although they work just fine within the database. Without the right details stored in geometry_columns, the actual geometry values appear as nonsensical character strings to applications looking for projected points, lines or polygons.

Calling the probe function checks each column of type geometry, adding new values to geometry_columns and reporting conflicts.

Circling back to your question, GeoServer doesn't believe the renamed table contains spatial data if the name change isn't reflected in geometry_columns. Something else to consider is that the probe function creates a duplicate record reflecting the new table name but does not get rid of the original record--another potential hang-up for GeoServer.

All that said, I would suggest you: 1) run the probe then immediately delete the old record; or 2) follow up your name change with an ALTER on geometry_columns to change the f_table_name value.

Sorry for the wordiness, but I hope it helps.

Improve this answer
  • Thank you for your reply. I'm beginning th understand postGIS better now. Still, I'm a bit unsure of the necessary steps that need to be taken. Should I first of all perform the name change, then the probe()? How do I alter the geometry_columns? – Mimo Oct 12 '11 at 8:13
  • The simplest way to make the change in geometry_columns is to open pgAdmin, right click the table, select view data, select view all rows, click in the cell containing the old table name and type the new table name. or, UPDATE geometry_columns SET f_table_name = 'new_table_name' WHERE f_schema_name = 'schema_name' AND f_table_name = 'old_table_name' – rec.thegeom Oct 13 '11 at 5:38
1

I'm not sure what SELECT probe_geometry_columns() does, but you can easily check the geometry_columns table to see if your new table name is in there or if it is still pointing to the old one.

I suspect that you need to reload the GeoServer store to make sure it "notices" the changes. Alternatively a stop and start will do it.

Improve this answer
  • What do you mean by "reload the GeoServer store"? – Mimo Oct 10 '11 at 18:42
  • On the status page (docs.geoserver.org/latest/en/user/webadmin/server/status.html) there is a reload button. – Ian Turton♦ Oct 11 '11 at 5:18
  • Thanks. However, the reload did not work. I still get an GeoServer error which complains about not finding the renamed layer. It still tries to find it under the old name. – Mimo Oct 11 '11 at 7:19
  • you did change the layer definition to point to the new table name? – Ian Turton♦ Oct 11 '11 at 7:53

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.