We have recently updated our database to Postgis 2.0.
In our system we used to create views for tables containing a geometry column. After creating the view we also manually updated the geometry_columns table with necessary info. The views are used to create Geoserver layers and also to feed data for ogr2ogr.
The problem in Postgis 2.0 is that geometry_columns table was replaced with a view that retrieves data from system catalogs. In our case, for the created views, we don't retrieve correct info in 'coord_dimension', 'srid' and 'type' columns.
Info: after some research i've found out that geometry_columns view is using pg_constraint table to fill those columns, but for that view there are no constraints defined.
Does anyone have any idea how can we fix this problem? How can we force gemetry_columns view to retrieve necessary info? Is there a workaround or other way in doing this?
Best Answer
I have found a solution to my problem: All the needed information can be found at this link https://postgis.net/docs/using_postgis_dbmanagement.html#Manual_Register_Spatial_Column
Solution 1: Create the table and add the geometry column typmod based, the creation process would register it correctly in geometry_columns.
Correct: CREATE TABLE pois_ny(gid SERIAL PRIMARY KEY, geom geometry(POINT,4326) );
Incorrect: CREATE TABLE pois_ny(gid SERIAL PRIMARY KEY);SELECT AddGeometryColumn('pois_ny', 'geom', 4326, 'POINT', 2, false);
Solution 2: Create the view and force typmod for the geometry columns
e.g. CREATE VIEW vw_pois_ny_parks AS SELECT geom::geometry(POINT,4326) As geom FROM pois_ny