Thread: [GENERAL] Geometric, getting x and y co-ordinates from point datatype
Hi there, Suppose you have a table: id pos 1 (0,5) 2 (-1,6) 3 (5.5,9.8) Are there functions to get to the individual coords such that SELECT id,get_x(pos),get_y(pos) FROM table; gives id get_x get_y 1 0 5 2 -1 6 3 5.5 9.8 From doing a bit of snooping about in the Programmer's Guide, I can only assume that this would involve a modified version point_out. Are these functions already out there? If not, can someone give me pointers to the answer? Would the solution indeed be a modified version of point_out and if so, can someone give me the point_out source to work with? (I don't program in C -hell, I don't really program in anything, I dabble- but I'm quite happy to experiment) Regards, Stuart. +-------------------------+--------------------------------------+ | Stuart Rison | Ludwig Institute for Cancer Research | +-------------------------+ 91 Riding House Street | | Tel. (0171) 878 4041 | London, W1P 8BT, UNITED KINGDOM. | | Fax. (0171) 878 4040 | stuart@ludwig.ucl.ac.uk | +-------------------------+--------------------------------------+
Following my original posting, I did a bit of hunitng in the source and
used them to come up with these functions:
/* get_x.c
a function to return the x coord of a point */
#include "postgres.h"
#include "utils/geo_decls.h"
double
get_x(Point *pt)
{
if (!PointerIsValid(pt))
return NULL;
return pt->x;
} /* get_x() */
/* get_y.c
a function to return the y coord of a point */
#include "postgres.h"
#include "utils/geo_decls.h"
double
get_y(Point *pt)
{
if (!PointerIsValid(pt))
return NULL;
return pt->y;
} /* get_y() */
... then I do ...
cc -c get_<x|y>.c -I/usr/local/pgsql/include
ld -G -Bdynamic -o get_<x|y>.so get_<x|y>.o
which gives me get_x.so and get_y.so
... then I do (within psql)...
brecard10=> CREATE FUNCTION get_<x|y>(point) RETURNS float
brecard10-> AS '/usr/people/postgres/get_<x|y>.so' LANGUAGE 'c';
AND NOW FOR THE CRAZY PART...
brecard10=> select * from points;
id|pos
--+-----------
1|(1,2)
2|(-1.3,4.77)
3|(0,-3)
(3 rows)
brecard10=> select id,pos,get_x(pos) from points;
id|pos |get_x
--+-----------+-----
1|(1,2) | 1
2|(-1.3,4.77)| -1.3
3|(0,-3) | 0
(3 rows)
Rejoice I thought, I'm a genius, wealth, power and good looks will soon
follow...
brecard10=> select id,pos,get_y(pos) from points;
id|pos |get_y
--+-----------+-----
1|(1,2) | 1
2|(-1.3,4.77)| -1.3
3|(0,-3) | 0
(3 rows)
There goes the nobel, my seat in the parliament and my winning smile!
I have no training in C so I've reached this far by trial and error. I
have however discovered that if write a function such as
/* pants.c
weird of what! */
#include "postgres.h"
#include "utils/geo_decls.h"
double
pants(Point *pt)
{
return 2.0;
}
and compile it as above and make it a function I get:
brecard10=> select id,pos,pants(pos) from points;
id|pos |pants
--+-----------+-----
1|(1,2) | 1
2|(-1.3,4.77)| -1.3
3|(0,-3) | 0
(3 rows)
HELP!
WHAT'S GOING ON!!!
Why can't I get to pt->y?
Why does function pants behave just like get_x (and get_y)?
Stuart.
+-------------------------+--------------------------------------+
| Stuart Rison | Ludwig Institute for Cancer Research |
+-------------------------+ 91 Riding House Street |
| Tel. (0171) 878 4041 | London, W1P 8BT, UNITED KINGDOM. |
| Fax. (0171) 878 4040 | stuart@ludwig.ucl.ac.uk |
+-------------------------+--------------------------------------+
Re: [GENERAL] Geometric, getting x and y co-ordinates GOING MAD!!!!!
From
selkovjr@mcs.anl.gov
Date:
> I have no training in C so I've reached this far by trial and error. I
> have however discovered that if write a function such as
>
> /* pants.c
> weird of what! */
> #include "postgres.h"
> #include "utils/geo_decls.h"
> double
> pants(Point *pt)
> {
> return 2.0;
> }
>
> and compile it as above and make it a function I get:
>
> brecard10=> select id,pos,pants(pos) from points;
> id|pos |pants
> --+-----------+-----
> 1|(1,2) | 1
> 2|(-1.3,4.77)| -1.3
> 3|(0,-3) | 0
> (3 rows)
>
> HELP!
> WHAT'S GOING ON!!!
> Why can't I get to pt->y?
> Why does function pants behave just like get_x (and get_y)?
If you write your function as double, you can't declare it as float in sql. What you wanted is probably this:
#include "postgres.h"
#include "utils/geo_decls.h"
float32 x(Point *pt);
float32 y(Point *pt);
float32 x(Point *pt) {
float32 result = (float32) palloc(sizeof(float32data));
*result = (float)pt->x;
return (result);
}
float32 y(Point *pt) {
float32 result = (float32) palloc(sizeof(float32data));
*result = (float)pt->y;
return (result);
}
(note that float32 is a pointer type)
Then, in sql,
CREATE FUNCTION x(point)
RETURNS float4
AS '/home/postgres/src/point_adts/point_adts.so' <- this is where the above x() and y() functions live
LANGUAGE 'c';
CREATE FUNCTION y(point)
RETURNS float4
AS '/home/postgres/src/point_adts/point_adts.so'
LANGUAGE 'c';
--Gene
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Here's the complete working code:
begin 644 point_adts.tgz
M'XL(`&4!6#<``^V5\6^:0!3'_;7W5[QH$\$I@@IN-BZQ%AL7JIWHTM\:"H=E
M0XYRF-0MV]^^0T5L8]NDL2[-[A,3\+Z/]^[NO7<7$B^(KRTGIM7<6P$-N:FJ
MD`,`I9$^&\OG&AE`:];49K.I:AI3:XU:/0?JF\UHBSF-K0@@%Q(:3R-,G[;#
MT9/B^R7,\G]A_<"NY^.]QU!D65OG>V?^FXJ:YK\F*_5$56I*#N2]SV0'_WG^
MS5'WK#]J0W5.HRJ-[&HXI7?^\@UY@>W/';Q+2VM%FOKDQO(1ZO:,SKGYH0V5
M_K%@]$\OOS*W(OLG257V6[M"J(`*<&8,3[^8X%&(;S$XB\":>;;E^XN*3RP'
M.T!NOF,[AB0`E0#&S"KOS@.;YN%NCB,/4^8EG5UWI'?&.O0F@^ZX/QPD3JT8
M$D\0D7GL!9B"&Y%9$HSBM5,VC]4LVI!UP+%P9IB37J]_)0)"GNM@%_2KRV1H
M;<W6EQ@EKRV)M(^%M2R*"`>.YR+$EM':V(@(;5Y;6X$D@HZFM@T5>FM%;+T5
MLBU2\M`4V3ZV@A8ZBF90<;><KP=*$H'2'R@5T"ORO]7_7L!:P?<EEN/]UMA+
M_:^J2G;^R\GYW]"T&N__0_"H>^!>6%:$B$;Z>#(:F."R3HH;J&-"L7I+9KB:
M;M3J1,C*YT$%%Y'1&9Q/.N<Z%.WB"3L?'L59O&V<<>?4T"$$(5SUDLBD_L#4
M1V/H#\9#IGSK&!/=%(J"7%;%(M.?D"M*67M.5R6U_$GZN#1!R-0-O3M.=E$L
M)VL4H3<:7D!X\IK>/`2[-];>:XR7^E_+[O]FHU%CJBK+==[_AZ"07J/Y=`.D
MVSS*1MD%ZM/J%)-K!]O^4D3+5JVS/+$ZOTR*!DIATF'9^.+!^$;8-H=?*,U_
M*K/@<S^&-@CK$1%"=B$16Z#>3TS<=-BQ8DM,_*8.2H^^%,.X\OG^9*-'.)Y'
M`0@K,_;E[VQ.BT/.:?'\G/YU,7`X'`Z'P^%P.!P.A\/A<#@<#H?#X7#>/7\!
(A%`/L@`H````
`
end
>> HELP! >> WHAT'S GOING ON!!! >> Why can't I get to pt->y? >> Why does function pants behave just like get_x (and get_y)? > >If you write your function as double, you can't declare it as float in sql. I thought something like that might have been the case I did try to declare the return value as float in my get_<x|y> attempts but I didn't get anywhere with it! >What you wanted is probably this: > <snip code that works!! to Gene then the fame, power, fortune and good looks (and I get my sanity back!> I guess the bottom lines is that you can't really 'bluff' your way in 'C' that easy and that I'll have to actually learn some if I want to get 'C' functions to work! This all steemed from me trying to write a standard deviation/variance set of aggregate function. This was just because a point seemed -at the time- like quite a 'cute' way of storing to floats in one base type eliminating the need for arrays (the two floats being the sum of elements and the sum of the elements squared stored as the x and y coords of a point respectively). This means that if anybody is interested I now have these aggregate functions working (needs PL/pgSQL as opposed to a solution previously posted by Jan (Weick) which used Pl/Tcl). Cheers, S. +-------------------------+--------------------------------------+ | Stuart Rison | Ludwig Institute for Cancer Research | +-------------------------+ 91 Riding House Street | | Tel. (0171) 878 4041 | London, W1P 8BT, UNITED KINGDOM. | | Fax. (0171) 878 4040 | stuart@ludwig.ucl.ac.uk | +-------------------------+--------------------------------------+
Re: [GENERAL] Geometric, getting x and y co-ordinates GOING MAD!!!!!
From
selkovjr@mcs.anl.gov
Date:
On Fri, 4 Jun 1999, Stuart Rison wrote: [snipped -- a float return type drama with a happy end] > I guess the bottom lines is that you can't really 'bluff' your way in 'C' > that easy and that I'll have to actually learn some if I want to get 'C' > functions to work! That wasn't so much about C as it was about how postgres handles return values. Here's the relevant doc page: http://www.postgresql.org/docs/programmer/xfunc414.htm As far as 'bluffing', that is what perl was intentionally built for. C is remarkably simple, but it assumes you know not only WHAT it does but also HOW. It's easy to get shot if you forget about the how part for a moment. > This all steemed from me trying to write a standard deviation/variance set > of aggregate function. This was just because a point seemed -at the time- > like quite a 'cute' way of storing to floats in one base type eliminating > the need for arrays (the two floats being the sum of elements and the sum > of the elements squared stored as the x and y coords of a point > respectively). Although point type is a cute way of storing float pairs, it may become extremely inefficient in case of mega-tables. What are you going to do with your points? Do you build indices on them? How are the points distributed in 2-D? The type of distribution and order of points affect the performance of R-trees. Also, if you were looking to store the (mean, SD) values in one column, you would be better off with the whole new type. If your science/confession would allow you to represent random distributions as intervals, such as (mean - SD/2, mean + SD/2), the intervals could be stored as a 1-D geometric type and indexed with R-tree, with some caution. If that makes sense, welcome to my segment type: http://wit.mcs.anl.gov/~selkovjr/seg-type.tgz It already has some provision for the (mean, SD) syntax, but that needs debugging. It works great with 'lower .. upper' syntax, where either 'lower' or 'upper' can be omitted. Besides, it is a variable precision type: your query returns exactly as many significant digits as you have inserted. (I couldn't stand frustration it gives you when it returns 1.2000000 for the value you stored as 1.20. Even 1.20 and 1.2 make a huge difference when you deal with measurements) --Gene
At 7:33 pm -0400 7/6/99, selkovjr@mcs.anl.gov wrote: >On Fri, 4 Jun 1999, Stuart Rison wrote: > >[snipped -- a float return type drama with a happy end] > > >That wasn't so much about C as it was about how postgres handles >return values. Here's the relevant doc page: > >http://www.postgresql.org/docs/programmer/xfunc414.htm > >As far as 'bluffing', that is what perl was intentionally built for. C >is remarkably simple, but it assumes you know not only WHAT it does >but also HOW. It's easy to get shot if you forget about the how part >for a moment. That's very true, I certainly seem to do most of my bluffing in Perl. Thanks for the doc ref, it makes -a bit- more sense now. >> This all steemed from me trying to write a standard deviation/variance set >> of aggregate function. This was just because a point seemed -at the time- >> like quite a 'cute' way of storing to floats in one base type eliminating >> the need for arrays (the two floats being the sum of elements and the sum >> of the elements squared stored as the x and y coords of a point >> respectively). > >Although point type is a cute way of storing float pairs, it may >become extremely inefficient in case of mega-tables. What are you >going to do with your points? Do you build indices on them? How are the >points distributed in 2-D? The type of distribution and order of >points affect the performance of R-trees. I think I may have confused you, did you think I was storing a table of points as a method of storing a value with a confidence interval (e.g. 6.3+/-0.37) or perhaps matching x and y values for linear regression type stats? Saddly, my aggregate functions are far more trivial then that!!! The point (and there is only one) is used literally as a way of storing two floats by the 'sfunc1' of my stddev and variance aggregates. It's a very crude aggregate more to teach myself the basics of defining a new aggregate then to be used extensively based on a posting by Jan Weick a long time ago which used pg/tcl. The idea is that you need to keep track of a minimum of three values to get an accurate calculation of variance (and by extension standard deviation) in a single pass algorithm (which I would argue is what an aggregate is): - the sum of the elements in a series - the sum of the square of the elements in a series - the number of elements in a series 'sfunc2' could easily cope with the number of elements in a series but that left sfunc1 to store two floats and I couldn't find a way of getting sfunc1 to cope with arrays so I just used a point instead (Jan used a Tcl list). >Also, if you were looking to store the (mean, SD) values in one >column, you would be better off with the whole new type. If your >science/confession would allow you to represent random distributions >as intervals, such as (mean - SD/2, mean + SD/2), the intervals could >be stored as a 1-D geometric type and indexed with R-tree, with some >caution. If that makes sense, welcome to my segment type: > >http://wit.mcs.anl.gov/~selkovjr/seg-type.tgz Yes, it makes sense and I had a look your segment type work. Although I don't have a need for it yet, it looks wery impressive (big sigh... what a long and steep learning 'C' curve ahead of me... can I write all me functions in Perl and get those to be linked dynimically ;) ) >It already has some provision for the (mean, SD) syntax, but that >needs debugging. It works great with 'lower .. upper' syntax, where >either 'lower' or 'upper' can be omitted. Besides, it is a variable >precision type: your query returns exactly as many significant digits >as you have inserted. (I couldn't stand frustration it gives you when it >returns 1.2000000 for the value you stored as 1.20. Even 1.20 and 1.2 >make a huge difference when you deal with measurements) > >--Gene As a final suggestion for a TO-DO, should basic statistical function (STDDEV, VARIANCE and perhaps MODAL) be added to the standard aggregates set? Best regards, Stuart. +-------------------------+--------------------------------------+ | Stuart Rison | Ludwig Institute for Cancer Research | +-------------------------+ 91 Riding House Street | | Tel. (0171) 878 4041 | London, W1P 8BT, UNITED KINGDOM. | | Fax. (0171) 878 4040 | stuart@ludwig.ucl.ac.uk | +-------------------------+--------------------------------------+