[postgis-users] SRID in geometry_columns view

[Richard Greenwood]

On Tue, Jul 24, 2012 at 4:10 PM, Mike Toews <mwtoews at gmail.com> wrote:
> 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.

Okay, but there is an example in the doc's showing just geometry(srid)
which doesn't work for me. Guess that's what you're saying?

>> 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.

My issue is trying to get the the geometry_columns view to recognize
my srid, not geometry type, but I did try specific geometry types
which produced the same error.

> 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);

I'm dealing with a view, not a table. My table shows the correct srid
in geometry_columns but the view which is based upon the table shows a
srid of 0. I don't want or need to transform the geometry. I just need
for its srid to be correctly reflected in the geometry_columns view.

Using ST_SetSRID() as you suggest above in the creation of the view
works in so far as that it doesn't generate an error when I create my
view, but geometry_columns still lists a srid of 0 for my view.

Thanks for your suggestions, but I still don't get it.

Rich

-- 
Richard Greenwood
richard.greenwood at gmail.com
www.greenwoodmap.com