[postgis-users] Enter details into Geom column

[Ben Madin]

James,

The error message has two possibilities:

ERROR: relation "public.CLEANEDCAMDENGPS" does not exist

either their is no table called 'CLEANEDCAMDENGPS' (and I think you've probably got the idea about the issues with case), but it is also possible that the schema is not 'public', depending on your access to a database. If it is on your local computer, and you have no idea what I'm talking about, then it probably is public. If you are using a shared installation, it may not be public.

if you are using psql (the command line), and try a command like \dt, you will see a list of the 'relations' which do exist - something like 

               List of relations
   Schema   |       Name       | Type  | Owner 
------------+------------------+-------+-------
 backoffice | access           | table | ben
 backoffice | accesslevel      | table | ben
 backoffice | categories       | table | ben

if your table is not in this list, (it's specified as backoffice.access for instance) then it may not exist, or you need to look up search_path.

cheers

Ben

 

On 31/05/2011, at 2:35 AM, James Smith 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

-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20110531/f3501ff6/attachment.html>