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

[Paul Ramsey]

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