Skip site navigation (1) Skip section navigation (2)

Re: [PORTS] Re: Notes on Postgresql 7.0 on FreeBSD

From: Bruce Momjian <pgman(at)candle(dot)pha(dot)pa(dot)us>
To: Rolf Grossmann <grossman(at)securitas(dot)net>
Cc: pgsql-ports(at)postgreSQL(dot)org
Subject: Re: [PORTS] Re: Notes on Postgresql 7.0 on FreeBSD
Date: 2000-02-24 01:32:41
Message-ID: 200002240132.UAA28717@candle.pha.pa.us (view raw or flat)
Thread:
Lists: pgsql-ports
Do these go into the 7.0 tree?


Content-Description: message body text

> Hi,
> 
> I wrote
> 
> > [...] but I don't know
> > whether the latter two are actually a bug in postgres or a bug in the
> > OS or even allowed difference. I'm including my results for reference.
> 
> Looks like I forgot to include the other two files, sorry. Here they are.
> 
> Bye, Rolf

> --
> -- FLOAT8
> --
> CREATE TABLE FLOAT8_TBL(f1 float8);
> INSERT INTO FLOAT8_TBL(f1) VALUES ('0.0');
> INSERT INTO FLOAT8_TBL(f1) VALUES ('1004.30');
> INSERT INTO FLOAT8_TBL(f1) VALUES ('-34.84');
> INSERT INTO FLOAT8_TBL(f1) VALUES ('1.2345678901234e+200');
> INSERT INTO FLOAT8_TBL(f1) VALUES ('1.2345678901234e-200');
> SELECT '' AS five, FLOAT8_TBL.*;
>  five |          f1          
> ------+----------------------
>       |                    0
>       |               1004.3
>       |               -34.84
>       | 1.2345678901234e+200
>       | 1.2345678901234e-200
> (5 rows)
> 
> SELECT '' AS four, f.* FROM FLOAT8_TBL f WHERE f.f1 <> '1004.3';
>  four |          f1          
> ------+----------------------
>       |                    0
>       |               -34.84
>       | 1.2345678901234e+200
>       | 1.2345678901234e-200
> (4 rows)
> 
> SELECT '' AS one, f.* FROM FLOAT8_TBL f WHERE f.f1 = '1004.3';
>  one |   f1   
> -----+--------
>      | 1004.3
> (1 row)
> 
> SELECT '' AS three, f.* FROM FLOAT8_TBL f WHERE '1004.3' > f.f1;
>  three |          f1          
> -------+----------------------
>        |                    0
>        |               -34.84
>        | 1.2345678901234e-200
> (3 rows)
> 
> SELECT '' AS three, f.* FROM FLOAT8_TBL f WHERE  f.f1 < '1004.3';
>  three |          f1          
> -------+----------------------
>        |                    0
>        |               -34.84
>        | 1.2345678901234e-200
> (3 rows)
> 
> SELECT '' AS four, f.* FROM FLOAT8_TBL f WHERE '1004.3' >= f.f1;
>  four |          f1          
> ------+----------------------
>       |                    0
>       |               1004.3
>       |               -34.84
>       | 1.2345678901234e-200
> (4 rows)
> 
> SELECT '' AS four, f.* FROM FLOAT8_TBL f WHERE  f.f1 <= '1004.3';
>  four |          f1          
> ------+----------------------
>       |                    0
>       |               1004.3
>       |               -34.84
>       | 1.2345678901234e-200
> (4 rows)
> 
> SELECT '' AS three, f.f1, f.f1 * '-10' AS x 
>    FROM FLOAT8_TBL f
>    WHERE f.f1 > '0.0';
>  three |          f1          |           x           
> -------+----------------------+-----------------------
>        |               1004.3 |                -10043
>        | 1.2345678901234e+200 | -1.2345678901234e+201
>        | 1.2345678901234e-200 | -1.2345678901234e-199
> (3 rows)
> 
> SELECT '' AS three, f.f1, f.f1 + '-10' AS x
>    FROM FLOAT8_TBL f
>    WHERE f.f1 > '0.0';
>  three |          f1          |          x           
> -------+----------------------+----------------------
>        |               1004.3 |                994.3
>        | 1.2345678901234e+200 | 1.2345678901234e+200
>        | 1.2345678901234e-200 |                  -10
> (3 rows)
> 
> SELECT '' AS three, f.f1, f.f1 / '-10' AS x
>    FROM FLOAT8_TBL f
>    WHERE f.f1 > '0.0';
>  three |          f1          |           x           
> -------+----------------------+-----------------------
>        |               1004.3 |               -100.43
>        | 1.2345678901234e+200 | -1.2345678901234e+199
>        | 1.2345678901234e-200 | -1.2345678901234e-201
> (3 rows)
> 
> SELECT '' AS three, f.f1, f.f1 - '-10' AS x
>    FROM FLOAT8_TBL f
>    WHERE f.f1 > '0.0';
>  three |          f1          |          x           
> -------+----------------------+----------------------
>        |               1004.3 |               1014.3
>        | 1.2345678901234e+200 | 1.2345678901234e+200
>        | 1.2345678901234e-200 |                   10
> (3 rows)
> 
> SELECT '' AS one, f.f1 ^ '2.0' AS square_f1
>    FROM FLOAT8_TBL f where f.f1 = '1004.3';
>  one | square_f1  
> -----+------------
>      | 1008618.49
> (1 row)
> 
> -- absolute value 
> SELECT '' AS five, f.f1, @f.f1 AS abs_f1 
>    FROM FLOAT8_TBL f;
>  five |          f1          |        abs_f1        
> ------+----------------------+----------------------
>       |                    0 |                    0
>       |               1004.3 |               1004.3
>       |               -34.84 |                34.84
>       | 1.2345678901234e+200 | 1.2345678901234e+200
>       | 1.2345678901234e-200 | 1.2345678901234e-200
> (5 rows)
> 
> -- truncate 
> SELECT '' AS five, f.f1, %f.f1 AS trunc_f1
>    FROM FLOAT8_TBL f;
>  five |          f1          |       trunc_f1       
> ------+----------------------+----------------------
>       |                    0 |                    0
>       |               1004.3 |                 1004
>       |               -34.84 |                  -34
>       | 1.2345678901234e+200 | 1.2345678901234e+200
>       | 1.2345678901234e-200 |                    0
> (5 rows)
> 
> -- round 
> SELECT '' AS five, f.f1, f.f1 % AS round_f1
>    FROM FLOAT8_TBL f;
>  five |          f1          |       round_f1       
> ------+----------------------+----------------------
>       |                    0 |                    0
>       |               1004.3 |                 1004
>       |               -34.84 |                  -35
>       | 1.2345678901234e+200 | 1.2345678901234e+200
>       | 1.2345678901234e-200 |                    0
> (5 rows)
> 
> -- square root 
> SELECT '' AS three, f.f1, |/f.f1 AS sqrt_f1
>    FROM FLOAT8_TBL f
>    WHERE f.f1 > '0.0';
>  three |          f1          |        sqrt_f1        
> -------+----------------------+-----------------------
>        |               1004.3 |      31.6906926399535
>        | 1.2345678901234e+200 | 1.11111110611109e+100
>        | 1.2345678901234e-200 | 1.11111110611109e-100
> (3 rows)
> 
> -- take exp of ln(f.f1) 
> SELECT '' AS three, f.f1, : ( ; f.f1) AS exp_ln_f1
>    FROM FLOAT8_TBL f
>    WHERE f.f1 > '0.0';
>  three |          f1          |       exp_ln_f1       
> -------+----------------------+-----------------------
>        |               1004.3 |                1004.3
>        | 1.2345678901234e+200 | 1.23456789012338e+200
>        | 1.2345678901234e-200 | 1.23456789012339e-200
> (3 rows)
> 
> -- cube root 
> SELECT '' AS five, f.f1, ||/f.f1 AS cbrt_f1 FROM FLOAT8_TBL f;
>  five |          f1          |       cbrt_f1        
> ------+----------------------+----------------------
>       |                    0 |                    0
>       |               1004.3 |      10.014312837827
>       |               -34.84 |    -3.26607421344208
>       | 1.2345678901234e+200 | 4.97933859234765e+66
>       | 1.2345678901234e-200 |  2.3112042409018e-67
> (5 rows)
> 
> SELECT '' AS five, FLOAT8_TBL.*;
>  five |          f1          
> ------+----------------------
>       |                    0
>       |               1004.3
>       |               -34.84
>       | 1.2345678901234e+200
>       | 1.2345678901234e-200
> (5 rows)
> 
> UPDATE FLOAT8_TBL
>    SET f1 = FLOAT8_TBL.f1 * '-1'
>    WHERE FLOAT8_TBL.f1 > '0.0';
> SELECT '' AS bad, f.f1 * '1e200' from FLOAT8_TBL f;
> ERROR:  floating point exception! The last floating point operation either exceeded legal ranges or was a divide by zero
> SELECT '' AS bad, f.f1 ^ '1e200' from FLOAT8_TBL f;
> ERROR:  pow() result is out of range
> SELECT '' AS bad, (; (f.f1)) from FLOAT8_TBL f where f.f1 = '0.0' ;
> ERROR:  can't take log of zero
> SELECT '' AS bad, (; (f.f1)) from FLOAT8_TBL f where f.f1 < '0.0' ;
> ERROR:  can't take log of a negative number
> SELECT '' AS bad, : (f.f1) from FLOAT8_TBL f;
> ERROR:  exp() result is out of range
> SELECT '' AS bad, f.f1 / '0.0' from FLOAT8_TBL f;
> ERROR:  float8div: divide by zero error
> SELECT '' AS five, FLOAT8_TBL.*;
>  five |          f1           
> ------+-----------------------
>       |                     0
>       |                -34.84
>       |               -1004.3
>       | -1.2345678901234e+200
>       | -1.2345678901234e-200
> (5 rows)
> 
> -- test for over and under flow 
> INSERT INTO FLOAT8_TBL(f1) VALUES ('10e400');
> ERROR:  Input '10e400' is out of range for float8
> INSERT INTO FLOAT8_TBL(f1) VALUES ('-10e400');
> ERROR:  Input '-10e400' is out of range for float8
> INSERT INTO FLOAT8_TBL(f1) VALUES ('10e-400');
> INSERT INTO FLOAT8_TBL(f1) VALUES ('-10e-400');
> -- maintain external table consistency across platforms
> -- delete all values and reinsert well-behaved ones
> DELETE FROM FLOAT8_TBL;
> INSERT INTO FLOAT8_TBL(f1) VALUES ('0.0');
> INSERT INTO FLOAT8_TBL(f1) VALUES ('-34.84');
> INSERT INTO FLOAT8_TBL(f1) VALUES ('-1004.30');
> INSERT INTO FLOAT8_TBL(f1) VALUES ('-1.2345678901234e+200');
> INSERT INTO FLOAT8_TBL(f1) VALUES ('-1.2345678901234e-200');
> SELECT '' AS five, FLOAT8_TBL.*;
>  five |          f1           
> ------+-----------------------
>       |                     0
>       |                -34.84
>       |               -1004.3
>       | -1.2345678901234e+200
>       | -1.2345678901234e-200
> (5 rows)
> 

