On Tue, Jul 24, 2012 at 10:40 PM, Mike Toews <mwto...@gmail.com> wrote: > On 25 July 2012 13:48, Richard Greenwood <richard.greenw...@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.greenw...@gmail.com www.greenwoodmap.com _______________________________________________ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
