[postgis-users] SRID in geometry_columns view

[Mike Toews]

On 25 July 2012 09:49, Richard Greenwood <richard.greenwood at gmail.com> wrote:
> I am having difficulty getting my views' SRIDs into the
> geometry_columns view in PostGIS 2.0. The doc's [1] suggest casting
> the geometry in the view so I tried:
>    wkb_geometry::geometry(3739)
> which generates the error:
>    ERROR:  Invalid geometry type modifier: 3739

Yup, this invalid, but it is not what the manual says. The typmod is
either: geometry(type,srid) or if SRID is not known, then
geometry(type), where 'type' can be one of Geometry, Point, PointZ,
etc, etc.

> next I tried:
>    wkb_geometry::geometry(Geometry,3739)
> which generates the error:
>    ERROR:  cannot change data type of view column "wkb_geometry" from
> geometry to geometry(Geometry,3739)

You need to either assign an SRID or reproject to that SRID. Are you
geometries mixed? If not, you might want to use a more specific
geometry type, like Polygon or MultiPolygon, etc., rather than
Geometry.

To assign a missing or incorrect SRID[1]:

ALTER TABLE my_table
    ALTER COLUMN wkb_geometry TYPE geometry(Geometry,3739)
    USING ST_SetSRID(wkb_geometry,3739);

Or if it needs to be transformed (reprojected) to a different SRID[2]:

ALTER TABLE my_table
    ALTER COLUMN wkb_geometry TYPE geometry(Geometry,3739)
    USING ST_Transform(wkb_geometry,3739);

-Mike

[1] http://postgis.refractions.net/docs/ST_SetSRID.html
[2] http://postgis.refractions.net/docs/ST_Transform.html