[postgis-users] SRID in geometry_columns view

[Richard Greenwood]

On Tue, Jul 24, 2012 at 10:40 PM, Mike Toews <mwtoews at gmail.com> wrote:
> On 25 July 2012 13:48, Richard Greenwood <richard.greenwood at gmail.com> wrote:
>> 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?
>
> I now see what you are looking at, and it's a typo in the docs.
>
>> 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.
>
> OK, I follow you correctly now. You are using the older-style
> constraints on your table, which looks something like:
>
> ALTER TABLE my_table
>   ADD CONSTRAINT enforce_srid_wkb_geometry
>   CHECK (st_srid(wkb_geometry) = 3739);
>
> Although this shows the correct SRID for the table in the
> geometry_columns view, it doesn't propagate further to derived views.
> The simplest way to get this to work is to drop the older style
> constraint, and use the new 2.0 typmod syntax, described above.
>
> ALTER TABLE my_table DROP CONSTRAINT enforce_srid_wkb_geometry;
> ALTER TABLE my_table DROP CONSTRAINT enforce_geotype_wkb_geometry;
>
> -- you'll also need to temporarily drop your view; now, e.g. set as Point
>
> ALTER TABLE my_table
>     ALTER COLUMN wkb_geometry TYPE geometry(Point,3739)
>     USING ST_SetSRID(wkb_geometry,3739);
>
> After restoring your view, you should see the correct geometry type
> and SRID for the source table, and all derived views.

Got it. You are correct that I was using the "old style" constraints
and by switching to the typmod syntax I now see the correct srid in
views derived from the table.

Thanks you very much for your help.

Rich

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