[GENERAL] Update quey
I have a table (pubacc_lo) from the US government with 500,00+ rows. It has latitude and longitude in three columns each for degrees, minutes and seconds. I need a Point geometry column. So I wrote this query: with mydata AS (SELECT (pubacc_lo.lat_degrees + pubacc_lo.lat_minutes/60 + pubacc_lo.lat_seconds/3600) as lat , (pubacc_lo.long_degrees + pubacc_lo.long_minutes/60 + pubacc_lo.long_seconds/3600) as long FROM pubacc_lo) UPDATE pubacc_lo SET lonlat_84 = ST_SetSRID(ST_makePOINT(long,lat),4326) FROM mydata; It appears to work, but is going to take days it seems to finish. Anybody have a faster way? -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Update quey
Thank you! That worked fine. From: bricklen [mailto:brick...@gmail.com] Sent: Friday, August 23, 2013 10:08 AM To: Hall, Samuel L (Sam) Cc: pgsql-general@postgresql.org Subject: Re: [GENERAL] Update quey On Fri, Aug 23, 2013 at 8:04 AM, Hall, Samuel L (Sam) sam.h...@alcatel-lucent.commailto:sam.h...@alcatel-lucent.com wrote: I have a table (pubacc_lo) from the US government with 500,00+ rows. It has latitude and longitude in three columns each for degrees, minutes and seconds. I need a Point geometry column. So I wrote this query: with mydata AS (SELECT (pubacc_lo.lat_degrees + pubacc_lo.lat_minutes/60 + pubacc_lo.lat_seconds/3600) as lat , (pubacc_lo.long_degrees + pubacc_lo.long_minutes/60 + pubacc_lo.long_seconds/3600) as long FROM pubacc_lo) UPDATE pubacc_lo SET lonlat_84 = ST_SetSRID(ST_makePOINT(long,lat),4326) FROM mydata; It appears to work, but is going to take days it seems to finish. Anybody have a faster way? Create a new table, rather than updating the existing one. CREATE TABLE pubacc_lo_new AS select *, (pubacc_lo.lat_degrees + pubacc_lo.lat_minutes/60 + pubacc_lo.lat_seconds/3600) as lat , (pubacc_lo.long_degrees + pubacc_lo.long_minutes/60 + pubacc_lo.long_seconds/3600) as long from pubacc_lo; Then either rename them, or use the new table.
Re: [GENERAL] Update quey
On Fri, Aug 23, 2013 at 8:04 AM, Hall, Samuel L (Sam) sam.h...@alcatel-lucent.com wrote: I have a table (pubacc_lo) from the US government with 500,00+ rows. It has latitude and longitude in three columns each for degrees, minutes and seconds. I need a Point geometry column. So I wrote this query: with mydata AS (SELECT (pubacc_lo.lat_degrees + pubacc_lo.lat_minutes/60 + pubacc_lo.lat_seconds/3600) as lat , (pubacc_lo.long_degrees + pubacc_lo.long_minutes/60 + pubacc_lo.long_seconds/3600) as long FROM pubacc_lo) UPDATE pubacc_lo SET lonlat_84 = ST_SetSRID(ST_makePOINT(long,lat),4326) FROM mydata; It appears to work, but is going to take days it seems to finish. Anybody have a faster way? Create a new table, rather than updating the existing one. CREATE TABLE pubacc_lo_new AS select *, (pubacc_lo.lat_degrees + pubacc_lo.lat_minutes/60 + pubacc_lo.lat_seconds/3600) as lat , (pubacc_lo.long_degrees + pubacc_lo.long_minutes/60 + pubacc_lo.long_seconds/3600) as long from pubacc_lo; Then either rename them, or use the new table.
