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
