[postgis-users] Adding geometry to an existing table

[Puneet Kishor]

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