Re: Update quey
| От | bricklen |
|---|---|
| Тема | Re: Update quey |
| Дата | |
| Msg-id | CAGrpgQ8T+A5JsEMsZ2HSWDXpSCrVqgOZWwFSenMzbw1LyRBHmw@mail.gmail.com обсуждение исходный текст |
| Ответ на | Update quey ("Hall, Samuel L (Sam)" <sam.hall@alcatel-lucent.com>) |
| Ответы |
Re: Update quey
|
| Список | pgsql-general |
On Fri, Aug 23, 2013 at 8:04 AM, Hall, Samuel L (Sam) <sam.hall@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
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.
В списке pgsql-general по дате отправления: