[postgis-users] Convert already loaded table into a spatial table (points)

[Paul Ramsey]

Because of transaction handling, updating every row in a database is
the equivalent of a full load. It might actually take longer than the
initial load.

I'd suggest something like

  create newtable as select st_makepoint(x,y) as point, a1, a2, a2...
from oldtable
  drop oldtable
  alter newtable rename to oldtable

P.

On Thu, Aug 12, 2010 at 11:03 AM, Fabio Renzo Panettieri
<fpanettieri at xoomcode.com> wrote:
> On Thu, 2010-08-12 at 12:53 -0500, Appel, Tony wrote:
>> I have already loaded a table that contains X, Y and Z values. AT the
>> time of loading it was thought we did not need a spatial component to
>> this table.
>>
>>
>>
>> Now we realize that we do have a need but do not want to reload the
>> 190 million records again and create the point features on load.
>>
>>
>>
>> Is it possible to create the point geometry in the existing table….if
>> so, how?
>
>
> You can always add a new geometry column to your existing table.
>
> SELECT AddGeometryColumn
> ('public','my_table','the_geom',4326,'POINT',2);
> http://postgis.refractions.net/docs/AddGeometryColumn.html
>
> And after that you create the geometries, assuming you have points stored:
>
> UPDATE my_table SET the_geom = ST_MakePoint(x, y, z);
> http://postgis.refractions.net/docs/ST_MakePoint.html
>
>
> --
> Fabio R. Panettieri
> Software Architect
> http://www.xoomcode.com
>
> _______________________________________________
> postgis-users mailing list
> postgis-users at postgis.refractions.net
> http://postgis.refractions.net/mailman/listinfo/postgis-users
>