[postgis-users] Enter details into Geom column

[Mike Toews]

If you have a table named "CLEANEDCAMDENGPS" (in public), and you want
a column "GPS_POINTS":

SELECT AddGeometryColumn('CLEANEDCAMDENGPS', 'GPS_POINTS', 4326, 'POINT', 2);

works fine. There is some confusion in your first message with another
table "CAMDENGPS" and another geometry column "geom". Also, just be
warned that although you can name tables/columns with mixed and upper
case, I've found it to be more trouble than it is worth. For instance,
you will always need to use double quotes for these entities:

SELECT ST_X("GPS_POINTS"), ST_Y("GPS_POINTS")
FROM "CLEANEDCAMDENGPS"
LIMIT 10;

You can easily rename them in pgAdmin to a lowercase equivalent, which
doesn't require quoting.

-Mike

On 31 May 2011 06:35, James Smith <james.david.smith at gmail.com> wrote:
>
> Dear Brent,
>
> Thank you for your reply and simple explanation, it's much appreciated. Unfortunately, it doesn't seem to work. When I try to create the Geom column, I get this error:
>
> -----
> ERROR: function st_creategeometrycolumn(unknown, unknown, unknown, integer, unknown, integer) does not exist
> SQL state: 42883
> Hint: No function matches the given name and argument types. You might need to add explicit type casts.
> Character: 8
> -----
>
> So I browsed the PostGIS functions, and thought that I should perhaps use the function 'AddGeometryColumn' instead, so changed the code to below:
>
> ----
> select AddGeometryColumn('public', 'CLEANEDCAMDENGPS','geom',4326,'POINT',2);
> ----
>
> However this returns an error of the below:
>
> ----
> ERROR: relation "public.CLEANEDCAMDENGPS" does not exist
> ----
>
> I should perhaps say at this point, that the table called CLEANEDCAMDENGPS is within a database called CAMDENGPS. I played around with trying to put the database name into the statement too, but with no luck.
>
> Any thoughts?
>
> Thanks again, and yes, I'll take onboard your point about captials and table names from this point forwards.
>
> Cheers
>
> James
>
>
>
> On 29 May 2011 23:44, <pcreso at pcreso.com> wrote:
>>
>> 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
>
>
> _______________________________________________
> postgis-users mailing list
> postgis-users at postgis.refractions.net
> http://postgis.refractions.net/mailman/listinfo/postgis-users
>