[prev in list] [next in list] [prev in thread] [next in thread]
List: postgis-users
Subject: Re: [postgis-users] Enter details into Geom column
From: Ricardo Bayley <ricardo.bayley () gmail ! com>
Date: 2011-05-31 1:11:48
Message-ID: BANLkTim1UzNYrTL0JiY8TmU=a52x5xCNHA () mail ! gmail ! com
[Download RAW message or body]
[Attachment #2 (multipart/alternative)]
Hi Ben
Try instead of "public.CLEANEDCAMDENGPS" --> public."CLEANEDCAMDENGPS"
notice the "".
I strongly suggest to lowercase all your table and column names.
Hope this helped.
Ricardo
2011/5/30 Ben Madin <lists@remoteinformation.com.au>
> 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@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@postgis.refractions.net
>> > http://postgis.refractions.net/mailman/listinfo/postgis-users
>> >
>>
>> _______________________________________________
>> postgis-users mailing list
>> postgis-users@postgis.refractions.net
>> http://postgis.refractions.net/mailman/listinfo/postgis-users
>>
>
> _______________________________________________
> postgis-users mailing list
> postgis-users@postgis.refractions.net
> http://postgis.refractions.net/mailman/listinfo/postgis-users
>
>
>
> _______________________________________________
> postgis-users mailing list
> postgis-users@postgis.refractions.net
> http://postgis.refractions.net/mailman/listinfo/postgis-users
>
>
[Attachment #5 (text/html)]
Hi Ben<div><br></div><div>Try instead of "public.CLEANEDCAMDENGPS" --> \
public."CLEANEDCAMDENGPS"</div><div><br></div><div>notice the \
"".</div><div><br></div><div>I strongly suggest to lowercase all your table \
and column names.</div> <div><br></div><div><br></div><div>Hope this \
helped.</div><div><br></div><div>Ricardo</div><div><br></div><div><br></div><div><br><br><div \
class="gmail_quote">2011/5/30 Ben Madin <span dir="ltr"><<a \
href="mailto:lists@remoteinformation.com.au">lists@remoteinformation.com.au</a>></span><br>
<blockquote class="gmail_quote" style="margin:0 0 0 .8ex;border-left:1px #ccc \
solid;padding-left:1ex;"><div \
style="word-wrap:break-word">James,<div><br></div><div>The error message has two \
possibilities:</div><div class="im"> <div><br></div><div>ERROR: relation \
"public.CLEANEDCAMDENGPS" does not \
exist<br></div><div><br></div></div><div>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.</div> <div><br></div><div>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 </div><div><br></div><div><div> \
List of relations</div> <div> Schema | Name | Type | Owner \
</div><div>------------+------------------+-------+-------</div><div> backoffice | \
access | table | ben</div><div> backoffice | accesslevel | \
table | ben</div> <div> backoffice | categories | table | \
ben</div></div><div><br></div><div>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.</div> <div><br></div><div>cheers</div><div><br></div><font \
color="#888888"><div>Ben</div></font><div><div></div><div \
class="h5"><div><br></div><div> </div><div><br></div><div><div><div>On 31/05/2011, \
at 2:35 AM, James Smith wrote:</div> <br><blockquote type="cite">Dear \
Brent,<br><br>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:<br> <br>-----<br>ERROR: function \
st_creategeometrycolumn(unknown, unknown, unknown, integer, unknown, integer) does \
not exist<br>
SQL state: 42883<br>Hint: No function matches the given name and argument types. You \
might need to add explicit type casts.<br>Character: 8<br>-----<br><br>So I browsed \
the PostGIS functions, and thought that I should perhaps use the function \
'AddGeometryColumn' instead, so changed the code to below:<br>
<br>----<br>select AddGeometryColumn('public', \
'CLEANEDCAMDENGPS','geom',4326,'POINT',2);<br>----<br><br>However \
this returns an error of the below:<br><br>----<br>ERROR: relation \
"public.CLEANEDCAMDENGPS" does not exist<br>
----<br><br>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.<br>
<br>Any thoughts?<br><br>Thanks again, and yes, I'll take onboard your point \
about captials and table names from this point \
forwards.<br><br>Cheers<br><br>James<br><br><br><br><div class="gmail_quote">On 29 \
May 2011 23:44, <span dir="ltr"><<a href="mailto:pcreso@pcreso.com" \
target="_blank">pcreso@pcreso.com</a>></span> wrote:<br>
<blockquote class="gmail_quote" style="margin:0 0 0 .8ex;border-left:1px #ccc \
solid;padding-left:1ex"><table border="0" cellpadding="0" \
cellspacing="0"><tbody><tr><td style="font:inherit" valign="top"><br>Hi \
James,<br><br>
I suggest you avoid upper case letters in table & column names if you can. It \
makes a few things easier....<br><br>The syntax in both SQL statements is wrong. \
Try:<br><br><span style="font-style:italic">select \
ST_CreateGeometryColumn('public','CLEANEDCAMDENGPS','geom',4326,'POINT',2);</span><br \
style="font-style:italic">
<br>the fields are (in order):<br><br>schema where table can be found \
('public')<br>the table name where you want the new column (<span \
style="font-style:italic">'CLEANEDCAMDENGPS')</span><br>the name of the \
geometry column to create (<span style="font-style:italic">'geom')</span><br>
the SRID of the geometry column to create (<span \
style="font-style:italic">4326)</span><br>the geometry type<span \
style="font-style:italic"> ('POINT')</span><br>the number of dimensions \
(<span style="font-style:italic"></span>2 - x & y)<br>
<br>All string values need to be quoted.<br><br>To populate this column \
try:<br><br><span style="font-style:italic">update \
"CLEANEDCAMDENGPS"</span><br style="font-style:italic"><span \
style="font-style:italic">set \
geom=setsrid(makepoint("LONGITUDE","LATITUDE"),4326);</span><br>
<br>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 =).<br><br><br>HTH,<br><br> Brent Wood<div>
<div></div><div><br><br><br>James Smith wrote:<br>> Dear all,<br>><br>> \
Would appreciate some help. I have created an existing database (with<br>> PostGIS \
extension) and it has a table called CLEANEDCAMDENGPS which <br>
> is populated with approx 600,000 rows. There are 20 or so columns in the<br>> \
table, two of which are Latitude and Longitude (WGS84). I would now<br>> like to \
create a Geom column with points in, the values of which<br>> should be taken \
from the latitude and longitude column. Could someone<br>> provide me with sample \
code as to how to do this please? I had a go<br>> with the below, but don't \
really know what I'm doing... neither of the<br>
> statements work...<br>><br>> --CREATE THE COLUMN--<br>> SELECT \
AddGeometryColumn('CAMDENGPS', 'GPS_POINTS', 'geom', 4326, \
'POINT', 2)<br>><br>> --POPULATE THE COLUMN--<br>
> INSERT INTO CLEANEDCAMDENGPS (GPS_POINTS)<br>> VALUES ( \
ST_GEOMFROMTEXT('POINT((SELECT LONGITUDE FROM<br>> CLEANEDCAMDENGPS) (SELECT \
LATITUDE FROM CLEANEDCAMDENGPS))', 4326,<br>> 'Point'));<br>
><br>> Thank you<br>><br>> James<br>> \
_______________________________________________<br>> postgis-users mailing \
list<br>> <a href="mailto:postgis-users@postgis.refractions.net" \
target="_blank">postgis-users@postgis.refractions.net</a><br>
>
<a href="http://postgis.refractions.net/mailman/listinfo/postgis-users" \
target="_blank">http://postgis.refractions.net/mailman/listinfo/postgis-users</a><br>><br><br>_______________________________________________<br>
postgis-users mailing list<br><a href="mailto:postgis-users@postgis.refractions.net" \
target="_blank">postgis-users@postgis.refractions.net</a><br><a \
href="http://postgis.refractions.net/mailman/listinfo/postgis-users" \
target="_blank">http://postgis.refractions.net/mailman/listinfo/postgis-users</a><br>
</div></div></td></tr></tbody></table></blockquote></div><br>
_______________________________________________<br>postgis-users mailing list<br><a \
href="mailto:postgis-users@postgis.refractions.net" \
target="_blank">postgis-users@postgis.refractions.net</a><br><a \
href="http://postgis.refractions.net/mailman/listinfo/postgis-users" \
target="_blank">http://postgis.refractions.net/mailman/listinfo/postgis-users</a><br> \
</blockquote></div><br></div></div></div></div><br>_______________________________________________<br>
postgis-users mailing list<br>
<a href="mailto:postgis-users@postgis.refractions.net">postgis-users@postgis.refractions.net</a><br>
<a href="http://postgis.refractions.net/mailman/listinfo/postgis-users" \
target="_blank">http://postgis.refractions.net/mailman/listinfo/postgis-users</a><br> \
<br></blockquote></div><br></div>
_______________________________________________
postgis-users mailing list
postgis-users@postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users
[prev in list] [next in list] [prev in thread] [next in thread]
Configure |
About |
News |
Add a list |
Sponsored by KoreLogic