[postgis-users] Adding geometry to an existing table

[pcreso at pcreso.com]

Hi Shaun,

You add a geometry column to the table, then run an update sql to populate it.

eg, assuming your table is called mytable, located in the public schema, & you want to create a two dimensional point with lat/long coordinates (EPSG:4326) called "geom":

select ST_AddGeometryColumn('public','mytable','geom','4326','POINT',2);

then update this table to populate the new geometry column from your x & Y columns (called lon & lat) set to EPSG:4326 coordinates:

update mytable set geom = ST_Setsrid(ST_Makepoint(lon, lat),4326);

Note that if you are doing any spatial queries (such as point in polygon, etc), that you should also add a spatial index to this column.


HTH,

   Brent Wood


--- On Sun, 4/29/12, Shaun Langley <shaunlangley at gmail.com> wrote:

From: Shaun Langley <shaunlangley at gmail.com>
Subject: [postgis-users] Adding geometry to an existing table
To: postgis-users at postgis.refractions.net
Date: Sunday, April 29, 2012, 6:34 AM

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?
Regards,  Shaun Langley
-----Inline Attachment Follows-----

_______________________________________________
postgis-users mailing list
postgis-users at postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20120428/7e080036/attachment.html>