[postgis-users] Enter details into Geom column

[James Smith]

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
>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20110530/5bf7710a/attachment.html>