[postgis-devel] Geometry Columns View

[Paragon Corporation]

Brian,

It's standard sql syntax.  I think its more or less the same too across all
relational databases.
http://www.postgresql.org/docs/9.0/static/sql-altertable.html


For example -- lets say you had an integer field and you wanted to change it
to a numeric of length 10, precision 5


You would do 

ALTER TABLE mytable ALTER COLUMN sqft TYPE numeric(10,5);

Same idea.  Geometry is now a column type that has modifiers associated with
it so you treat it pretty much like how you would treat numeric
varchar etc that take modifiers.

I'll be updating the documents hopefully before release to go into more
detail about this.  Just haven't had time yet.


oops strk missed the type -- it's really

ALTER TABLE geocoder_ca ALTER COLUMN the_geom TYPE GEOMETRY(polygon, 4326);

One thing to be cautious about.  If you have a column where the st_srid is
say -1 or 0 and you wanted to cast it to a type 
that is  4326, an error will be thrown unless you do something like this.


ALTER TABLE geocoder_ca ALTER COLUMN the_geom TYPE Geometry(polygon,4326)
USING ST_SetSRID(the_geom, 4326);

Similarly if the geometries really aren't you would use transform.  e.g.
lets say they are state plane calfornia and you want to convert them to WGS
84 long lat
you would do

ALTER TABLE geocoder_ca ALTER COLUMN the_geom TYPE Geometry(polygon,4326)
USING ST_Transform(the_geom, 4326);

et.c Just like you do for any type that would otherwise violate casting if
not converted.

Hope that helps,
Regina
http://www.postgis.us


 

> -----Original Message-----
> From: postgis-devel-bounces at postgis.refractions.net 
> [mailto:postgis-devel-bounces at postgis.refractions.net] On 
> Behalf Of maplabs at light42.com
> Sent: Sunday, October 23, 2011 1:34 PM
> To: PostGIS Development Discussion
> Subject: Re: [postgis-devel] Geometry Columns View
> 
> 
> 
> On Tue, 18 Oct 2011 10:23:06 +0200, Sandro Santilli  wrote:
> On Tue, Oct 18, 2011 at 12:38:51AM -0400, maplabs at light42.com wrote:
> > > What makes some tables show their geometry type as GEOMETRY while 
> > > others are POINT or MULTIPOLYGON, etc ?
> >
> > The column type. 
> > GEOMETRY type now accept a modifier to express SRID and TYPE. 
> >
> > Try something like:
> > ALTER TABLE geocoder_ca ALTER the_geom GEOMETRY(polygon, 4326);
> >
> 
> thank you for this reply - but I do not understand this 
> syntax What is the meaning of  TYPE(param,param)  ? Is this 
> explained somewhere?
> 
> thanks in advance
>   -Brian
> 
>   > --strk;
> >
> >   ()   Free GIS & Flash consultant/developer
> >   /\   http://strk.keybit.net/services.html
> > _______________________________________________
> > postgis-devel mailing list
> > postgis-devel at postgis.refractions.net
> > http://postgis.refractions.net/mailman/listinfo/postgis-devel
> >
> >
> 
> 
> _______________________________________________
> postgis-devel mailing list
> postgis-devel at postgis.refractions.net
> http://postgis.refractions.net/mailman/listinfo/postgis-devel
>