[postgis-users] Conversion from undefined geometry to geography?

[Nicholas Bower]

Interesting.  So if I do it as you've suggested, how do I get a polygon
constraint on the new column as normally present with,

alter table xxx add column boundary geography(POLYGON, 4326);


On Thu, Apr 15, 2010 at 1:08 AM, Paul Ramsey <pramsey at cleverelephant.ca>wrote:

> Don't update a 250M row table, do
>
> create newtable as select *,geography(the_geom) as geog from oldtable;
> drop oldtable;
> alter table newtable rename to newtable;
>
> Even with indexes and constraints dropped, the cost of updating all
> those rows in a transactional context is very very high. If you don't
> mind taking some extra risks, you can also
>
> set fsync=off;
>
> before you start and turn it back on when you're done.
>
> P
>
> On Tue, Apr 13, 2010 at 11:03 PM, Nicholas Bower <nick at petangent.net>
> wrote:
> > Ok thanks - so this is really my solution by the looks;
> > UPDATE mytable SET new_geog_col = geography(ST_SetSRID(old_geom_col,
> 4326));
> > Be curious to see how it performs on a quarter billion rows - hopefully
> > should be fine with constraints/indexes dropped as usual.
> >
> > On Wed, Apr 14, 2010 at 3:50 PM, Paragon Corporation <lr at pcorp.us>
> wrote:
> >>
> >> You mean ST_SetSRID :)
> >>
> >> R
> >> -----Original Message-----
> >> From: postgis-users-bounces at postgis.refractions.net
> >> [mailto:postgis-users-bounces at postgis.refractions.net] On Behalf Of
> Paul
> >> Ramsey
> >> Sent: Tuesday, April 13, 2010 11:59 PM
> >> To: PostGIS Users Discussion
> >> Subject: Re: [postgis-users] Conversion from undefined geometry to
> >> geography?
> >>
> >> Just use geography(the_geom), it'll work fine. If you fine you have srid
> >> issues (I don't think you should) use
> >> geography(setsrid(the_geom,4326))
> >>
> >> P
> >>
> >> On Tue, Apr 13, 2010 at 8:47 PM, Nicholas Bower <nick at petangent.net>
> >> wrote:
> >> > We have some tables in which we've used Geometry to store polygon and
> >> > point lan/lon (srid = -1).  We embarked on this before PostGIS 1.5.x's
> >> > spherical Geography objects.
> >> > Now we intend to upgrade and migrate these to new Geography types (eg
> >> > new geography col, copy/convert, drop original geometry col).
> >> > Is there an existing function that provides a conversion from Geometry
> >> > to Geography that could be used in a simple table update?  This
> >> > doesn't give away too many clues what will happen when given a
> >> > geometry with srid = -1 for example:
> >> > http://postgis.refractions.net/docs/geography.html
> >> > TIA.
> >> >
> >> >
> >> > _______________________________________________
> >> > postgis-users mailing list
> >> > postgis-users at postgis.refractions.net
> >> > http://postgis.refractions.net/mailman/listinfo/postgis-users
> >> >
> >> >
> >> _______________________________________________
> >> postgis-users mailing list
> >> postgis-users at postgis.refractions.net
> >> http://postgis.refractions.net/mailman/listinfo/postgis-users
> >>
> >>
> >> _______________________________________________
> >> postgis-users mailing list
> >> postgis-users at postgis.refractions.net
> >> http://postgis.refractions.net/mailman/listinfo/postgis-users
> >
> >
> > _______________________________________________
> > postgis-users mailing list
> > postgis-users at postgis.refractions.net
> > http://postgis.refractions.net/mailman/listinfo/postgis-users
> >
> >
> _______________________________________________
> postgis-users mailing list
> postgis-users at postgis.refractions.net
> http://postgis.refractions.net/mailman/listinfo/postgis-users
>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20100415/7be860fa/attachment.html>