> --
> -- GEOMETRY
> --
> --
> -- Points
> --
> SELECT '' AS four, center(f1) AS center
>    FROM BOX_TBL;
>  four | center  
> ------+---------
>       | (1,1)
>       | (2,2)
>       | (2.5,3)
>       | (3,3)
> (4 rows)
> 
> SELECT '' AS four, (@@ f1) AS center
>    FROM BOX_TBL;
>  four | center  
> ------+---------
>       | (1,1)
>       | (2,2)
>       | (2.5,3)
>       | (3,3)
> (4 rows)
> 
> SELECT '' AS six, point(f1) AS center
>    FROM CIRCLE_TBL;
>  six |  center   
> -----+-----------
>      | (0,0)
>      | (1,2)
>      | (1,3)
>      | (1,2)
>      | (100,200)
>      | (100,0)
> (6 rows)
> 
> SELECT '' AS six, (@@ f1) AS center
>    FROM CIRCLE_TBL;
>  six |  center   
> -----+-----------
>      | (0,0)
>      | (1,2)
>      | (1,3)
>      | (1,2)
>      | (100,200)
>      | (100,0)
> (6 rows)
> 
> SELECT '' AS two, (@@ f1) AS center
>    FROM POLYGON_TBL
>    WHERE (# f1) > 2;
>  two |               center                
> -----+-------------------------------------
>      | (1.33333333333333,1.33333333333333)
>      | (2.33333333333333,1.33333333333333)
> (2 rows)
> 
> -- "is horizontal" function
> SELECT '' AS two, p1.f1
>    FROM POINT_TBL p1
>    WHERE ishorizontal(p1.f1, point '(0,0)');
>  two |   f1    
> -----+---------
>      | (0,0)
>      | (-10,0)
> (2 rows)
> 
> -- "is horizontal" operator
> SELECT '' AS two, p1.f1
>    FROM POINT_TBL p1
>    WHERE p1.f1 ?- point '(0,0)';
>  two |   f1    
> -----+---------
>      | (0,0)
>      | (-10,0)
> (2 rows)
> 
> -- "is vertical" function
> SELECT '' AS one, p1.f1
>    FROM POINT_TBL p1
>    WHERE isvertical(p1.f1, point '(5.1,34.5)');
>  one |     f1     
> -----+------------
>      | (5.1,34.5)
> (1 row)
> 
> -- "is vertical" operator
> SELECT '' AS one, p1.f1
>    FROM POINT_TBL p1
>    WHERE p1.f1 ?| point '(5.1,34.5)';
>  one |     f1     
> -----+------------
>      | (5.1,34.5)
> (1 row)
> 
> --
> -- Line segments
> --
> -- intersection
> SELECT '' AS count, p.f1, l.s, l.s # p.f1 AS intersection
>    FROM LSEG_TBL l, POINT_TBL p;
> ERROR:  Unable to identify an operator '#' for types 'lseg' and 'point'
> 	You will have to retype this query using an explicit cast
> -- closest point
> SELECT '' AS thirty, p.f1, l.s, p.f1 ## l.s AS closest
>    FROM LSEG_TBL l, POINT_TBL p;
>  thirty |     f1     |               s               |                closest                
> --------+------------+-------------------------------+---------------------------------------
>         | (0,0)      | [(1,2),(3,4)]                 | (1,2)
>         | (-10,0)    | [(1,2),(3,4)]                 | (1,2)
>         | (-3,4)     | [(1,2),(3,4)]                 | (1,2)
>         | (5.1,34.5) | [(1,2),(3,4)]                 | (3,4)
>         | (-5,-12)   | [(1,2),(3,4)]                 | (1,2)
>         | (10,10)    | [(1,2),(3,4)]                 | (3,4)
>         | (0,0)      | [(0,0),(6,6)]                 | (0,0)
>         | (-10,0)    | [(0,0),(6,6)]                 | (0,0)
>         | (-3,4)     | [(0,0),(6,6)]                 | (0.5,0.5)
>         | (5.1,34.5) | [(0,0),(6,6)]                 | (6,6)
>         | (-5,-12)   | [(0,0),(6,6)]                 | (0,0)
>         | (10,10)    | [(0,0),(6,6)]                 | (6,6)
>         | (0,0)      | [(10,-10),(-3,-4)]            | (-2.04878048780488,-4.4390243902439)
>         | (-10,0)    | [(10,-10),(-3,-4)]            | (-3,-4)
>         | (-3,4)     | [(10,-10),(-3,-4)]            | (-3,-4)
>         | (5.1,34.5) | [(10,-10),(-3,-4)]            | (-3,-4)
>         | (-5,-12)   | [(10,-10),(-3,-4)]            | (-1.60487804878049,-4.64390243902439)
>         | (10,10)    | [(10,-10),(-3,-4)]            | (2.39024390243902,-6.48780487804878)
>         | (0,0)      | [(-1000000,200),(300000,-40)] | (0.0028402365895872,15.384614860264)
>         | (-10,0)    | [(-1000000,200),(300000,-40)] | (-9.99715942258202,15.3864610140472)
>         | (-3,4)     | [(-1000000,200),(300000,-40)] | (-2.99789812267519,15.3851688427303)
>         | (5.1,34.5) | [(-1000000,200),(300000,-40)] | (5.09647083221496,15.3836744976925)
>         | (-5,-12)   | [(-1000000,200),(300000,-40)] | (-4.99494420845634,15.3855375281616)
>         | (10,10)    | [(-1000000,200),(300000,-40)] | (10.000993741978,15.3827690473092)
>         | (0,0)      | [(11,22),(33,44)]             | (11,22)
>         | (-10,0)    | [(11,22),(33,44)]             | (11,22)
>         | (-3,4)     | [(11,22),(33,44)]             | (11,22)
>         | (5.1,34.5) | [(11,22),(33,44)]             | (14.3,25.3)
>         | (-5,-12)   | [(11,22),(33,44)]             | (11,22)
>         | (10,10)    | [(11,22),(33,44)]             | (11,22)
> (30 rows)
> 
> --
> -- Lines
> --
> --
> -- Boxes
> --
> SELECT '' as six, box(f1) AS box FROM CIRCLE_TBL;
>  six |                                    box                                     
> -----+----------------------------------------------------------------------------
>      | (2.12132034355964,2.12132034355964),(-2.12132034355964,-2.12132034355964)
>      | (71.7106781186547,72.7106781186547),(-69.7106781186547,-68.7106781186547)
>      | (4.53553390593274,6.53553390593274),(-2.53553390593274,-0.535533905932737)
>      | (3.12132034355964,4.12132034355964),(-1.12132034355964,-0.121320343559642)
>      | (107.071067811865,207.071067811865),(92.9289321881345,192.928932188135)
>      | (170.710678118655,70.7106781186547),(29.2893218813453,-70.7106781186547)
> (6 rows)
> 
> -- translation
> SELECT '' AS twentyfour, b.f1 + p.f1 AS translation
>    FROM BOX_TBL b, POINT_TBL p;
>  twentyfour |       translation       
> ------------+-------------------------
>             | (2,2),(0,0)
>             | (3,3),(1,1)
>             | (2.5,3.5),(2.5,2.5)
>             | (3,3),(3,3)
>             | (-8,2),(-10,0)
>             | (-7,3),(-9,1)
>             | (-7.5,3.5),(-7.5,2.5)
>             | (-7,3),(-7,3)
>             | (-1,6),(-3,4)
>             | (0,7),(-2,5)
>             | (-0.5,7.5),(-0.5,6.5)
>             | (0,7),(0,7)
>             | (7.1,36.5),(5.1,34.5)
>             | (8.1,37.5),(6.1,35.5)
>             | (7.6,38),(7.6,37)
>             | (8.1,37.5),(8.1,37.5)
>             | (-3,-10),(-5,-12)
>             | (-2,-9),(-4,-11)
>             | (-2.5,-8.5),(-2.5,-9.5)
>             | (-2,-9),(-2,-9)
>             | (12,12),(10,10)
>             | (13,13),(11,11)
>             | (12.5,13.5),(12.5,12.5)
>             | (13,13),(13,13)
> (24 rows)
> 
> SELECT '' AS twentyfour, b.f1 - p.f1 AS translation
>    FROM BOX_TBL b, POINT_TBL p;
>  twentyfour |        translation        
> ------------+---------------------------
>             | (2,2),(0,0)
>             | (3,3),(1,1)
>             | (2.5,3.5),(2.5,2.5)
>             | (3,3),(3,3)
>             | (12,2),(10,0)
>             | (13,3),(11,1)
>             | (12.5,3.5),(12.5,2.5)
>             | (13,3),(13,3)
>             | (5,-2),(3,-4)
>             | (6,-1),(4,-3)
>             | (5.5,-0.5),(5.5,-1.5)
>             | (6,-1),(6,-1)
>             | (-3.1,-32.5),(-5.1,-34.5)
>             | (-2.1,-31.5),(-4.1,-33.5)
>             | (-2.6,-31),(-2.6,-32)
>             | (-2.1,-31.5),(-2.1,-31.5)
>             | (7,14),(5,12)
>             | (8,15),(6,13)
>             | (7.5,15.5),(7.5,14.5)
>             | (8,15),(8,15)
>             | (-8,-8),(-10,-10)
>             | (-7,-7),(-9,-9)
>             | (-7.5,-6.5),(-7.5,-7.5)
>             | (-7,-7),(-7,-7)
> (24 rows)
> 
> -- scaling and rotation
> SELECT '' AS twentyfour, b.f1 * p.f1 AS rotation
>    FROM BOX_TBL b, POINT_TBL p;
>  twentyfour |          rotation           
> ------------+-----------------------------
>             | (0,0),(0,0)
>             | (0,0),(0,0)
>             | (0,0),(0,0)
>             | (0,0),(0,0)
>             | (0,0),(-20,-20)
>             | (-10,-10),(-30,-30)
>             | (-25,-25),(-25,-35)
>             | (-30,-30),(-30,-30)
>             | (0,2),(-14,0)
>             | (-7,3),(-21,1)
>             | (-17.5,2.5),(-21.5,-0.5)
>             | (-21,3),(-21,3)
>             | (0,79.2),(-58.8,0)
>             | (-29.4,118.8),(-88.2,39.6)
>             | (-73.5,104.1),(-108,99)
>             | (-88.2,118.8),(-88.2,118.8)
>             | (14,0),(0,-34)
>             | (21,-17),(7,-51)
>             | (29.5,-42.5),(17.5,-47.5)
>             | (21,-51),(21,-51)
>             | (0,40),(0,0)
>             | (0,60),(0,20)
>             | (0,60),(-10,50)
>             | (0,60),(0,60)
> (24 rows)
> 
> SELECT '' AS twenty, b.f1 / p.f1 AS rotation
>    FROM BOX_TBL b, POINT_TBL p
>    WHERE (p.f1 <-> point '(0,0)') >= 1;
>  twenty |                                     rotation                                      
> --------+-----------------------------------------------------------------------------------
>         | (0,0),(-0.2,-0.2)
>         | (-0.1,-0.1),(-0.3,-0.3)
>         | (-0.25,-0.25),(-0.25,-0.35)
>         | (-0.3,-0.3),(-0.3,-0.3)
>         | (0.08,0),(0,-0.56)
>         | (0.12,-0.28),(0.04,-0.84)
>         | (0.26,-0.7),(0.1,-0.82)
>         | (0.12,-0.84),(0.12,-0.84)
>         | (0.0651176557643925,0),(0,-0.0483449262493217)
>         | (0.0976764836465887,-0.0241724631246608),(0.0325588278821962,-0.0725173893739825)
>         | (0.109762715208919,-0.0562379754328844),(0.0813970697054906,-0.0604311578116521)
>         | (0.0976764836465887,-0.0725173893739825),(0.0976764836465887,-0.0725173893739825)
>         | (0,0.0828402366863905),(-0.201183431952663,0)
>         | (-0.100591715976331,0.124260355029586),(-0.301775147928994,0.0414201183431953)
>         | (-0.251479289940828,0.103550295857988),(-0.322485207100592,0.0739644970414201)
>         | (-0.301775147928994,0.124260355029586),(-0.301775147928994,0.124260355029586)
>         | (0.2,0),(0,0)
>         | (0.3,0),(0.1,0)
>         | (0.3,0.05),(0.25,0)
>         | (0.3,0),(0.3,0)
> (20 rows)
> 
> --
> -- Paths
> --
> SET geqo TO 'off';
> SELECT '' AS eight, points(f1) AS npoints, f1 AS path FROM PATH_TBL;
>  eight | npoints |           path            
> -------+---------+---------------------------
>        |       2 | [(1,2),(3,4)]
>        |       2 | ((1,2),(3,4))
>        |       4 | [(0,0),(3,0),(4,5),(1,6)]
>        |       2 | ((1,2),(3,4))
>        |       2 | ((1,2),(3,4))
>        |       2 | [(1,2),(3,4)]
>        |       2 | [(11,12),(13,14)]
>        |       2 | ((11,12),(13,14))
> (8 rows)
> 
> SELECT '' AS four, path(f1) FROM POLYGON_TBL;
>  four |        path         
> ------+---------------------
>       | ((2,0),(2,4),(0,0))
>       | ((3,1),(3,3),(1,0))
>       | ((0,0))
>       | ((0,1),(0,1))
> (4 rows)
> 
> -- translation
> SELECT '' AS eight, p1.f1 + point '(10,10)' AS dist_add
>    FROM PATH_TBL p1;
>  eight |             dist_add              
> -------+-----------------------------------
>        | [(11,12),(13,14)]
>        | ((11,12),(13,14))
>        | [(10,10),(13,10),(14,15),(11,16)]
>        | ((11,12),(13,14))
>        | ((11,12),(13,14))
>        | [(11,12),(13,14)]
>        | [(21,22),(23,24)]
>        | ((21,22),(23,24))
> (8 rows)
> 
> -- scaling and rotation
> SELECT '' AS eight, p1.f1 * point '(2,-1)' AS dist_mul
>    FROM PATH_TBL p1;
>  eight |           dist_mul           
> -------+------------------------------
>        | [(4,3),(10,5)]
>        | ((4,3),(10,5))
>        | [(0,0),(6,-3),(13,6),(8,11)]
>        | ((4,3),(10,5))
>        | ((4,3),(10,5))
>        | [(4,3),(10,5)]
>        | [(34,13),(40,15)]
>        | ((34,13),(40,15))
> (8 rows)
> 
> RESET geqo;
> --
> -- Polygons
> --
> -- containment
> SELECT '' AS twentyfour, p.f1, poly.f1, poly.f1 ~ p.f1 AS contains
>    FROM POLYGON_TBL poly, POINT_TBL p;
>  twentyfour |     f1     |         f1          | contains 
> ------------+------------+---------------------+----------
>             | (0,0)      | ((2,0),(2,4),(0,0)) | t
>             | (-10,0)    | ((2,0),(2,4),(0,0)) | f
>             | (-3,4)     | ((2,0),(2,4),(0,0)) | f
>             | (5.1,34.5) | ((2,0),(2,4),(0,0)) | f
>             | (-5,-12)   | ((2,0),(2,4),(0,0)) | f
>             | (10,10)    | ((2,0),(2,4),(0,0)) | f
>             | (0,0)      | ((3,1),(3,3),(1,0)) | f
>             | (-10,0)    | ((3,1),(3,3),(1,0)) | f
>             | (-3,4)     | ((3,1),(3,3),(1,0)) | f
>             | (5.1,34.5) | ((3,1),(3,3),(1,0)) | f
>             | (-5,-12)   | ((3,1),(3,3),(1,0)) | f
>             | (10,10)    | ((3,1),(3,3),(1,0)) | f
>             | (0,0)      | ((0,0))             | t
>             | (-10,0)    | ((0,0))             | f
>             | (-3,4)     | ((0,0))             | f
>             | (5.1,34.5) | ((0,0))             | f
>             | (-5,-12)   | ((0,0))             | f
>             | (10,10)    | ((0,0))             | f
>             | (0,0)      | ((0,1),(0,1))       | f
>             | (-10,0)    | ((0,1),(0,1))       | f
>             | (-3,4)     | ((0,1),(0,1))       | f
>             | (5.1,34.5) | ((0,1),(0,1))       | f
>             | (-5,-12)   | ((0,1),(0,1))       | f
>             | (10,10)    | ((0,1),(0,1))       | f
> (24 rows)
> 
> SELECT '' AS twentyfour, p.f1, poly.f1, p.f1 @ poly.f1 AS contained
>    FROM POLYGON_TBL poly, POINT_TBL p;
>  twentyfour |     f1     |         f1          | contained 
> ------------+------------+---------------------+-----------
>             | (0,0)      | ((2,0),(2,4),(0,0)) | t
>             | (-10,0)    | ((2,0),(2,4),(0,0)) | f
>             | (-3,4)     | ((2,0),(2,4),(0,0)) | f
>             | (5.1,34.5) | ((2,0),(2,4),(0,0)) | f
>             | (-5,-12)   | ((2,0),(2,4),(0,0)) | f
>             | (10,10)    | ((2,0),(2,4),(0,0)) | f
>             | (0,0)      | ((3,1),(3,3),(1,0)) | f
>             | (-10,0)    | ((3,1),(3,3),(1,0)) | f
>             | (-3,4)     | ((3,1),(3,3),(1,0)) | f
>             | (5.1,34.5) | ((3,1),(3,3),(1,0)) | f
>             | (-5,-12)   | ((3,1),(3,3),(1,0)) | f
>             | (10,10)    | ((3,1),(3,3),(1,0)) | f
>             | (0,0)      | ((0,0))             | t
>             | (-10,0)    | ((0,0))             | f
>             | (-3,4)     | ((0,0))             | f
>             | (5.1,34.5) | ((0,0))             | f
>             | (-5,-12)   | ((0,0))             | f
>             | (10,10)    | ((0,0))             | f
>             | (0,0)      | ((0,1),(0,1))       | f
>             | (-10,0)    | ((0,1),(0,1))       | f
>             | (-3,4)     | ((0,1),(0,1))       | f
>             | (5.1,34.5) | ((0,1),(0,1))       | f
>             | (-5,-12)   | ((0,1),(0,1))       | f
>             | (10,10)    | ((0,1),(0,1))       | f
> (24 rows)
> 
> SELECT '' AS four, points(f1) AS npoints, f1 AS polygon
>    FROM POLYGON_TBL;
>  four | npoints |       polygon       
> ------+---------+---------------------
>       |       3 | ((2,0),(2,4),(0,0))
>       |       3 | ((3,1),(3,3),(1,0))
>       |       1 | ((0,0))
>       |       2 | ((0,1),(0,1))
> (4 rows)
> 
> SELECT '' AS four, polygon(f1)
>    FROM BOX_TBL;
>  four |                  polygon                  
> ------+-------------------------------------------
>       | ((0,0),(0,2),(2,2),(2,0))
>       | ((1,1),(1,3),(3,3),(3,1))
>       | ((2.5,2.5),(2.5,3.5),(2.5,3.5),(2.5,2.5))
>       | ((3,3),(3,3),(3,3),(3,3))
> (4 rows)
> 
> SELECT '' AS four, polygon(f1)
>    FROM PATH_TBL WHERE isclosed(f1);
>  four |      polygon      
> ------+-------------------
>       | ((1,2),(3,4))
>       | ((1,2),(3,4))
>       | ((1,2),(3,4))
>       | ((11,12),(13,14))
> (4 rows)
> 
> SELECT '' AS four, f1 AS open_path, polygon( pclose(f1)) AS polygon
>    FROM PATH_TBL
>    WHERE isopen(f1);
>  four |         open_path         |          polygon          
> ------+---------------------------+---------------------------
>       | [(1,2),(3,4)]             | ((1,2),(3,4))
>       | [(0,0),(3,0),(4,5),(1,6)] | ((0,0),(3,0),(4,5),(1,6))
>       | [(1,2),(3,4)]             | ((1,2),(3,4))
>       | [(11,12),(13,14)]         | ((11,12),(13,14))
> (4 rows)
> 
> -- convert circles to polygons using the default number of points
> SELECT '' AS six, polygon(f1)
>    FROM CIRCLE_TBL;
>  six |                                                                                                                                                                                           polygon                                                                                                                                                                                           
> -----+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
>      | ((-3,0),(-2.59807621135076,1.50000000000442),(-1.49999999999116,2.59807621135842),(1.53102359078377e-11,3),(1.50000000001768,2.59807621134311),(2.59807621136607,1.4999999999779),(3,-3.06204718156754e-11),(2.59807621133545,-1.50000000003094),(1.49999999996464,-2.59807621137373),(-4.59307077235131e-11,-3),(-1.5000000000442,-2.5980762113278),(-2.59807621138138,-1.49999999995138))
>      | ((-99,2),(-85.6025403783588,52.0000000001473),(-48.9999999997054,88.602540378614),(1.00000000051034,102),(51.0000000005893,88.6025403781036),(87.6025403788692,51.9999999992634),(101,1.99999999897932),(87.6025403778485,-48.0000000010313),(50.9999999988214,-84.6025403791243),(0.999999998468976,-98),(-49.0000000014732,-84.6025403775933),(-85.6025403793795,-47.9999999983795))
>      | ((-4,3),(-3.33012701891794,5.50000000000737),(-1.49999999998527,7.3301270189307),(1.00000000002552,8),(3.50000000002946,7.33012701890518),(5.33012701894346,5.49999999996317),(6,2.99999999994897),(5.33012701889242,0.499999999948437),(3.49999999994107,-1.33012701895622),(0.999999999923449,-2),(-1.50000000007366,-1.33012701887967),(-3.33012701896897,0.500000000081028))
>      | ((-2,2),(-1.59807621135076,3.50000000000442),(-0.499999999991161,4.59807621135842),(1.00000000001531,5),(2.50000000001768,4.59807621134311),(3.59807621136607,3.4999999999779),(4,1.99999999996938),(3.59807621133545,0.499999999969062),(2.49999999996464,-0.598076211373729),(0.999999999954069,-1),(-0.500000000044197,-0.598076211327799),(-1.59807621138138,0.500000000048616))
>      | ((90,200),(91.3397459621641,205.000000000015),(95.0000000000295,208.660254037861),(100.000000000051,210),(105.000000000059,208.66025403781),(108.660254037887,204.999999999926),(110,199.999999999898),(108.660254037785,194.999999999897),(104.999999999882,191.339745962088),(99.9999999998469,190),(94.9999999998527,191.339745962241),(91.3397459620621,195.000000000162))
>      | ((0,0),(13.3974596216412,50.0000000001473),(50.0000000002946,86.602540378614),(100.00000000051,100),(150.000000000589,86.6025403781036),(186.602540378869,49.9999999992634),(200,-1.02068239385585e-09),(186.602540377848,-50.0000000010313),(149.999999998821,-86.6025403791243),(99.999999998469,-100),(49.9999999985268,-86.6025403775933),(13.3974596206205,-49.9999999983795))
> (6 rows)
> 
> -- convert the circle to an 8-point polygon
> SELECT '' AS six, polygon(8, f1)
>    FROM CIRCLE_TBL;
>  six |                                                                                                                  polygon                                                                                                                  
> -----+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
>      | ((-3,0),(-2.12132034355423,2.12132034356506),(1.53102359078377e-11,3),(2.12132034357588,2.1213203435434),(3,-3.06204718156754e-11),(2.12132034353258,-2.12132034358671),(-4.59307077235131e-11,-3),(-2.12132034359753,-2.12132034352175))
>      | ((-99,2),(-69.7106781184743,72.7106781188352),(1.00000000051034,102),(71.710678119196,72.7106781181134),(101,1.99999999897932),(71.7106781177526,-68.7106781195569),(0.999999998468976,-98),(-69.7106781199178,-68.7106781173917))
>      | ((-4,3),(-2.53553390592372,6.53553390594176),(1.00000000002552,8),(4.5355339059598,6.53553390590567),(6,2.99999999994897),(4.53553390588763,-0.535533905977846),(0.999999999923449,-2),(-2.53553390599589,-0.535533905869586))
>      | ((-2,2),(-1.12132034355423,4.12132034356506),(1.00000000001531,5),(3.12132034357588,4.1213203435434),(4,1.99999999996938),(3.12132034353258,-0.121320343586707),(0.999999999954069,-1),(-1.12132034359753,-0.121320343521752))
>      | ((90,200),(92.9289321881526,207.071067811884),(100.000000000051,210),(107.07106781192,207.071067811811),(110,199.999999999898),(107.071067811775,192.928932188044),(99.9999999998469,190),(92.9289321880082,192.928932188261))
>      | ((0,0),(29.2893218815257,70.7106781188352),(100.00000000051,100),(170.710678119196,70.7106781181134),(200,-1.02068239385585e-09),(170.710678117753,-70.7106781195569),(99.999999998469,-100),(29.2893218800822,-70.7106781173917))
> (6 rows)
> 
> --
> -- Circles
> --
> SELECT '' AS six, circle(f1, 50.0)
>    FROM POINT_TBL;
>  six |     circle      
> -----+-----------------
>      | <(0,0),50>
>      | <(-10,0),50>
>      | <(-3,4),50>
>      | <(5.1,34.5),50>
>      | <(-5,-12),50>
>      | <(10,10),50>
> (6 rows)
> 
> SELECT '' AS four, circle(f1)
>    FROM BOX_TBL;
>  four |         circle          
> ------+-------------------------
>       | <(1,1),1.4142135623731>
>       | <(2,2),1.4142135623731>
>       | <(2.5,3),0.5>
>       | <(3,3),0>
> (4 rows)
> 
> SELECT '' AS two, circle(f1)
>    FROM POLYGON_TBL
>    WHERE (# f1) >= 3;
>  two |                         circle                         
> -----+--------------------------------------------------------
>      | <(1.33333333333333,1.33333333333333),2.04168905063636>
>      | <(2.33333333333333,1.33333333333333),1.47534300379185>
> (2 rows)
> 
> SELECT '' AS twentyfour, c1.f1 AS circle, p1.f1 AS point, (p1.f1 <-> c1.f1) AS distance
>    FROM CIRCLE_TBL c1, POINT_TBL p1
>    WHERE (p1.f1 <-> c1.f1) > 0
>    ORDER BY distance, circle, point using <<;
>  twentyfour |     circle     |   point    |     distance     
> ------------+----------------+------------+------------------
>             | <(100,0),100>  | (5.1,34.5) | 0.97653192697797
>             | <(1,2),3>      | (-3,4)     | 1.47213595499958
>             | <(0,0),3>      | (-3,4)     |                2
>             | <(100,0),100>  | (-3,4)     | 3.07764064044152
>             | <(100,0),100>  | (-5,-12)   | 5.68348972285122
>             | <(1,3),5>      | (-10,0)    | 6.40175425099138
>             | <(1,3),5>      | (10,10)    | 6.40175425099138
>             | <(0,0),3>      | (-10,0)    |                7
>             | <(1,2),3>      | (-10,0)    | 8.18033988749895
>             | <(1,2),3>      | (10,10)    |  9.0415945787923
>             | <(0,0),3>      | (-5,-12)   |               10
>             | <(100,0),100>  | (-10,0)    |               10
>             | <(0,0),3>      | (10,10)    |  11.142135623731
>             | <(1,3),5>      | (-5,-12)   | 11.1554944214035
>             | <(1,2),3>      | (-5,-12)   | 12.2315462117278
>             | <(1,3),5>      | (5.1,34.5) | 26.7657047773223
>             | <(1,2),3>      | (5.1,34.5) |  29.757594539282
>             | <(0,0),3>      | (5.1,34.5) | 31.8749193547455
>             | <(100,200),10> | (5.1,34.5) | 180.778038568384
>             | <(100,200),10> | (10,10)    | 200.237960416286
>             | <(100,200),10> | (-3,4)     | 211.415898254845
>             | <(100,200),10> | (0,0)      | 213.606797749979
>             | <(100,200),10> | (-10,0)    | 218.254244210267
>             | <(100,200),10> | (-5,-12)   | 226.577682802077
> (24 rows)
> 


-- 
  Bruce Momjian                        |  http://www.op.net/~candle
  pgman(at)candle(dot)pha(dot)pa(dot)us               |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026

In response to

pgsql-ports by date

Next:From: Ralf TrefzDate: 2000-02-25 06:15:07
Subject: unsubscribe
Previous:From: Rolf GrossmannDate: 2000-02-24 00:30:27
Subject: Re: Notes on Postgresql 7.0 on FreeBSD

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group