From: | bricklen <bricklen(at)gmail(dot)com> |
---|---|
To: | "Hall, Samuel L (Sam)" <sam(dot)hall(at)alcatel-lucent(dot)com> |
Cc: | "pgsql-general(at)postgresql(dot)org" <pgsql-general(at)postgresql(dot)org> |
Subject: | Re: Update quey |
Date: | 2013-08-23 15:08:06 |
Message-ID: | CAGrpgQ8T+A5JsEMsZ2HSWDXpSCrVqgOZWwFSenMzbw1LyRBHmw@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
On Fri, Aug 23, 2013 at 8:04 AM, Hall, Samuel L (Sam) <
sam(dot)hall(at)alcatel-lucent(dot)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.
From | Date | Subject | |
---|---|---|---|
Next Message | Hall, Samuel L (Sam) | 2013-08-23 15:13:02 | Re: Update quey |
Previous Message | Hall, Samuel L (Sam) | 2013-08-23 15:04:11 | Update quey |