[postgis] PostgreSql point to Postgis point conversion

[Obe, Regina DND\MIS]

To get the X and Y  values of a postgres sql object, you can try creating
functions in postgresql or just apply the following algorithm if its a one
shot deal.  Note I don't know how slow this would be though, but this is how
I would create the functions to compensate for the fact that postgresql
doesn't seem to have any built in.  Just check my logi

To figure out the X value of a postgres point P do

width(box(point '(0,0)', P)*(CASE WHEN (P >> point '(0,0)') THEN 1 ELSE -1
END) 

To figure out the Y value of a postgres point P do

height(box(point '(0,0)', P)* (CASE WHEN P >^ point '(0,0)') THEN 1 ELSE -1
END) 


So your update statement if you were updating a table would look like this
(I'm assuming your point field is called location and you will store the x
and y corrdinates in locationx locationy

UPDATE table1
	SET locationx = width(box(point '(0,0)', location)*(CASE WHEN
(location >> point '(0,0)') THEN 1 ELSE -1 END) ,
		locationy = height(box(point '(0,0)', location)* (CASE WHEN
location >^ point '(0,0)') THEN 1 ELSE -1 END)



The property I am using here (which is probably convoluted - but kind of
makes sense to me thinking about it) is that
If you create a box from the origin (0,0) to a point, then the width of that
box represents the absolute value of the x coordinate of that point.  To
find the sign of x - you then ask is the point to the right or left of the
origin using >> -- if to the right then it is positive otherwise it is
negative

Similarly to find the y coordinate of that point. It would be the height of
the bounded box (then check if it is above or below origin to figure out the
sign of it).









-----Original Message-----
From: M Vermeij [mailto:mj_vermeij at yahoo.com]
Sent: Monday, November 26, 2001 8:42 AM
To: postgis at yahoogroups.com
Subject: Re: [postgis] PostgreSql point to Postgis point conversion


I am trying to use the :

update a set g = geometryfromtext(('POINT(' ||
> x || ' '
> ||y||')')::geometry,123);
 you mentioned. However instead of you're x and y
columns I would like to use the x and y coordinates
which are already stored in a postgres point field,
but can't seem to retrieve them seperately. 

Question:
If the postgres point field is called 'location' what
whould the UPDATE command then be? ('location2' is the
PostGis point field.)

UPDATE table1 set location2 =geometryfromtext(( 
???????    )::geometry,-1);




--- Dave Blasby <dblasby at refractions.net> wrote:
> Here's the easiest way to do it - just use
> postgresql's builtin TEXT
> type to create the WKT:
> 
> t1=# create table a (x float, y float, g geometry);
> 		....
> 
> t1=# select * from a;
>  x  | y  | g 
> ----+----+---
>   1 |  2 | 
>  11 | 22 | 
> (2 rows)
> 
> 
> To make these into points, we make some WKT (|| is
> the concat operator):
> 
> 
> t1=# select 'POINT(' || x || ' ' ||y||')' from a;
>    ?column?   
> --------------
>  POINT(1 2)
>  POINT(11 22)
> (2 rows)
> 
> So, 
> 
> t1=# update a set g = geometryfromtext(('POINT(' ||
> x || ' '
> ||y||')')::geometry,123);
> t1=# select * from a;
>  x  | y  |           g           
> ----+----+-----------------------
>   1 |  2 | SRID=123;POINT(1 2)
>  11 | 22 | SRID=123;POINT(11 22)
> (2 rows)
> 
> 
> dave
> 


__________________________________________________
Do You Yahoo!?
Yahoo! GeoCities - quick and easy web site hosting, just $8.95/month.
http://geocities.yahoo.com/ps/info1

To unsubscribe from this group, send an email to:
postgis-unsubscribe at yahoogroups.com

 

Your use of Yahoo! Groups is subject to http://docs.yahoo.com/info/terms/ 


------------------------ Yahoo! Groups Sponsor ---------------------~-->
E-mail viral marketing - with FastTree
Email to 50. You might reach 500.
Unlimited use and tracking, $20/mo.
http://www.fasttree.com/s/11.htm
http://us.click.yahoo.com/UGVLpD/MJRDAA/yigFAA/PhFolB/TM
---------------------------------------------------------------------~->

To unsubscribe from this group, send an email to:
postgis-unsubscribe at yahoogroups.com

 

Your use of Yahoo! Groups is subject to http://docs.yahoo.com/info/terms/