[postgis-users] geometryfromtext problem

[Carl Anderson]

On 04/10/2004 10:42:17 AM, Joel Gwynn wrote:
> I'm having trouble doing something I've done before.  I've got a  
> geometry column, but for some reason I can't populate it.  I have a  
> lat field and a long field (both floats) that i'm trying to use to  
> populate the_geom like so:
> 
> update locations set the_geom=GeometryFromText('POINT (' || cast(long  
> as varchar(10)) || ' ' || cast(lat as varchar(10)) || ')', -1);
> 
> which yields:
> 
> ERROR:  Function 'geometryfromtext(varchar, int4)' does not exist
>        Unable to identify a function that satisfies the given  
> argument types
>        You may need to add explicit typecasts
>

update locations set the_geom=GeometryFromText(('POINT (' || cast(long
 as varchar(10)) || ' ' || cast(lat as varchar(10)) || ')')::geometry,
 -1);

Pg will not always do an imlicit cast when you are || thing together
and a explicit cast is necessary.

looking at the definition of  (using \df geometryfromtext
you'll see
   GEOMETRYFROMTEXT (geometry, integer)

so PostGIS is relying on the implicit cast from text -> to geometry

CREATE CAST ( text AS geometry) WITH FUNCTION geometry(text) AS  
IMPLICIT;

your complex statement just confused the type casting mechinism in  
Postgres.

alternative forms of you statement

update locations set the_geom=GeometryFromText(gometry('POINT (' ||  
cast(long
 as varchar(10)) || ' ' || cast(lat as varchar(10)) || ')'),
 -1);

update locations set the_geom=GeometryFromText(cast(('POINT (' || cast 
(long  as varchar(10)) || ' ' || cast(lat as varchar(10)) || ')') as  
geometry),  -1);

note that i am grouping the || strings with ( ) so that Postgres does  
not cast only the first or last member or the list

'POINT('||lat_str||' '||long_str||')'::geometry is not the same as
('POINT('||lat_str||' '||long_str||')')::gemetry

C.

Carl Anderson