Обсуждение: 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
Re: text to point conversion not working. ( cannot cast type text to point )
От
Martijn van Oosterhout
Дата:
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.