[postgis-users] Enter details into Geom column

[pcreso at pcreso.com]

Hi James,

I suggest you avoid upper case letters in table & column names if you can. It makes a few things easier....

The syntax in both SQL statements is wrong. Try:

select ST_CreateGeometryColumn('public','CLEANEDCAMDENGPS','geom',4326,'POINT',2);

the fields are (in order):

schema where table can be found ('public')
the table name where you want the new column ('CLEANEDCAMDENGPS')
the name of the geometry column to create ('geom')
the SRID of the geometry column to create (4326)
the geometry type  ('POINT')
the number of dimensions (2 - x & y)

All string values need to be quoted.

To populate this column try:

update "CLEANEDCAMDENGPS"
set geom=setsrid(makepoint("LONGITUDE","LATITUDE"),4326);

So, create a point geometry from the two numeric columns (makepoint), force the SRID of this geometry to 4326 (setsrid), & write this value to your new column (update table set column =).


HTH,

  Brent Wood


James Smith wrote:
> Dear all,
>
> Would appreciate some help. I have created an existing database (with
> PostGIS extension) and it has a table called CLEANEDCAMDENGPS which 
> is populated with approx 600,000 rows. There are 20 or so columns in the
> table, two of which are Latitude and Longitude (WGS84). I would now
> like to create a Geom column with points in, the values of which
> should be taken from the latitude and longitude column. Could someone
> provide me with sample code as to how to do this please? I had a go
> with the below, but don't really know what I'm doing... neither of the
> statements work...
>
> --CREATE THE COLUMN--
> SELECT AddGeometryColumn('CAMDENGPS', 'GPS_POINTS', 'geom', 4326, 'POINT', 2)
>
> --POPULATE THE COLUMN--
> INSERT INTO CLEANEDCAMDENGPS (GPS_POINTS)
> VALUES ( ST_GEOMFROMTEXT('POINT((SELECT LONGITUDE FROM
> CLEANEDCAMDENGPS) (SELECT LATITUDE FROM CLEANEDCAMDENGPS))', 4326,
> 'Point'));
>
> Thank you
>
> James
> _______________________________________________
> postgis-users mailing list
> postgis-users at postgis.refractions.net
> http://postgis.refractions.net/mailman/listinfo/postgis-users
>

_______________________________________________
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/20110529/5e50f239/attachment.html>