Thread: text to point conversion not working. ( cannot cast type text to point )

Hi all,

Using pgsql 8.0.1

I'm just starting with using the geometry data types in postgres, and ran into
what seems like a very basic problem.  Namely, I can't seem to convert/cast
type text into type point when that text results from any expression.  Ie, it
*only* works for a plain string literal.

Examples:

select '1,2'::point;
 point
-------
 (1,2)

  That works with a string literal.  This does not.

select ('1' || ',2')::point;
ERROR:  cannot cast type text to point

  Nor does this.

select cast('1' || ',2' as point);
ERROR:  cannot cast type text to point

  Nor this.

select '1,2'::varchar::point;
ERROR:  cannot cast type character varying to point

  Nor this.

select '1,2'::char::point;
ERROR:  cannot cast type character to point

  This works.  With a string literal.

select point_in('1,2');
 point_in
----------
 (1,2)

  But this does not.  :(

select point_in('1' || ',2');
ERROR:  function point_in(text) does not exist


So, is there a built-in way to do this, or.....?



Background:

I have a hierarchical table where I have coordinate data for only the leaf
nodes.  I therefore want to find the center of all the leaf nodes under a
given parent node, and set the parent node coordinate to that center point.

I can calcululate that center point using aggregate functions (min, max) to
find the necessary x,y values.    So my query would look something like this:

update parent_table set col =
(select (max(pnt[0])-min(pnt[0]))/2+min(pnt[0]) || ',' ||
max(pnt[1])-max(pnt[1])/2+min(pnt[1])  from point_tmp where condition)
where condition2 ;

Where point_tmp.tmp is defined as a point column.

However, when I try to do it, I get a similar error:

   column "col" is of type point but expression is of type text

If the above task can be performed some other way, perhaps I don't require
string concatenation....



--
Dan Libby

On Wed, Sep 27, 2006 at 08:14:29PM -0600, Dan Libby wrote:
> Hi all,
>
> Using pgsql 8.0.1
>
> I'm just starting with using the geometry data types in postgres, and ran into
> what seems like a very basic problem.  Namely, I can't seem to convert/cast
> type text into type point when that text results from any expression.  Ie, it
> *only* works for a plain string literal.

String literals are not text. You can however cheat a little like so:

# SELECT point_in( textout ( '1' || ',2' ) );
 point_in
----------
 (1,2)
(1 row)

textout turns a text value to a "string literal" (sort of) which is
then parsed by the point input function.

If you want you can encapsulate this into a function and create the
cast yourself.

Hope this helps,
--
Martijn van Oosterhout   <kleptog@svana.org>   http://svana.org/kleptog/
> From each according to his ability. To each according to his ability to litigate.

On Wed, 27 Sep 2006, Dan Libby wrote:

> Background:
>
> I have a hierarchical table where I have coordinate data for only the leaf
> nodes.  I therefore want to find the center of all the leaf nodes under a
> given parent node, and set the parent node coordinate to that center point.
>
> I can calcululate that center point using aggregate functions (min, max) to
> find the necessary x,y values.    So my query would look something like this:
>
> update parent_table set col =
> (select (max(pnt[0])-min(pnt[0]))/2+min(pnt[0]) || ',' ||
> max(pnt[1])-max(pnt[1])/2+min(pnt[1])  from point_tmp where condition)
> where condition2 ;
>
> Where point_tmp.tmp is defined as a point column.
>
> However, when I try to do it, I get a similar error:
>
>    column "col" is of type point but expression is of type text
>
> If the above task can be performed some other way, perhaps I don't require
> string concatenation....

I don't have 8.0.x to check, but there's likely a
point(double precision, double precision) function you can use.