[SQL] Get x from point?

[Daniel Lau]

Hi all,

I am wondering if we are able to extract the x-coordinate from a
point-type attribute with SQL. I have been looking for this sort of
functions from documents and websites but it seems the function does not
exist. Or, any altnernate to do so?

regards,
Daniel Lau
Hong Kong University of Science and Technology


---(end of broadcast)---
TIP 6: Have you searched our list archives?

   http://archives.postgresql.org

[SQL] Get x from path/polygon?

[Daniel Lau]

Hi all,

I succeed in extracting x and y value from POINT. Is it also possible to
extract x and y value from PATH and POLYGON?

I tried to get x and y from PATH using the similar method. However, the
machine says the PATH is not an array and does not allow me to do so.

Any hints on this?  Thanks in advance.

regards,
Daniel Lau

On Mon, 22 Dec 2003, Michael Fuhr wrote:

> On Mon, Dec 22, 2003 at 04:54:01PM +0800, Daniel Lau wrote:
> > I am wondering if we are able to extract the x-coordinate from a
> > point-type attribute with SQL. I have been looking for this sort of
> > functions from documents and websites but it seems the function does not
> > exist. Or, any altnernate to do so?
> 
> From the bottom of the Geometric Functions and Operators chapter of
> the documentation:
> 
> "It is possible to access the two component numbers of a point as
> though it were an array with indices 0 and 1.  For example, if t.p
> is a point column then SELECT p[0] FROM t retrieves the X coordinate
> and UPDATE t SET p[1] = ... changes the Y coordinate.  In the same way,
> a value of type box or lseg may be treated as an array of two point
> values."
> 
> http://www.postgresql.org/docs/current/static/functions-geometry.html
> 
> -- 
> Michael Fuhr
> http://www.fuhr.org/~mfuhr/
> 
> ---(end of broadcast)---
> TIP 3: if posting/reading through Usenet, please send an appropriate
>   subscribe-nomail command to [EMAIL PROTECTED] so that your
>   message can get through to the mailing list cleanly
> 


---(end of broadcast)---
TIP 6: Have you searched our list archives?

   http://archives.postgresql.org

[SQL] Type conversion from TEXT to DOUBLE PRECISION

[Daniel Lau]

Hi all,

Thank you for reading this mail.

I am trying to do the following:
Extract the first half of _aaa and put it in column _bbb

Here is the table, named: _table:

Varchar[10]   |   Double Precision
_aaa_bbb
_

1234567890

I used two functions to do it: substring() and to_number(). The SQL is
like this:
UPDATE _table SET _bbb = to_number(substring(_aaa from 1 for 5), '9');
The machine fails me and said
ERROR: invalid input syntac for type numeric: " "

I guess the machine can not treat TEXT as CHAR[5]. I tries to CAST TEXT as
CHAR[5]. It also doesnt allow me to do so.

Can anyone give me some hints on this?

regards,
Daniel




---(end of broadcast)---
TIP 6: Have you searched our list archives?

   http://archives.postgresql.org

Re: [SQL] Type conversion from TEXT to DOUBLE PRECISION

[Daniel Lau]

On Fri, 9 Jan 2004, Tom Lane wrote:

> Richard Huxton <[EMAIL PROTECTED]> writes:
> > Since your error seems to be complaining about a space, I'd guess you've got 
> > other than numeric values in _aaa.
> 
> In fact, with a bit of experimentation I see the same error message:
> 
> regression=# select to_number('12345', '9');
>  to_number
> ---
>  12345
> (1 row)
> 
> regression=# select to_number('1234 ', '9');
>  to_number
> ---
>   1234
> (1 row)
> 
> regression=# select to_number(' 1234', '9');
>  to_number
> ---
>   1234
> (1 row)
> 
> regression=# select to_number(' ', '9');
> ERROR:  invalid input syntax for type numeric: " "
> regression=# select to_number('z', '9');
> ERROR:  invalid input syntax for type numeric: " "
> regression=#
> 
> The error message's report of the input string seems a tad misleading,
> especially in the last case.  (Karel, is this fixable?)  But anyway,
> it sure looks like the problem is bad input data.
> 
>   regards, tom lane
> 

Thanks Tom and Richard. Yes, it is the problem of bad input data. I have
4000 rows of data and there are 10 rows containing blank string ('
'). I have to add a Where clause to carry out the SQL:

UPDATE _table
SET _bbb = to_number(substring(_aaa from 1 for 5), '9')
WHERE _aaa <> ' ';

I guess a function checking if a string contains only numbers would be
betteroff. But I find no such functions. Checking that it's not blank
would be the only solution I can think of.

Thanks again.

regards,
Daniel Lau


---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])