1-- 2-- GEOMETRY 3-- 4 5-- Back off displayed precision a little bit to reduce platform-to-platform 6-- variation in results. 7SET extra_float_digits TO -3; 8 9-- 10-- Points 11-- 12 13SELECT '' AS four, center(f1) AS center 14 FROM BOX_TBL; 15 16SELECT '' AS four, (@@ f1) AS center 17 FROM BOX_TBL; 18 19SELECT '' AS six, point(f1) AS center 20 FROM CIRCLE_TBL; 21 22SELECT '' AS six, (@@ f1) AS center 23 FROM CIRCLE_TBL; 24 25SELECT '' AS two, (@@ f1) AS center 26 FROM POLYGON_TBL 27 WHERE (# f1) > 2; 28 29-- "is horizontal" function 30SELECT '' AS two, p1.f1 31 FROM POINT_TBL p1 32 WHERE ishorizontal(p1.f1, point '(0,0)'); 33 34-- "is horizontal" operator 35SELECT '' AS two, p1.f1 36 FROM POINT_TBL p1 37 WHERE p1.f1 ?- point '(0,0)'; 38 39-- "is vertical" function 40SELECT '' AS one, p1.f1 41 FROM POINT_TBL p1 42 WHERE isvertical(p1.f1, point '(5.1,34.5)'); 43 44-- "is vertical" operator 45SELECT '' AS one, p1.f1 46 FROM POINT_TBL p1 47 WHERE p1.f1 ?| point '(5.1,34.5)'; 48 49-- Slope 50SELECT p1.f1, p2.f1, slope(p1.f1, p2.f1) FROM POINT_TBL p1, POINT_TBL p2; 51 52-- Add point 53SELECT p1.f1, p2.f1, p1.f1 + p2.f1 FROM POINT_TBL p1, POINT_TBL p2; 54 55-- Subtract point 56SELECT p1.f1, p2.f1, p1.f1 - p2.f1 FROM POINT_TBL p1, POINT_TBL p2; 57 58-- Multiply with point 59SELECT p1.f1, p2.f1, p1.f1 * p2.f1 FROM POINT_TBL p1, POINT_TBL p2 WHERE p1.f1[0] BETWEEN 1 AND 1000; 60 61-- Underflow error 62SELECT p1.f1, p2.f1, p1.f1 * p2.f1 FROM POINT_TBL p1, POINT_TBL p2 WHERE p1.f1[0] < 1; 63 64-- Divide by point 65SELECT p1.f1, p2.f1, p1.f1 / p2.f1 FROM POINT_TBL p1, POINT_TBL p2 WHERE p2.f1[0] BETWEEN 1 AND 1000; 66 67-- Overflow error 68SELECT p1.f1, p2.f1, p1.f1 / p2.f1 FROM POINT_TBL p1, POINT_TBL p2 WHERE p2.f1[0] > 1000; 69 70-- Division by 0 error 71SELECT p1.f1, p2.f1, p1.f1 / p2.f1 FROM POINT_TBL p1, POINT_TBL p2 WHERE p2.f1 ~= '(0,0)'::point; 72 73-- Distance to line 74SELECT p.f1, l.s, p.f1 <-> l.s FROM POINT_TBL p, LINE_TBL l; 75 76-- Distance to line segment 77SELECT p.f1, l.s, p.f1 <-> l.s FROM POINT_TBL p, LSEG_TBL l; 78 79-- Distance to box 80SELECT p.f1, b.f1, p.f1 <-> b.f1 FROM POINT_TBL p, BOX_TBL b; 81 82-- Distance to path 83SELECT p.f1, p1.f1, p.f1 <-> p1.f1 FROM POINT_TBL p, PATH_TBL p1; 84 85-- Distance to polygon 86SELECT p.f1, p1.f1, p.f1 <-> p1.f1 FROM POINT_TBL p, POLYGON_TBL p1; 87 88-- Closest point to line 89SELECT p.f1, l.s, p.f1 ## l.s FROM POINT_TBL p, LINE_TBL l; 90 91-- Closest point to line segment 92SELECT p.f1, l.s, p.f1 ## l.s FROM POINT_TBL p, LSEG_TBL l; 93 94-- Closest point to box 95SELECT p.f1, b.f1, p.f1 ## b.f1 FROM POINT_TBL p, BOX_TBL b; 96 97-- On line 98SELECT p.f1, l.s FROM POINT_TBL p, LINE_TBL l WHERE p.f1 <@ l.s; 99 100-- On line segment 101SELECT p.f1, l.s FROM POINT_TBL p, LSEG_TBL l WHERE p.f1 <@ l.s; 102 103-- On path 104SELECT p.f1, p1.f1 FROM POINT_TBL p, PATH_TBL p1 WHERE p.f1 <@ p1.f1; 105 106-- 107-- Lines 108-- 109 110-- Vertical 111SELECT s FROM LINE_TBL WHERE ?| s; 112 113-- Horizontal 114SELECT s FROM LINE_TBL WHERE ?- s; 115 116-- Same as line 117SELECT l1.s, l2.s FROM LINE_TBL l1, LINE_TBL l2 WHERE l1.s = l2.s; 118 119-- Parallel to line 120SELECT l1.s, l2.s FROM LINE_TBL l1, LINE_TBL l2 WHERE l1.s ?|| l2.s; 121 122-- Perpendicular to line 123SELECT l1.s, l2.s FROM LINE_TBL l1, LINE_TBL l2 WHERE l1.s ?-| l2.s; 124 125-- Distance to line 126SELECT l1.s, l2.s, l1.s <-> l2.s FROM LINE_TBL l1, LINE_TBL l2; 127 128-- Distance to box 129SELECT l.s, b.f1, l.s <-> b.f1 FROM LINE_TBL l, BOX_TBL b; 130 131-- Intersect with line 132SELECT l1.s, l2.s FROM LINE_TBL l1, LINE_TBL l2 WHERE l1.s ?# l2.s; 133 134-- Intersect with box 135SELECT l.s, b.f1 FROM LINE_TBL l, BOX_TBL b WHERE l.s ?# b.f1; 136 137-- Intersection point with line 138SELECT l1.s, l2.s, l1.s # l2.s FROM LINE_TBL l1, LINE_TBL l2; 139 140-- Closest point to line segment 141SELECT l.s, l1.s, l.s ## l1.s FROM LINE_TBL l, LSEG_TBL l1; 142 143-- Closest point to box 144SELECT l.s, b.f1, l.s ## b.f1 FROM LINE_TBL l, BOX_TBL b; 145 146-- 147-- Line segments 148-- 149 150-- intersection 151SELECT '' AS count, p.f1, l.s, l.s # p.f1 AS intersection 152 FROM LSEG_TBL l, POINT_TBL p; 153 154-- Length 155SELECT s, @-@ s FROM LSEG_TBL; 156 157-- Vertical 158SELECT s FROM LSEG_TBL WHERE ?| s; 159 160-- Horizontal 161SELECT s FROM LSEG_TBL WHERE ?- s; 162 163-- Center 164SELECT s, @@ s FROM LSEG_TBL; 165 166-- To point 167SELECT s, s::point FROM LSEG_TBL; 168 169-- Has points less than line segment 170SELECT l1.s, l2.s FROM LSEG_TBL l1, LSEG_TBL l2 WHERE l1.s < l2.s; 171 172-- Has points less than or equal to line segment 173SELECT l1.s, l2.s FROM LSEG_TBL l1, LSEG_TBL l2 WHERE l1.s <= l2.s; 174 175-- Has points equal to line segment 176SELECT l1.s, l2.s FROM LSEG_TBL l1, LSEG_TBL l2 WHERE l1.s = l2.s; 177 178-- Has points greater than or equal to line segment 179SELECT l1.s, l2.s FROM LSEG_TBL l1, LSEG_TBL l2 WHERE l1.s >= l2.s; 180 181-- Has points greater than line segment 182SELECT l1.s, l2.s FROM LSEG_TBL l1, LSEG_TBL l2 WHERE l1.s > l2.s; 183 184-- Has points not equal to line segment 185SELECT l1.s, l2.s FROM LSEG_TBL l1, LSEG_TBL l2 WHERE l1.s != l2.s; 186 187-- Parallel with line segment 188SELECT l1.s, l2.s FROM LSEG_TBL l1, LSEG_TBL l2 WHERE l1.s ?|| l2.s; 189 190-- Perpendicular with line segment 191SELECT l1.s, l2.s FROM LSEG_TBL l1, LSEG_TBL l2 WHERE l1.s ?-| l2.s; 192 193-- Distance to line 194SELECT l.s, l1.s, l.s <-> l1.s FROM LSEG_TBL l, LINE_TBL l1; 195 196-- Distance to line segment 197SELECT l1.s, l2.s, l1.s <-> l2.s FROM LSEG_TBL l1, LSEG_TBL l2; 198 199-- Distance to box 200SELECT l.s, b.f1, l.s <-> b.f1 FROM LSEG_TBL l, BOX_TBL b; 201 202-- Intersect with line segment 203SELECT l.s, l1.s FROM LSEG_TBL l, LINE_TBL l1 WHERE l.s ?# l1.s; 204 205-- Intersect with box 206SELECT l.s, b.f1 FROM LSEG_TBL l, BOX_TBL b WHERE l.s ?# b.f1; 207 208-- Intersection point with line segment 209SELECT l1.s, l2.s, l1.s # l2.s FROM LSEG_TBL l1, LSEG_TBL l2; 210 211-- Closest point to line 212SELECT l.s, l1.s, l.s ## l1.s FROM LSEG_TBL l, LINE_TBL l1; 213 214-- Closest point to line segment 215SELECT l1.s, l2.s, l1.s ## l2.s FROM LSEG_TBL l1, LSEG_TBL l2; 216 217-- Closest point to box 218SELECT l.s, b.f1, l.s ## b.f1 FROM LSEG_TBL l, BOX_TBL b; 219 220-- On line 221SELECT l.s, l1.s FROM LSEG_TBL l, LINE_TBL l1 WHERE l.s <@ l1.s; 222 223-- On box 224SELECT l.s, b.f1 FROM LSEG_TBL l, BOX_TBL b WHERE l.s <@ b.f1; 225 226-- 227-- Boxes 228-- 229 230SELECT '' as six, box(f1) AS box FROM CIRCLE_TBL; 231 232-- translation 233SELECT '' AS twentyfour, b.f1 + p.f1 AS translation 234 FROM BOX_TBL b, POINT_TBL p; 235 236SELECT '' AS twentyfour, b.f1 - p.f1 AS translation 237 FROM BOX_TBL b, POINT_TBL p; 238 239-- Multiply with point 240SELECT b.f1, p.f1, b.f1 * p.f1 FROM BOX_TBL b, POINT_TBL p WHERE p.f1[0] BETWEEN 1 AND 1000; 241 242-- Overflow error 243SELECT b.f1, p.f1, b.f1 * p.f1 FROM BOX_TBL b, POINT_TBL p WHERE p.f1[0] > 1000; 244 245-- Divide by point 246SELECT b.f1, p.f1, b.f1 / p.f1 FROM BOX_TBL b, POINT_TBL p WHERE p.f1[0] BETWEEN 1 AND 1000; 247 248-- To box 249SELECT f1::box 250 FROM POINT_TBL; 251 252SELECT bound_box(a.f1, b.f1) 253 FROM BOX_TBL a, BOX_TBL b; 254 255-- Below box 256SELECT b1.f1, b2.f1, b1.f1 <^ b2.f1 FROM BOX_TBL b1, BOX_TBL b2; 257 258-- Above box 259SELECT b1.f1, b2.f1, b1.f1 >^ b2.f1 FROM BOX_TBL b1, BOX_TBL b2; 260 261-- Intersection point with box 262SELECT b1.f1, b2.f1, b1.f1 # b2.f1 FROM BOX_TBL b1, BOX_TBL b2; 263 264-- Diagonal 265SELECT f1, diagonal(f1) FROM BOX_TBL; 266 267-- Distance to box 268SELECT b1.f1, b2.f1, b1.f1 <-> b2.f1 FROM BOX_TBL b1, BOX_TBL b2; 269 270-- 271-- Paths 272-- 273 274-- Points 275SELECT f1, npoints(f1) FROM PATH_TBL; 276 277-- Area 278SELECT f1, area(f1) FROM PATH_TBL; 279 280-- Length 281SELECT f1, @-@ f1 FROM PATH_TBL; 282 283-- Center 284SELECT f1, @@ f1 FROM PATH_TBL; 285 286-- To polygon 287SELECT f1, f1::polygon FROM PATH_TBL WHERE isclosed(f1); 288 289-- Open path cannot be converted to polygon error 290SELECT f1, f1::polygon FROM PATH_TBL WHERE isopen(f1); 291 292-- Has points less than path 293SELECT p1.f1, p2.f1 FROM PATH_TBL p1, PATH_TBL p2 WHERE p1.f1 < p2.f1; 294 295-- Has points less than or equal to path 296SELECT p1.f1, p2.f1 FROM PATH_TBL p1, PATH_TBL p2 WHERE p1.f1 <= p2.f1; 297 298-- Has points equal to path 299SELECT p1.f1, p2.f1 FROM PATH_TBL p1, PATH_TBL p2 WHERE p1.f1 = p2.f1; 300 301-- Has points greater than or equal to path 302SELECT p1.f1, p2.f1 FROM PATH_TBL p1, PATH_TBL p2 WHERE p1.f1 >= p2.f1; 303 304-- Has points greater than path 305SELECT p1.f1, p2.f1 FROM PATH_TBL p1, PATH_TBL p2 WHERE p1.f1 > p2.f1; 306 307-- Add path 308SELECT p1.f1, p2.f1, p1.f1 + p2.f1 FROM PATH_TBL p1, PATH_TBL p2; 309 310-- Add point 311SELECT p.f1, p1.f1, p.f1 + p1.f1 FROM PATH_TBL p, POINT_TBL p1; 312 313-- Subtract point 314SELECT p.f1, p1.f1, p.f1 - p1.f1 FROM PATH_TBL p, POINT_TBL p1; 315 316-- Multiply with point 317SELECT p.f1, p1.f1, p.f1 * p1.f1 FROM PATH_TBL p, POINT_TBL p1; 318 319-- Divide by point 320SELECT p.f1, p1.f1, p.f1 / p1.f1 FROM PATH_TBL p, POINT_TBL p1 WHERE p1.f1[0] BETWEEN 1 AND 1000; 321 322-- Division by 0 error 323SELECT p.f1, p1.f1, p.f1 / p1.f1 FROM PATH_TBL p, POINT_TBL p1 WHERE p1.f1 ~= '(0,0)'::point; 324 325-- Distance to path 326SELECT p1.f1, p2.f1, p1.f1 <-> p2.f1 FROM PATH_TBL p1, PATH_TBL p2; 327 328-- 329-- Polygons 330-- 331 332-- containment 333SELECT '' AS twentyfour, p.f1, poly.f1, poly.f1 @> p.f1 AS contains 334 FROM POLYGON_TBL poly, POINT_TBL p; 335 336SELECT '' AS twentyfour, p.f1, poly.f1, p.f1 <@ poly.f1 AS contained 337 FROM POLYGON_TBL poly, POINT_TBL p; 338 339SELECT '' AS four, npoints(f1) AS npoints, f1 AS polygon 340 FROM POLYGON_TBL; 341 342SELECT '' AS four, polygon(f1) 343 FROM BOX_TBL; 344 345SELECT '' AS four, polygon(f1) 346 FROM PATH_TBL WHERE isclosed(f1); 347 348SELECT '' AS four, f1 AS open_path, polygon( pclose(f1)) AS polygon 349 FROM PATH_TBL 350 WHERE isopen(f1); 351 352-- To box 353SELECT f1, f1::box FROM POLYGON_TBL; 354 355-- To path 356SELECT f1, f1::path FROM POLYGON_TBL; 357 358-- Same as polygon 359SELECT p1.f1, p2.f1 FROM POLYGON_TBL p1, POLYGON_TBL p2 WHERE p1.f1 ~= p2.f1; 360 361-- Contained by polygon 362SELECT p1.f1, p2.f1 FROM POLYGON_TBL p1, POLYGON_TBL p2 WHERE p1.f1 <@ p2.f1; 363 364-- Contains polygon 365SELECT p1.f1, p2.f1 FROM POLYGON_TBL p1, POLYGON_TBL p2 WHERE p1.f1 @> p2.f1; 366 367-- Overlap with polygon 368SELECT p1.f1, p2.f1 FROM POLYGON_TBL p1, POLYGON_TBL p2 WHERE p1.f1 && p2.f1; 369 370-- Left of polygon 371SELECT p1.f1, p2.f1 FROM POLYGON_TBL p1, POLYGON_TBL p2 WHERE p1.f1 << p2.f1; 372 373-- Overlap of left of polygon 374SELECT p1.f1, p2.f1 FROM POLYGON_TBL p1, POLYGON_TBL p2 WHERE p1.f1 &< p2.f1; 375 376-- Right of polygon 377SELECT p1.f1, p2.f1 FROM POLYGON_TBL p1, POLYGON_TBL p2 WHERE p1.f1 >> p2.f1; 378 379-- Overlap of right of polygon 380SELECT p1.f1, p2.f1 FROM POLYGON_TBL p1, POLYGON_TBL p2 WHERE p1.f1 &> p2.f1; 381 382-- Below polygon 383SELECT p1.f1, p2.f1 FROM POLYGON_TBL p1, POLYGON_TBL p2 WHERE p1.f1 <<| p2.f1; 384 385-- Overlap or below polygon 386SELECT p1.f1, p2.f1 FROM POLYGON_TBL p1, POLYGON_TBL p2 WHERE p1.f1 &<| p2.f1; 387 388-- Above polygon 389SELECT p1.f1, p2.f1 FROM POLYGON_TBL p1, POLYGON_TBL p2 WHERE p1.f1 |>> p2.f1; 390 391-- Overlap or above polygon 392SELECT p1.f1, p2.f1 FROM POLYGON_TBL p1, POLYGON_TBL p2 WHERE p1.f1 |&> p2.f1; 393 394-- Distance to polygon 395SELECT p1.f1, p2.f1, p1.f1 <-> p2.f1 FROM POLYGON_TBL p1, POLYGON_TBL p2; 396 397-- 398-- Circles 399-- 400 401SELECT '' AS six, circle(f1, 50.0) 402 FROM POINT_TBL; 403 404SELECT '' AS four, circle(f1) 405 FROM BOX_TBL; 406 407SELECT '' AS two, circle(f1) 408 FROM POLYGON_TBL 409 WHERE (# f1) >= 3; 410 411SELECT '' AS twentyfour, c1.f1 AS circle, p1.f1 AS point, (p1.f1 <-> c1.f1) AS distance 412 FROM CIRCLE_TBL c1, POINT_TBL p1 413 WHERE (p1.f1 <-> c1.f1) > 0 414 ORDER BY distance, area(c1.f1), p1.f1[0]; 415 416-- To polygon 417SELECT f1, f1::polygon FROM CIRCLE_TBL WHERE f1 >= '<(0,0),1>'; 418 419-- To polygon with less points 420SELECT f1, polygon(8, f1) FROM CIRCLE_TBL WHERE f1 >= '<(0,0),1>'; 421 422-- Too less points error 423SELECT f1, polygon(1, f1) FROM CIRCLE_TBL WHERE f1 >= '<(0,0),1>'; 424 425-- Zero radius error 426SELECT f1, polygon(10, f1) FROM CIRCLE_TBL WHERE f1 < '<(0,0),1>'; 427 428-- Same as circle 429SELECT c1.f1, c2.f1 FROM CIRCLE_TBL c1, CIRCLE_TBL c2 WHERE c1.f1 ~= c2.f1; 430 431-- Overlap with circle 432SELECT c1.f1, c2.f1 FROM CIRCLE_TBL c1, CIRCLE_TBL c2 WHERE c1.f1 && c2.f1; 433 434-- Overlap or left of circle 435SELECT c1.f1, c2.f1 FROM CIRCLE_TBL c1, CIRCLE_TBL c2 WHERE c1.f1 &< c2.f1; 436 437-- Left of circle 438SELECT c1.f1, c2.f1 FROM CIRCLE_TBL c1, CIRCLE_TBL c2 WHERE c1.f1 << c2.f1; 439 440-- Right of circle 441SELECT c1.f1, c2.f1 FROM CIRCLE_TBL c1, CIRCLE_TBL c2 WHERE c1.f1 >> c2.f1; 442 443-- Overlap or right of circle 444SELECT c1.f1, c2.f1 FROM CIRCLE_TBL c1, CIRCLE_TBL c2 WHERE c1.f1 &> c2.f1; 445 446-- Contained by circle 447SELECT c1.f1, c2.f1 FROM CIRCLE_TBL c1, CIRCLE_TBL c2 WHERE c1.f1 <@ c2.f1; 448 449-- Contain by circle 450SELECT c1.f1, c2.f1 FROM CIRCLE_TBL c1, CIRCLE_TBL c2 WHERE c1.f1 @> c2.f1; 451 452-- Below circle 453SELECT c1.f1, c2.f1 FROM CIRCLE_TBL c1, CIRCLE_TBL c2 WHERE c1.f1 <<| c2.f1; 454 455-- Above circle 456SELECT c1.f1, c2.f1 FROM CIRCLE_TBL c1, CIRCLE_TBL c2 WHERE c1.f1 |>> c2.f1; 457 458-- Overlap or below circle 459SELECT c1.f1, c2.f1 FROM CIRCLE_TBL c1, CIRCLE_TBL c2 WHERE c1.f1 &<| c2.f1; 460 461-- Overlap or above circle 462SELECT c1.f1, c2.f1 FROM CIRCLE_TBL c1, CIRCLE_TBL c2 WHERE c1.f1 |&> c2.f1; 463 464-- Area equal with circle 465SELECT c1.f1, c2.f1 FROM CIRCLE_TBL c1, CIRCLE_TBL c2 WHERE c1.f1 = c2.f1; 466 467-- Area not equal with circle 468SELECT c1.f1, c2.f1 FROM CIRCLE_TBL c1, CIRCLE_TBL c2 WHERE c1.f1 != c2.f1; 469 470-- Area less than circle 471SELECT c1.f1, c2.f1 FROM CIRCLE_TBL c1, CIRCLE_TBL c2 WHERE c1.f1 < c2.f1; 472 473-- Area greater than circle 474SELECT c1.f1, c2.f1 FROM CIRCLE_TBL c1, CIRCLE_TBL c2 WHERE c1.f1 > c2.f1; 475 476-- Area less than or equal circle 477SELECT c1.f1, c2.f1 FROM CIRCLE_TBL c1, CIRCLE_TBL c2 WHERE c1.f1 <= c2.f1; 478 479-- Area greater than or equal circle 480SELECT c1.f1, c2.f1 FROM CIRCLE_TBL c1, CIRCLE_TBL c2 WHERE c1.f1 >= c2.f1; 481 482-- Area less than circle 483SELECT c1.f1, c2.f1 FROM CIRCLE_TBL c1, CIRCLE_TBL c2 WHERE c1.f1 < c2.f1; 484 485-- Area greater than circle 486SELECT c1.f1, c2.f1 FROM CIRCLE_TBL c1, CIRCLE_TBL c2 WHERE c1.f1 < c2.f1; 487 488-- Add point 489SELECT c.f1, p.f1, c.f1 + p.f1 FROM CIRCLE_TBL c, POINT_TBL p; 490 491-- Subtract point 492SELECT c.f1, p.f1, c.f1 - p.f1 FROM CIRCLE_TBL c, POINT_TBL p; 493 494-- Multiply with point 495SELECT c.f1, p.f1, c.f1 * p.f1 FROM CIRCLE_TBL c, POINT_TBL p; 496 497-- Divide by point 498SELECT c.f1, p.f1, c.f1 / p.f1 FROM CIRCLE_TBL c, POINT_TBL p WHERE p.f1[0] BETWEEN 1 AND 1000; 499 500-- Overflow error 501SELECT c.f1, p.f1, c.f1 / p.f1 FROM CIRCLE_TBL c, POINT_TBL p WHERE p.f1[0] > 1000; 502 503-- Division by 0 error 504SELECT c.f1, p.f1, c.f1 / p.f1 FROM CIRCLE_TBL c, POINT_TBL p WHERE p.f1 ~= '(0,0)'::point; 505 506-- Distance to polygon 507SELECT c.f1, p.f1, c.f1 <-> p.f1 FROM CIRCLE_TBL c, POLYGON_TBL p; 508