[postgis-users] Conversion from undefined geometry to geography?
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@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
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 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@postgis.refractions.net > http://postgis.refractions.net/mailman/listinfo/postgis-users > > ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] Conversion from undefined geometry to geography?
You mean ST_SetSRID :) R -Original Message- From: postgis-users-boun...@postgis.refractions.net [mailto:postgis-users-boun...@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 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@postgis.refractions.net > http://postgis.refractions.net/mailman/listinfo/postgis-users > > ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] Conversion from undefined geometry to geography?
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 wrote: > You mean ST_SetSRID :) > > R > -Original Message- > From: postgis-users-boun...@postgis.refractions.net > [mailto:postgis-users-boun...@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 > 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@postgis.refractions.net > > http://postgis.refractions.net/mailman/listinfo/postgis-users > > > > > ___ > postgis-users mailing list > postgis-users@postgis.refractions.net > http://postgis.refractions.net/mailman/listinfo/postgis-users > > > ___ > postgis-users mailing list > postgis-users@postgis.refractions.net > http://postgis.refractions.net/mailman/listinfo/postgis-users > ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] Conversion from undefined geometry to geography?
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 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 wrote: >> >> You mean ST_SetSRID :) >> >> R >> -Original Message- >> From: postgis-users-boun...@postgis.refractions.net >> [mailto:postgis-users-boun...@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 >> 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@postgis.refractions.net >> > http://postgis.refractions.net/mailman/listinfo/postgis-users >> > >> > >> ___ >> postgis-users mailing list >> postgis-users@postgis.refractions.net >> http://postgis.refractions.net/mailman/listinfo/postgis-users >> >> >> ___ >> postgis-users mailing list >> postgis-users@postgis.refractions.net >> http://postgis.refractions.net/mailman/listinfo/postgis-users > > > ___ > postgis-users mailing list > postgis-users@postgis.refractions.net > http://postgis.refractions.net/mailman/listinfo/postgis-users > > ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] Conversion from undefined geometry to geography?
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 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 > 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 > wrote: > >> > >> You mean ST_SetSRID :) > >> > >> R > >> -Original Message- > >> From: postgis-users-boun...@postgis.refractions.net > >> [mailto:postgis-users-boun...@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 > >> 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@postgis.refractions.net > >> > http://postgis.refractions.net/mailman/listinfo/postgis-users > >> > > >> > > >> ___ > >> postgis-users mailing list > >> postgis-users@postgis.refractions.net > >> http://postgis.refractions.net/mailman/listinfo/postgis-users > >> > >> > >> ___ > >> postgis-users mailing list > >> postgis-users@postgis.refractions.net > >> http://postgis.refractions.net/mailman/listinfo/postgis-users > > > > > > ___ > > postgis-users mailing list > > postgis-users@postgis.refractions.net > > http://postgis.refractions.net/mailman/listinfo/postgis-users > > > > > ___ > postgis-users mailing list > postgis-users@postgis.refractions.net > http://postgis.refractions.net/mailman/listinfo/postgis-users > ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
