[postgis-users] Adding geometry to an existing table

[Shaun Langley]

Thanks!  I'll try that in a minute and see if it works!

Regards,
Shaun

On Apr 28, 2012, at 2:57 PM, Puneet Kishor <punk.kish at gmail.com> wrote:

> 
> On Apr 28, 2012, at 1:34 PM, Shaun Langley wrote:
> 
>> I got myself into quite a mess and I'm hoping somebody out there can help me. I've created a database that includes in the range of 150 million records so far and I need to make a change to it. I have lat lon fields stored as strings in separate columns. What I would like to do is to take these 2 columns and created geometry column that combines both fields. I don't think there's a global function that will allow me to do this, but is there a way for me to use string formatting to do this easily?
>> 
> 
> 
> CREATE TABLE t (
> 	id INTEGER PRIMARY KEY,
> 	lat TEXT,
> 	lng TEXT
> );
> 
> INSERT INTO t (id, lat, lng) VALUES 
> 	(1, '43', '-89'),
> 	(2, '24', '-121'),
> 	(3, '-32', '48');
> 
> ### Oh no... need to do things the PostGIS way
> 
> ALTER TABLE t ADD COLUMN the_geom GEOMETRY;
> 
> ### Oops... shoulda made them columns numbers
> 
> UPDATE t
> SET the_geom = GeomFromText('POINT(' || Cast(lng AS REAL) || ' ' || Cast(lat AS REAL) || ')', 4326);
> 
> 
> 
> 
> --
> Puneet Kishor
> _______________________________________________
> postgis-users mailing list
> postgis-users at postgis.refractions.net
> http://postgis.refractions.net/mailman/listinfo/postgis-users