1-- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - 2-- 3-- 4-- PostGIS - Spatial Types for PostgreSQL 5-- http://postgis.net 6-- Copyright 2001-2003 Refractions Research Inc. 7-- 8-- This is free software; you can redistribute and/or modify it under 9-- the terms of the GNU General Public Licence. See the COPYING file. 10-- 11-- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - 12-- 13-- WARNING: Any change in this file must be evaluated for compatibility. 14-- Changes cleanly handled by postgis_upgrade.sql are fine, 15-- other changes will require a bump in Major version. 16-- Currently only function replaceble by CREATE OR REPLACE 17-- are cleanly handled. 18-- 19-- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - 20 21#include "sqldefines.h" 22 23-- INSTALL VERSION: POSTGIS_LIB_VERSION 24 25BEGIN; 26SET LOCAL client_min_messages TO warning; 27 28-- Check that no other postgis is installed 29DO $$ 30DECLARE 31 rec RECORD; 32BEGIN 33 FOR rec IN 34 SELECT n.nspname, p.proname FROM pg_proc p, pg_namespace n 35 WHERE p.proname = 'postgis_version' 36 AND p.pronamespace = n.oid 37 LOOP 38 RAISE EXCEPTION 'PostGIS is already installed in schema ''%''', rec.nspname; 39 END LOOP; 40END 41$$ LANGUAGE 'plpgsql'; 42 43-- Let the user know about a deprecated signature and its new name, if any 44CREATE OR REPLACE FUNCTION _postgis_deprecate(oldname text, newname text, version text) 45RETURNS void AS 46$$ 47DECLARE 48 curver_text text; 49BEGIN 50 -- 51 -- Raises a NOTICE if it was deprecated in this version, 52 -- a WARNING if in a previous version (only up to minor version checked) 53 -- 54 curver_text := POSTGIS_LIB_VERSION; 55 IF split_part(curver_text,'.',1)::int > split_part(version,'.',1)::int OR 56 ( split_part(curver_text,'.',1) = split_part(version,'.',1) AND 57 split_part(curver_text,'.',2) != split_part(version,'.',2) ) 58 THEN 59 RAISE WARNING '% signature was deprecated in %. Please use %', oldname, version, newname; 60 ELSE 61 RAISE DEBUG '% signature was deprecated in %. Please use %', oldname, version, newname; 62 END IF; 63END; 64$$ LANGUAGE 'plpgsql' IMMUTABLE STRICT 65 COST 100; 66 67------------------------------------------------------------------- 68-- SPHEROID TYPE 69------------------------------------------------------------------- 70CREATE OR REPLACE FUNCTION spheroid_in(cstring) 71 RETURNS spheroid 72 AS 'MODULE_PATHNAME','ellipsoid_in' 73 LANGUAGE 'c' IMMUTABLE STRICT _PARALLEL; 74 75CREATE OR REPLACE FUNCTION spheroid_out(spheroid) 76 RETURNS cstring 77 AS 'MODULE_PATHNAME','ellipsoid_out' 78 LANGUAGE 'c' IMMUTABLE STRICT _PARALLEL; 79 80-- Availability: 0.5.0 81CREATE TYPE spheroid ( 82 alignment = double, 83 internallength = 65, 84 input = spheroid_in, 85 output = spheroid_out 86); 87 88------------------------------------------------------------------- 89-- GEOMETRY TYPE (lwgeom) 90------------------------------------------------------------------- 91CREATE OR REPLACE FUNCTION geometry_in(cstring) 92 RETURNS geometry 93 AS 'MODULE_PATHNAME','LWGEOM_in' 94 LANGUAGE 'c' IMMUTABLE STRICT _PARALLEL; 95 96CREATE OR REPLACE FUNCTION geometry_out(geometry) 97 RETURNS cstring 98 AS 'MODULE_PATHNAME','LWGEOM_out' 99 LANGUAGE 'c' IMMUTABLE STRICT _PARALLEL; 100 101-- Availability: 2.0.0 102CREATE OR REPLACE FUNCTION geometry_typmod_in(cstring[]) 103 RETURNS integer 104 AS 'MODULE_PATHNAME','geometry_typmod_in' 105 LANGUAGE 'c' IMMUTABLE STRICT _PARALLEL; 106 107-- Availability: 2.0.0 108CREATE OR REPLACE FUNCTION geometry_typmod_out(integer) 109 RETURNS cstring 110 AS 'MODULE_PATHNAME','postgis_typmod_out' 111 LANGUAGE 'c' IMMUTABLE STRICT _PARALLEL; 112 113CREATE OR REPLACE FUNCTION geometry_analyze(internal) 114 RETURNS bool 115 AS 'MODULE_PATHNAME', 'gserialized_analyze_nd' 116 LANGUAGE 'c' VOLATILE STRICT; 117 118CREATE OR REPLACE FUNCTION geometry_recv(internal) 119 RETURNS geometry 120 AS 'MODULE_PATHNAME','LWGEOM_recv' 121 LANGUAGE 'c' IMMUTABLE STRICT _PARALLEL; 122 123CREATE OR REPLACE FUNCTION geometry_send(geometry) 124 RETURNS bytea 125 AS 'MODULE_PATHNAME','LWGEOM_send' 126 LANGUAGE 'c' IMMUTABLE STRICT _PARALLEL; 127 128-- Availability: 0.1.0 129CREATE TYPE geometry ( 130 internallength = variable, 131 input = geometry_in, 132 output = geometry_out, 133 send = geometry_send, 134 receive = geometry_recv, 135 typmod_in = geometry_typmod_in, 136 typmod_out = geometry_typmod_out, 137 delimiter = ':', 138 alignment = double, 139 analyze = geometry_analyze, 140 storage = main 141); 142 143-- Availability: 2.0.0 144-- Special cast for enforcing the typmod restrictions 145CREATE OR REPLACE FUNCTION geometry(geometry, integer, boolean) 146 RETURNS geometry 147 AS 'MODULE_PATHNAME','geometry_enforce_typmod' 148 LANGUAGE 'c' IMMUTABLE STRICT _PARALLEL; 149 150-- Availability: 2.0.0 151CREATE CAST (geometry AS geometry) WITH FUNCTION geometry(geometry, integer, boolean) AS IMPLICIT; 152 153-- Availability: 2.1.0 154CREATE OR REPLACE FUNCTION geometry(point) 155 RETURNS geometry 156 AS 'MODULE_PATHNAME','point_to_geometry' 157 LANGUAGE 'c' IMMUTABLE STRICT _PARALLEL; 158 159-- Availability: 2.1.0 160CREATE OR REPLACE FUNCTION point(geometry) 161 RETURNS point 162 AS 'MODULE_PATHNAME','geometry_to_point' 163 LANGUAGE 'c' IMMUTABLE STRICT _PARALLEL; 164 165-- Availability: 2.1.0 166CREATE OR REPLACE FUNCTION geometry(path) 167 RETURNS geometry 168 AS 'MODULE_PATHNAME','path_to_geometry' 169 LANGUAGE 'c' IMMUTABLE STRICT _PARALLEL; 170 171-- Availability: 2.1.0 172CREATE OR REPLACE FUNCTION path(geometry) 173 RETURNS path 174 AS 'MODULE_PATHNAME','geometry_to_path' 175 LANGUAGE 'c' IMMUTABLE STRICT _PARALLEL; 176 177-- Availability: 2.1.0 178CREATE OR REPLACE FUNCTION geometry(polygon) 179 RETURNS geometry 180 AS 'MODULE_PATHNAME','polygon_to_geometry' 181 LANGUAGE 'c' IMMUTABLE STRICT _PARALLEL; 182 183-- Availability: 2.1.0 184CREATE OR REPLACE FUNCTION polygon(geometry) 185 RETURNS polygon 186 AS 'MODULE_PATHNAME','geometry_to_polygon' 187 LANGUAGE 'c' IMMUTABLE STRICT _PARALLEL; 188 189CREATE CAST (geometry AS point) WITH FUNCTION point(geometry); 190CREATE CAST (point AS geometry) WITH FUNCTION geometry(point); 191CREATE CAST (geometry AS path) WITH FUNCTION path(geometry); 192CREATE CAST (path AS geometry) WITH FUNCTION geometry(path); 193CREATE CAST (geometry AS polygon) WITH FUNCTION polygon(geometry); 194CREATE CAST (polygon AS geometry) WITH FUNCTION geometry(polygon); 195 196------------------------------------------------------------------- 197-- BOX3D TYPE 198-- Point coordinate data access 199------------------------------------------- 200-- PostGIS equivalent function: X(geometry) 201CREATE OR REPLACE FUNCTION ST_X(geometry) 202 RETURNS float8 203 AS 'MODULE_PATHNAME','LWGEOM_x_point' 204 LANGUAGE 'c' IMMUTABLE STRICT _PARALLEL; 205 206-- PostGIS equivalent function: Y(geometry) 207CREATE OR REPLACE FUNCTION ST_Y(geometry) 208 RETURNS float8 209 AS 'MODULE_PATHNAME','LWGEOM_y_point' 210 LANGUAGE 'c' IMMUTABLE STRICT _PARALLEL; 211 212-- Availability: 1.2.2 213CREATE OR REPLACE FUNCTION ST_Z(geometry) 214 RETURNS float8 215 AS 'MODULE_PATHNAME','LWGEOM_z_point' 216 LANGUAGE 'c' IMMUTABLE STRICT _PARALLEL; 217 218-- Availability: 1.2.2 219CREATE OR REPLACE FUNCTION ST_M(geometry) 220 RETURNS float8 221 AS 'MODULE_PATHNAME','LWGEOM_m_point' 222 LANGUAGE 'c' IMMUTABLE STRICT _PARALLEL; 223 224------------------------------------------- 225------------------------------------------------------------------- 226 227CREATE OR REPLACE FUNCTION box3d_in(cstring) 228 RETURNS box3d 229 AS 'MODULE_PATHNAME', 'BOX3D_in' 230 LANGUAGE 'c' IMMUTABLE STRICT _PARALLEL; 231 232CREATE OR REPLACE FUNCTION box3d_out(box3d) 233 RETURNS cstring 234 AS 'MODULE_PATHNAME', 'BOX3D_out' 235 LANGUAGE 'c' IMMUTABLE STRICT _PARALLEL; 236 237-- Availability: 0.1.0 238CREATE TYPE box3d ( 239 alignment = double, 240 internallength = 52, 241 input = box3d_in, 242 output = box3d_out 243); 244 245----------------------------------------------------------------------- 246-- BOX2D 247----------------------------------------------------------------------- 248 249CREATE OR REPLACE FUNCTION box2d_in(cstring) 250 RETURNS box2d 251 AS 'MODULE_PATHNAME','BOX2D_in' 252 LANGUAGE 'c' IMMUTABLE STRICT _PARALLEL; 253 254CREATE OR REPLACE FUNCTION box2d_out(box2d) 255 RETURNS cstring 256 AS 'MODULE_PATHNAME','BOX2D_out' 257 LANGUAGE 'c' IMMUTABLE STRICT _PARALLEL; 258 259-- Availability: 0.8.2 260CREATE TYPE box2d ( 261 internallength = 65, 262 input = box2d_in, 263 output = box2d_out, 264 storage = plain 265); 266 267------------------------------------------------------------------- 268-- BOX2DF TYPE (INTERNAL ONLY) 269------------------------------------------------------------------- 270-- 271-- Box2Df type is used by the GiST index bindings. 272-- In/out functions are stubs, as all access should be internal. 273--- 274-- Availability: 2.0.0 275CREATE OR REPLACE FUNCTION box2df_in(cstring) 276 RETURNS box2df 277 AS 'MODULE_PATHNAME','box2df_in' 278 LANGUAGE 'c' IMMUTABLE STRICT _PARALLEL; 279 280-- Availability: 2.0.0 281CREATE OR REPLACE FUNCTION box2df_out(box2df) 282 RETURNS cstring 283 AS 'MODULE_PATHNAME','box2df_out' 284 LANGUAGE 'c' IMMUTABLE STRICT _PARALLEL; 285 286-- Availability: 2.0.0 287CREATE TYPE box2df ( 288 internallength = 16, 289 input = box2df_in, 290 output = box2df_out, 291 storage = plain, 292 alignment = double 293); 294 295------------------------------------------------------------------- 296-- GIDX TYPE (INTERNAL ONLY) 297------------------------------------------------------------------- 298-- 299-- GIDX type is used by the N-D and GEOGRAPHY GiST index bindings. 300-- In/out functions are stubs, as all access should be internal. 301--- 302 303-- Availability: 1.5.0 304CREATE OR REPLACE FUNCTION gidx_in(cstring) 305 RETURNS gidx 306 AS 'MODULE_PATHNAME','gidx_in' 307 LANGUAGE 'c' IMMUTABLE STRICT _PARALLEL; 308 309-- Availability: 1.5.0 310CREATE OR REPLACE FUNCTION gidx_out(gidx) 311 RETURNS cstring 312 AS 'MODULE_PATHNAME','gidx_out' 313 LANGUAGE 'c' IMMUTABLE STRICT _PARALLEL; 314 315-- Availability: 1.5.0 316CREATE TYPE gidx ( 317 internallength = variable, 318 input = gidx_in, 319 output = gidx_out, 320 storage = plain, 321 alignment = double 322); 323 324------------------------------------------------------------------- 325-- BTREE indexes 326------------------------------------------------------------------- 327CREATE OR REPLACE FUNCTION geometry_lt(geom1 geometry, geom2 geometry) 328 RETURNS bool 329 AS 'MODULE_PATHNAME', 'lwgeom_lt' 330 LANGUAGE 'c' IMMUTABLE STRICT _PARALLEL; 331 332CREATE OR REPLACE FUNCTION geometry_le(geom1 geometry, geom2 geometry) 333 RETURNS bool 334 AS 'MODULE_PATHNAME', 'lwgeom_le' 335 LANGUAGE 'c' IMMUTABLE STRICT _PARALLEL; 336 337CREATE OR REPLACE FUNCTION geometry_gt(geom1 geometry, geom2 geometry) 338 RETURNS bool 339 AS 'MODULE_PATHNAME', 'lwgeom_gt' 340 LANGUAGE 'c' IMMUTABLE STRICT _PARALLEL; 341 342CREATE OR REPLACE FUNCTION geometry_ge(geom1 geometry, geom2 geometry) 343 RETURNS bool 344 AS 'MODULE_PATHNAME', 'lwgeom_ge' 345 LANGUAGE 'c' IMMUTABLE STRICT _PARALLEL; 346 347CREATE OR REPLACE FUNCTION geometry_eq(geom1 geometry, geom2 geometry) 348 RETURNS bool 349 AS 'MODULE_PATHNAME', 'lwgeom_eq' 350 LANGUAGE 'c' IMMUTABLE STRICT _PARALLEL; 351 352CREATE OR REPLACE FUNCTION geometry_cmp(geom1 geometry, geom2 geometry) 353 RETURNS integer 354 AS 'MODULE_PATHNAME', 'lwgeom_cmp' 355 LANGUAGE 'c' IMMUTABLE STRICT _PARALLEL; 356 357-- 358-- Sorting operators for Btree 359-- 360 361-- Availability: 0.9.0 362CREATE OPERATOR < ( 363 LEFTARG = geometry, RIGHTARG = geometry, PROCEDURE = geometry_lt, 364 COMMUTATOR = '>', NEGATOR = '>=', 365 RESTRICT = contsel, JOIN = contjoinsel 366); 367 368-- Availability: 0.9.0 369CREATE OPERATOR <= ( 370 LEFTARG = geometry, RIGHTARG = geometry, PROCEDURE = geometry_le, 371 COMMUTATOR = '>=', NEGATOR = '>', 372 RESTRICT = contsel, JOIN = contjoinsel 373); 374 375-- Availability: 0.9.0 376CREATE OPERATOR = ( 377 LEFTARG = geometry, RIGHTARG = geometry, PROCEDURE = geometry_eq, 378 COMMUTATOR = '=', -- we might implement a faster negator here 379 RESTRICT = contsel, JOIN = contjoinsel 380); 381 382-- Availability: 0.9.0 383CREATE OPERATOR >= ( 384 LEFTARG = geometry, RIGHTARG = geometry, PROCEDURE = geometry_ge, 385 COMMUTATOR = '<=', NEGATOR = '<', 386 RESTRICT = contsel, JOIN = contjoinsel 387); 388 389-- Availability: 0.9.0 390CREATE OPERATOR > ( 391 LEFTARG = geometry, RIGHTARG = geometry, PROCEDURE = geometry_gt, 392 COMMUTATOR = '<', NEGATOR = '<=', 393 RESTRICT = contsel, JOIN = contjoinsel 394); 395 396-- Availability: 0.9.0 397CREATE OPERATOR CLASS btree_geometry_ops 398 DEFAULT FOR TYPE geometry USING btree AS 399 OPERATOR 1 < , 400 OPERATOR 2 <= , 401 OPERATOR 3 = , 402 OPERATOR 4 >= , 403 OPERATOR 5 > , 404 FUNCTION 1 geometry_cmp (geom1 geometry, geom2 geometry); 405 406-- 407-- Sorting operators for Btree 408-- 409 410-- Availability: 2.5.0 411CREATE OR REPLACE FUNCTION geometry_hash(geometry) 412 RETURNS integer 413 AS 'MODULE_PATHNAME','lwgeom_hash' 414 LANGUAGE 'c' STRICT IMMUTABLE _PARALLEL; 415 416-- Availability: 2.5.0 417CREATE OPERATOR CLASS hash_geometry_ops 418 DEFAULT FOR TYPE geometry USING hash AS 419 OPERATOR 1 = , 420 FUNCTION 1 geometry_hash(geometry); 421 422----------------------------------------------------------------------------- 423-- GiST 2D GEOMETRY-over-GSERIALIZED INDEX 424----------------------------------------------------------------------------- 425 426-- ---------- ---------- ---------- ---------- ---------- ---------- ---------- 427-- GiST Support Functions 428-- ---------- ---------- ---------- ---------- ---------- ---------- ---------- 429 430-- Availability: 2.0.0 431CREATE OR REPLACE FUNCTION geometry_gist_distance_2d(internal,geometry,int4) 432 RETURNS float8 433 AS 'MODULE_PATHNAME' ,'gserialized_gist_distance_2d' 434 LANGUAGE 'c' _PARALLEL; 435 436-- Availability: 2.0.0 437CREATE OR REPLACE FUNCTION geometry_gist_consistent_2d(internal,geometry,int4) 438 RETURNS bool 439 AS 'MODULE_PATHNAME' ,'gserialized_gist_consistent_2d' 440 LANGUAGE 'c' _PARALLEL; 441 442-- Availability: 2.0.0 443CREATE OR REPLACE FUNCTION geometry_gist_compress_2d(internal) 444 RETURNS internal 445 AS 'MODULE_PATHNAME','gserialized_gist_compress_2d' 446 LANGUAGE 'c' _PARALLEL; 447 448-- Availability: 2.0.0 449CREATE OR REPLACE FUNCTION geometry_gist_penalty_2d(internal,internal,internal) 450 RETURNS internal 451 AS 'MODULE_PATHNAME' ,'gserialized_gist_penalty_2d' 452 LANGUAGE 'c' _PARALLEL; 453 454-- Availability: 2.0.0 455CREATE OR REPLACE FUNCTION geometry_gist_picksplit_2d(internal, internal) 456 RETURNS internal 457 AS 'MODULE_PATHNAME' ,'gserialized_gist_picksplit_2d' 458 LANGUAGE 'c' _PARALLEL; 459 460-- Availability: 2.0.0 461CREATE OR REPLACE FUNCTION geometry_gist_union_2d(bytea, internal) 462 RETURNS internal 463 AS 'MODULE_PATHNAME' ,'gserialized_gist_union_2d' 464 LANGUAGE 'c' _PARALLEL; 465 466-- Availability: 2.0.0 467CREATE OR REPLACE FUNCTION geometry_gist_same_2d(geom1 geometry, geom2 geometry, internal) 468 RETURNS internal 469 AS 'MODULE_PATHNAME' ,'gserialized_gist_same_2d' 470 LANGUAGE 'c' _PARALLEL; 471 472-- Availability: 2.0.0 473CREATE OR REPLACE FUNCTION geometry_gist_decompress_2d(internal) 474 RETURNS internal 475 AS 'MODULE_PATHNAME' ,'gserialized_gist_decompress_2d' 476 LANGUAGE 'c' _PARALLEL; 477 478----------------------------------------------------------------------------- 479 480-- Availability: 2.1.0 481-- Given a table, column and query geometry, returns the estimate of what proportion 482-- of the table would be returned by a query using the &&/&&& operators. The mode 483-- changes whether the estimate is in x/y only or in all available dimensions. 484CREATE OR REPLACE FUNCTION _postgis_selectivity(tbl regclass, att_name text, geom geometry, mode text default '2') 485 RETURNS float8 486 AS 'MODULE_PATHNAME', '_postgis_gserialized_sel' 487 LANGUAGE 'c' STRICT _PARALLEL; 488 489-- Availability: 2.1.0 490-- Given a two tables and columns, returns estimate of the proportion of rows 491-- a &&/&&& join will return relative to the number of rows an unconstrained 492-- table join would return. Mode flips result between evaluation in x/y only 493-- and evaluation in all available dimensions. 494CREATE OR REPLACE FUNCTION _postgis_join_selectivity(regclass, text, regclass, text, text default '2') 495 RETURNS float8 496 AS 'MODULE_PATHNAME', '_postgis_gserialized_joinsel' 497 LANGUAGE 'c' STRICT _PARALLEL; 498 499-- Availability: 2.1.0 500-- Given a table and a column, returns the statistics information stored by 501-- PostgreSQL, in a JSON text form. Mode determines whether the 2D statistics 502-- or the ND statistics are returned. 503CREATE OR REPLACE FUNCTION _postgis_stats(tbl regclass, att_name text, text default '2') 504 RETURNS text 505 AS 'MODULE_PATHNAME', '_postgis_gserialized_stats' 506 LANGUAGE 'c' STRICT _PARALLEL; 507 508-- Availability: 2.5.0 509-- Given a table and a column, returns the extent of all boxes in the 510-- first page of the index (the head of the index) 511CREATE OR REPLACE FUNCTION _postgis_index_extent(tbl regclass, col text) 512 RETURNS box2d 513 AS 'MODULE_PATHNAME','_postgis_gserialized_index_extent' 514 LANGUAGE 'c' STABLE STRICT; 515 516-- Availability: 2.1.0 517CREATE OR REPLACE FUNCTION gserialized_gist_sel_2d (internal, oid, internal, int4) 518 RETURNS float8 519 AS 'MODULE_PATHNAME', 'gserialized_gist_sel_2d' 520 LANGUAGE 'c' _PARALLEL; 521 522-- Availability: 2.1.0 523CREATE OR REPLACE FUNCTION gserialized_gist_sel_nd (internal, oid, internal, int4) 524 RETURNS float8 525 AS 'MODULE_PATHNAME', 'gserialized_gist_sel_nd' 526 LANGUAGE 'c' _PARALLEL; 527 528-- Availability: 2.1.0 529CREATE OR REPLACE FUNCTION gserialized_gist_joinsel_2d (internal, oid, internal, smallint) 530 RETURNS float8 531 AS 'MODULE_PATHNAME', 'gserialized_gist_joinsel_2d' 532 LANGUAGE 'c' _PARALLEL; 533 534-- Availability: 2.1.0 535CREATE OR REPLACE FUNCTION gserialized_gist_joinsel_nd (internal, oid, internal, smallint) 536 RETURNS float8 537 AS 'MODULE_PATHNAME', 'gserialized_gist_joinsel_nd' 538 LANGUAGE 'c' _PARALLEL; 539 540----------------------------------------------------------------------------- 541-- GEOMETRY Operators 542----------------------------------------------------------------------------- 543 544-- ---------- ---------- ---------- ---------- ---------- ---------- ---------- 545-- 2D GEOMETRY Operators 546-- ---------- ---------- ---------- ---------- ---------- ---------- ---------- 547 548-- Availability: 2.0.0 549CREATE OR REPLACE FUNCTION geometry_overlaps(geom1 geometry, geom2 geometry) 550 RETURNS boolean 551 AS 'MODULE_PATHNAME' ,'gserialized_overlaps_2d' 552 LANGUAGE 'c' IMMUTABLE STRICT _PARALLEL; 553 554-- Availability: 0.1.0 555-- Changed: 2.0.0 use gserialized selectivity estimators 556CREATE OPERATOR && ( 557 LEFTARG = geometry, RIGHTARG = geometry, PROCEDURE = geometry_overlaps, 558 COMMUTATOR = '&&', 559 RESTRICT = gserialized_gist_sel_2d, 560 JOIN = gserialized_gist_joinsel_2d 561); 562 563-- Availability: 2.0.0 564CREATE OR REPLACE FUNCTION geometry_same(geom1 geometry, geom2 geometry) 565 RETURNS boolean 566 AS 'MODULE_PATHNAME' ,'gserialized_same_2d' 567 LANGUAGE 'c' IMMUTABLE STRICT _PARALLEL; 568 569-- Availability: 0.1.0 570CREATE OPERATOR ~= ( 571 LEFTARG = geometry, RIGHTARG = geometry, PROCEDURE = geometry_same, 572 RESTRICT = contsel, JOIN = contjoinsel 573); 574 575-- As of 2.2.0 this no longer returns the centroid/centroid distance, it 576-- returns the actual distance, to support the 'recheck' functionality 577-- enabled in the KNN operator 578-- Availability: 2.0.0 579CREATE OR REPLACE FUNCTION geometry_distance_centroid(geom1 geometry, geom2 geometry) 580 RETURNS float8 581#if POSTGIS_PGSQL_VERSION >= 95 582 AS 'MODULE_PATHNAME' ,'distance' 583#else 584 AS 'MODULE_PATHNAME' ,'gserialized_distance_centroid_2d' 585#endif 586 LANGUAGE 'c' IMMUTABLE STRICT _PARALLEL; 587 588-- Availability: 2.0.0 589CREATE OR REPLACE FUNCTION geometry_distance_box(geom1 geometry, geom2 geometry) 590 RETURNS float8 591 AS 'MODULE_PATHNAME' ,'gserialized_distance_box_2d' 592 LANGUAGE 'c' IMMUTABLE STRICT _PARALLEL; 593 594-- Availability: 2.0.0 595CREATE OPERATOR <-> ( 596 LEFTARG = geometry, RIGHTARG = geometry, PROCEDURE = geometry_distance_centroid, 597 COMMUTATOR = '<->' 598); 599 600-- Availability: 2.0.0 601CREATE OPERATOR <#> ( 602 LEFTARG = geometry, RIGHTARG = geometry, PROCEDURE = geometry_distance_box, 603 COMMUTATOR = '<#>' 604); 605 606-- Availability: 2.0.0 607CREATE OR REPLACE FUNCTION geometry_contains(geom1 geometry, geom2 geometry) 608 RETURNS bool 609 AS 'MODULE_PATHNAME', 'gserialized_contains_2d' 610 LANGUAGE 'c' IMMUTABLE STRICT _PARALLEL; 611 612-- Availability: 2.0.0 613CREATE OR REPLACE FUNCTION geometry_within(geom1 geometry, geom2 geometry) 614 RETURNS bool 615 AS 'MODULE_PATHNAME', 'gserialized_within_2d' 616 LANGUAGE 'c' IMMUTABLE STRICT _PARALLEL; 617 618-- Availability: 0.1.0 619CREATE OPERATOR @ ( 620 LEFTARG = geometry, RIGHTARG = geometry, PROCEDURE = geometry_within, 621 COMMUTATOR = '~', 622 RESTRICT = contsel, JOIN = contjoinsel 623); 624 625-- Availability: 0.1.0 626CREATE OPERATOR ~ ( 627 LEFTARG = geometry, RIGHTARG = geometry, PROCEDURE = geometry_contains, 628 COMMUTATOR = '@', 629 RESTRICT = contsel, JOIN = contjoinsel 630); 631 632-- Availability: 2.0.0 633CREATE OR REPLACE FUNCTION geometry_left(geom1 geometry, geom2 geometry) 634 RETURNS bool 635 AS 'MODULE_PATHNAME', 'gserialized_left_2d' 636 LANGUAGE 'c' IMMUTABLE STRICT _PARALLEL; 637 638-- Availability: 0.1.0 639CREATE OPERATOR << ( 640 LEFTARG = geometry, RIGHTARG = geometry, PROCEDURE = geometry_left, 641 COMMUTATOR = '>>', 642 RESTRICT = positionsel, JOIN = positionjoinsel 643); 644 645-- Availability: 2.0.0 646CREATE OR REPLACE FUNCTION geometry_overleft(geom1 geometry, geom2 geometry) 647 RETURNS bool 648 AS 'MODULE_PATHNAME', 'gserialized_overleft_2d' 649 LANGUAGE 'c' IMMUTABLE STRICT _PARALLEL; 650 651-- Availability: 0.1.0 652CREATE OPERATOR &< ( 653 LEFTARG = geometry, RIGHTARG = geometry, PROCEDURE = geometry_overleft, 654 RESTRICT = positionsel, JOIN = positionjoinsel 655); 656 657-- Availability: 2.0.0 658CREATE OR REPLACE FUNCTION geometry_below(geom1 geometry, geom2 geometry) 659 RETURNS bool 660 AS 'MODULE_PATHNAME', 'gserialized_below_2d' 661 LANGUAGE 'c' IMMUTABLE STRICT _PARALLEL; 662 663-- Availability: 0.1.0 664CREATE OPERATOR <<| ( 665 LEFTARG = geometry, RIGHTARG = geometry, PROCEDURE = geometry_below, 666 COMMUTATOR = '|>>', 667 RESTRICT = positionsel, JOIN = positionjoinsel 668); 669 670-- Availability: 2.0.0 671CREATE OR REPLACE FUNCTION geometry_overbelow(geom1 geometry, geom2 geometry) 672 RETURNS bool 673 AS 'MODULE_PATHNAME', 'gserialized_overbelow_2d' 674 LANGUAGE 'c' IMMUTABLE STRICT _PARALLEL; 675 676-- Availability: 0.1.0 677CREATE OPERATOR &<| ( 678 LEFTARG = geometry, RIGHTARG = geometry, PROCEDURE = geometry_overbelow, 679 RESTRICT = positionsel, JOIN = positionjoinsel 680); 681 682-- Availability: 2.0.0 683CREATE OR REPLACE FUNCTION geometry_overright(geom1 geometry, geom2 geometry) 684 RETURNS bool 685 AS 'MODULE_PATHNAME', 'gserialized_overright_2d' 686 LANGUAGE 'c' IMMUTABLE STRICT _PARALLEL; 687 688-- Availability: 0.1.0 689CREATE OPERATOR &> ( 690 LEFTARG = geometry, RIGHTARG = geometry, PROCEDURE = geometry_overright, 691 RESTRICT = positionsel, JOIN = positionjoinsel 692); 693 694-- Availability: 2.0.0 695CREATE OR REPLACE FUNCTION geometry_right(geom1 geometry, geom2 geometry) 696 RETURNS bool 697 AS 'MODULE_PATHNAME', 'gserialized_right_2d' 698 LANGUAGE 'c' IMMUTABLE STRICT _PARALLEL; 699 700-- Availability: 0.1.0 701CREATE OPERATOR >> ( 702 LEFTARG = geometry, RIGHTARG = geometry, PROCEDURE = geometry_right, 703 COMMUTATOR = '<<', 704 RESTRICT = positionsel, JOIN = positionjoinsel 705); 706 707-- Availability: 2.0.0 708CREATE OR REPLACE FUNCTION geometry_overabove(geom1 geometry, geom2 geometry) 709 RETURNS bool 710 AS 'MODULE_PATHNAME', 'gserialized_overabove_2d' 711 LANGUAGE 'c' IMMUTABLE STRICT _PARALLEL; 712 713-- Availability: 0.1.0 714CREATE OPERATOR |&> ( 715 LEFTARG = geometry, RIGHTARG = geometry, PROCEDURE = geometry_overabove, 716 RESTRICT = positionsel, JOIN = positionjoinsel 717); 718 719-- Availability: 2.0.0 720CREATE OR REPLACE FUNCTION geometry_above(geom1 geometry, geom2 geometry) 721 RETURNS bool 722 AS 'MODULE_PATHNAME', 'gserialized_above_2d' 723 LANGUAGE 'c' IMMUTABLE STRICT _PARALLEL; 724 725-- Availability: 0.1.0 726CREATE OPERATOR |>> ( 727 LEFTARG = geometry, RIGHTARG = geometry, PROCEDURE = geometry_above, 728 COMMUTATOR = '<<|', 729 RESTRICT = positionsel, JOIN = positionjoinsel 730); 731 732-- Availability: 2.0.0 733CREATE OPERATOR CLASS gist_geometry_ops_2d 734 DEFAULT FOR TYPE geometry USING GIST AS 735 STORAGE box2df, 736 OPERATOR 1 << , 737 OPERATOR 2 &< , 738 OPERATOR 3 && , 739 OPERATOR 4 &> , 740 OPERATOR 5 >> , 741 OPERATOR 6 ~= , 742 OPERATOR 7 ~ , 743 OPERATOR 8 @ , 744 OPERATOR 9 &<| , 745 OPERATOR 10 <<| , 746 OPERATOR 11 |>> , 747 OPERATOR 12 |&> , 748 OPERATOR 13 <-> FOR ORDER BY pg_catalog.float_ops, 749 OPERATOR 14 <#> FOR ORDER BY pg_catalog.float_ops, 750 FUNCTION 8 geometry_gist_distance_2d (internal, geometry, int4), 751 FUNCTION 1 geometry_gist_consistent_2d (internal, geometry, int4), 752 FUNCTION 2 geometry_gist_union_2d (bytea, internal), 753 FUNCTION 3 geometry_gist_compress_2d (internal), 754 FUNCTION 4 geometry_gist_decompress_2d (internal), 755 FUNCTION 5 geometry_gist_penalty_2d (internal, internal, internal), 756 FUNCTION 6 geometry_gist_picksplit_2d (internal, internal), 757 FUNCTION 7 geometry_gist_same_2d (geom1 geometry, geom2 geometry, internal); 758 759----------------------------------------------------------------------------- 760-- GiST ND GEOMETRY-over-GSERIALIZED 761----------------------------------------------------------------------------- 762 763-- ---------- ---------- ---------- ---------- ---------- ---------- ---------- 764-- GiST Support Functions 765-- ---------- ---------- ---------- ---------- ---------- ---------- ---------- 766 767-- Availability: 2.0.0 768CREATE OR REPLACE FUNCTION geometry_gist_consistent_nd(internal,geometry,int4) 769 RETURNS bool 770 AS 'MODULE_PATHNAME' ,'gserialized_gist_consistent' 771 LANGUAGE 'c' _PARALLEL; 772 773-- Availability: 2.0.0 774CREATE OR REPLACE FUNCTION geometry_gist_compress_nd(internal) 775 RETURNS internal 776 AS 'MODULE_PATHNAME','gserialized_gist_compress' 777 LANGUAGE 'c' _PARALLEL; 778 779-- Availability: 2.0.0 780CREATE OR REPLACE FUNCTION geometry_gist_penalty_nd(internal,internal,internal) 781 RETURNS internal 782 AS 'MODULE_PATHNAME' ,'gserialized_gist_penalty' 783 LANGUAGE 'c' _PARALLEL; 784 785-- Availability: 2.0.0 786CREATE OR REPLACE FUNCTION geometry_gist_picksplit_nd(internal, internal) 787 RETURNS internal 788 AS 'MODULE_PATHNAME' ,'gserialized_gist_picksplit' 789 LANGUAGE 'c' _PARALLEL; 790 791-- Availability: 2.0.0 792CREATE OR REPLACE FUNCTION geometry_gist_union_nd(bytea, internal) 793 RETURNS internal 794 AS 'MODULE_PATHNAME' ,'gserialized_gist_union' 795 LANGUAGE 'c' _PARALLEL; 796 797-- Availability: 2.0.0 798CREATE OR REPLACE FUNCTION geometry_gist_same_nd(geometry, geometry, internal) 799 RETURNS internal 800 AS 'MODULE_PATHNAME' ,'gserialized_gist_same' 801 LANGUAGE 'c' _PARALLEL; 802 803-- Availability: 2.0.0 804CREATE OR REPLACE FUNCTION geometry_gist_decompress_nd(internal) 805 RETURNS internal 806 AS 'MODULE_PATHNAME' ,'gserialized_gist_decompress' 807 LANGUAGE 'c' _PARALLEL; 808 809-- ---------- ---------- ---------- ---------- ---------- ---------- ---------- 810-- N-D GEOMETRY Operators 811-- ---------- ---------- ---------- ---------- ---------- ---------- ---------- 812 813-- Availability: 2.0.0 814CREATE OR REPLACE FUNCTION geometry_overlaps_nd(geometry, geometry) 815 RETURNS boolean 816 AS 'MODULE_PATHNAME' ,'gserialized_overlaps' 817 LANGUAGE 'c' IMMUTABLE STRICT _PARALLEL; 818 819-- Availability: 2.0.0 820CREATE OPERATOR &&& ( 821 LEFTARG = geometry, RIGHTARG = geometry, PROCEDURE = geometry_overlaps_nd, 822 COMMUTATOR = '&&&', 823 RESTRICT = gserialized_gist_sel_nd, 824 JOIN = gserialized_gist_joinsel_nd 825); 826 827-- Availability: 2.2.0 828CREATE OR REPLACE FUNCTION geometry_distance_centroid_nd(geometry,geometry) 829 RETURNS float8 830 AS 'MODULE_PATHNAME', 'gserialized_distance_nd' 831 LANGUAGE 'c' IMMUTABLE STRICT _PARALLEL; 832 833-- Availability: 2.2.0 834CREATE OPERATOR <<->> ( 835 LEFTARG = geometry, RIGHTARG = geometry, 836 PROCEDURE = geometry_distance_centroid_nd, 837 COMMUTATOR = '<<->>' 838); 839 840-- 841-- This is for use with |=| operator, which does not directly use 842-- ST_DistanceCPA just in case it'll ever need to change behavior 843-- (operators definition cannot be altered) 844-- 845-- Availability: 2.2.0 846CREATE OR REPLACE FUNCTION geometry_distance_cpa(geometry, geometry) 847 RETURNS float8 848 AS 'MODULE_PATHNAME', 'ST_DistanceCPA' 849 LANGUAGE 'c' IMMUTABLE STRICT _PARALLEL; 850 851-- Availability: 2.2.0 852CREATE OPERATOR |=| ( 853 LEFTARG = geometry, RIGHTARG = geometry, 854 PROCEDURE = geometry_distance_cpa, 855 COMMUTATOR = '|=|' 856); 857 858-- Availability: 2.2.0 859CREATE OR REPLACE FUNCTION geometry_gist_distance_nd(internal,geometry,int4) 860 RETURNS float8 861 AS 'MODULE_PATHNAME', 'gserialized_gist_distance' 862 LANGUAGE 'c' _PARALLEL; 863 864-- Availability: 2.0.0 865CREATE OPERATOR CLASS gist_geometry_ops_nd 866 FOR TYPE geometry USING GIST AS 867 STORAGE gidx, 868 OPERATOR 3 &&& , 869-- OPERATOR 6 ~= , 870-- OPERATOR 7 ~ , 871-- OPERATOR 8 @ , 872 -- Availability: 2.2.0 873 OPERATOR 13 <<->> FOR ORDER BY pg_catalog.float_ops, 874#if POSTGIS_PGSQL_VERSION >= 95 875 -- Availability: 2.2.0 876 OPERATOR 20 |=| FOR ORDER BY pg_catalog.float_ops, 877#endif 878 -- Availability: 2.2.0 879 FUNCTION 8 geometry_gist_distance_nd (internal, geometry, int4), 880 FUNCTION 1 geometry_gist_consistent_nd (internal, geometry, int4), 881 FUNCTION 2 geometry_gist_union_nd (bytea, internal), 882 FUNCTION 3 geometry_gist_compress_nd (internal), 883 FUNCTION 4 geometry_gist_decompress_nd (internal), 884 FUNCTION 5 geometry_gist_penalty_nd (internal, internal, internal), 885 FUNCTION 6 geometry_gist_picksplit_nd (internal, internal), 886 FUNCTION 7 geometry_gist_same_nd (geometry, geometry, internal); 887 888-- Availability: 2.2.0 889CREATE OR REPLACE FUNCTION ST_ShiftLongitude(geometry) 890 RETURNS geometry 891 AS 'MODULE_PATHNAME', 'LWGEOM_longitude_shift' 892 LANGUAGE 'c' IMMUTABLE STRICT _PARALLEL; 893 894-- Availability: 2.3.0 895CREATE OR REPLACE FUNCTION ST_WrapX(geom geometry, wrap float8, move float8) 896 RETURNS geometry 897 AS 'MODULE_PATHNAME', 'ST_WrapX' 898 LANGUAGE 'c' IMMUTABLE STRICT _PARALLEL; 899 900-- Availability: 1.2.2 901-- Deprecation in 2.2.0 902CREATE OR REPLACE FUNCTION ST_Shift_Longitude(geometry) 903 RETURNS geometry AS 904 $$ SELECT @extschema@._postgis_deprecate('ST_Shift_Longitude', 'ST_ShiftLongitude', '2.2.0'); 905 SELECT @extschema@.ST_ShiftLongitude($1); 906 $$ 907 LANGUAGE 'sql' IMMUTABLE STRICT _PARALLEL; 908 909----------------------------------------------------------------------------- 910-- BOX3D FUNCTIONS 911----------------------------------------------------------------------------- 912 913-- Availability: 1.2.2 914CREATE OR REPLACE FUNCTION ST_XMin(box3d) 915 RETURNS FLOAT8 916 AS 'MODULE_PATHNAME','BOX3D_xmin' 917 LANGUAGE 'c' IMMUTABLE STRICT _PARALLEL; 918 919-- Availability: 1.2.2 920CREATE OR REPLACE FUNCTION ST_YMin(box3d) 921 RETURNS FLOAT8 922 AS 'MODULE_PATHNAME','BOX3D_ymin' 923 LANGUAGE 'c' IMMUTABLE STRICT _PARALLEL; 924 925-- Availability: 1.2.2 926CREATE OR REPLACE FUNCTION ST_ZMin(box3d) 927 RETURNS FLOAT8 928 AS 'MODULE_PATHNAME','BOX3D_zmin' 929 LANGUAGE 'c' IMMUTABLE STRICT _PARALLEL; 930 931-- Availability: 1.2.2 932CREATE OR REPLACE FUNCTION ST_XMax(box3d) 933 RETURNS FLOAT8 934 AS 'MODULE_PATHNAME','BOX3D_xmax' 935 LANGUAGE 'c' IMMUTABLE STRICT _PARALLEL; 936 937-- Availability: 1.2.2 938CREATE OR REPLACE FUNCTION ST_YMax(box3d) 939 RETURNS FLOAT8 940 AS 'MODULE_PATHNAME','BOX3D_ymax' 941 LANGUAGE 'c' IMMUTABLE STRICT _PARALLEL; 942 943-- Availability: 1.2.2 944CREATE OR REPLACE FUNCTION ST_ZMax(box3d) 945 RETURNS FLOAT8 946 AS 'MODULE_PATHNAME','BOX3D_zmax' 947 LANGUAGE 'c' IMMUTABLE STRICT _PARALLEL; 948 949----------------------------------------------------------------------------- 950-- BOX2D FUNCTIONS 951----------------------------------------------------------------------------- 952 953-- Availability: 1.2.2 954CREATE OR REPLACE FUNCTION ST_Expand(box2d,float8) 955 RETURNS box2d 956 AS 'MODULE_PATHNAME', 'BOX2D_expand' 957 LANGUAGE 'c' IMMUTABLE STRICT _PARALLEL; 958 959-- Availability: 2.3.0 960CREATE OR REPLACE FUNCTION ST_Expand(box box2d, dx float8, dy float8) 961 RETURNS box2d 962 AS 'MODULE_PATHNAME', 'BOX2D_expand' 963 LANGUAGE 'c' IMMUTABLE STRICT _PARALLEL; 964 965-- Availability: 1.5.0 966CREATE OR REPLACE FUNCTION postgis_getbbox(geometry) 967 RETURNS box2d 968 AS 'MODULE_PATHNAME','LWGEOM_to_BOX2DF' 969 LANGUAGE 'c' IMMUTABLE STRICT _PARALLEL; 970 971-- Availability: 1.2.2 972CREATE OR REPLACE FUNCTION ST_MakeBox2d(geom1 geometry, geom2 geometry) 973 RETURNS box2d 974 AS 'MODULE_PATHNAME', 'BOX2D_construct' 975 LANGUAGE 'c' IMMUTABLE STRICT _PARALLEL; 976 977----------------------------------------------------------------------- 978-- ST_ESTIMATED_EXTENT( <schema name>, <table name>, <column name> ) 979----------------------------------------------------------------------- 980 981-- Availability: 2.3.0 982CREATE OR REPLACE FUNCTION ST_EstimatedExtent(text,text,text,boolean) RETURNS box2d AS 983 'MODULE_PATHNAME', 'gserialized_estimated_extent' 984 LANGUAGE 'c' IMMUTABLE STRICT SECURITY DEFINER; 985 986-- Availability: 2.1.0 987CREATE OR REPLACE FUNCTION ST_EstimatedExtent(text,text,text) RETURNS box2d AS 988 'MODULE_PATHNAME', 'gserialized_estimated_extent' 989 LANGUAGE 'c' IMMUTABLE STRICT SECURITY DEFINER; 990 991-- Availability: 1.2.2 992-- Deprecation in 2.1.0 993CREATE OR REPLACE FUNCTION ST_estimated_extent(text,text,text) RETURNS box2d AS 994 $$ SELECT @extschema@._postgis_deprecate('ST_Estimated_Extent', 'ST_EstimatedExtent', '2.1.0'); 995 -- We use security invoker instead of security definer 996 -- to prevent malicious injection of a different same named function 997 SELECT @extschema@.ST_EstimatedExtent($1, $2, $3); 998 $$ 999 LANGUAGE 'sql' IMMUTABLE STRICT SECURITY INVOKER; 1000 1001----------------------------------------------------------------------- 1002-- ST_ESTIMATED_EXTENT( <table name>, <column name> ) 1003----------------------------------------------------------------------- 1004 1005-- Availability: 2.1.0 1006CREATE OR REPLACE FUNCTION ST_EstimatedExtent(text,text) RETURNS box2d AS 1007 'MODULE_PATHNAME', 'gserialized_estimated_extent' 1008 LANGUAGE 'c' IMMUTABLE STRICT SECURITY DEFINER; 1009 1010-- Availability: 1.2.2 1011-- Deprecation in 2.1.0 1012CREATE OR REPLACE FUNCTION ST_estimated_extent(text,text) RETURNS box2d AS 1013 $$ SELECT @extschema@._postgis_deprecate('ST_Estimated_Extent', 'ST_EstimatedExtent', '2.1.0'); 1014 -- We use security invoker instead of security definer 1015 -- to prevent malicious injection of a same named different function 1016 -- that would be run under elevated permissions 1017 SELECT @extschema@.ST_EstimatedExtent($1, $2); 1018 $$ 1019 LANGUAGE 'sql' IMMUTABLE STRICT SECURITY INVOKER; 1020 1021----------------------------------------------------------------------- 1022-- FIND_EXTENT( <schema name>, <table name>, <column name> ) 1023----------------------------------------------------------------------- 1024 1025-- Availability: 2.2.0 1026CREATE OR REPLACE FUNCTION ST_FindExtent(text,text,text) RETURNS box2d AS 1027$$ 1028DECLARE 1029 schemaname alias for $1; 1030 tablename alias for $2; 1031 columnname alias for $3; 1032 myrec RECORD; 1033BEGIN 1034 FOR myrec IN EXECUTE 'SELECT @extschema@.ST_Extent("' || columnname || '") As extent FROM "' || schemaname || '"."' || tablename || '"' LOOP 1035 return myrec.extent; 1036 END LOOP; 1037END; 1038$$ 1039LANGUAGE 'plpgsql' IMMUTABLE STRICT _PARALLEL; 1040 1041-- Availability: 1.2.2 1042-- Deprecation in 2.2.0 1043CREATE OR REPLACE FUNCTION ST_find_extent(text,text,text) RETURNS box2d AS 1044 $$ SELECT @extschema@._postgis_deprecate('ST_Find_Extent', 'ST_FindExtent', '2.2.0'); 1045 SELECT @extschema@.ST_FindExtent($1,$2,$3); 1046 $$ 1047 LANGUAGE 'sql' IMMUTABLE STRICT _PARALLEL; 1048 1049----------------------------------------------------------------------- 1050-- FIND_EXTENT( <table name>, <column name> ) 1051----------------------------------------------------------------------- 1052 1053-- Availability: 2.2.0 1054CREATE OR REPLACE FUNCTION ST_FindExtent(text,text) RETURNS box2d AS 1055$$ 1056DECLARE 1057 tablename alias for $1; 1058 columnname alias for $2; 1059 myrec RECORD; 1060 1061BEGIN 1062 FOR myrec IN EXECUTE 'SELECT @extschema@.ST_Extent("' || columnname || '") As extent FROM "' || tablename || '"' LOOP 1063 return myrec.extent; 1064 END LOOP; 1065END; 1066$$ 1067LANGUAGE 'plpgsql' IMMUTABLE STRICT _PARALLEL; 1068 1069-- Availability: 1.2.2 1070-- Deprecation in 2.2.0 1071CREATE OR REPLACE FUNCTION ST_find_extent(text,text) RETURNS box2d AS 1072 $$ SELECT @extschema@._postgis_deprecate('ST_Find_Extent', 'ST_FindExtent', '2.2.0'); 1073 SELECT @extschema@.ST_FindExtent($1,$2); 1074 $$ 1075 LANGUAGE 'sql' IMMUTABLE STRICT _PARALLEL; 1076 1077------------------------------------------- 1078-- other lwgeom functions 1079------------------------------------------- 1080-- Availability: 1.5.0 1081CREATE OR REPLACE FUNCTION postgis_addbbox(geometry) 1082 RETURNS geometry 1083 AS 'MODULE_PATHNAME','LWGEOM_addBBOX' 1084 LANGUAGE 'c' IMMUTABLE STRICT _PARALLEL; 1085 1086-- Availability: 1.5.0 1087CREATE OR REPLACE FUNCTION postgis_dropbbox(geometry) 1088 RETURNS geometry 1089 AS 'MODULE_PATHNAME','LWGEOM_dropBBOX' 1090 LANGUAGE 'c' IMMUTABLE STRICT _PARALLEL; 1091 1092-- Availability: 1.5.0 1093CREATE OR REPLACE FUNCTION postgis_hasbbox(geometry) 1094 RETURNS bool 1095 AS 'MODULE_PATHNAME', 'LWGEOM_hasBBOX' 1096 LANGUAGE 'c' IMMUTABLE STRICT _PARALLEL; 1097 1098-- Availability: 2.5.0 1099CREATE OR REPLACE FUNCTION ST_QuantizeCoordinates(g geometry, prec_x int, prec_y int DEFAULT NULL, prec_z int DEFAULT NULL, prec_m int DEFAULT NULL) 1100 RETURNS geometry 1101 AS 'MODULE_PATHNAME', 'ST_QuantizeCoordinates' 1102 LANGUAGE 'c' IMMUTABLE _PARALLEL 1103 COST 10; 1104 1105------------------------------------------------------------------------ 1106-- DEBUG 1107------------------------------------------------------------------------ 1108 1109-- Availability: 2.2.0 1110CREATE OR REPLACE FUNCTION ST_MemSize(geometry) 1111 RETURNS int4 1112 AS 'MODULE_PATHNAME', 'LWGEOM_mem_size' 1113 LANGUAGE 'c' IMMUTABLE STRICT _PARALLEL 1114 COST 5; 1115 1116-- Availability: 1.2.2 1117-- Deprecation in 2.2.0 1118CREATE OR REPLACE FUNCTION ST_mem_size(geometry) 1119 RETURNS int4 AS 1120 $$ SELECT @extschema@._postgis_deprecate('ST_Mem_Size', 'ST_MemSize', '2.2.0'); 1121 SELECT @extschema@.ST_MemSize($1); 1122 $$ 1123 LANGUAGE 'sql' IMMUTABLE STRICT SECURITY INVOKER; 1124 1125-- Availability: 1.2.2 1126CREATE OR REPLACE FUNCTION ST_summary(geometry) 1127 RETURNS text 1128 AS 'MODULE_PATHNAME', 'LWGEOM_summary' 1129 LANGUAGE 'c' IMMUTABLE STRICT _PARALLEL 1130 COST 25; 1131 1132-- Availability: 1.2.2 1133CREATE OR REPLACE FUNCTION ST_Npoints(geometry) 1134 RETURNS int4 1135 AS 'MODULE_PATHNAME', 'LWGEOM_npoints' 1136 LANGUAGE 'c' IMMUTABLE STRICT _PARALLEL 1137 COST 10; 1138 1139-- Availability: 1.2.2 1140CREATE OR REPLACE FUNCTION ST_nrings(geometry) 1141 RETURNS int4 1142 AS 'MODULE_PATHNAME', 'LWGEOM_nrings' 1143 LANGUAGE 'c' IMMUTABLE STRICT _PARALLEL; 1144 1145------------------------------------------------------------------------ 1146-- Measures 1147------------------------------------------------------------------------ 1148-- Availability: 2.0.0 1149CREATE OR REPLACE FUNCTION ST_3DLength(geometry) 1150 RETURNS FLOAT8 1151 AS 'MODULE_PATHNAME', 'LWGEOM_length_linestring' 1152 LANGUAGE 'c' IMMUTABLE STRICT _PARALLEL 1153 COST 20; 1154 1155-- Availability: 1.2.2 1156CREATE OR REPLACE FUNCTION ST_Length2d(geometry) 1157 RETURNS FLOAT8 1158 AS 'MODULE_PATHNAME', 'LWGEOM_length2d_linestring' 1159 LANGUAGE 'c' IMMUTABLE STRICT _PARALLEL 1160 COST 10; 1161 1162-- PostGIS equivalent function: length2d(geometry) 1163CREATE OR REPLACE FUNCTION ST_Length(geometry) 1164 RETURNS FLOAT8 1165 AS 'MODULE_PATHNAME', 'LWGEOM_length2d_linestring' 1166 LANGUAGE 'c' IMMUTABLE STRICT _PARALLEL 1167 COST 10; 1168 1169-- Availability in 2.2.0 1170CREATE OR REPLACE FUNCTION ST_LengthSpheroid(geometry, spheroid) 1171 RETURNS FLOAT8 1172 AS 'MODULE_PATHNAME','LWGEOM_length_ellipsoid_linestring' 1173 LANGUAGE 'c' IMMUTABLE STRICT _PARALLEL 1174 COST 500; 1175 1176-- this is a fake (for back-compatibility) 1177-- uses 3d if 3d is available, 2d otherwise 1178-- Availability: 2.0.0 1179-- Deprecation in 2.2.0 1180CREATE OR REPLACE FUNCTION ST_3DLength_spheroid(geometry, spheroid) 1181 RETURNS FLOAT8 AS 1182 $$ SELECT @extschema@._postgis_deprecate('ST_3DLength_Spheroid', 'ST_LengthSpheroid', '2.2.0'); 1183 SELECT @extschema@.ST_LengthSpheroid($1,$2); 1184 $$ 1185 LANGUAGE 'sql' IMMUTABLE STRICT 1186 COST 100; 1187 1188-- Availability: 1.2.2 1189-- Deprecation in 2.2.0 1190CREATE OR REPLACE FUNCTION ST_length_spheroid(geometry, spheroid) 1191 RETURNS FLOAT8 AS 1192 $$ SELECT @extschema@._postgis_deprecate('ST_Length_Spheroid', 'ST_LengthSpheroid', '2.2.0'); 1193 SELECT @extschema@.ST_LengthSpheroid($1,$2); 1194 $$ 1195 LANGUAGE 'sql' IMMUTABLE STRICT _PARALLEL; 1196 1197-- Availability: 2.2.0 1198CREATE OR REPLACE FUNCTION ST_Length2DSpheroid(geometry, spheroid) 1199 RETURNS FLOAT8 1200 AS 'MODULE_PATHNAME','LWGEOM_length2d_ellipsoid' 1201 LANGUAGE 'c' IMMUTABLE STRICT _PARALLEL 1202 COST 500; 1203 1204-- Availability: 1.2.2 1205-- Deprecation in 2.2.0 1206CREATE OR REPLACE FUNCTION ST_length2d_spheroid(geometry, spheroid) 1207 RETURNS FLOAT8 AS 1208 $$ SELECT @extschema@._postgis_deprecate('ST_Length2D_Spheroid', 'ST_Length2DSpheroid', '2.2.0'); 1209 SELECT @extschema@.ST_Length2DSpheroid($1,$2); 1210 $$ 1211 LANGUAGE 'sql' IMMUTABLE STRICT _PARALLEL; 1212 1213-- Availability: 2.0.0 1214CREATE OR REPLACE FUNCTION ST_3DPerimeter(geometry) 1215 RETURNS FLOAT8 1216 AS 'MODULE_PATHNAME', 'LWGEOM_perimeter_poly' 1217 LANGUAGE 'c' IMMUTABLE STRICT _PARALLEL 1218 COST 10; 1219 1220-- Availability: 1.2.2 1221CREATE OR REPLACE FUNCTION ST_perimeter2d(geometry) 1222 RETURNS FLOAT8 1223 AS 'MODULE_PATHNAME', 'LWGEOM_perimeter2d_poly' 1224 LANGUAGE 'c' IMMUTABLE STRICT _PARALLEL 1225 COST 10; 1226 1227-- PostGIS equivalent function: perimeter2d(geometry) 1228CREATE OR REPLACE FUNCTION ST_Perimeter(geometry) 1229 RETURNS FLOAT8 1230 AS 'MODULE_PATHNAME', 'LWGEOM_perimeter2d_poly' 1231 LANGUAGE 'c' IMMUTABLE STRICT _PARALLEL 1232 COST 10; 1233 1234-- Availability: 1.2.2 1235-- Deprecation in 1.3.4 1236CREATE OR REPLACE FUNCTION ST_area2d(geometry) 1237 RETURNS FLOAT8 1238 AS 'MODULE_PATHNAME', 'LWGEOM_area_polygon' 1239 LANGUAGE 'c' IMMUTABLE STRICT _PARALLEL 1240 COST 10; 1241 1242-- PostGIS equivalent function: area(geometry) 1243CREATE OR REPLACE FUNCTION ST_Area(geometry) 1244 RETURNS FLOAT8 1245 AS 'MODULE_PATHNAME','area' 1246 LANGUAGE 'c' IMMUTABLE STRICT _PARALLEL 1247 COST 10; 1248 1249-- Availability: 2.4.0 1250CREATE OR REPLACE FUNCTION ST_IsPolygonCW(geometry) 1251 RETURNS boolean 1252 AS 'MODULE_PATHNAME','ST_IsPolygonCW' 1253 LANGUAGE 'c' IMMUTABLE STRICT _PARALLEL 1254 COST 10; 1255 1256-- Availability: 2.4.0 1257CREATE OR REPLACE FUNCTION ST_IsPolygonCCW(geometry) 1258 RETURNS boolean 1259 AS 'MODULE_PATHNAME','ST_IsPolygonCCW' 1260 LANGUAGE 'c' IMMUTABLE STRICT _PARALLEL 1261 COST 10; 1262 1263-- Availability: 2.0.0 1264CREATE OR REPLACE FUNCTION ST_DistanceSpheroid(geom1 geometry, geom2 geometry,spheroid) 1265 RETURNS FLOAT8 1266 AS 'MODULE_PATHNAME','LWGEOM_distance_ellipsoid' 1267 LANGUAGE 'c' IMMUTABLE STRICT _PARALLEL 1268 COST 200; --upped this 1269 1270-- Availability: 1.2.2 1271-- Deprecation in 2.2.0 1272CREATE OR REPLACE FUNCTION ST_distance_spheroid(geom1 geometry, geom2 geometry,spheroid) 1273 RETURNS FLOAT8 AS 1274 $$ SELECT @extschema@._postgis_deprecate('ST_Distance_Spheroid', 'ST_DistanceSpheroid', '2.2.0'); 1275 SELECT @extschema@.ST_DistanceSpheroid($1,$2,$3); 1276 $$ 1277 LANGUAGE 'sql' IMMUTABLE STRICT _PARALLEL; 1278 1279-- Minimum distance. 2d only. 1280 1281-- PostGIS equivalent function: distance(geom1 geometry, geom2 geometry) 1282CREATE OR REPLACE FUNCTION ST_Distance(geom1 geometry, geom2 geometry) 1283 RETURNS float8 1284 AS 'MODULE_PATHNAME', 'distance' 1285 LANGUAGE 'c' IMMUTABLE STRICT _PARALLEL 1286 COST 25; --changed from 100 should be 1/5th to 1/10 spheroid 1287 1288-- Availability: 2.2.0 1289CREATE OR REPLACE FUNCTION ST_PointInsideCircle(geometry,float8,float8,float8) 1290 RETURNS bool 1291 AS 'MODULE_PATHNAME', 'LWGEOM_inside_circle_point' 1292 LANGUAGE 'c' IMMUTABLE STRICT _PARALLEL; 1293 1294-- Availability: 1.2.2 1295-- Deprecation in 2.2.0 1296CREATE OR REPLACE FUNCTION ST_point_inside_circle(geometry,float8,float8,float8) 1297 RETURNS bool AS 1298 $$ SELECT @extschema@._postgis_deprecate('ST_Point_Inside_Circle', 'ST_PointInsideCircle', '2.2.0'); 1299 SELECT @extschema@.ST_PointInsideCircle($1,$2,$3,$4); 1300 $$ 1301 LANGUAGE 'sql' IMMUTABLE STRICT _PARALLEL; 1302 1303-- Availability: 1.2.2 1304CREATE OR REPLACE FUNCTION ST_azimuth(geom1 geometry, geom2 geometry) 1305 RETURNS float8 1306 AS 'MODULE_PATHNAME', 'LWGEOM_azimuth' 1307 LANGUAGE 'c' IMMUTABLE STRICT _PARALLEL; 1308 1309-- Availability: 2.5.0 1310CREATE OR REPLACE FUNCTION ST_Angle(pt1 geometry, pt2 geometry, pt3 geometry, pt4 geometry default 'POINT EMPTY'::geometry) 1311 RETURNS float8 1312 AS 'MODULE_PATHNAME', 'LWGEOM_angle' 1313 LANGUAGE 'c' IMMUTABLE STRICT _PARALLEL; 1314 1315-- Availability: Future 1316-- CREATE OR REPLACE FUNCTION _ST_DistanceRectTree(g1 geometry, g2 geometry) 1317-- RETURNS float8 1318-- AS 'MODULE_PATHNAME', 'ST_DistanceRectTree' 1319-- LANGUAGE 'c' IMMUTABLE STRICT _PARALLEL; 1320 1321-- Availability: Future 1322-- CREATE OR REPLACE FUNCTION _ST_DistanceRectTreeCached(g1 geometry, g2 geometry) 1323-- RETURNS float8 1324-- AS 'MODULE_PATHNAME', 'ST_DistanceRectTreeCached' 1325-- LANGUAGE 'c' IMMUTABLE STRICT _PARALLEL; 1326 1327------------------------------------------------------------------------ 1328-- MISC 1329------------------------------------------------------------------------ 1330 1331-- Availability: 2.1.0 1332CREATE OR REPLACE FUNCTION ST_Force2D(geometry) 1333 RETURNS geometry 1334 AS 'MODULE_PATHNAME', 'LWGEOM_force_2d' 1335 LANGUAGE 'c' IMMUTABLE STRICT _PARALLEL 1336 COST 1; -- reset cost, see #3675 1337 1338-- Availability: 1.2.2 1339-- Deprecation in 2.1.0 1340CREATE OR REPLACE FUNCTION ST_force_2d(geometry) 1341 RETURNS geometry AS 1342 $$ SELECT @extschema@._postgis_deprecate('ST_Force_2d', 'ST_Force2D', '2.1.0'); 1343 SELECT @extschema@.ST_Force2D($1); 1344 $$ 1345 LANGUAGE 'sql' IMMUTABLE STRICT _PARALLEL; 1346 1347-- Availability: 2.1.0 1348CREATE OR REPLACE FUNCTION ST_Force3DZ(geometry) 1349 RETURNS geometry 1350 AS 'MODULE_PATHNAME', 'LWGEOM_force_3dz' 1351 LANGUAGE 'c' IMMUTABLE STRICT _PARALLEL 1352 COST 1; -- reset cost, see #3675 1353 1354-- Availability: 1.2.2 1355-- Deprecation in 2.1.0 1356CREATE OR REPLACE FUNCTION ST_force_3dz(geometry) 1357 RETURNS geometry AS 1358 $$ SELECT @extschema@._postgis_deprecate('ST_Force_3dz', 'ST_Force3DZ', '2.1.0'); 1359 SELECT @extschema@.ST_Force3DZ($1); 1360 $$ 1361 LANGUAGE 'sql' IMMUTABLE STRICT _PARALLEL; 1362 1363-- Availability: 2.1.0 1364CREATE OR REPLACE FUNCTION ST_Force3D(geometry) 1365 RETURNS geometry 1366 AS 'MODULE_PATHNAME', 'LWGEOM_force_3dz' 1367 LANGUAGE 'c' IMMUTABLE STRICT _PARALLEL 1368 COST 1; -- reset cost, see #3675 1369 1370-- Availability: 1.2.2 1371-- Deprecation in 2.1.0 1372CREATE OR REPLACE FUNCTION ST_force_3d(geometry) 1373 RETURNS geometry AS 1374 $$ SELECT @extschema@._postgis_deprecate('ST_Force_3d', 'ST_Force3D', '2.1.0'); 1375 SELECT @extschema@.ST_Force3D($1); 1376 $$ 1377 LANGUAGE 'sql' IMMUTABLE STRICT _PARALLEL; 1378 1379-- Availability: 2.1.0 1380CREATE OR REPLACE FUNCTION ST_Force3DM(geometry) 1381 RETURNS geometry 1382 AS 'MODULE_PATHNAME', 'LWGEOM_force_3dm' 1383 LANGUAGE 'c' IMMUTABLE STRICT _PARALLEL 1384 COST 1; -- reset cost, see #3675 1385 1386-- Availability: 1.2.2 1387-- Deprecation in 2.1.0 1388CREATE OR REPLACE FUNCTION ST_force_3dm(geometry) 1389 RETURNS geometry AS 1390 $$ SELECT @extschema@._postgis_deprecate('ST_Force_3dm', 'ST_Force3DM', '2.1.0'); 1391 SELECT @extschema@.ST_Force3DM($1); 1392 $$ 1393 LANGUAGE 'sql' IMMUTABLE STRICT _PARALLEL; 1394 1395-- Availability: 2.1.0 1396CREATE OR REPLACE FUNCTION ST_Force4D(geometry) 1397 RETURNS geometry 1398 AS 'MODULE_PATHNAME', 'LWGEOM_force_4d' 1399 LANGUAGE 'c' IMMUTABLE STRICT _PARALLEL 1400 COST 1; -- reset cost, see #3675 1401 1402-- Availability: 1.2.2 1403-- Deprecation in 2.1.0 1404CREATE OR REPLACE FUNCTION ST_force_4d(geometry) 1405 RETURNS geometry AS 1406 $$ SELECT @extschema@._postgis_deprecate('ST_Force_4d', 'ST_Force4D', '2.1.0'); 1407 SELECT @extschema@.ST_Force4D($1); 1408 $$ 1409 LANGUAGE 'sql' IMMUTABLE STRICT _PARALLEL; 1410 1411-- Availability: 2.1.0 1412CREATE OR REPLACE FUNCTION ST_ForceCollection(geometry) 1413 RETURNS geometry 1414 AS 'MODULE_PATHNAME', 'LWGEOM_force_collection' 1415 LANGUAGE 'c' IMMUTABLE STRICT _PARALLEL 1416 COST 1; -- reset cost, see #3675 1417 1418-- Availability: 1.2.2 1419-- Deprecation in 2.1.0 1420CREATE OR REPLACE FUNCTION ST_force_collection(geometry) 1421 RETURNS geometry AS 1422 $$ SELECT @extschema@._postgis_deprecate('ST_Force_Collection', 'ST_ForceCollection', '2.1.0'); 1423 SELECT @extschema@.ST_ForceCollection($1); 1424 $$ 1425 LANGUAGE 'sql' IMMUTABLE STRICT _PARALLEL; 1426 1427-- Availability: 1.5.0 1428CREATE OR REPLACE FUNCTION ST_CollectionExtract(geometry, integer) 1429 RETURNS geometry 1430 AS 'MODULE_PATHNAME', 'ST_CollectionExtract' 1431 LANGUAGE 'c' IMMUTABLE STRICT _PARALLEL; 1432 1433-- Availability: 2.0.0 1434CREATE OR REPLACE FUNCTION ST_CollectionHomogenize(geometry) 1435 RETURNS geometry 1436 AS 'MODULE_PATHNAME', 'ST_CollectionHomogenize' 1437 LANGUAGE 'c' IMMUTABLE STRICT _PARALLEL; 1438 1439-- Availability: 1.2.2 1440CREATE OR REPLACE FUNCTION ST_Multi(geometry) 1441 RETURNS geometry 1442 AS 'MODULE_PATHNAME', 'LWGEOM_force_multi' 1443 LANGUAGE 'c' IMMUTABLE STRICT _PARALLEL 1444 COST 1; -- reset cost, see #3675 1445 1446-- Availability: 2.2.0 1447CREATE OR REPLACE FUNCTION ST_ForceCurve(geometry) 1448 RETURNS geometry 1449 AS 'MODULE_PATHNAME', 'LWGEOM_force_curve' 1450 LANGUAGE 'c' IMMUTABLE STRICT _PARALLEL; 1451 1452-- Availability: 2.1.0 1453CREATE OR REPLACE FUNCTION ST_ForceSFS(geometry) 1454 RETURNS geometry 1455 AS 'MODULE_PATHNAME', 'LWGEOM_force_sfs' 1456 LANGUAGE 'c' IMMUTABLE STRICT _PARALLEL; 1457 1458-- Availability: 2.1.0 1459CREATE OR REPLACE FUNCTION ST_ForceSFS(geometry, version text) 1460 RETURNS geometry 1461 AS 'MODULE_PATHNAME', 'LWGEOM_force_sfs' 1462 LANGUAGE 'c' IMMUTABLE STRICT _PARALLEL 1463 COST 1; -- reset cost, see #3675 1464 1465-- Availability: 1.2.2 1466CREATE OR REPLACE FUNCTION ST_Expand(box3d,float8) 1467 RETURNS box3d 1468 AS 'MODULE_PATHNAME', 'BOX3D_expand' 1469 LANGUAGE 'c' IMMUTABLE STRICT _PARALLEL 1470 COST 1; -- reset cost, see #3675 1471 1472-- Availability: 2.3.0 1473CREATE OR REPLACE FUNCTION ST_Expand(box box3d, dx float8, dy float8, dz float8 DEFAULT 0) 1474 RETURNS box3d 1475 AS 'MODULE_PATHNAME', 'BOX3D_expand' 1476 LANGUAGE 'c' IMMUTABLE STRICT _PARALLEL 1477 COST 1; -- reset cost, see #3675 1478 1479-- Availability: 1.2.2 1480CREATE OR REPLACE FUNCTION ST_Expand(geometry,float8) 1481 RETURNS geometry 1482 AS 'MODULE_PATHNAME', 'LWGEOM_expand' 1483 LANGUAGE 'c' IMMUTABLE STRICT _PARALLEL 1484 COST 1; -- reset cost, see #3675 1485 1486-- Availability: 2.3.0 1487CREATE OR REPLACE FUNCTION ST_Expand(geom geometry, dx float8, dy float8, dz float8 DEFAULT 0, dm float8 DEFAULT 0) 1488 RETURNS geometry 1489 AS 'MODULE_PATHNAME', 'LWGEOM_expand' 1490 LANGUAGE 'c' IMMUTABLE STRICT _PARALLEL 1491 COST 1; -- reset cost, see #3675 1492 1493-- PostGIS equivalent function: envelope(geometry) 1494CREATE OR REPLACE FUNCTION ST_Envelope(geometry) 1495 RETURNS geometry 1496 AS 'MODULE_PATHNAME', 'LWGEOM_envelope' 1497 LANGUAGE 'c' IMMUTABLE STRICT _PARALLEL 1498 COST 1; -- reset cost, see #3675 1499 1500-- Availability: 2.2.0 1501CREATE OR REPLACE FUNCTION ST_BoundingDiagonal(geom geometry, fits boolean DEFAULT false) 1502 RETURNS geometry 1503 AS 'MODULE_PATHNAME', 'ST_BoundingDiagonal' 1504 LANGUAGE 'c' IMMUTABLE STRICT _PARALLEL; 1505 1506-- Availability: 1.2.2 1507CREATE OR REPLACE FUNCTION ST_Reverse(geometry) 1508 RETURNS geometry 1509 AS 'MODULE_PATHNAME', 'LWGEOM_reverse' 1510 LANGUAGE 'c' IMMUTABLE STRICT _PARALLEL 1511 COST 1; -- reset cost, see #3675 1512 1513-- Availability: 2.4.0 1514CREATE OR REPLACE FUNCTION ST_ForcePolygonCW(geometry) 1515 RETURNS geometry 1516 AS 'MODULE_PATHNAME', 'LWGEOM_force_clockwise_poly' 1517 LANGUAGE 'c' IMMUTABLE STRICT _PARALLEL 1518 COST 15; 1519 1520-- Availability: 2.4.0 1521CREATE OR REPLACE FUNCTION ST_ForcePolygonCCW(geometry) 1522 RETURNS geometry 1523 AS $$ SELECT @extschema@.ST_Reverse(@extschema@.ST_ForcePolygonCW($1)) $$ 1524 LANGUAGE SQL IMMUTABLE STRICT _PARALLEL 1525 COST 15; 1526 1527-- Availability: 1.2.2 1528CREATE OR REPLACE FUNCTION ST_ForceRHR(geometry) 1529 RETURNS geometry 1530 AS 'MODULE_PATHNAME', 'LWGEOM_force_clockwise_poly' 1531 LANGUAGE 'c' IMMUTABLE STRICT _PARALLEL 1532 COST 1; -- reset cost, see #3675 1533 1534-- Availability: 1.5.0 1535CREATE OR REPLACE FUNCTION postgis_noop(geometry) 1536 RETURNS geometry 1537 AS 'MODULE_PATHNAME', 'LWGEOM_noop' 1538 LANGUAGE 'c' IMMUTABLE STRICT _PARALLEL 1539 COST 10; 1540 1541-- Availability: 2.3.0 1542CREATE OR REPLACE FUNCTION ST_Normalize(geom geometry) 1543 RETURNS geometry 1544 AS 'MODULE_PATHNAME', 'ST_Normalize' 1545 LANGUAGE 'c' IMMUTABLE STRICT _PARALLEL 1546 COST 1; -- reset cost, see #3675 1547 1548-- Deprecation in 1.5.0 1549CREATE OR REPLACE FUNCTION ST_zmflag(geometry) 1550 RETURNS smallint 1551 AS 'MODULE_PATHNAME', 'LWGEOM_zmflag' 1552 LANGUAGE 'c' IMMUTABLE STRICT _PARALLEL 1553 COST 5; 1554 1555-- Availability: 1.2.2 1556CREATE OR REPLACE FUNCTION ST_NDims(geometry) 1557 RETURNS smallint 1558 AS 'MODULE_PATHNAME', 'LWGEOM_ndims' 1559 LANGUAGE 'c' IMMUTABLE STRICT _PARALLEL 1560 COST 5; 1561 1562-- Availability: 1.2.2 1563CREATE OR REPLACE FUNCTION ST_AsEWKT(geometry) 1564 RETURNS TEXT 1565 AS 'MODULE_PATHNAME','LWGEOM_asEWKT' 1566 LANGUAGE 'c' IMMUTABLE STRICT _PARALLEL 1567 COST 750; --this looks suspicious, requires recheck 1568 1569-- Availability: 2.2.0 1570CREATE OR REPLACE FUNCTION ST_AsTWKB(geom geometry, prec int4 default NULL, prec_z int4 default NULL, prec_m int4 default NULL, with_sizes boolean default NULL, with_boxes boolean default NULL) 1571 RETURNS bytea 1572 AS 'MODULE_PATHNAME','TWKBFromLWGEOM' 1573 LANGUAGE 'c' IMMUTABLE _PARALLEL; 1574 1575-- Availability: 2.2.0 1576CREATE OR REPLACE FUNCTION ST_AsTWKB(geom geometry[], ids bigint[], prec int4 default NULL, prec_z int4 default NULL, prec_m int4 default NULL, with_sizes boolean default NULL, with_boxes boolean default NULL) 1577 RETURNS bytea 1578 AS 'MODULE_PATHNAME','TWKBFromLWGEOMArray' 1579 LANGUAGE 'c' IMMUTABLE _PARALLEL; 1580 1581-- Availability: 1.2.2 1582CREATE OR REPLACE FUNCTION ST_AsEWKB(geometry) 1583 RETURNS BYTEA 1584 AS 'MODULE_PATHNAME','WKBFromLWGEOM' 1585 LANGUAGE 'c' IMMUTABLE STRICT _PARALLEL 1586 COST 10; 1587 1588-- Availability: 1.2.2 1589CREATE OR REPLACE FUNCTION ST_AsHEXEWKB(geometry) 1590 RETURNS TEXT 1591 AS 'MODULE_PATHNAME','LWGEOM_asHEXEWKB' 1592 LANGUAGE 'c' IMMUTABLE STRICT _PARALLEL 1593 COST 25; 1594 1595-- Availability: 1.2.2 1596CREATE OR REPLACE FUNCTION ST_AsHEXEWKB(geometry, text) 1597 RETURNS TEXT 1598 AS 'MODULE_PATHNAME','LWGEOM_asHEXEWKB' 1599 LANGUAGE 'c' IMMUTABLE STRICT _PARALLEL 1600 COST 25; 1601 1602-- Availability: 1.2.2 1603CREATE OR REPLACE FUNCTION ST_AsEWKB(geometry,text) 1604 RETURNS bytea 1605 AS 'MODULE_PATHNAME','WKBFromLWGEOM' 1606 LANGUAGE 'c' IMMUTABLE STRICT _PARALLEL 1607 COST 10; 1608 1609-- Availability: 2.0.0 1610CREATE OR REPLACE FUNCTION ST_AsLatLonText(geom geometry, tmpl text DEFAULT '') 1611 RETURNS text 1612 AS 'MODULE_PATHNAME','LWGEOM_to_latlon' 1613 LANGUAGE 'c' IMMUTABLE STRICT _PARALLEL; 1614 1615-- Deprecation in 1.2.3 1616CREATE OR REPLACE FUNCTION GeomFromEWKB(bytea) 1617 RETURNS geometry 1618 AS 'MODULE_PATHNAME','LWGEOMFromEWKB' 1619 LANGUAGE 'c' IMMUTABLE STRICT _PARALLEL; 1620 1621-- Availability: 1.2.2 1622CREATE OR REPLACE FUNCTION ST_GeomFromEWKB(bytea) 1623 RETURNS geometry 1624 AS 'MODULE_PATHNAME','LWGEOMFromEWKB' 1625 LANGUAGE 'c' IMMUTABLE STRICT _PARALLEL; 1626 1627-- Availability: 2.2 1628CREATE OR REPLACE FUNCTION ST_GeomFromTWKB(bytea) 1629 RETURNS geometry 1630 AS 'MODULE_PATHNAME','LWGEOMFromTWKB' 1631 LANGUAGE 'c' IMMUTABLE STRICT _PARALLEL; 1632 1633-- Deprecation in 1.2.3 1634CREATE OR REPLACE FUNCTION GeomFromEWKT(text) 1635 RETURNS geometry 1636 AS 'MODULE_PATHNAME','parse_WKT_lwgeom' 1637 LANGUAGE 'c' IMMUTABLE STRICT _PARALLEL; 1638 1639-- Availability: 1.2.2 1640CREATE OR REPLACE FUNCTION ST_GeomFromEWKT(text) 1641 RETURNS geometry 1642 AS 'MODULE_PATHNAME','parse_WKT_lwgeom' 1643 LANGUAGE 'c' IMMUTABLE STRICT _PARALLEL; 1644 1645-- Availability: 1.5.0 1646CREATE OR REPLACE FUNCTION postgis_cache_bbox() 1647 RETURNS trigger 1648 AS 'MODULE_PATHNAME', 'cache_bbox' 1649 LANGUAGE 'c'; 1650 1651------------------------------------------------------------------------ 1652-- CONSTRUCTORS 1653------------------------------------------------------------------------ 1654 1655-- Availability: 1.2.2 1656CREATE OR REPLACE FUNCTION ST_MakePoint(float8, float8) 1657 RETURNS geometry 1658 AS 'MODULE_PATHNAME', 'LWGEOM_makepoint' 1659 LANGUAGE 'c' IMMUTABLE STRICT _PARALLEL; 1660 1661-- Availability: 1.2.2 1662CREATE OR REPLACE FUNCTION ST_MakePoint(float8, float8, float8) 1663 RETURNS geometry 1664 AS 'MODULE_PATHNAME', 'LWGEOM_makepoint' 1665 LANGUAGE 'c' IMMUTABLE STRICT _PARALLEL; 1666 1667-- Availability: 1.2.2 1668CREATE OR REPLACE FUNCTION ST_MakePoint(float8, float8, float8, float8) 1669 RETURNS geometry 1670 AS 'MODULE_PATHNAME', 'LWGEOM_makepoint' 1671 LANGUAGE 'c' IMMUTABLE STRICT _PARALLEL; 1672 1673-- Availability: 1.3.4 1674CREATE OR REPLACE FUNCTION ST_MakePointM(float8, float8, float8) 1675 RETURNS geometry 1676 AS 'MODULE_PATHNAME', 'LWGEOM_makepoint3dm' 1677 LANGUAGE 'c' IMMUTABLE STRICT _PARALLEL; 1678 1679-- Availability: 2.0.0 1680CREATE OR REPLACE FUNCTION ST_3DMakeBox(geom1 geometry, geom2 geometry) 1681 RETURNS box3d 1682 AS 'MODULE_PATHNAME', 'BOX3D_construct' 1683 LANGUAGE 'c' IMMUTABLE STRICT _PARALLEL; 1684 1685-- Availability: 1.4.0 1686CREATE OR REPLACE FUNCTION ST_MakeLine (geometry[]) 1687 RETURNS geometry 1688 AS 'MODULE_PATHNAME', 'LWGEOM_makeline_garray' 1689 LANGUAGE 'c' IMMUTABLE STRICT _PARALLEL; 1690 1691-- Availability: 1.2.2 1692CREATE OR REPLACE FUNCTION ST_LineFromMultiPoint(geometry) 1693 RETURNS geometry 1694 AS 'MODULE_PATHNAME', 'LWGEOM_line_from_mpoint' 1695 LANGUAGE 'c' IMMUTABLE STRICT _PARALLEL; 1696 1697-- Availability: 1.2.2 1698CREATE OR REPLACE FUNCTION ST_MakeLine(geom1 geometry, geom2 geometry) 1699 RETURNS geometry 1700 AS 'MODULE_PATHNAME', 'LWGEOM_makeline' 1701 LANGUAGE 'c' IMMUTABLE STRICT _PARALLEL; 1702 1703-- Availability: 1.2.2 1704CREATE OR REPLACE FUNCTION ST_AddPoint(geom1 geometry, geom2 geometry) 1705 RETURNS geometry 1706 AS 'MODULE_PATHNAME', 'LWGEOM_addpoint' 1707 LANGUAGE 'c' IMMUTABLE STRICT _PARALLEL; 1708 1709-- Availability: 1.2.2 1710CREATE OR REPLACE FUNCTION ST_AddPoint(geom1 geometry, geom2 geometry, integer) 1711 RETURNS geometry 1712 AS 'MODULE_PATHNAME', 'LWGEOM_addpoint' 1713 LANGUAGE 'c' IMMUTABLE STRICT _PARALLEL; 1714 1715-- Availability: 1.2.2 1716CREATE OR REPLACE FUNCTION ST_RemovePoint(geometry, integer) 1717 RETURNS geometry 1718 AS 'MODULE_PATHNAME', 'LWGEOM_removepoint' 1719 LANGUAGE 'c' IMMUTABLE STRICT _PARALLEL; 1720 1721-- Availability: 1.2.2 1722CREATE OR REPLACE FUNCTION ST_SetPoint(geometry, integer, geometry) 1723 RETURNS geometry 1724 AS 'MODULE_PATHNAME', 'LWGEOM_setpoint_linestring' 1725 LANGUAGE 'c' IMMUTABLE STRICT _PARALLEL; 1726 1727-- Availability: 1.5.0 1728-- Availability: 2.0.0 - made srid optional 1729CREATE OR REPLACE FUNCTION ST_MakeEnvelope(float8, float8, float8, float8, integer DEFAULT 0) 1730 RETURNS geometry 1731 AS 'MODULE_PATHNAME', 'ST_MakeEnvelope' 1732 LANGUAGE 'c' IMMUTABLE STRICT _PARALLEL; 1733 1734-- Availability: 1.2.2 1735CREATE OR REPLACE FUNCTION ST_MakePolygon(geometry, geometry[]) 1736 RETURNS geometry 1737 AS 'MODULE_PATHNAME', 'LWGEOM_makepoly' 1738 LANGUAGE 'c' IMMUTABLE STRICT _PARALLEL; 1739 1740-- Availability: 1.2.2 1741CREATE OR REPLACE FUNCTION ST_MakePolygon(geometry) 1742 RETURNS geometry 1743 AS 'MODULE_PATHNAME', 'LWGEOM_makepoly' 1744 LANGUAGE 'c' IMMUTABLE STRICT _PARALLEL; 1745 1746-- Availability: 1.2.2 1747CREATE OR REPLACE FUNCTION ST_BuildArea(geometry) 1748 RETURNS geometry 1749 AS 'MODULE_PATHNAME', 'ST_BuildArea' 1750 LANGUAGE 'c' IMMUTABLE STRICT _PARALLEL 1751 COST 1; -- reset cost, see #3675 1752 1753-- Availability: 1.4.0 1754CREATE OR REPLACE FUNCTION ST_Polygonize (geometry[]) 1755 RETURNS geometry 1756 AS 'MODULE_PATHNAME', 'polygonize_garray' 1757 LANGUAGE 'c' IMMUTABLE STRICT _PARALLEL 1758 COST 1; -- reset cost, see #3675 1759 1760-- Availability: 2.2 1761CREATE OR REPLACE FUNCTION ST_ClusterIntersecting(geometry[]) 1762 RETURNS geometry[] 1763 AS 'MODULE_PATHNAME', 'clusterintersecting_garray' 1764 LANGUAGE 'c' IMMUTABLE STRICT _PARALLEL; 1765 1766-- Availability: 2.2 1767CREATE OR REPLACE FUNCTION ST_ClusterWithin(geometry[], float8) 1768 RETURNS geometry[] 1769 AS 'MODULE_PATHNAME', 'cluster_within_distance_garray' 1770 LANGUAGE 'c' IMMUTABLE STRICT _PARALLEL; 1771 1772-- Availability: 2.3 1773CREATE OR REPLACE FUNCTION ST_ClusterDBSCAN (geometry, eps float8, minpoints int) 1774 RETURNS int 1775 AS 'MODULE_PATHNAME', 'ST_ClusterDBSCAN' 1776 LANGUAGE 'c' IMMUTABLE STRICT WINDOW _PARALLEL; 1777 1778-- Availability: 1.2.2 1779CREATE OR REPLACE FUNCTION ST_LineMerge(geometry) 1780 RETURNS geometry 1781 AS 'MODULE_PATHNAME', 'linemerge' 1782 LANGUAGE 'c' IMMUTABLE STRICT _PARALLEL 1783 COST 1; -- reset cost, see #3675 1784 1785----------------------------------------------------------------------------- 1786-- Affine transforms 1787----------------------------------------------------------------------------- 1788 1789-- Availability: 1.2.2 1790CREATE OR REPLACE FUNCTION ST_Affine(geometry,float8,float8,float8,float8,float8,float8,float8,float8,float8,float8,float8,float8) 1791 RETURNS geometry 1792 AS 'MODULE_PATHNAME', 'LWGEOM_affine' 1793 LANGUAGE 'c' IMMUTABLE STRICT _PARALLEL 1794 COST 1; -- reset cost, see #3675 1795 1796-- Availability: 1.2.2 1797CREATE OR REPLACE FUNCTION ST_Affine(geometry,float8,float8,float8,float8,float8,float8) 1798 RETURNS geometry 1799 AS 'SELECT @extschema@.ST_Affine($1, $2, $3, 0, $4, $5, 0, 0, 0, 1, $6, $7, 0)' 1800 LANGUAGE 'sql' IMMUTABLE STRICT _PARALLEL; 1801 1802-- Availability: 1.2.2 1803CREATE OR REPLACE FUNCTION ST_Rotate(geometry,float8) 1804 RETURNS geometry 1805 AS 'SELECT @extschema@.ST_Affine($1, cos($2), -sin($2), 0, sin($2), cos($2), 0, 0, 0, 1, 0, 0, 0)' 1806 LANGUAGE 'sql' IMMUTABLE STRICT _PARALLEL; 1807 1808-- Availability: 2.0.0 1809CREATE OR REPLACE FUNCTION ST_Rotate(geometry,float8,float8,float8) 1810 RETURNS geometry 1811 AS 'SELECT @extschema@.ST_Affine($1, cos($2), -sin($2), 0, sin($2), cos($2), 0, 0, 0, 1, $3 - cos($2) * $3 + sin($2) * $4, $4 - sin($2) * $3 - cos($2) * $4, 0)' 1812 LANGUAGE 'sql' IMMUTABLE STRICT _PARALLEL; 1813 1814-- Availability: 2.0.0 1815CREATE OR REPLACE FUNCTION ST_Rotate(geometry,float8,geometry) 1816 RETURNS geometry 1817 AS 'SELECT @extschema@.ST_Affine($1, cos($2), -sin($2), 0, sin($2), cos($2), 0, 0, 0, 1, @extschema@.ST_X($3) - cos($2) * @extschema@.ST_X($3) + sin($2) * @extschema@.ST_Y($3), @extschema@.ST_Y($3) - sin($2) * @extschema@.ST_X($3) - cos($2) * @extschema@.ST_Y($3), 0)' 1818 LANGUAGE 'sql' IMMUTABLE STRICT _PARALLEL; 1819 1820-- Availability: 1.2.2 1821CREATE OR REPLACE FUNCTION ST_RotateZ(geometry,float8) 1822 RETURNS geometry 1823 AS 'SELECT @extschema@.ST_Rotate($1, $2)' 1824 LANGUAGE 'sql' IMMUTABLE STRICT _PARALLEL; 1825 1826-- Availability: 1.2.2 1827CREATE OR REPLACE FUNCTION ST_RotateX(geometry,float8) 1828 RETURNS geometry 1829 AS 'SELECT @extschema@.ST_Affine($1, 1, 0, 0, 0, cos($2), -sin($2), 0, sin($2), cos($2), 0, 0, 0)' 1830 LANGUAGE 'sql' IMMUTABLE STRICT _PARALLEL; 1831 1832-- Availability: 1.2.2 1833CREATE OR REPLACE FUNCTION ST_RotateY(geometry,float8) 1834 RETURNS geometry 1835 AS 'SELECT @extschema@.ST_Affine($1, cos($2), 0, sin($2), 0, 1, 0, -sin($2), 0, cos($2), 0, 0, 0)' 1836 LANGUAGE 'sql' IMMUTABLE STRICT _PARALLEL; 1837 1838-- Availability: 1.2.2 1839CREATE OR REPLACE FUNCTION ST_Translate(geometry,float8,float8,float8) 1840 RETURNS geometry 1841 AS 'SELECT @extschema@.ST_Affine($1, 1, 0, 0, 0, 1, 0, 0, 0, 1, $2, $3, $4)' 1842 LANGUAGE 'sql' IMMUTABLE STRICT _PARALLEL; 1843 1844-- Availability: 1.2.2 1845CREATE OR REPLACE FUNCTION ST_Translate(geometry,float8,float8) 1846 RETURNS geometry 1847 AS 'SELECT @extschema@.ST_Translate($1, $2, $3, 0)' 1848 LANGUAGE 'sql' IMMUTABLE STRICT _PARALLEL; 1849 1850-- Availability: 2.2.0 1851CREATE OR REPLACE FUNCTION ST_Scale(geometry,geometry) 1852 RETURNS geometry 1853 AS 'MODULE_PATHNAME', 'ST_Scale' 1854 LANGUAGE 'c' IMMUTABLE STRICT _PARALLEL; 1855 1856-- Availability: 2.5.0 1857CREATE OR REPLACE FUNCTION ST_Scale(geometry,geometry,origin geometry) 1858 RETURNS geometry 1859 AS 'MODULE_PATHNAME', 'ST_Scale' 1860 LANGUAGE 'c' IMMUTABLE STRICT _PARALLEL; 1861 1862-- Availability: 1.2.2 1863CREATE OR REPLACE FUNCTION ST_Scale(geometry,float8,float8,float8) 1864 RETURNS geometry 1865 --AS 'SELECT ST_Affine($1, $2, 0, 0, 0, $3, 0, 0, 0, $4, 0, 0, 0)' 1866 AS 'SELECT @extschema@.ST_Scale($1, @extschema@.ST_MakePoint($2, $3, $4))' 1867 LANGUAGE 'sql' IMMUTABLE STRICT _PARALLEL; 1868 1869-- Availability: 1.2.2 1870CREATE OR REPLACE FUNCTION ST_Scale(geometry,float8,float8) 1871 RETURNS geometry 1872 AS 'SELECT @extschema@.ST_Scale($1, $2, $3, 1)' 1873 LANGUAGE 'sql' IMMUTABLE STRICT _PARALLEL; 1874 1875-- Availability: 1.2.2 1876CREATE OR REPLACE FUNCTION ST_Transscale(geometry,float8,float8,float8,float8) 1877 RETURNS geometry 1878 AS 'SELECT @extschema@.ST_Affine($1, $4, 0, 0, 0, $5, 0, 1879 0, 0, 1, $2 * $4, $3 * $5, 0)' 1880 LANGUAGE 'sql' IMMUTABLE STRICT _PARALLEL; 1881 1882----------------------------------------------------------------------- 1883-- Dumping 1884----------------------------------------------------------------------- 1885 1886-- Availability: 1.0.0 1887CREATE TYPE geometry_dump AS ( 1888 path integer[], 1889 geom geometry 1890); 1891 1892-- Availability: 1.2.2 1893CREATE OR REPLACE FUNCTION ST_Dump(geometry) 1894 RETURNS SETOF geometry_dump 1895 AS 'MODULE_PATHNAME', 'LWGEOM_dump' 1896 LANGUAGE 'c' IMMUTABLE STRICT _PARALLEL 1897 COST 100; 1898 1899-- Availability: 1.2.2 1900CREATE OR REPLACE FUNCTION ST_DumpRings(geometry) 1901 RETURNS SETOF geometry_dump 1902 AS 'MODULE_PATHNAME', 'LWGEOM_dump_rings' 1903 LANGUAGE 'c' IMMUTABLE STRICT _PARALLEL; 1904 1905----------------------------------------------------------------------- 1906-- ST_DumpPoints() 1907----------------------------------------------------------------------- 1908-- This function mimicks that of ST_Dump for collections, but this function 1909-- that returns a path and all the points that make up a particular geometry. 1910-- Availability: 1.5.0 1911CREATE OR REPLACE FUNCTION ST_DumpPoints(geometry) 1912 RETURNS SETOF geometry_dump 1913 AS 'MODULE_PATHNAME', 'LWGEOM_dumppoints' 1914 LANGUAGE 'c' IMMUTABLE STRICT _PARALLEL 1915 COST 100; 1916 1917------------------------------------------------------------------- 1918-- SPATIAL_REF_SYS 1919------------------------------------------------------------------- 1920CREATE TABLE spatial_ref_sys ( 1921 srid integer not null primary key 1922 check (srid > 0 and srid <= SRID_USR_MAX), 1923 auth_name varchar(256), 1924 auth_srid integer, 1925 srtext varchar(2048), 1926 proj4text varchar(2048) 1927); 1928 1929----------------------------------------------------------------------- 1930-- POPULATE_GEOMETRY_COLUMNS() 1931----------------------------------------------------------------------- 1932-- Truncates and refills the geometry_columns table from all tables and 1933-- views in the database that contain geometry columns. This function 1934-- is a simple wrapper for populate_geometry_columns(oid). In essence, 1935-- this function ensures every geometry column in the database has the 1936-- appropriate spatial contraints (for tables) and exists in the 1937-- geometry_columns table. 1938-- Availability: 1.4.0 1939-- Revised: 2.0.0 -- no longer deletes from geometry_columns 1940-- Has new use_typmod option that defaults to true. 1941-- If use typmod is set to false will use old constraint behavior. 1942-- Will only touch table missing typmod or geometry constraints 1943----------------------------------------------------------------------- 1944CREATE OR REPLACE FUNCTION populate_geometry_columns(use_typmod boolean DEFAULT true) 1945 RETURNS text AS 1946$$ 1947DECLARE 1948 inserted integer; 1949 oldcount integer; 1950 probed integer; 1951 stale integer; 1952 gcs RECORD; 1953 gc RECORD; 1954 gsrid integer; 1955 gndims integer; 1956 gtype text; 1957 query text; 1958 gc_is_valid boolean; 1959 1960BEGIN 1961 SELECT count(*) INTO oldcount FROM @extschema@.geometry_columns; 1962 inserted := 0; 1963 1964 -- Count the number of geometry columns in all tables and views 1965 SELECT count(DISTINCT c.oid) INTO probed 1966 FROM pg_class c, 1967 pg_attribute a, 1968 pg_type t, 1969 pg_namespace n 1970 WHERE c.relkind IN('r','v','f') 1971 AND t.typname = 'geometry' 1972 AND a.attisdropped = false 1973 AND a.atttypid = t.oid 1974 AND a.attrelid = c.oid 1975 AND c.relnamespace = n.oid 1976 AND n.nspname NOT ILIKE 'pg_temp%' AND c.relname != 'raster_columns' ; 1977 1978 -- Iterate through all non-dropped geometry columns 1979 RAISE DEBUG 'Processing Tables.....'; 1980 1981 FOR gcs IN 1982 SELECT DISTINCT ON (c.oid) c.oid, n.nspname, c.relname 1983 FROM pg_class c, 1984 pg_attribute a, 1985 pg_type t, 1986 pg_namespace n 1987 WHERE c.relkind IN( 'r', 'f') 1988 AND t.typname = 'geometry' 1989 AND a.attisdropped = false 1990 AND a.atttypid = t.oid 1991 AND a.attrelid = c.oid 1992 AND c.relnamespace = n.oid 1993 AND n.nspname NOT ILIKE 'pg_temp%' AND c.relname != 'raster_columns' 1994 LOOP 1995 1996 inserted := inserted + @extschema@.populate_geometry_columns(gcs.oid, use_typmod); 1997 END LOOP; 1998 1999 IF oldcount > inserted THEN 2000 stale = oldcount-inserted; 2001 ELSE 2002 stale = 0; 2003 END IF; 2004 2005 RETURN 'probed:' ||probed|| ' inserted:'||inserted; 2006END 2007 2008$$ 2009LANGUAGE 'plpgsql' VOLATILE; 2010 2011----------------------------------------------------------------------- 2012-- POPULATE_GEOMETRY_COLUMNS(tbl_oid oid) 2013----------------------------------------------------------------------- 2014-- DELETEs from and reINSERTs into the geometry_columns table all entries 2015-- associated with the oid of a particular table or view. 2016-- 2017-- If the provided oid is for a table, this function tries to determine 2018-- the srid, dimension, and geometry type of the all geometries 2019-- in the table, adding contraints as necessary to the table. If 2020-- successful, an appropriate row is inserted into the geometry_columns 2021-- table, otherwise, the exception is caught and an error notice is 2022-- raised describing the problem. (This is so the wrapper function 2023-- populate_geometry_columns() can apply spatial constraints to all 2024-- geometry columns across an entire database at once without erroring 2025-- out) 2026-- 2027-- If the provided oid is for a view, as with a table oid, this function 2028-- tries to determine the srid, dimension, and type of all the geometries 2029-- in the view, inserting appropriate entries into the geometry_columns 2030-- table. 2031-- Availability: 1.4.0 2032----------------------------------------------------------------------- 2033CREATE OR REPLACE FUNCTION populate_geometry_columns(tbl_oid oid, use_typmod boolean DEFAULT true) 2034 RETURNS integer AS 2035$$ 2036DECLARE 2037 gcs RECORD; 2038 gc RECORD; 2039 gc_old RECORD; 2040 gsrid integer; 2041 gndims integer; 2042 gtype text; 2043 query text; 2044 gc_is_valid boolean; 2045 inserted integer; 2046 constraint_successful boolean := false; 2047 2048BEGIN 2049 inserted := 0; 2050 2051 -- Iterate through all geometry columns in this table 2052 FOR gcs IN 2053 SELECT n.nspname, c.relname, a.attname 2054 FROM pg_class c, 2055 pg_attribute a, 2056 pg_type t, 2057 pg_namespace n 2058 WHERE c.relkind IN('r', 'f') 2059 AND t.typname = 'geometry' 2060 AND a.attisdropped = false 2061 AND a.atttypid = t.oid 2062 AND a.attrelid = c.oid 2063 AND c.relnamespace = n.oid 2064 AND n.nspname NOT ILIKE 'pg_temp%' 2065 AND c.oid = tbl_oid 2066 LOOP 2067 2068 RAISE DEBUG 'Processing column %.%.%', gcs.nspname, gcs.relname, gcs.attname; 2069 2070 gc_is_valid := true; 2071 -- Find the srid, coord_dimension, and type of current geometry 2072 -- in geometry_columns -- which is now a view 2073 2074 SELECT type, srid, coord_dimension INTO gc_old 2075 FROM geometry_columns 2076 WHERE f_table_schema = gcs.nspname AND f_table_name = gcs.relname AND f_geometry_column = gcs.attname; 2077 2078 IF upper(gc_old.type) = 'GEOMETRY' THEN 2079 -- This is an unconstrained geometry we need to do something 2080 -- We need to figure out what to set the type by inspecting the data 2081 EXECUTE 'SELECT @extschema@.ST_srid(' || quote_ident(gcs.attname) || ') As srid, @extschema@.GeometryType(' || quote_ident(gcs.attname) || ') As type, @extschema@.ST_NDims(' || quote_ident(gcs.attname) || ') As dims ' || 2082 ' FROM ONLY ' || quote_ident(gcs.nspname) || '.' || quote_ident(gcs.relname) || 2083 ' WHERE ' || quote_ident(gcs.attname) || ' IS NOT NULL LIMIT 1;' 2084 INTO gc; 2085 IF gc IS NULL THEN -- there is no data so we can not determine geometry type 2086 RAISE WARNING 'No data in table %.%, so no information to determine geometry type and srid', gcs.nspname, gcs.relname; 2087 RETURN 0; 2088 END IF; 2089 gsrid := gc.srid; gtype := gc.type; gndims := gc.dims; 2090 2091 IF use_typmod THEN 2092 BEGIN 2093 EXECUTE 'ALTER TABLE ' || quote_ident(gcs.nspname) || '.' || quote_ident(gcs.relname) || ' ALTER COLUMN ' || quote_ident(gcs.attname) || 2094 ' TYPE geometry(' || postgis_type_name(gtype, gndims, true) || ', ' || gsrid::text || ') '; 2095 inserted := inserted + 1; 2096 EXCEPTION 2097 WHEN invalid_parameter_value OR feature_not_supported THEN 2098 RAISE WARNING 'Could not convert ''%'' in ''%.%'' to use typmod with srid %, type %: %', quote_ident(gcs.attname), quote_ident(gcs.nspname), quote_ident(gcs.relname), gsrid, postgis_type_name(gtype, gndims, true), SQLERRM; 2099 gc_is_valid := false; 2100 END; 2101 2102 ELSE 2103 -- Try to apply srid check to column 2104 constraint_successful = false; 2105 IF (gsrid > 0 AND postgis_constraint_srid(gcs.nspname, gcs.relname,gcs.attname) IS NULL ) THEN 2106 BEGIN 2107 EXECUTE 'ALTER TABLE ONLY ' || quote_ident(gcs.nspname) || '.' || quote_ident(gcs.relname) || 2108 ' ADD CONSTRAINT ' || quote_ident('enforce_srid_' || gcs.attname) || 2109 ' CHECK (ST_srid(' || quote_ident(gcs.attname) || ') = ' || gsrid || ')'; 2110 constraint_successful := true; 2111 EXCEPTION 2112 WHEN check_violation THEN 2113 RAISE WARNING 'Not inserting ''%'' in ''%.%'' into geometry_columns: could not apply constraint CHECK (st_srid(%) = %)', quote_ident(gcs.attname), quote_ident(gcs.nspname), quote_ident(gcs.relname), quote_ident(gcs.attname), gsrid; 2114 gc_is_valid := false; 2115 END; 2116 END IF; 2117 2118 -- Try to apply ndims check to column 2119 IF (gndims IS NOT NULL AND postgis_constraint_dims(gcs.nspname, gcs.relname,gcs.attname) IS NULL ) THEN 2120 BEGIN 2121 EXECUTE 'ALTER TABLE ONLY ' || quote_ident(gcs.nspname) || '.' || quote_ident(gcs.relname) || ' 2122 ADD CONSTRAINT ' || quote_ident('enforce_dims_' || gcs.attname) || ' 2123 CHECK (st_ndims(' || quote_ident(gcs.attname) || ') = '||gndims||')'; 2124 constraint_successful := true; 2125 EXCEPTION 2126 WHEN check_violation THEN 2127 RAISE WARNING 'Not inserting ''%'' in ''%.%'' into geometry_columns: could not apply constraint CHECK (st_ndims(%) = %)', quote_ident(gcs.attname), quote_ident(gcs.nspname), quote_ident(gcs.relname), quote_ident(gcs.attname), gndims; 2128 gc_is_valid := false; 2129 END; 2130 END IF; 2131 2132 -- Try to apply geometrytype check to column 2133 IF (gtype IS NOT NULL AND postgis_constraint_type(gcs.nspname, gcs.relname,gcs.attname) IS NULL ) THEN 2134 BEGIN 2135 EXECUTE 'ALTER TABLE ONLY ' || quote_ident(gcs.nspname) || '.' || quote_ident(gcs.relname) || ' 2136 ADD CONSTRAINT ' || quote_ident('enforce_geotype_' || gcs.attname) || ' 2137 CHECK (geometrytype(' || quote_ident(gcs.attname) || ') = ' || quote_literal(gtype) || ')'; 2138 constraint_successful := true; 2139 EXCEPTION 2140 WHEN check_violation THEN 2141 -- No geometry check can be applied. This column contains a number of geometry types. 2142 RAISE WARNING 'Could not add geometry type check (%) to table column: %.%.%', gtype, quote_ident(gcs.nspname),quote_ident(gcs.relname),quote_ident(gcs.attname); 2143 END; 2144 END IF; 2145 --only count if we were successful in applying at least one constraint 2146 IF constraint_successful THEN 2147 inserted := inserted + 1; 2148 END IF; 2149 END IF; 2150 END IF; 2151 2152 END LOOP; 2153 2154 RETURN inserted; 2155END 2156 2157$$ 2158LANGUAGE 'plpgsql' VOLATILE; 2159 2160----------------------------------------------------------------------- 2161-- ADDGEOMETRYCOLUMN 2162-- <catalogue>, <schema>, <table>, <column>, <srid>, <type>, <dim> 2163----------------------------------------------------------------------- 2164-- 2165-- Type can be one of GEOMETRY, GEOMETRYCOLLECTION, POINT, MULTIPOINT, POLYGON, 2166-- MULTIPOLYGON, LINESTRING, or MULTILINESTRING. 2167-- 2168-- Geometry types (except GEOMETRY) are checked for consistency using a CHECK constraint. 2169-- Uses an ALTER TABLE command to add the geometry column to the table. 2170-- Addes a row to geometry_columns. 2171-- Addes a constraint on the table that all the geometries MUST have the same 2172-- SRID. Checks the coord_dimension to make sure its between 0 and 3. 2173-- Should also check the precision grid (future expansion). 2174-- 2175----------------------------------------------------------------------- 2176CREATE OR REPLACE FUNCTION AddGeometryColumn(catalog_name varchar,schema_name varchar,table_name varchar,column_name varchar,new_srid_in integer,new_type varchar,new_dim integer, use_typmod boolean DEFAULT true) 2177 RETURNS text 2178 AS 2179$$ 2180DECLARE 2181 rec RECORD; 2182 sr varchar; 2183 real_schema name; 2184 sql text; 2185 new_srid integer; 2186 2187BEGIN 2188 2189 -- Verify geometry type 2190 IF (postgis_type_name(new_type,new_dim) IS NULL ) 2191 THEN 2192 RAISE EXCEPTION 'Invalid type name "%(%)" - valid ones are: 2193 POINT, MULTIPOINT, 2194 LINESTRING, MULTILINESTRING, 2195 POLYGON, MULTIPOLYGON, 2196 CIRCULARSTRING, COMPOUNDCURVE, MULTICURVE, 2197 CURVEPOLYGON, MULTISURFACE, 2198 GEOMETRY, GEOMETRYCOLLECTION, 2199 POINTM, MULTIPOINTM, 2200 LINESTRINGM, MULTILINESTRINGM, 2201 POLYGONM, MULTIPOLYGONM, 2202 CIRCULARSTRINGM, COMPOUNDCURVEM, MULTICURVEM 2203 CURVEPOLYGONM, MULTISURFACEM, TRIANGLE, TRIANGLEM, 2204 POLYHEDRALSURFACE, POLYHEDRALSURFACEM, TIN, TINM 2205 or GEOMETRYCOLLECTIONM', new_type, new_dim; 2206 RETURN 'fail'; 2207 END IF; 2208 2209 -- Verify dimension 2210 IF ( (new_dim >4) OR (new_dim <2) ) THEN 2211 RAISE EXCEPTION 'invalid dimension'; 2212 RETURN 'fail'; 2213 END IF; 2214 2215 IF ( (new_type LIKE '%M') AND (new_dim!=3) ) THEN 2216 RAISE EXCEPTION 'TypeM needs 3 dimensions'; 2217 RETURN 'fail'; 2218 END IF; 2219 2220 -- Verify SRID 2221 IF ( new_srid_in > 0 ) THEN 2222 IF new_srid_in > SRID_USR_MAX THEN 2223 RAISE EXCEPTION 'AddGeometryColumn() - SRID must be <= %', SRID_USR_MAX; 2224 END IF; 2225 new_srid := new_srid_in; 2226 SELECT SRID INTO sr FROM spatial_ref_sys WHERE SRID = new_srid; 2227 IF NOT FOUND THEN 2228 RAISE EXCEPTION 'AddGeometryColumn() - invalid SRID'; 2229 RETURN 'fail'; 2230 END IF; 2231 ELSE 2232 new_srid := @extschema@.ST_SRID('POINT EMPTY'::@extschema@.geometry); 2233 IF ( new_srid_in != new_srid ) THEN 2234 RAISE NOTICE 'SRID value % converted to the officially unknown SRID value %', new_srid_in, new_srid; 2235 END IF; 2236 END IF; 2237 2238 -- Verify schema 2239 IF ( schema_name IS NOT NULL AND schema_name != '' ) THEN 2240 sql := 'SELECT nspname FROM pg_namespace ' || 2241 'WHERE text(nspname) = ' || quote_literal(schema_name) || 2242 'LIMIT 1'; 2243 RAISE DEBUG '%', sql; 2244 EXECUTE sql INTO real_schema; 2245 2246 IF ( real_schema IS NULL ) THEN 2247 RAISE EXCEPTION 'Schema % is not a valid schemaname', quote_literal(schema_name); 2248 RETURN 'fail'; 2249 END IF; 2250 END IF; 2251 2252 IF ( real_schema IS NULL ) THEN 2253 RAISE DEBUG 'Detecting schema'; 2254 sql := 'SELECT n.nspname AS schemaname ' || 2255 'FROM pg_catalog.pg_class c ' || 2256 'JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace ' || 2257 'WHERE c.relkind = ' || quote_literal('r') || 2258 ' AND n.nspname NOT IN (' || quote_literal('pg_catalog') || ', ' || quote_literal('pg_toast') || ')' || 2259 ' AND pg_catalog.pg_table_is_visible(c.oid)' || 2260 ' AND c.relname = ' || quote_literal(table_name); 2261 RAISE DEBUG '%', sql; 2262 EXECUTE sql INTO real_schema; 2263 2264 IF ( real_schema IS NULL ) THEN 2265 RAISE EXCEPTION 'Table % does not occur in the search_path', quote_literal(table_name); 2266 RETURN 'fail'; 2267 END IF; 2268 END IF; 2269 2270 -- Add geometry column to table 2271 IF use_typmod THEN 2272 sql := 'ALTER TABLE ' || 2273 quote_ident(real_schema) || '.' || quote_ident(table_name) 2274 || ' ADD COLUMN ' || quote_ident(column_name) || 2275 ' geometry(' || @extschema@.postgis_type_name(new_type, new_dim) || ', ' || new_srid::text || ')'; 2276 RAISE DEBUG '%', sql; 2277 ELSE 2278 sql := 'ALTER TABLE ' || 2279 quote_ident(real_schema) || '.' || quote_ident(table_name) 2280 || ' ADD COLUMN ' || quote_ident(column_name) || 2281 ' geometry '; 2282 RAISE DEBUG '%', sql; 2283 END IF; 2284 EXECUTE sql; 2285 2286 IF NOT use_typmod THEN 2287 -- Add table CHECKs 2288 sql := 'ALTER TABLE ' || 2289 quote_ident(real_schema) || '.' || quote_ident(table_name) 2290 || ' ADD CONSTRAINT ' 2291 || quote_ident('enforce_srid_' || column_name) 2292 || ' CHECK (st_srid(' || quote_ident(column_name) || 2293 ') = ' || new_srid::text || ')' ; 2294 RAISE DEBUG '%', sql; 2295 EXECUTE sql; 2296 2297 sql := 'ALTER TABLE ' || 2298 quote_ident(real_schema) || '.' || quote_ident(table_name) 2299 || ' ADD CONSTRAINT ' 2300 || quote_ident('enforce_dims_' || column_name) 2301 || ' CHECK (st_ndims(' || quote_ident(column_name) || 2302 ') = ' || new_dim::text || ')' ; 2303 RAISE DEBUG '%', sql; 2304 EXECUTE sql; 2305 2306 IF ( NOT (new_type = 'GEOMETRY')) THEN 2307 sql := 'ALTER TABLE ' || 2308 quote_ident(real_schema) || '.' || quote_ident(table_name) || ' ADD CONSTRAINT ' || 2309 quote_ident('enforce_geotype_' || column_name) || 2310 ' CHECK (GeometryType(' || 2311 quote_ident(column_name) || ')=' || 2312 quote_literal(new_type) || ' OR (' || 2313 quote_ident(column_name) || ') is null)'; 2314 RAISE DEBUG '%', sql; 2315 EXECUTE sql; 2316 END IF; 2317 END IF; 2318 2319 RETURN 2320 real_schema || '.' || 2321 table_name || '.' || column_name || 2322 ' SRID:' || new_srid::text || 2323 ' TYPE:' || new_type || 2324 ' DIMS:' || new_dim::text || ' '; 2325END; 2326$$ 2327LANGUAGE 'plpgsql' VOLATILE STRICT; 2328 2329---------------------------------------------------------------------------- 2330-- ADDGEOMETRYCOLUMN ( <schema>, <table>, <column>, <srid>, <type>, <dim> ) 2331---------------------------------------------------------------------------- 2332-- 2333-- This is a wrapper to the real AddGeometryColumn, for use 2334-- when catalogue is undefined 2335-- 2336---------------------------------------------------------------------------- 2337CREATE OR REPLACE FUNCTION AddGeometryColumn(schema_name varchar,table_name varchar,column_name varchar,new_srid integer,new_type varchar,new_dim integer, use_typmod boolean DEFAULT true) RETURNS text AS $$ 2338DECLARE 2339 ret text; 2340BEGIN 2341 SELECT @extschema@.AddGeometryColumn('',$1,$2,$3,$4,$5,$6,$7) into ret; 2342 RETURN ret; 2343END; 2344$$ 2345LANGUAGE 'plpgsql' STABLE STRICT; 2346 2347---------------------------------------------------------------------------- 2348-- ADDGEOMETRYCOLUMN ( <table>, <column>, <srid>, <type>, <dim> ) 2349---------------------------------------------------------------------------- 2350-- 2351-- This is a wrapper to the real AddGeometryColumn, for use 2352-- when catalogue and schema are undefined 2353-- 2354---------------------------------------------------------------------------- 2355CREATE OR REPLACE FUNCTION AddGeometryColumn(table_name varchar,column_name varchar,new_srid integer,new_type varchar,new_dim integer, use_typmod boolean DEFAULT true) RETURNS text AS $$ 2356DECLARE 2357 ret text; 2358BEGIN 2359 SELECT @extschema@.AddGeometryColumn('','',$1,$2,$3,$4,$5, $6) into ret; 2360 RETURN ret; 2361END; 2362$$ 2363LANGUAGE 'plpgsql' VOLATILE STRICT; 2364 2365----------------------------------------------------------------------- 2366-- DROPGEOMETRYCOLUMN 2367-- <catalogue>, <schema>, <table>, <column> 2368----------------------------------------------------------------------- 2369-- 2370-- Removes geometry column reference from geometry_columns table. 2371-- Drops the column with pgsql >= 73. 2372-- Make some silly enforcements on it for pgsql < 73 2373-- 2374----------------------------------------------------------------------- 2375CREATE OR REPLACE FUNCTION DropGeometryColumn(catalog_name varchar, schema_name varchar,table_name varchar,column_name varchar) 2376 RETURNS text 2377 AS 2378$$ 2379DECLARE 2380 myrec RECORD; 2381 okay boolean; 2382 real_schema name; 2383 2384BEGIN 2385 2386 -- Find, check or fix schema_name 2387 IF ( schema_name != '' ) THEN 2388 okay = false; 2389 2390 FOR myrec IN SELECT nspname FROM pg_namespace WHERE text(nspname) = schema_name LOOP 2391 okay := true; 2392 END LOOP; 2393 2394 IF ( okay <> true ) THEN 2395 RAISE NOTICE 'Invalid schema name - using current_schema()'; 2396 SELECT current_schema() into real_schema; 2397 ELSE 2398 real_schema = schema_name; 2399 END IF; 2400 ELSE 2401 SELECT current_schema() into real_schema; 2402 END IF; 2403 2404 -- Find out if the column is in the geometry_columns table 2405 okay = false; 2406 FOR myrec IN SELECT * from @extschema@.geometry_columns where f_table_schema = text(real_schema) and f_table_name = table_name and f_geometry_column = column_name LOOP 2407 okay := true; 2408 END LOOP; 2409 IF (okay <> true) THEN 2410 RAISE EXCEPTION 'column not found in geometry_columns table'; 2411 RETURN false; 2412 END IF; 2413 2414 -- Remove table column 2415 EXECUTE 'ALTER TABLE ' || quote_ident(real_schema) || '.' || 2416 quote_ident(table_name) || ' DROP COLUMN ' || 2417 quote_ident(column_name); 2418 2419 RETURN real_schema || '.' || table_name || '.' || column_name ||' effectively removed.'; 2420 2421END; 2422$$ 2423LANGUAGE 'plpgsql' VOLATILE STRICT; 2424 2425----------------------------------------------------------------------- 2426-- DROPGEOMETRYCOLUMN 2427-- <schema>, <table>, <column> 2428----------------------------------------------------------------------- 2429-- 2430-- This is a wrapper to the real DropGeometryColumn, for use 2431-- when catalogue is undefined 2432-- 2433----------------------------------------------------------------------- 2434CREATE OR REPLACE FUNCTION DropGeometryColumn(schema_name varchar, table_name varchar,column_name varchar) 2435 RETURNS text 2436 AS 2437$$ 2438DECLARE 2439 ret text; 2440BEGIN 2441 SELECT @extschema@.DropGeometryColumn('',$1,$2,$3) into ret; 2442 RETURN ret; 2443END; 2444$$ 2445LANGUAGE 'plpgsql' VOLATILE STRICT; 2446 2447----------------------------------------------------------------------- 2448-- DROPGEOMETRYCOLUMN 2449-- <table>, <column> 2450----------------------------------------------------------------------- 2451-- 2452-- This is a wrapper to the real DropGeometryColumn, for use 2453-- when catalogue and schema is undefined. 2454-- 2455----------------------------------------------------------------------- 2456CREATE OR REPLACE FUNCTION DropGeometryColumn(table_name varchar, column_name varchar) 2457 RETURNS text 2458 AS 2459$$ 2460DECLARE 2461 ret text; 2462BEGIN 2463 SELECT @extschema@.DropGeometryColumn('','',$1,$2) into ret; 2464 RETURN ret; 2465END; 2466$$ 2467LANGUAGE 'plpgsql' VOLATILE STRICT; 2468 2469----------------------------------------------------------------------- 2470-- DROPGEOMETRYTABLE 2471-- <catalogue>, <schema>, <table> 2472----------------------------------------------------------------------- 2473-- 2474-- Drop a table and all its references in geometry_columns 2475-- 2476----------------------------------------------------------------------- 2477CREATE OR REPLACE FUNCTION DropGeometryTable(catalog_name varchar, schema_name varchar, table_name varchar) 2478 RETURNS text 2479 AS 2480$$ 2481DECLARE 2482 real_schema name; 2483 2484BEGIN 2485 2486 IF ( schema_name = '' ) THEN 2487 SELECT current_schema() into real_schema; 2488 ELSE 2489 real_schema = schema_name; 2490 END IF; 2491 2492 -- TODO: Should we warn if table doesn't exist probably instead just saying dropped 2493 -- Remove table 2494 EXECUTE 'DROP TABLE IF EXISTS ' 2495 || quote_ident(real_schema) || '.' || 2496 quote_ident(table_name) || ' RESTRICT'; 2497 2498 RETURN 2499 real_schema || '.' || 2500 table_name ||' dropped.'; 2501 2502END; 2503$$ 2504LANGUAGE 'plpgsql' VOLATILE STRICT; 2505 2506----------------------------------------------------------------------- 2507-- DROPGEOMETRYTABLE 2508-- <schema>, <table> 2509----------------------------------------------------------------------- 2510-- 2511-- Drop a table and all its references in geometry_columns 2512-- 2513----------------------------------------------------------------------- 2514CREATE OR REPLACE FUNCTION DropGeometryTable(schema_name varchar, table_name varchar) RETURNS text AS 2515$$ SELECT @extschema@.DropGeometryTable('',$1,$2) $$ 2516LANGUAGE 'sql' VOLATILE STRICT; 2517 2518----------------------------------------------------------------------- 2519-- DROPGEOMETRYTABLE 2520-- <table> 2521----------------------------------------------------------------------- 2522-- 2523-- Drop a table and all its references in geometry_columns 2524-- For PG>=73 use current_schema() 2525-- 2526----------------------------------------------------------------------- 2527CREATE OR REPLACE FUNCTION DropGeometryTable(table_name varchar) RETURNS text AS 2528$$ SELECT @extschema@.DropGeometryTable('','',$1) $$ 2529LANGUAGE 'sql' VOLATILE STRICT; 2530 2531----------------------------------------------------------------------- 2532-- UPDATEGEOMETRYSRID 2533-- <catalogue>, <schema>, <table>, <column>, <srid> 2534----------------------------------------------------------------------- 2535-- 2536-- Change SRID of all features in a spatially-enabled table 2537-- 2538----------------------------------------------------------------------- 2539-- Changed: 2.1.4 check against real_schema 2540CREATE OR REPLACE FUNCTION UpdateGeometrySRID(catalogn_name varchar,schema_name varchar,table_name varchar,column_name varchar,new_srid_in integer) 2541 RETURNS text 2542 AS 2543$$ 2544DECLARE 2545 myrec RECORD; 2546 okay boolean; 2547 cname varchar; 2548 real_schema name; 2549 unknown_srid integer; 2550 new_srid integer := new_srid_in; 2551 2552BEGIN 2553 2554 -- Find, check or fix schema_name 2555 IF ( schema_name != '' ) THEN 2556 okay = false; 2557 2558 FOR myrec IN SELECT nspname FROM pg_namespace WHERE text(nspname) = schema_name LOOP 2559 okay := true; 2560 END LOOP; 2561 2562 IF ( okay <> true ) THEN 2563 RAISE EXCEPTION 'Invalid schema name'; 2564 ELSE 2565 real_schema = schema_name; 2566 END IF; 2567 ELSE 2568 SELECT INTO real_schema current_schema()::text; 2569 END IF; 2570 2571 -- Ensure that column_name is in geometry_columns 2572 okay = false; 2573 FOR myrec IN SELECT type, coord_dimension FROM @extschema@.geometry_columns WHERE f_table_schema = text(real_schema) and f_table_name = table_name and f_geometry_column = column_name LOOP 2574 okay := true; 2575 END LOOP; 2576 IF (NOT okay) THEN 2577 RAISE EXCEPTION 'column not found in geometry_columns table'; 2578 RETURN false; 2579 END IF; 2580 2581 -- Ensure that new_srid is valid 2582 IF ( new_srid > 0 ) THEN 2583 IF ( SELECT count(*) = 0 from spatial_ref_sys where srid = new_srid ) THEN 2584 RAISE EXCEPTION 'invalid SRID: % not found in spatial_ref_sys', new_srid; 2585 RETURN false; 2586 END IF; 2587 ELSE 2588 unknown_srid := @extschema@.ST_SRID('POINT EMPTY'::@extschema@.geometry); 2589 IF ( new_srid != unknown_srid ) THEN 2590 new_srid := unknown_srid; 2591 RAISE NOTICE 'SRID value % converted to the officially unknown SRID value %', new_srid_in, new_srid; 2592 END IF; 2593 END IF; 2594 2595 IF postgis_constraint_srid(real_schema, table_name, column_name) IS NOT NULL THEN 2596 -- srid was enforced with constraints before, keep it that way. 2597 -- Make up constraint name 2598 cname = 'enforce_srid_' || column_name; 2599 2600 -- Drop enforce_srid constraint 2601 EXECUTE 'ALTER TABLE ' || quote_ident(real_schema) || 2602 '.' || quote_ident(table_name) || 2603 ' DROP constraint ' || quote_ident(cname); 2604 2605 -- Update geometries SRID 2606 EXECUTE 'UPDATE ' || quote_ident(real_schema) || 2607 '.' || quote_ident(table_name) || 2608 ' SET ' || quote_ident(column_name) || 2609 ' = @extschema@.ST_SetSRID(' || quote_ident(column_name) || 2610 ', ' || new_srid::text || ')'; 2611 2612 -- Reset enforce_srid constraint 2613 EXECUTE 'ALTER TABLE ' || quote_ident(real_schema) || 2614 '.' || quote_ident(table_name) || 2615 ' ADD constraint ' || quote_ident(cname) || 2616 ' CHECK (st_srid(' || quote_ident(column_name) || 2617 ') = ' || new_srid::text || ')'; 2618 ELSE 2619 -- We will use typmod to enforce if no srid constraints 2620 -- We are using postgis_type_name to lookup the new name 2621 -- (in case Paul changes his mind and flips geometry_columns to return old upper case name) 2622 EXECUTE 'ALTER TABLE ' || quote_ident(real_schema) || '.' || quote_ident(table_name) || 2623 ' ALTER COLUMN ' || quote_ident(column_name) || ' TYPE geometry(' || @extschema@.postgis_type_name(myrec.type, myrec.coord_dimension, true) || ', ' || new_srid::text || ') USING @extschema@.ST_SetSRID(' || quote_ident(column_name) || ',' || new_srid::text || ');' ; 2624 END IF; 2625 2626 RETURN real_schema || '.' || table_name || '.' || column_name ||' SRID changed to ' || new_srid::text; 2627 2628END; 2629$$ 2630LANGUAGE 'plpgsql' VOLATILE STRICT; 2631 2632----------------------------------------------------------------------- 2633-- UPDATEGEOMETRYSRID 2634-- <schema>, <table>, <column>, <srid> 2635----------------------------------------------------------------------- 2636CREATE OR REPLACE FUNCTION UpdateGeometrySRID(varchar,varchar,varchar,integer) 2637 RETURNS text 2638 AS $$ 2639DECLARE 2640 ret text; 2641BEGIN 2642 SELECT @extschema@.UpdateGeometrySRID('',$1,$2,$3,$4) into ret; 2643 RETURN ret; 2644END; 2645$$ 2646LANGUAGE 'plpgsql' VOLATILE STRICT; 2647 2648----------------------------------------------------------------------- 2649-- UPDATEGEOMETRYSRID 2650-- <table>, <column>, <srid> 2651----------------------------------------------------------------------- 2652CREATE OR REPLACE FUNCTION UpdateGeometrySRID(varchar,varchar,integer) 2653 RETURNS text 2654 AS $$ 2655DECLARE 2656 ret text; 2657BEGIN 2658 SELECT @extschema@.UpdateGeometrySRID('','',$1,$2,$3) into ret; 2659 RETURN ret; 2660END; 2661$$ 2662LANGUAGE 'plpgsql' VOLATILE STRICT; 2663 2664----------------------------------------------------------------------- 2665-- FIND_SRID( <schema>, <table>, <geom col> ) 2666----------------------------------------------------------------------- 2667-- Changed: 2.1.8 improve performance 2668CREATE OR REPLACE FUNCTION find_srid(varchar,varchar,varchar) RETURNS int4 AS 2669$$ 2670DECLARE 2671 schem varchar = $1; 2672 tabl varchar = $2; 2673 sr int4; 2674BEGIN 2675-- if the table contains a . and the schema is empty 2676-- split the table into a schema and a table 2677-- otherwise drop through to default behavior 2678 IF ( schem = '' and strpos(tabl,'.') > 0 ) THEN 2679 schem = substr(tabl,1,strpos(tabl,'.')-1); 2680 tabl = substr(tabl,length(schem)+2); 2681 END IF; 2682 2683 select SRID into sr from @extschema@.geometry_columns where (f_table_schema = schem or schem = '') and f_table_name = tabl and f_geometry_column = $3; 2684 IF NOT FOUND THEN 2685 RAISE EXCEPTION 'find_srid() - could not find the corresponding SRID - is the geometry registered in the GEOMETRY_COLUMNS table? Is there an uppercase/lowercase mismatch?'; 2686 END IF; 2687 return sr; 2688END; 2689$$ 2690LANGUAGE 'plpgsql' IMMUTABLE STRICT _PARALLEL; 2691 2692--------------------------------------------------------------- 2693-- PROJ support 2694--------------------------------------------------------------- 2695 2696CREATE OR REPLACE FUNCTION get_proj4_from_srid(integer) RETURNS text AS 2697$$ 2698BEGIN 2699 RETURN proj4text::text FROM @extschema@.spatial_ref_sys WHERE srid= $1; 2700END; 2701$$ 2702LANGUAGE 'plpgsql' IMMUTABLE STRICT _PARALLEL; 2703 2704-- Availability: 1.2.2 2705CREATE OR REPLACE FUNCTION ST_SetSRID(geometry,int4) 2706 RETURNS geometry 2707 AS 'MODULE_PATHNAME','LWGEOM_set_srid' 2708 LANGUAGE 'c' IMMUTABLE STRICT _PARALLEL 2709 COST 1; 2710 2711CREATE OR REPLACE FUNCTION ST_SRID(geometry) 2712 RETURNS int4 2713 AS 'MODULE_PATHNAME','LWGEOM_get_srid' 2714 LANGUAGE 'c' IMMUTABLE STRICT _PARALLEL 2715 COST 5; 2716 2717CREATE OR REPLACE FUNCTION postgis_transform_geometry(geometry,text,text,int) 2718 RETURNS geometry 2719 AS 'MODULE_PATHNAME','transform_geom' 2720 LANGUAGE 'c' IMMUTABLE STRICT _PARALLEL; 2721 2722-- PostGIS equivalent of old function: transform(geometry,integer) 2723CREATE OR REPLACE FUNCTION ST_Transform(geometry,integer) 2724 RETURNS geometry 2725 AS 'MODULE_PATHNAME','transform' 2726 LANGUAGE 'c' IMMUTABLE STRICT _PARALLEL 2727 COST 1; -- reset cost, see #3675 2728 2729-- Availability: 2.3.0 2730CREATE OR REPLACE FUNCTION ST_Transform(geom geometry, to_proj text) 2731 RETURNS geometry AS 2732'SELECT @extschema@.postgis_transform_geometry($1, proj4text, $2, 0) 2733FROM spatial_ref_sys WHERE srid=@extschema@.ST_SRID($1);' 2734 LANGUAGE sql IMMUTABLE STRICT _PARALLEL; 2735 2736-- Availability: 2.3.0 2737CREATE OR REPLACE FUNCTION ST_Transform(geom geometry, from_proj text, to_proj text) 2738 RETURNS geometry AS 2739'SELECT @extschema@.postgis_transform_geometry($1, $2, $3, 0)' 2740 LANGUAGE sql IMMUTABLE STRICT _PARALLEL; 2741 2742-- Availability: 2.3.0 2743CREATE OR REPLACE FUNCTION ST_Transform(geom geometry, from_proj text, to_srid integer) 2744 RETURNS geometry AS 2745'SELECT @extschema@.postgis_transform_geometry($1, $2, proj4text, $3) 2746FROM spatial_ref_sys WHERE srid=$3;' 2747 LANGUAGE sql IMMUTABLE STRICT _PARALLEL; 2748 2749----------------------------------------------------------------------- 2750-- POSTGIS_VERSION() 2751----------------------------------------------------------------------- 2752 2753CREATE OR REPLACE FUNCTION postgis_version() RETURNS text 2754 AS 'MODULE_PATHNAME' 2755 LANGUAGE 'c' IMMUTABLE; 2756 2757CREATE OR REPLACE FUNCTION postgis_liblwgeom_version() RETURNS text 2758 AS 'MODULE_PATHNAME' 2759 LANGUAGE 'c' IMMUTABLE; 2760 2761CREATE OR REPLACE FUNCTION postgis_proj_version() RETURNS text 2762 AS 'MODULE_PATHNAME' 2763 LANGUAGE 'c' IMMUTABLE; 2764 2765-- 2766-- IMPORTANT: 2767-- Starting at 1.1.0 this function is used by postgis_proc_upgrade.pl 2768-- to extract version of postgis being installed. 2769-- Do not modify this w/out also changing postgis_proc_upgrade.pl 2770-- 2771CREATE OR REPLACE FUNCTION postgis_scripts_installed() RETURNS text 2772 AS _POSTGIS_SQL_SELECT_POSTGIS_SCRIPTS_VERSION 2773 LANGUAGE 'sql' IMMUTABLE; 2774 2775CREATE OR REPLACE FUNCTION postgis_lib_version() RETURNS text 2776 AS 'MODULE_PATHNAME' 2777 LANGUAGE 'c' IMMUTABLE; -- a new lib will require a new session 2778 2779-- NOTE: from 1.1.0 to 1.5.x this was the same of postgis_lib_version() 2780-- NOTE: from 2.0.0 up it includes postgis_svn_revision() 2781CREATE OR REPLACE FUNCTION postgis_scripts_released() RETURNS text 2782 AS 'MODULE_PATHNAME' 2783 LANGUAGE 'c' IMMUTABLE; 2784 2785CREATE OR REPLACE FUNCTION postgis_geos_version() RETURNS text 2786 AS 'MODULE_PATHNAME' 2787 LANGUAGE 'c' IMMUTABLE; 2788 2789CREATE OR REPLACE FUNCTION postgis_svn_version() RETURNS text 2790 AS 'MODULE_PATHNAME' 2791 LANGUAGE 'c' IMMUTABLE; 2792 2793CREATE OR REPLACE FUNCTION postgis_libxml_version() RETURNS text 2794 AS 'MODULE_PATHNAME' 2795 LANGUAGE 'c' IMMUTABLE; 2796 2797CREATE OR REPLACE FUNCTION postgis_scripts_build_date() RETURNS text 2798 AS _POSTGIS_SQL_SELECT_POSTGIS_BUILD_DATE 2799 LANGUAGE 'sql' IMMUTABLE; 2800 2801CREATE OR REPLACE FUNCTION postgis_lib_build_date() RETURNS text 2802 AS 'MODULE_PATHNAME' 2803 LANGUAGE 'c' IMMUTABLE; 2804 2805CREATE OR REPLACE FUNCTION _postgis_scripts_pgsql_version() RETURNS text 2806 AS _POSTGIS_SQL_SELECT_POSTGIS_PGSQL_VERSION 2807 LANGUAGE 'sql' IMMUTABLE; 2808 2809CREATE OR REPLACE FUNCTION _postgis_pgsql_version() RETURNS text 2810AS $$ 2811 SELECT CASE WHEN split_part(s,'.',1)::integer > 9 THEN split_part(s,'.',1) || '0' ELSE split_part(s,'.', 1) || split_part(s,'.', 2) END AS v 2812 FROM substring(version(), 'PostgreSQL ([0-9\.]+)') AS s; 2813$$ LANGUAGE 'sql' STABLE; 2814 2815-- Availability: 2.5.0 2816CREATE OR REPLACE FUNCTION postgis_extensions_upgrade() RETURNS text 2817AS $$ 2818DECLARE rec record; sql text; 2819BEGIN 2820 -- if at a version different from default version or we are at a dev version, 2821 -- then do an upgrade to default version 2822 2823 FOR rec in SELECT name, default_version, installed_version 2824 FROM pg_available_extensions 2825 WHERE installed_version > '' AND name IN('postgis', 'postgis_sfcgal', 'postgis_tiger_geocoder', 'postgis_topology') 2826 AND ( default_version <> installed_version OR 2827 ( default_version = installed_version AND default_version ILIKE '%dev%' AND installed_version ILIKE '%dev%' ) ) LOOP 2828 2829 -- we need to upgrade to next so our installed is different from current 2830 -- and then we can upgrade to default_version 2831 IF rec.installed_version = rec.default_version THEN 2832 sql = 'ALTER EXTENSION ' || rec.name || ' UPDATE TO ' || quote_ident(rec.default_version || 'next') || ';'; 2833 EXECUTE sql; 2834 RAISE NOTICE '%', sql; 2835 END IF; 2836 2837 sql = 'ALTER EXTENSION ' || rec.name || ' UPDATE TO ' || quote_ident(rec.default_version) || ';'; 2838 EXECUTE sql; 2839 RAISE NOTICE '%', sql; 2840 END LOOP; 2841 2842 RETURN @extschema@.postgis_full_version(); 2843 2844END 2845$$ language plpgsql; 2846 2847-- Changed: 2.4.0 2848CREATE OR REPLACE FUNCTION postgis_full_version() RETURNS text 2849AS $$ 2850DECLARE 2851 libver text; 2852 svnver text; 2853 projver text; 2854 geosver text; 2855 sfcgalver text; 2856 cgalver text; 2857 gdalver text; 2858 libxmlver text; 2859 liblwgeomver text; 2860 dbproc text; 2861 relproc text; 2862 fullver text; 2863 rast_lib_ver text; 2864 rast_scr_ver text; 2865 topo_scr_ver text; 2866 json_lib_ver text; 2867 protobuf_lib_ver text; 2868 sfcgal_lib_ver text; 2869 sfcgal_scr_ver text; 2870 pgsql_scr_ver text; 2871 pgsql_ver text; 2872 core_is_extension bool; 2873BEGIN 2874 SELECT @extschema@.postgis_lib_version() INTO libver; 2875 SELECT @extschema@.postgis_proj_version() INTO projver; 2876 SELECT @extschema@.postgis_geos_version() INTO geosver; 2877 SELECT @extschema@.postgis_libjson_version() INTO json_lib_ver; 2878 SELECT @extschema@.postgis_libprotobuf_version() INTO protobuf_lib_ver; 2879 SELECT @extschema@._postgis_scripts_pgsql_version() INTO pgsql_scr_ver; 2880 SELECT @extschema@._postgis_pgsql_version() INTO pgsql_ver; 2881 BEGIN 2882 SELECT @extschema@.postgis_gdal_version() INTO gdalver; 2883 EXCEPTION 2884 WHEN undefined_function THEN 2885 gdalver := NULL; 2886 RAISE NOTICE 'Function postgis_gdal_version() not found. Is raster support enabled and rtpostgis.sql installed?'; 2887 END; 2888 BEGIN 2889 SELECT @extschema@.postgis_sfcgal_version() INTO sfcgalver; 2890 BEGIN 2891 SELECT @extschema@.postgis_sfcgal_scripts_installed() INTO sfcgal_scr_ver; 2892 EXCEPTION 2893 WHEN undefined_function THEN 2894 sfcgal_scr_ver := 'missing'; 2895 END; 2896 EXCEPTION 2897 WHEN undefined_function THEN 2898 sfcgalver := NULL; 2899 END; 2900 SELECT @extschema@.postgis_liblwgeom_version() INTO liblwgeomver; 2901 SELECT @extschema@.postgis_libxml_version() INTO libxmlver; 2902 SELECT @extschema@.postgis_scripts_installed() INTO dbproc; 2903 SELECT @extschema@.postgis_scripts_released() INTO relproc; 2904 select @extschema@.postgis_svn_version() INTO svnver; 2905 BEGIN 2906 SELECT topology.postgis_topology_scripts_installed() INTO topo_scr_ver; 2907 EXCEPTION 2908 WHEN undefined_function OR invalid_schema_name THEN 2909 topo_scr_ver := NULL; 2910 RAISE DEBUG 'Function postgis_topology_scripts_installed() not found. Is topology support enabled and topology.sql installed?'; 2911 WHEN insufficient_privilege THEN 2912 RAISE NOTICE 'Topology support cannot be inspected. Is current user granted USAGE on schema "topology" ?'; 2913 WHEN OTHERS THEN 2914 RAISE NOTICE 'Function postgis_topology_scripts_installed() could not be called: % (%)', SQLERRM, SQLSTATE; 2915 END; 2916 2917 BEGIN 2918 SELECT postgis_raster_scripts_installed() INTO rast_scr_ver; 2919 EXCEPTION 2920 WHEN undefined_function THEN 2921 rast_scr_ver := NULL; 2922 RAISE NOTICE 'Function postgis_raster_scripts_installed() not found. Is raster support enabled and rtpostgis.sql installed?'; 2923 END; 2924 2925 BEGIN 2926 SELECT @extschema@.postgis_raster_lib_version() INTO rast_lib_ver; 2927 EXCEPTION 2928 WHEN undefined_function THEN 2929 rast_lib_ver := NULL; 2930 RAISE NOTICE 'Function postgis_raster_lib_version() not found. Is raster support enabled and rtpostgis.sql installed?'; 2931 END; 2932 2933 fullver = 'POSTGIS="' || libver; 2934 2935 IF svnver IS NOT NULL THEN 2936 fullver = fullver || ' r' || svnver; 2937 END IF; 2938 2939 fullver = fullver || '"'; 2940 2941 IF EXISTS ( 2942 SELECT * FROM pg_catalog.pg_extension 2943 WHERE extname = 'postgis') 2944 THEN 2945 fullver = fullver || ' [EXTENSION]'; 2946 core_is_extension := true; 2947 ELSE 2948 core_is_extension := false; 2949 END IF; 2950 2951 IF liblwgeomver != relproc THEN 2952 fullver = fullver || ' (liblwgeom version mismatch: "' || liblwgeomver || '")'; 2953 END IF; 2954 2955 fullver = fullver || ' PGSQL="' || pgsql_scr_ver || '"'; 2956 IF pgsql_scr_ver != pgsql_ver THEN 2957 fullver = fullver || ' (procs need upgrade for use with "' || pgsql_ver || '")'; 2958 END IF; 2959 2960 IF geosver IS NOT NULL THEN 2961 fullver = fullver || ' GEOS="' || geosver || '"'; 2962 END IF; 2963 2964 IF sfcgalver IS NOT NULL THEN 2965 fullver = fullver || ' SFCGAL="' || sfcgalver || '"'; 2966 END IF; 2967 2968 IF projver IS NOT NULL THEN 2969 fullver = fullver || ' PROJ="' || projver || '"'; 2970 END IF; 2971 2972 IF gdalver IS NOT NULL THEN 2973 fullver = fullver || ' GDAL="' || gdalver || '"'; 2974 END IF; 2975 2976 IF libxmlver IS NOT NULL THEN 2977 fullver = fullver || ' LIBXML="' || libxmlver || '"'; 2978 END IF; 2979 2980 IF json_lib_ver IS NOT NULL THEN 2981 fullver = fullver || ' LIBJSON="' || json_lib_ver || '"'; 2982 END IF; 2983 2984 IF protobuf_lib_ver IS NOT NULL THEN 2985 fullver = fullver || ' LIBPROTOBUF="' || protobuf_lib_ver || '"'; 2986 END IF; 2987 2988 IF dbproc != relproc THEN 2989 fullver = fullver || ' (core procs from "' || dbproc || '" need upgrade)'; 2990 END IF; 2991 2992 IF topo_scr_ver IS NOT NULL THEN 2993 fullver = fullver || ' TOPOLOGY'; 2994 IF topo_scr_ver != relproc THEN 2995 fullver = fullver || ' (topology procs from "' || topo_scr_ver || '" need upgrade)'; 2996 END IF; 2997 IF core_is_extension AND NOT EXISTS ( 2998 SELECT * FROM pg_catalog.pg_extension 2999 WHERE extname = 'postgis_topology') 3000 THEN 3001 fullver = fullver || ' [UNPACKAGED!]'; 3002 END IF; 3003 END IF; 3004 3005 IF rast_lib_ver IS NOT NULL THEN 3006 fullver = fullver || ' RASTER'; 3007 IF rast_lib_ver != relproc THEN 3008 fullver = fullver || ' (raster lib from "' || rast_lib_ver || '" need upgrade)'; 3009 END IF; 3010 END IF; 3011 3012 IF rast_scr_ver IS NOT NULL AND rast_scr_ver != relproc THEN 3013 fullver = fullver || ' (raster procs from "' || rast_scr_ver || '" need upgrade)'; 3014 END IF; 3015 3016 IF sfcgal_scr_ver IS NOT NULL AND sfcgal_scr_ver != relproc THEN 3017 fullver = fullver || ' (sfcgal procs from "' || sfcgal_scr_ver || '" need upgrade)'; 3018 END IF; 3019 3020 RETURN fullver; 3021END 3022$$ 3023LANGUAGE 'plpgsql' IMMUTABLE; 3024 3025--------------------------------------------------------------- 3026-- CASTS 3027--------------------------------------------------------------- 3028 3029CREATE OR REPLACE FUNCTION box2d(geometry) 3030 RETURNS box2d 3031 AS 'MODULE_PATHNAME','LWGEOM_to_BOX2D' 3032 LANGUAGE 'c' IMMUTABLE STRICT _PARALLEL 3033 COST 10; 3034 3035CREATE OR REPLACE FUNCTION box3d(geometry) 3036 RETURNS box3d 3037 AS 'MODULE_PATHNAME','LWGEOM_to_BOX3D' 3038 LANGUAGE 'c' IMMUTABLE STRICT _PARALLEL 3039 COST 10; 3040 3041CREATE OR REPLACE FUNCTION box(geometry) 3042 RETURNS box 3043 AS 'MODULE_PATHNAME','LWGEOM_to_BOX' 3044 LANGUAGE 'c' IMMUTABLE STRICT _PARALLEL 3045 COST 10; 3046 3047CREATE OR REPLACE FUNCTION box2d(box3d) 3048 RETURNS box2d 3049 AS 'MODULE_PATHNAME','BOX3D_to_BOX2D' 3050 LANGUAGE 'c' IMMUTABLE STRICT _PARALLEL; 3051 3052CREATE OR REPLACE FUNCTION box3d(box2d) 3053 RETURNS box3d 3054 AS 'MODULE_PATHNAME','BOX2D_to_BOX3D' 3055 LANGUAGE 'c' IMMUTABLE STRICT _PARALLEL; 3056 3057CREATE OR REPLACE FUNCTION box(box3d) 3058 RETURNS box 3059 AS 'MODULE_PATHNAME','BOX3D_to_BOX' 3060 LANGUAGE 'c' IMMUTABLE STRICT _PARALLEL; 3061 3062CREATE OR REPLACE FUNCTION text(geometry) 3063 RETURNS text 3064 AS 'MODULE_PATHNAME','LWGEOM_to_text' 3065 LANGUAGE 'c' IMMUTABLE STRICT _PARALLEL 3066 COST 25; 3067 3068-- this is kept for backward-compatibility 3069-- Deprecation in 1.2.3 3070CREATE OR REPLACE FUNCTION box3dtobox(box3d) 3071 RETURNS box 3072 AS 'MODULE_PATHNAME','BOX3D_to_BOX' 3073 LANGUAGE 'c' IMMUTABLE STRICT _PARALLEL; 3074 3075CREATE OR REPLACE FUNCTION geometry(box2d) 3076 RETURNS geometry 3077 AS 'MODULE_PATHNAME','BOX2D_to_LWGEOM' 3078 LANGUAGE 'c' IMMUTABLE STRICT _PARALLEL; 3079 3080CREATE OR REPLACE FUNCTION geometry(box3d) 3081 RETURNS geometry 3082 AS 'MODULE_PATHNAME','BOX3D_to_LWGEOM' 3083 LANGUAGE 'c' IMMUTABLE STRICT _PARALLEL; 3084 3085CREATE OR REPLACE FUNCTION geometry(text) 3086 RETURNS geometry 3087 AS 'MODULE_PATHNAME','parse_WKT_lwgeom' 3088 LANGUAGE 'c' IMMUTABLE STRICT _PARALLEL; 3089 3090CREATE OR REPLACE FUNCTION geometry(bytea) 3091 RETURNS geometry 3092 AS 'MODULE_PATHNAME','LWGEOM_from_bytea' 3093 LANGUAGE 'c' IMMUTABLE STRICT _PARALLEL; 3094 3095CREATE OR REPLACE FUNCTION bytea(geometry) 3096 RETURNS bytea 3097 AS 'MODULE_PATHNAME','LWGEOM_to_bytea' 3098 LANGUAGE 'c' IMMUTABLE STRICT _PARALLEL 3099 COST 10; 3100 3101-- 7.3+ explicit casting definitions 3102CREATE CAST (geometry AS box2d) WITH FUNCTION box2d(geometry) AS IMPLICIT; 3103CREATE CAST (geometry AS box3d) WITH FUNCTION box3d(geometry) AS IMPLICIT; 3104 3105-- ticket: 2262 changed 2.1.0 to assignment to prevent PostGIS 3106-- from misusing PostgreSQL geometric functions 3107CREATE CAST (geometry AS box) WITH FUNCTION box(geometry) AS ASSIGNMENT; 3108 3109CREATE CAST (box3d AS box2d) WITH FUNCTION box2d(box3d) AS IMPLICIT; 3110CREATE CAST (box2d AS box3d) WITH FUNCTION box3d(box2d) AS IMPLICIT; 3111CREATE CAST (box2d AS geometry) WITH FUNCTION geometry(box2d) AS IMPLICIT; 3112CREATE CAST (box3d AS box) WITH FUNCTION box(box3d) AS IMPLICIT; 3113CREATE CAST (box3d AS geometry) WITH FUNCTION geometry(box3d) AS IMPLICIT; 3114CREATE CAST (text AS geometry) WITH FUNCTION geometry(text) AS IMPLICIT; 3115CREATE CAST (geometry AS text) WITH FUNCTION text(geometry) AS IMPLICIT; 3116CREATE CAST (bytea AS geometry) WITH FUNCTION geometry(bytea) AS IMPLICIT; 3117CREATE CAST (geometry AS bytea) WITH FUNCTION bytea(geometry) AS IMPLICIT; 3118 3119--------------------------------------------------------------- 3120-- Algorithms 3121--------------------------------------------------------------- 3122 3123-- Availability: 1.2.2 3124CREATE OR REPLACE FUNCTION ST_Simplify(geometry, float8) 3125 RETURNS geometry 3126 AS 'MODULE_PATHNAME', 'LWGEOM_simplify2d' 3127 LANGUAGE 'c' IMMUTABLE STRICT _PARALLEL 3128 COST 1; -- reset cost, see #3675 3129 3130-- Availability: 2.2.0 3131CREATE OR REPLACE FUNCTION ST_Simplify(geometry, float8, boolean) 3132 RETURNS geometry 3133 AS 'MODULE_PATHNAME', 'LWGEOM_simplify2d' 3134 LANGUAGE 'c' IMMUTABLE STRICT _PARALLEL 3135 COST 1; -- reset cost, see #3675 3136 3137-- Availability: 2.2.0 3138CREATE OR REPLACE FUNCTION ST_SimplifyVW(geometry, float8) 3139 RETURNS geometry 3140 AS 'MODULE_PATHNAME', 'LWGEOM_SetEffectiveArea' 3141 LANGUAGE 'c' IMMUTABLE STRICT _PARALLEL 3142 COST 1; -- reset cost, see #3675 3143 3144-- Availability: 2.2.0 3145CREATE OR REPLACE FUNCTION ST_SetEffectiveArea(geometry, float8 default -1, integer default 1) 3146 RETURNS geometry 3147 AS 'MODULE_PATHNAME', 'LWGEOM_SetEffectiveArea' 3148 LANGUAGE 'c' IMMUTABLE STRICT _PARALLEL 3149 COST 1; -- reset cost, see #3675 3150 3151-- Availability: 2.5.0 3152CREATE OR REPLACE FUNCTION ST_FilterByM(geometry, double precision, double precision default null, boolean default false) 3153 RETURNS geometry 3154 AS 'MODULE_PATHNAME', 'LWGEOM_FilterByM' 3155 LANGUAGE 'c' IMMUTABLE _PARALLEL 3156 COST 1; -- reset cost, see #3675 3157 3158-- Availability: 2.5.0 3159CREATE OR REPLACE FUNCTION ST_ChaikinSmoothing(geometry, integer default 1, boolean default false) 3160 RETURNS geometry 3161 AS 'MODULE_PATHNAME', 'LWGEOM_ChaikinSmoothing' 3162 LANGUAGE 'c' IMMUTABLE STRICT _PARALLEL 3163 COST 1; -- reset cost, see #3675 3164 3165-- ST_SnapToGrid(input, xoff, yoff, xsize, ysize) 3166-- Availability: 1.2.2 3167CREATE OR REPLACE FUNCTION ST_SnapToGrid(geometry, float8, float8, float8, float8) 3168 RETURNS geometry 3169 AS 'MODULE_PATHNAME', 'LWGEOM_snaptogrid' 3170 LANGUAGE 'c' IMMUTABLE STRICT _PARALLEL 3171 COST 1; -- reset cost, see #3675 3172 3173-- ST_SnapToGrid(input, xsize, ysize) # offsets=0 3174-- Availability: 1.2.2 3175CREATE OR REPLACE FUNCTION ST_SnapToGrid(geometry, float8, float8) 3176 RETURNS geometry 3177 AS 'SELECT @extschema@.ST_SnapToGrid($1, 0, 0, $2, $3)' 3178 LANGUAGE 'sql' IMMUTABLE STRICT _PARALLEL 3179 COST 1; -- reset cost, see #3675 3180 3181-- ST_SnapToGrid(input, size) # xsize=ysize=size, offsets=0 3182-- Availability: 1.2.2 3183CREATE OR REPLACE FUNCTION ST_SnapToGrid(geometry, float8) 3184 RETURNS geometry 3185 AS 'SELECT @extschema@.ST_SnapToGrid($1, 0, 0, $2, $2)' 3186 LANGUAGE 'sql' IMMUTABLE STRICT _PARALLEL; 3187 3188-- ST_SnapToGrid(input, point_offsets, xsize, ysize, zsize, msize) 3189-- Availability: 1.2.2 3190CREATE OR REPLACE FUNCTION ST_SnapToGrid(geom1 geometry, geom2 geometry, float8, float8, float8, float8) 3191 RETURNS geometry 3192 AS 'MODULE_PATHNAME', 'LWGEOM_snaptogrid_pointoff' 3193 LANGUAGE 'c' IMMUTABLE STRICT _PARALLEL 3194 COST 1; -- reset cost, see #3675 3195 3196-- Availability: 1.2.2 3197CREATE OR REPLACE FUNCTION ST_Segmentize(geometry, float8) 3198 RETURNS geometry 3199 AS 'MODULE_PATHNAME', 'LWGEOM_segmentize2d' 3200 LANGUAGE 'c' IMMUTABLE STRICT _PARALLEL 3201 COST 1; -- reset cost, see #3675 3202 3203--------------------------------------------------------------- 3204-- LRS 3205--------------------------------------------------------------- 3206 3207-- Availability: 2.1.0 3208CREATE OR REPLACE FUNCTION ST_LineInterpolatePoint(geometry, float8) 3209 RETURNS geometry 3210 AS 'MODULE_PATHNAME', 'LWGEOM_line_interpolate_point' 3211 LANGUAGE 'c' IMMUTABLE STRICT _PARALLEL; 3212 3213-- Availability: 2.5.0 3214CREATE OR REPLACE FUNCTION ST_LineInterpolatePoints(geometry, float8, repeat boolean DEFAULT true) 3215 RETURNS geometry 3216 AS 'MODULE_PATHNAME', 'LWGEOM_line_interpolate_point' 3217 LANGUAGE 'c' IMMUTABLE STRICT _PARALLEL; 3218 3219-- Availability: 1.2.2 3220-- Deprecation in 2.1.0 3221CREATE OR REPLACE FUNCTION ST_line_interpolate_point(geometry, float8) 3222 RETURNS geometry AS 3223 $$ SELECT @extschema@._postgis_deprecate('ST_Line_Interpolate_Point', 'ST_LineInterpolatePoint', '2.1.0'); 3224 SELECT @extschema@.ST_LineInterpolatePoint($1, $2); 3225 $$ 3226 LANGUAGE 'sql' IMMUTABLE STRICT _PARALLEL; 3227 3228-- Availability: 2.1.0 3229CREATE OR REPLACE FUNCTION ST_LineSubstring(geometry, float8, float8) 3230 RETURNS geometry 3231 AS 'MODULE_PATHNAME', 'LWGEOM_line_substring' 3232 LANGUAGE 'c' IMMUTABLE STRICT _PARALLEL; 3233 3234-- Availability: 1.2.2 3235-- Deprecation in 2.1.0 3236CREATE OR REPLACE FUNCTION ST_line_substring(geometry, float8, float8) 3237 RETURNS geometry AS 3238 $$ SELECT @extschema@._postgis_deprecate('ST_Line_Substring', 'ST_LineSubstring', '2.1.0'); 3239 SELECT @extschema@.ST_LineSubstring($1, $2, $3); 3240 $$ 3241 LANGUAGE 'sql' IMMUTABLE STRICT _PARALLEL; 3242 3243-- Availability: 2.1.0 3244CREATE OR REPLACE FUNCTION ST_LineLocatePoint(geom1 geometry, geom2 geometry) 3245 RETURNS float8 3246 AS 'MODULE_PATHNAME', 'LWGEOM_line_locate_point' 3247 LANGUAGE 'c' IMMUTABLE STRICT _PARALLEL; 3248 3249-- Availability: 1.2.2 3250-- Deprecation in 2.1.0 3251CREATE OR REPLACE FUNCTION ST_line_locate_point(geom1 geometry, geom2 geometry) 3252 RETURNS float8 AS 3253 $$ SELECT @extschema@._postgis_deprecate('ST_Line_Locate_Point', 'ST_LineLocatePoint', '2.1.0'); 3254 SELECT @extschema@.ST_LineLocatePoint($1, $2); 3255 $$ 3256 LANGUAGE 'sql' IMMUTABLE STRICT _PARALLEL; 3257 3258-- Availability: 1.2.2 3259-- Deprecation in 2.0.0 replaced by ST_LocateBetween 3260-- TODO: switch to use of _postgis_deprecate() in 2.3.0 (or drop) 3261CREATE OR REPLACE FUNCTION ST_locate_between_measures(geometry, float8, float8) 3262 RETURNS geometry 3263 AS 'MODULE_PATHNAME', 'LWGEOM_locate_between_m' 3264 LANGUAGE 'c' IMMUTABLE STRICT _PARALLEL; 3265 3266-- Availability: 1.2.2 3267-- Deprecation in 2.0.0 replaced by ST_LocateAlong 3268-- TODO: switch to use of _postgis_deprecate() in 2.3.0 (or drop) 3269CREATE OR REPLACE FUNCTION ST_locate_along_measure(geometry, float8) 3270 RETURNS geometry 3271 AS $$ SELECT @extschema@.ST_locate_between_measures($1, $2, $2) $$ 3272 LANGUAGE 'sql' IMMUTABLE STRICT _PARALLEL; 3273 3274-- Availability: 1.5.0 3275CREATE OR REPLACE FUNCTION ST_AddMeasure(geometry, float8, float8) 3276 RETURNS geometry 3277 AS 'MODULE_PATHNAME', 'ST_AddMeasure' 3278 LANGUAGE 'c' IMMUTABLE STRICT _PARALLEL; 3279 3280--------------------------------------------------------------- 3281-- TEMPORAL 3282--------------------------------------------------------------- 3283 3284-- Availability: 2.2.0 3285CREATE OR REPLACE FUNCTION ST_ClosestPointOfApproach(geometry, geometry) 3286 RETURNS float8 3287 AS 'MODULE_PATHNAME', 'ST_ClosestPointOfApproach' 3288 LANGUAGE 'c' IMMUTABLE STRICT _PARALLEL; 3289 3290-- Availability: 2.2.0 3291CREATE OR REPLACE FUNCTION ST_DistanceCPA(geometry, geometry) 3292 RETURNS float8 3293 AS 'MODULE_PATHNAME', 'ST_DistanceCPA' 3294 LANGUAGE 'c' IMMUTABLE STRICT _PARALLEL; 3295 3296-- Availability: 2.2.0 3297CREATE OR REPLACE FUNCTION ST_CPAWithin(geometry, geometry, float8) 3298 RETURNS bool 3299 AS 'MODULE_PATHNAME', 'ST_CPAWithin' 3300 LANGUAGE 'c' IMMUTABLE STRICT _PARALLEL; 3301 3302-- Availability: 2.2.0 3303CREATE OR REPLACE FUNCTION ST_IsValidTrajectory(geometry) 3304 RETURNS bool 3305 AS 'MODULE_PATHNAME', 'ST_IsValidTrajectory' 3306 LANGUAGE 'c' IMMUTABLE STRICT _PARALLEL; 3307 3308--------------------------------------------------------------- 3309-- GEOS 3310--------------------------------------------------------------- 3311 3312-- PostGIS equivalent function: intersection(geom1 geometry, geom2 geometry) 3313CREATE OR REPLACE FUNCTION ST_Intersection(geom1 geometry, geom2 geometry) 3314 RETURNS geometry 3315 AS 'MODULE_PATHNAME','intersection' 3316 LANGUAGE 'c' IMMUTABLE STRICT _PARALLEL 3317 COST 1; -- reset cost, see #3675 3318 3319-- PostGIS equivalent function: buffer(geometry,float8) 3320CREATE OR REPLACE FUNCTION ST_Buffer(geometry,float8) 3321 RETURNS geometry 3322 AS 'MODULE_PATHNAME','buffer' 3323 LANGUAGE 'c' IMMUTABLE STRICT _PARALLEL 3324 COST 1; -- reset cost, see #3675 3325 3326-- Availability: 1.5.0 - requires GEOS-3.2 or higher 3327CREATE OR REPLACE FUNCTION _ST_Buffer(geometry,float8,cstring) 3328 RETURNS geometry 3329 AS 'MODULE_PATHNAME','buffer' 3330 LANGUAGE 'c' IMMUTABLE STRICT _PARALLEL 3331 COST 1; -- reset cost, see #3675 3332 3333-- Availability: 1.2.2 3334CREATE OR REPLACE FUNCTION ST_Buffer(geometry,float8,integer) 3335 RETURNS geometry 3336 AS $$ SELECT @extschema@._ST_Buffer($1, $2, 3337 CAST('quad_segs='||CAST($3 AS text) as cstring)) 3338 $$ 3339 LANGUAGE 'sql' IMMUTABLE STRICT _PARALLEL; 3340 3341-- Availability: 1.5.0 3342CREATE OR REPLACE FUNCTION ST_Buffer(geometry,float8,text) 3343 RETURNS geometry 3344 AS $$ SELECT @extschema@._ST_Buffer($1, $2, 3345 CAST( regexp_replace($3, '^[0123456789]+$', 3346 'quad_segs='||$3) AS cstring) 3347 ) 3348 $$ 3349 LANGUAGE 'sql' IMMUTABLE STRICT _PARALLEL; 3350 3351-- Availability: 2.3.0 3352CREATE OR REPLACE FUNCTION ST_MinimumBoundingRadius(geometry, OUT center geometry, OUT radius double precision) 3353 AS 'MODULE_PATHNAME', 'ST_MinimumBoundingRadius' 3354 LANGUAGE 'c' IMMUTABLE STRICT _PARALLEL; 3355 3356-- Availability: 1.4.0 3357CREATE OR REPLACE FUNCTION ST_MinimumBoundingCircle(inputgeom geometry, segs_per_quarter integer DEFAULT 48) 3358 RETURNS geometry 3359 AS 'MODULE_PATHNAME', 'ST_MinimumBoundingCircle' 3360 LANGUAGE 'c' IMMUTABLE STRICT _PARALLEL; 3361 3362-- Availability: 2.5.0 3363CREATE OR REPLACE FUNCTION ST_OrientedEnvelope(geometry) 3364 RETURNS geometry 3365 AS 'MODULE_PATHNAME', 'ST_OrientedEnvelope' 3366 LANGUAGE 'c' IMMUTABLE STRICT _PARALLEL; 3367 3368-- Availability: 2.0.0 - requires GEOS-3.2 or higher 3369CREATE OR REPLACE FUNCTION ST_OffsetCurve(line geometry, distance float8, params text DEFAULT '') 3370 RETURNS geometry 3371 AS 'MODULE_PATHNAME','ST_OffsetCurve' 3372 LANGUAGE 'c' IMMUTABLE STRICT _PARALLEL 3373 COST 1; -- reset cost, see #3675 3374 3375-- Availability: 2.3.0 3376CREATE OR REPLACE FUNCTION ST_GeneratePoints(area geometry, npoints numeric) 3377 RETURNS geometry 3378 AS 'MODULE_PATHNAME','ST_GeneratePoints' 3379 LANGUAGE 'c' IMMUTABLE STRICT _PARALLEL 3380 COST 1; -- reset cost, see #3675 3381 3382-- PostGIS equivalent function: convexhull(geometry) 3383CREATE OR REPLACE FUNCTION ST_ConvexHull(geometry) 3384 RETURNS geometry 3385 AS 'MODULE_PATHNAME','convexhull' 3386 LANGUAGE 'c' IMMUTABLE STRICT _PARALLEL 3387 COST 1; -- reset cost, see #3675 3388 3389-- Only accepts LINESTRING as parameters. 3390-- Availability: 1.4.0 3391CREATE OR REPLACE FUNCTION _ST_LineCrossingDirection(geom1 geometry, geom2 geometry) 3392 RETURNS integer 3393 AS 'MODULE_PATHNAME', 'ST_LineCrossingDirection' 3394 LANGUAGE 'c' IMMUTABLE STRICT _PARALLEL 3395 COST 100; -- Guessed cost 3396 3397-- Availability: 1.4.0 3398CREATE OR REPLACE FUNCTION ST_LineCrossingDirection(geom1 geometry, geom2 geometry) 3399 RETURNS integer AS 3400 $$ SELECT CASE WHEN NOT $1 OPERATOR(@extschema@.&&) $2 THEN 0 ELSE @extschema@._ST_LineCrossingDirection($1,$2) END $$ 3401 LANGUAGE 'sql' IMMUTABLE _PARALLEL; 3402 3403-- Requires GEOS >= 3.0.0 3404-- Availability: 1.3.3 3405CREATE OR REPLACE FUNCTION ST_SimplifyPreserveTopology(geometry, float8) 3406 RETURNS geometry 3407 AS 'MODULE_PATHNAME','topologypreservesimplify' 3408 LANGUAGE 'c' IMMUTABLE STRICT _PARALLEL 3409 COST 1; -- reset cost, see #3675 3410 3411-- Requires GEOS >= 3.1.0 3412-- Availability: 1.4.0 3413CREATE OR REPLACE FUNCTION ST_IsValidReason(geometry) 3414 RETURNS text 3415 AS 'MODULE_PATHNAME', 'isvalidreason' 3416 LANGUAGE 'c' IMMUTABLE STRICT _PARALLEL 3417 COST 1000; 3418 3419-- Availability: 2.0.0 3420CREATE TYPE valid_detail AS ( 3421 valid bool, 3422 reason varchar, 3423 location geometry 3424); 3425 3426-- Requires GEOS >= 3.3.0 3427-- Availability: 2.0.0 3428CREATE OR REPLACE FUNCTION ST_IsValidDetail(geometry) 3429 RETURNS valid_detail 3430 AS 'MODULE_PATHNAME', 'isvaliddetail' 3431 LANGUAGE 'c' IMMUTABLE STRICT _PARALLEL 3432 COST 1000; 3433 3434-- Requires GEOS >= 3.3.0 3435-- Availability: 2.0.0 3436CREATE OR REPLACE FUNCTION ST_IsValidDetail(geometry, int4) 3437 RETURNS valid_detail 3438 AS 'MODULE_PATHNAME', 'isvaliddetail' 3439 LANGUAGE 'c' IMMUTABLE STRICT _PARALLEL 3440 COST 1000; 3441 3442-- Requires GEOS >= 3.3.0 3443-- Availability: 2.0.0 3444CREATE OR REPLACE FUNCTION ST_IsValidReason(geometry, int4) 3445 RETURNS text 3446 AS $$ 3447SELECT CASE WHEN valid THEN 'Valid Geometry' ELSE reason END FROM ( 3448 SELECT (@extschema@.ST_isValidDetail($1, $2)).* 3449) foo 3450 $$ 3451 LANGUAGE 'sql' IMMUTABLE STRICT _PARALLEL 3452 COST 100; 3453 3454-- Requires GEOS >= 3.3.0 3455-- Availability: 2.0.0 3456CREATE OR REPLACE FUNCTION ST_IsValid(geometry, int4) 3457 RETURNS boolean 3458 AS 'SELECT (@extschema@.ST_isValidDetail($1, $2)).valid' 3459 LANGUAGE 'sql' IMMUTABLE STRICT _PARALLEL; 3460 3461-- Requires GEOS >= 3.2.0 3462-- Availability: 1.5.0 3463CREATE OR REPLACE FUNCTION ST_HausdorffDistance(geom1 geometry, geom2 geometry) 3464 RETURNS FLOAT8 3465 AS 'MODULE_PATHNAME', 'hausdorffdistance' 3466 LANGUAGE 'c' IMMUTABLE STRICT _PARALLEL 3467 COST 100; -- Guessed cost 3468 3469-- Requires GEOS >= 3.2.0 3470-- Availability: 1.5.0 3471CREATE OR REPLACE FUNCTION ST_HausdorffDistance(geom1 geometry, geom2 geometry, float8) 3472 RETURNS FLOAT8 3473 AS 'MODULE_PATHNAME', 'hausdorffdistancedensify' 3474 LANGUAGE 'c' IMMUTABLE STRICT _PARALLEL 3475 COST 100; -- Guessed cost 3476 3477-- Requires GEOS >= 3.7.0 3478-- Availability: 2.4.0 3479CREATE OR REPLACE FUNCTION ST_FrechetDistance(geom1 geometry, geom2 geometry, float8 default -1) 3480 RETURNS FLOAT8 3481 AS 'MODULE_PATHNAME', 'ST_FrechetDistance' 3482 LANGUAGE 'c' IMMUTABLE STRICT _PARALLEL 3483 COST 100; -- Guessed cost 3484 3485-- PostGIS equivalent function: difference(geom1 geometry, geom2 geometry) 3486CREATE OR REPLACE FUNCTION ST_Difference(geom1 geometry, geom2 geometry) 3487 RETURNS geometry 3488 AS 'MODULE_PATHNAME','difference' 3489 LANGUAGE 'c' IMMUTABLE STRICT _PARALLEL 3490 COST 100; --guessed based on ST_Intersection 3491 3492-- PostGIS equivalent function: boundary(geometry) 3493CREATE OR REPLACE FUNCTION ST_Boundary(geometry) 3494 RETURNS geometry 3495 AS 'MODULE_PATHNAME','boundary' 3496 LANGUAGE 'c' IMMUTABLE STRICT _PARALLEL; 3497 3498-- Availability: 2.3.0 3499CREATE OR REPLACE FUNCTION ST_Points(geometry) 3500 RETURNS geometry 3501 AS 'MODULE_PATHNAME', 'ST_Points' 3502 LANGUAGE 'c' IMMUTABLE STRICT _PARALLEL; 3503 3504-- PostGIS equivalent function: symdifference(geom1 geometry, geom2 geometry) 3505CREATE OR REPLACE FUNCTION ST_SymDifference(geom1 geometry, geom2 geometry) 3506 RETURNS geometry 3507 AS 'MODULE_PATHNAME','symdifference' 3508 LANGUAGE 'c' IMMUTABLE STRICT _PARALLEL; 3509 3510-- Availability: 1.2.2 3511CREATE OR REPLACE FUNCTION ST_symmetricdifference(geom1 geometry, geom2 geometry) 3512 RETURNS geometry 3513 AS 'MODULE_PATHNAME','symdifference' 3514 LANGUAGE 'c' IMMUTABLE STRICT _PARALLEL; 3515 3516-- PostGIS equivalent function: GeomUnion(geom1 geometry, geom2 geometry) 3517CREATE OR REPLACE FUNCTION ST_Union(geom1 geometry, geom2 geometry) 3518 RETURNS geometry 3519 AS 'MODULE_PATHNAME','geomunion' 3520 LANGUAGE 'c' IMMUTABLE STRICT _PARALLEL; 3521 3522-- Availability: 2.0.0 3523-- Requires: GEOS-3.3.0 3524CREATE OR REPLACE FUNCTION ST_UnaryUnion(geometry) 3525 RETURNS geometry 3526 AS 'MODULE_PATHNAME','ST_UnaryUnion' 3527 LANGUAGE 'c' IMMUTABLE STRICT _PARALLEL; 3528 3529-- ST_RemoveRepeatedPoints(in geometry) 3530-- 3531-- Removes duplicate vertices in input. 3532-- Only checks consecutive points for lineal and polygonal geoms. 3533-- Checks all points for multipoint geoms. 3534-- 3535-- Availability: 2.2.0 3536CREATE OR REPLACE FUNCTION ST_RemoveRepeatedPoints(geom geometry, tolerance float8 default 0.0) 3537 RETURNS geometry 3538 AS 'MODULE_PATHNAME', 'ST_RemoveRepeatedPoints' 3539 LANGUAGE 'c' IMMUTABLE STRICT _PARALLEL 3540 COST 1; -- reset cost, see #3675 3541 3542-- Requires GEOS >= 3.5.0 3543-- Availability: 2.2.0 3544CREATE OR REPLACE FUNCTION ST_ClipByBox2d(geom geometry, box box2d) 3545 RETURNS geometry 3546 AS 'MODULE_PATHNAME', 'ST_ClipByBox2d' 3547 LANGUAGE 'c' IMMUTABLE STRICT _PARALLEL 3548 COST 50; -- Guessed cost 3549 3550-- Requires GEOS >= 3.5.0 3551-- Availability: 2.2.0 3552CREATE OR REPLACE FUNCTION ST_Subdivide(geom geometry, maxvertices integer DEFAULT 256) 3553 RETURNS setof geometry 3554 AS 'MODULE_PATHNAME', 'ST_Subdivide' 3555 LANGUAGE 'c' IMMUTABLE STRICT _PARALLEL 3556 COST 100; -- Guessed cost 3557 3558-------------------------------------------------------------------------------- 3559-- ST_CleanGeometry / ST_MakeValid 3560-------------------------------------------------------------------------------- 3561 3562-- ST_MakeValid(in geometry) 3563-- 3564-- Try to make the input valid maintaining the boundary profile. 3565-- May return a collection. 3566-- May return a geometry with inferior dimensions (dimensional collapses). 3567-- May return NULL if can't handle input. 3568-- 3569-- Requires: GEOS-3.3.0 3570-- Availability: 2.0.0 3571CREATE OR REPLACE FUNCTION ST_MakeValid(geometry) 3572 RETURNS geometry 3573 AS 'MODULE_PATHNAME', 'ST_MakeValid' 3574 LANGUAGE 'c' IMMUTABLE STRICT _PARALLEL 3575 COST 1; -- reset cost, see #3675 3576 3577-- ST_CleanGeometry(in geometry) 3578-- 3579-- Make input: 3580-- - Simple (lineal components) 3581-- - Valid (polygonal components) 3582-- - Obeying the RHR (if polygonal) 3583-- - Simplified of consecutive duplicated points 3584-- Ensuring: 3585-- - No input vertexes are discarded (except consecutive repeated ones) 3586-- - Output geometry type matches input 3587-- 3588-- Returns NULL on failure. 3589-- 3590-- Requires: GEOS-3.3.0 3591-- Availability: 2.0.0 3592CREATE OR REPLACE FUNCTION ST_CleanGeometry(geometry) 3593 RETURNS geometry 3594 AS 'MODULE_PATHNAME', 'ST_CleanGeometry' 3595 LANGUAGE 'c' IMMUTABLE STRICT _PARALLEL 3596 COST 1; -- reset cost, see #3675 3597 3598-------------------------------------------------------------------------------- 3599-- ST_Split 3600-------------------------------------------------------------------------------- 3601 3602-- ST_Split(in geometry, blade geometry) 3603-- 3604-- Split a geometry in parts after cutting it with given blade. 3605-- Returns a collection containing all parts. 3606-- 3607-- Note that multi-part geometries will be returned exploded, 3608-- no matter relation to blade. 3609-- 3610-- Availability: 2.0.0 3611-- 3612CREATE OR REPLACE FUNCTION ST_Split(geom1 geometry, geom2 geometry) 3613 RETURNS geometry 3614 AS 'MODULE_PATHNAME', 'ST_Split' 3615 LANGUAGE 'c' IMMUTABLE STRICT _PARALLEL 3616 COST 1; -- reset cost, see #3675 3617 3618-------------------------------------------------------------------------------- 3619-- ST_SharedPaths 3620-------------------------------------------------------------------------------- 3621 3622-- ST_SharedPaths(lineal1 geometry, lineal1 geometry) 3623-- 3624-- Returns a collection containing paths shared by the two 3625-- input geometries. Those going in the same direction are 3626-- in the first element of the collection, those going in the 3627-- opposite direction are in the second element. 3628-- 3629-- The paths themselves are given in the direction of the 3630-- first geometry. 3631-- 3632-- Availability: 2.0.0 3633-- Requires GEOS >= 3.3.0 3634-- 3635CREATE OR REPLACE FUNCTION ST_SharedPaths(geom1 geometry, geom2 geometry) 3636 RETURNS geometry 3637 AS 'MODULE_PATHNAME', 'ST_SharedPaths' 3638 LANGUAGE 'c' IMMUTABLE STRICT _PARALLEL 3639 COST 1; -- reset cost, see #3675 3640 3641-------------------------------------------------------------------------------- 3642-- ST_Snap 3643-------------------------------------------------------------------------------- 3644 3645-- ST_Snap(g1 geometry, g2 geometry, tolerance float8) 3646-- 3647-- Snap first geometry against second. 3648-- 3649-- Availability: 2.0.0 3650-- Requires GEOS >= 3.3.0 3651-- 3652CREATE OR REPLACE FUNCTION ST_Snap(geom1 geometry, geom2 geometry, float8) 3653 RETURNS geometry 3654 AS 'MODULE_PATHNAME', 'ST_Snap' 3655 LANGUAGE 'c' IMMUTABLE STRICT _PARALLEL 3656 COST 1; -- reset cost, see #3675 3657 3658-------------------------------------------------------------------------------- 3659-- ST_RelateMatch 3660-------------------------------------------------------------------------------- 3661 3662-- ST_RelateMatch(matrix text, pattern text) 3663-- 3664-- Returns true if pattern 'pattern' matches DE9 intersection matrix 'matrix' 3665-- 3666-- Availability: 2.0.0 3667-- Requires GEOS >= 3.3.0 3668-- 3669CREATE OR REPLACE FUNCTION ST_RelateMatch(text, text) 3670 RETURNS bool 3671 AS 'MODULE_PATHNAME', 'ST_RelateMatch' 3672 LANGUAGE 'c' IMMUTABLE STRICT _PARALLEL 3673 COST 100; -- Guessed cost 3674 3675-------------------------------------------------------------------------------- 3676-- ST_Node 3677-------------------------------------------------------------------------------- 3678 3679-- ST_Node(in geometry) 3680-- 3681-- Fully node lines in input using the least set of nodes while 3682-- preserving each of the input ones. 3683-- Returns a linestring or a multilinestring containing all parts. 3684-- 3685-- Availability: 2.0.0 3686-- Requires GEOS >= 3.3.0 3687-- 3688CREATE OR REPLACE FUNCTION ST_Node(g geometry) 3689 RETURNS geometry 3690 AS 'MODULE_PATHNAME', 'ST_Node' 3691 LANGUAGE 'c' IMMUTABLE STRICT _PARALLEL 3692 COST 1; -- reset cost, see #3675 3693 3694-------------------------------------------------------------------------------- 3695-- ST_DelaunayTriangles 3696-------------------------------------------------------------------------------- 3697 3698-- ST_DelaunayTriangles(g1 geometry, tolerance float8, flags int4) 3699-- 3700-- Builds Delaunay triangulation out of geometry vertices. 3701-- 3702-- Returns a collection of triangular polygons with flags=0 3703-- or a multilinestring with flags=1 3704-- 3705-- If a tolerance is given it will be used to snap the input points 3706-- each-other. 3707-- 3708-- 3709-- Availability: 2.1.0 3710-- 3711CREATE OR REPLACE FUNCTION ST_DelaunayTriangles(g1 geometry, tolerance float8 DEFAULT 0.0, flags int4 DEFAULT 0) 3712 RETURNS geometry 3713 AS 'MODULE_PATHNAME', 'ST_DelaunayTriangles' 3714 LANGUAGE 'c' IMMUTABLE STRICT _PARALLEL 3715 COST 1; -- reset cost, see #3675 3716 3717-------------------------------------------------------------------------------- 3718-- _ST_Voronoi 3719-------------------------------------------------------------------------------- 3720 3721-- ST_Voronoi(g1 geometry, clip geometry, tolerance float8, return_polygons boolean) 3722-- 3723-- Builds a Voronoi Diagram from the vertices of the supplied geometry. 3724-- 3725-- By default, the diagram will be extended to an envelope larger than the 3726-- input points. 3727-- 3728-- If a second geometry is supplied, the diagram will be extended to fill the 3729-- envelope of the second geometry, unless that is smaller than the default 3730-- envelope. 3731-- 3732-- If a tolerance is given it will be used to snap the input points 3733-- each-other. 3734-- 3735-- If return_polygons is true, returns a GeometryCollection of polygons. 3736-- If return_polygons is false, returns a MultiLineString. 3737-- 3738-- Availability: 2.3.0 3739-- Requires GEOS >= 3.5.0 3740-- 3741 3742CREATE OR REPLACE FUNCTION _ST_Voronoi(g1 geometry, clip geometry DEFAULT NULL, tolerance float8 DEFAULT 0.0, return_polygons boolean DEFAULT true) 3743 RETURNS geometry 3744 AS 'MODULE_PATHNAME', 'ST_Voronoi' 3745 LANGUAGE 'c' IMMUTABLE _PARALLEL 3746 COST 1; -- reset cost, see #3675 3747 3748CREATE OR REPLACE FUNCTION ST_VoronoiPolygons(g1 geometry, tolerance float8 DEFAULT 0.0, extend_to geometry DEFAULT NULL) 3749 RETURNS geometry 3750 AS $$ SELECT @extschema@._ST_Voronoi(g1, extend_to, tolerance, true) $$ 3751 LANGUAGE SQL IMMUTABLE _PARALLEL 3752 COST 1; -- reset cost, see #3675 3753 3754CREATE OR REPLACE FUNCTION ST_VoronoiLines(g1 geometry, tolerance float8 DEFAULT 0.0, extend_to geometry DEFAULT NULL) 3755 RETURNS geometry 3756 AS $$ SELECT @extschema@._ST_Voronoi(g1, extend_to, tolerance, false) $$ 3757 LANGUAGE SQL IMMUTABLE _PARALLEL 3758 COST 1; -- reset cost, see #3675 3759 3760-------------------------------------------------------------------------------- 3761-- Aggregates and their supporting functions 3762-------------------------------------------------------------------------------- 3763 3764------------------------------------------------------------------------ 3765 3766-- Availability: 2.2.0 3767CREATE OR REPLACE FUNCTION ST_CombineBBox(box3d,geometry) 3768 RETURNS box3d 3769 AS 'MODULE_PATHNAME', 'BOX3D_combine' 3770 LANGUAGE 'c' IMMUTABLE _PARALLEL; 3771 3772-- Availability: 2.3.0 3773CREATE OR REPLACE FUNCTION ST_CombineBBox(box3d,box3d) 3774 RETURNS box3d 3775 AS 'MODULE_PATHNAME', 'BOX3D_combine_BOX3D' 3776 LANGUAGE 'c' IMMUTABLE _PARALLEL; 3777 3778-- Availability: 1.2.2 3779-- Deprecation in 2.2.0 3780CREATE OR REPLACE FUNCTION ST_Combine_BBox(box3d,geometry) 3781 RETURNS box3d AS 3782 $$ SELECT @extschema@._postgis_deprecate('ST_Combine_BBox', 'ST_CombineBbox', '2.2.0'); 3783 SELECT @extschema@.ST_CombineBbox($1,$2); 3784 $$ 3785 LANGUAGE 'sql' IMMUTABLE; 3786 3787-- Availability: 2.2.0 3788CREATE OR REPLACE FUNCTION ST_CombineBbox(box2d,geometry) 3789 RETURNS box2d 3790 AS 'MODULE_PATHNAME', 'BOX2D_combine' 3791 LANGUAGE 'c' IMMUTABLE _PARALLEL; 3792 3793-- Availability: 1.2.2 3794-- Deprecation in 2.2.0 3795CREATE OR REPLACE FUNCTION ST_Combine_BBox(box2d,geometry) 3796 RETURNS box2d AS 3797 $$ SELECT @extschema@._postgis_deprecate('ST_Combine_BBox', 'ST_CombineBbox', '2.2.0'); 3798 SELECT @extschema@.ST_CombineBbox($1,$2); 3799 $$ 3800 LANGUAGE 'sql' IMMUTABLE; 3801 3802-- Availability: 1.2.2 3803-- Changed: 2.2.0 to use non-deprecated ST_CombineBBox (r13535) 3804-- Changed: 2.3.0 to support PostgreSQL 9.6 3805-- Changed: 2.3.1 to support PostgreSQL 9.6 parallel safe 3806CREATE AGGREGATE ST_Extent(geometry) ( 3807 sfunc = ST_CombineBBox, 3808 stype = box3d, 3809#if POSTGIS_PGSQL_VERSION >= 96 3810 combinefunc = ST_CombineBBox, 3811 parallel = safe, 3812#endif 3813 finalfunc = box2d 3814 ); 3815 3816-- Availability: 2.0.0 3817-- Changed: 2.2.0 to use non-deprecated ST_CombineBBox (r13535) 3818-- Changed: 2.3.0 to support PostgreSQL 9.6 3819-- Changed: 2.3.1 to support PostgreSQL 9.6 parallel safe 3820CREATE AGGREGATE ST_3DExtent(geometry)( 3821 sfunc = ST_CombineBBox, 3822#if POSTGIS_PGSQL_VERSION >= 96 3823 combinefunc = ST_CombineBBox, 3824 parallel = safe, 3825#endif 3826 stype = box3d 3827 ); 3828 3829-- Availability: 1.2.2 3830CREATE OR REPLACE FUNCTION ST_Collect(geom1 geometry, geom2 geometry) 3831 RETURNS geometry 3832 AS 'MODULE_PATHNAME', 'LWGEOM_collect' 3833 LANGUAGE 'c' IMMUTABLE _PARALLEL; 3834 3835-- Availability: 1.2.2 3836-- Changed: 2.3.0 to support PostgreSQL 9.6 3837-- Changed: 2.3.1 to support PostgreSQL 9.6 parallel safe 3838CREATE AGGREGATE ST_MemCollect(geometry)( 3839 sfunc = ST_collect, 3840#if POSTGIS_PGSQL_VERSION >= 96 3841 combinefunc = ST_collect, 3842 parallel = safe, 3843#endif 3844 stype = geometry 3845 ); 3846 3847-- Availability: 1.2.2 3848CREATE OR REPLACE FUNCTION ST_Collect(geometry[]) 3849 RETURNS geometry 3850 AS 'MODULE_PATHNAME', 'LWGEOM_collect_garray' 3851 LANGUAGE 'c' IMMUTABLE STRICT _PARALLEL; 3852 3853-- Availability: 1.2.2 3854-- Changed: 2.3.0 to support PostgreSQL 9.6 3855-- Changed: 2.3.1 to support PostgreSQL 9.6 parallel safe 3856CREATE AGGREGATE ST_MemUnion(geometry) ( 3857 sfunc = ST_Union, 3858#if POSTGIS_PGSQL_VERSION >= 96 3859 combinefunc = ST_Union, 3860 parallel = safe, 3861#endif 3862 stype = geometry 3863 ); 3864 3865 3866-- Availability: 1.4.0 3867-- Changed: 2.5.0 use 'internal' transfer type 3868CREATE OR REPLACE FUNCTION pgis_geometry_accum_transfn(internal, geometry) 3869 RETURNS internal 3870 AS 'MODULE_PATHNAME' 3871 LANGUAGE 'c' _PARALLEL; 3872 3873-- Availability: 2.2 3874-- Changed: 2.5.0 use 'internal' transfer type 3875CREATE OR REPLACE FUNCTION pgis_geometry_accum_transfn(internal, geometry, float8) 3876 RETURNS internal 3877 AS 'MODULE_PATHNAME' 3878 LANGUAGE 'c' _PARALLEL; 3879 3880-- Availability: 2.3 3881-- Changed: 2.5.0 use 'internal' transfer type 3882CREATE OR REPLACE FUNCTION pgis_geometry_accum_transfn(internal, geometry, float8, int) 3883 RETURNS internal 3884 AS 'MODULE_PATHNAME' 3885 LANGUAGE 'c' _PARALLEL; 3886 3887-- Availability: 1.4.0 3888-- Changed: 2.5.0 use 'internal' transfer type 3889CREATE OR REPLACE FUNCTION pgis_geometry_accum_finalfn(internal) 3890 RETURNS geometry[] 3891 AS 'MODULE_PATHNAME' 3892 LANGUAGE 'c' _PARALLEL; 3893 3894-- Availability: 1.4.0 3895-- Changed: 2.5.0 use 'internal' transfer type 3896CREATE OR REPLACE FUNCTION pgis_geometry_union_finalfn(internal) 3897 RETURNS geometry 3898 AS 'MODULE_PATHNAME' 3899 LANGUAGE 'c' _PARALLEL; 3900 3901-- Availability: 1.4.0 3902-- Changed: 2.5.0 use 'internal' transfer type 3903CREATE OR REPLACE FUNCTION pgis_geometry_collect_finalfn(internal) 3904 RETURNS geometry 3905 AS 'MODULE_PATHNAME' 3906 LANGUAGE 'c' _PARALLEL; 3907 3908-- Availability: 1.4.0 3909-- Changed: 2.5.0 use 'internal' transfer type 3910CREATE OR REPLACE FUNCTION pgis_geometry_polygonize_finalfn(internal) 3911 RETURNS geometry 3912 AS 'MODULE_PATHNAME' 3913 LANGUAGE 'c' _PARALLEL; 3914 3915-- Availability: 2.2 3916-- Changed: 2.5.0 use 'internal' transfer type 3917CREATE OR REPLACE FUNCTION pgis_geometry_clusterintersecting_finalfn(internal) 3918 RETURNS geometry[] 3919 AS 'MODULE_PATHNAME' 3920 LANGUAGE 'c' _PARALLEL; 3921 3922-- Availability: 2.2 3923-- Changed: 2.5.0 use 'internal' transfer type 3924CREATE OR REPLACE FUNCTION pgis_geometry_clusterwithin_finalfn(internal) 3925 RETURNS geometry[] 3926 AS 'MODULE_PATHNAME' 3927 LANGUAGE 'c' _PARALLEL; 3928 3929-- Availability: 1.4.0 3930-- Changed: 2.5.0 use 'internal' transfer type 3931CREATE OR REPLACE FUNCTION pgis_geometry_makeline_finalfn(internal) 3932 RETURNS geometry 3933 AS 'MODULE_PATHNAME' 3934 LANGUAGE 'c' _PARALLEL; 3935 3936-- Availability: 1.2.2 3937-- Changed: 2.4.0 marked parallel safe 3938-- Changed: 2.5.0 use 'internal' stype 3939CREATE AGGREGATE ST_Accum (geometry) ( 3940 sfunc = pgis_geometry_accum_transfn, 3941 stype = internal, 3942#if POSTGIS_PGSQL_VERSION >= 96 3943 parallel = safe, 3944#endif 3945 finalfunc = pgis_geometry_accum_finalfn 3946 ); 3947 3948-- Availability: 1.4.0 3949CREATE OR REPLACE FUNCTION ST_Union (geometry[]) 3950 RETURNS geometry 3951 AS 'MODULE_PATHNAME','pgis_union_geometry_array' 3952 LANGUAGE 'c' IMMUTABLE STRICT _PARALLEL; 3953 3954-- Availability: 1.2.2 3955-- Changed but upgrader helper no touch: 2.4.0 marked parallel safe 3956-- we don't want to force drop of this agg since its often used in views 3957-- parallel handling dealt with in postgis_drop_after.sql 3958-- Changed: 2.5.0 use 'internal' stype 3959CREATE AGGREGATE ST_Union (geometry) ( 3960 sfunc = pgis_geometry_accum_transfn, 3961 stype = internal, 3962#if POSTGIS_PGSQL_VERSION >= 96 3963 parallel = safe, 3964#endif 3965 finalfunc = pgis_geometry_union_finalfn 3966 ); 3967 3968-- Availability: 1.2.2 3969-- Changed: 2.4.0: marked parallel safe 3970-- Changed: 2.5.0 use 'internal' stype 3971CREATE AGGREGATE ST_Collect (geometry) ( 3972 SFUNC = pgis_geometry_accum_transfn, 3973 STYPE = internal, 3974#if POSTGIS_PGSQL_VERSION >= 96 3975 parallel = safe, 3976#endif 3977 FINALFUNC = pgis_geometry_collect_finalfn 3978 ); 3979 3980-- Availability: 2.2 3981-- Changed: 2.4.0: marked parallel safe 3982-- Changed: 2.5.0 use 'internal' stype 3983CREATE AGGREGATE ST_ClusterIntersecting (geometry) ( 3984 SFUNC = pgis_geometry_accum_transfn, 3985 STYPE = internal, 3986#if POSTGIS_PGSQL_VERSION >= 96 3987 parallel = safe, 3988#endif 3989 FINALFUNC = pgis_geometry_clusterintersecting_finalfn 3990 ); 3991 3992-- Availability: 2.2 3993-- Changed: 2.4.0 marked parallel safe 3994-- Changed: 2.5.0 use 'internal' stype 3995CREATE AGGREGATE ST_ClusterWithin (geometry, float8) ( 3996 SFUNC = pgis_geometry_accum_transfn, 3997 STYPE = internal, 3998#if POSTGIS_PGSQL_VERSION >= 96 3999 parallel = safe, 4000#endif 4001 FINALFUNC = pgis_geometry_clusterwithin_finalfn 4002 ); 4003 4004-- Availability: 1.2.2 4005-- Changed: 2.4.0 marked parallel safe 4006-- Changed: 2.5.0 use 'internal' stype 4007CREATE AGGREGATE ST_Polygonize (geometry) ( 4008 SFUNC = pgis_geometry_accum_transfn, 4009 STYPE = internal, 4010#if POSTGIS_PGSQL_VERSION >= 96 4011 parallel = safe, 4012#endif 4013 FINALFUNC = pgis_geometry_polygonize_finalfn 4014 ); 4015 4016-- Availability: 1.2.2 4017-- Changed: 2.4.0 marked parallel safe 4018-- Changed: 2.5.0 use 'internal' stype 4019CREATE AGGREGATE ST_MakeLine (geometry) ( 4020 SFUNC = pgis_geometry_accum_transfn, 4021 STYPE = internal, 4022#if POSTGIS_PGSQL_VERSION >= 96 4023 parallel = safe, 4024#endif 4025 FINALFUNC = pgis_geometry_makeline_finalfn 4026 ); 4027 4028-------------------------------------------------------------------------------- 4029 4030-- Availability: 2.3.0 4031CREATE OR REPLACE FUNCTION ST_ClusterKMeans(geom geometry, k integer) 4032 RETURNS integer 4033 AS 'MODULE_PATHNAME', 'ST_ClusterKMeans' 4034 LANGUAGE 'c' VOLATILE STRICT WINDOW; 4035 4036-- Availability: 1.2.2 4037CREATE OR REPLACE FUNCTION ST_Relate(geom1 geometry, geom2 geometry) 4038 RETURNS text 4039 AS 'MODULE_PATHNAME','relate_full' 4040 LANGUAGE 'c' IMMUTABLE STRICT _PARALLEL; 4041 4042-- Availability: 2.0.0 4043-- Requires GEOS >= 3.3.0 4044CREATE OR REPLACE FUNCTION ST_Relate(geom1 geometry, geom2 geometry, int4) 4045 RETURNS text 4046 AS 'MODULE_PATHNAME','relate_full' 4047 LANGUAGE 'c' IMMUTABLE STRICT _PARALLEL; 4048 4049-- PostGIS equivalent function: relate(geom1 geometry, geom2 geometry,text) 4050CREATE OR REPLACE FUNCTION ST_Relate(geom1 geometry, geom2 geometry,text) 4051 RETURNS boolean 4052 AS 'MODULE_PATHNAME','relate_pattern' 4053 LANGUAGE 'c' IMMUTABLE STRICT _PARALLEL; 4054 4055-- PostGIS equivalent function: disjoint(geom1 geometry, geom2 geometry) 4056CREATE OR REPLACE FUNCTION ST_Disjoint(geom1 geometry, geom2 geometry) 4057 RETURNS boolean 4058 AS 'MODULE_PATHNAME','disjoint' 4059 LANGUAGE 'c' IMMUTABLE STRICT _PARALLEL; 4060 4061-- PostGIS equivalent function: touches(geom1 geometry, geom2 geometry) 4062CREATE OR REPLACE FUNCTION _ST_Touches(geom1 geometry, geom2 geometry) 4063 RETURNS boolean 4064 AS 'MODULE_PATHNAME','touches' 4065 LANGUAGE 'c' IMMUTABLE STRICT _PARALLEL 4066 COST 100; -- Guessed cost 4067 4068-- Availability: 1.2.2 4069-- Inlines index magic 4070CREATE OR REPLACE FUNCTION ST_Touches(geom1 geometry, geom2 geometry) 4071 RETURNS boolean 4072 AS 'SELECT $1 OPERATOR(@extschema@.&&) $2 AND @extschema@._ST_Touches($1,$2)' 4073 LANGUAGE 'sql' IMMUTABLE _PARALLEL; 4074 4075-- Availability: 1.3.4 4076CREATE OR REPLACE FUNCTION _ST_DWithin(geom1 geometry, geom2 geometry,float8) 4077 RETURNS boolean 4078 AS 'MODULE_PATHNAME', 'LWGEOM_dwithin' 4079 LANGUAGE 'c' IMMUTABLE STRICT _PARALLEL 4080 COST 100; -- Guessed cost 4081 4082-- Availability: 1.2.2 4083CREATE OR REPLACE FUNCTION ST_DWithin(geom1 geometry, geom2 geometry, float8) 4084 RETURNS boolean 4085 AS 'SELECT $1 OPERATOR(@extschema@.&&) @extschema@.ST_Expand($2,$3) AND $2 OPERATOR(@extschema@.&&) @extschema@.ST_Expand($1,$3) AND @extschema@._ST_DWithin($1, $2, $3)' 4086 LANGUAGE 'sql' IMMUTABLE _PARALLEL; 4087 4088-- PostGIS equivalent function: intersects(geom1 geometry, geom2 geometry) 4089CREATE OR REPLACE FUNCTION _ST_Intersects(geom1 geometry, geom2 geometry) 4090 RETURNS boolean 4091 AS 'MODULE_PATHNAME','intersects' 4092 LANGUAGE 'c' IMMUTABLE STRICT _PARALLEL 4093 COST 100; -- Guessed cost 4094 4095-- Availability: 1.2.2 4096-- Inlines index magic 4097CREATE OR REPLACE FUNCTION ST_Intersects(geom1 geometry, geom2 geometry) 4098 RETURNS boolean 4099 AS 'SELECT $1 OPERATOR(@extschema@.&&) $2 AND @extschema@._ST_Intersects($1,$2)' 4100 LANGUAGE 'sql' IMMUTABLE _PARALLEL; 4101 4102-- PostGIS equivalent function: crosses(geom1 geometry, geom2 geometry) 4103CREATE OR REPLACE FUNCTION _ST_Crosses(geom1 geometry, geom2 geometry) 4104 RETURNS boolean 4105 AS 'MODULE_PATHNAME','crosses' 4106 LANGUAGE 'c' IMMUTABLE STRICT _PARALLEL 4107 COST 100; -- Guessed cost 4108 4109-- Availability: 1.2.2 4110-- Inlines index magic 4111CREATE OR REPLACE FUNCTION ST_Crosses(geom1 geometry, geom2 geometry) 4112 RETURNS boolean 4113 AS 'SELECT $1 OPERATOR(@extschema@.&&) $2 AND @extschema@._ST_Crosses($1,$2)' 4114 LANGUAGE 'sql' IMMUTABLE _PARALLEL; 4115 4116-- PostGIS equivalent function: contains(geom1 geometry, geom2 geometry) 4117CREATE OR REPLACE FUNCTION _ST_Contains(geom1 geometry, geom2 geometry) 4118 RETURNS boolean 4119 AS 'MODULE_PATHNAME','contains' 4120 LANGUAGE 'c' IMMUTABLE STRICT _PARALLEL 4121 COST 100; -- Guessed cost 4122 4123-- Availability: 1.2.2 4124-- Inlines index magic 4125CREATE OR REPLACE FUNCTION ST_Contains(geom1 geometry, geom2 geometry) 4126 RETURNS boolean 4127 AS 'SELECT $1 OPERATOR(@extschema@.~) $2 AND @extschema@._ST_Contains($1,$2)' 4128 LANGUAGE 'sql' IMMUTABLE _PARALLEL; 4129 4130-- Availability: 1.2.2 4131CREATE OR REPLACE FUNCTION _ST_CoveredBy(geom1 geometry, geom2 geometry) 4132 RETURNS boolean 4133 AS 'MODULE_PATHNAME', 'coveredby' 4134 LANGUAGE 'c' IMMUTABLE STRICT _PARALLEL 4135 COST 100; -- Guessed cost 4136 4137-- Availability: 1.2.2 4138CREATE OR REPLACE FUNCTION ST_CoveredBy(geom1 geometry, geom2 geometry) 4139 RETURNS boolean 4140 AS 'SELECT $1 OPERATOR(@extschema@.@) $2 AND @extschema@._ST_CoveredBy($1,$2)' 4141 LANGUAGE 'sql' IMMUTABLE _PARALLEL; 4142 4143-- Availability: 1.2.2 4144CREATE OR REPLACE FUNCTION _ST_Covers(geom1 geometry, geom2 geometry) 4145 RETURNS boolean 4146 AS 'MODULE_PATHNAME', 'covers' 4147 LANGUAGE 'c' IMMUTABLE STRICT _PARALLEL 4148 COST 100; -- Guessed cost 4149 4150-- Availability: 1.2.2 4151-- Inlines index magic 4152CREATE OR REPLACE FUNCTION ST_Covers(geom1 geometry, geom2 geometry) 4153 RETURNS boolean 4154 AS 'SELECT $1 OPERATOR(@extschema@.~) $2 AND @extschema@._ST_Covers($1,$2)' 4155 LANGUAGE 'sql' IMMUTABLE _PARALLEL; 4156 4157-- Availability: 1.4.0 4158CREATE OR REPLACE FUNCTION _ST_ContainsProperly(geom1 geometry, geom2 geometry) 4159 RETURNS boolean 4160 AS 'MODULE_PATHNAME','containsproperly' 4161 LANGUAGE 'c' IMMUTABLE STRICT _PARALLEL 4162 COST 100; -- Guessed cost 4163 4164-- Availability: 1.4.0 4165-- Inlines index magic 4166CREATE OR REPLACE FUNCTION ST_ContainsProperly(geom1 geometry, geom2 geometry) 4167 RETURNS boolean 4168 AS 'SELECT $1 OPERATOR(@extschema@.~) $2 AND @extschema@._ST_ContainsProperly($1,$2)' 4169 LANGUAGE 'sql' IMMUTABLE _PARALLEL; 4170 4171-- PostGIS equivalent function: overlaps(geom1 geometry, geom2 geometry) 4172CREATE OR REPLACE FUNCTION _ST_Overlaps(geom1 geometry, geom2 geometry) 4173 RETURNS boolean 4174 AS 'MODULE_PATHNAME','overlaps' 4175 LANGUAGE 'c' IMMUTABLE STRICT _PARALLEL 4176 COST 100; -- Guessed cost 4177 4178-- PostGIS equivalent function: within(geom1 geometry, geom2 geometry) 4179CREATE OR REPLACE FUNCTION _ST_Within(geom1 geometry, geom2 geometry) 4180 RETURNS boolean 4181 AS 'SELECT @extschema@._ST_Contains($2,$1)' 4182 LANGUAGE 'sql' IMMUTABLE _PARALLEL; 4183 4184-- Availability: 1.2.2 4185-- Inlines index magic 4186CREATE OR REPLACE FUNCTION ST_Within(geom1 geometry, geom2 geometry) 4187 RETURNS boolean 4188 AS 'SELECT $2 OPERATOR(@extschema@.~) $1 AND @extschema@._ST_Contains($2,$1)' 4189 LANGUAGE 'sql' IMMUTABLE _PARALLEL; 4190 4191-- Availability: 1.2.2 4192-- Inlines index magic 4193CREATE OR REPLACE FUNCTION ST_Overlaps(geom1 geometry, geom2 geometry) 4194 RETURNS boolean 4195 AS 'SELECT $1 OPERATOR(@extschema@.&&) $2 AND @extschema@._ST_Overlaps($1,$2)' 4196 LANGUAGE 'sql' IMMUTABLE _PARALLEL; 4197 4198-- PostGIS equivalent function: IsValid(geometry) 4199-- TODO: change null returns to true 4200CREATE OR REPLACE FUNCTION ST_IsValid(geometry) 4201 RETURNS boolean 4202 AS 'MODULE_PATHNAME', 'isvalid' 4203 LANGUAGE 'c' IMMUTABLE STRICT _PARALLEL 4204 COST 1000; 4205 4206-- Availability: 2.3.0 4207CREATE OR REPLACE FUNCTION ST_MinimumClearance(geometry) 4208 RETURNS float8 4209 AS 'MODULE_PATHNAME', 'ST_MinimumClearance' 4210 LANGUAGE 'c' IMMUTABLE STRICT _PARALLEL; 4211 4212-- Availability: 2.3.0 4213CREATE OR REPLACE FUNCTION ST_MinimumClearanceLine(geometry) 4214 RETURNS geometry 4215 AS 'MODULE_PATHNAME', 'ST_MinimumClearanceLine' 4216 LANGUAGE 'c' IMMUTABLE STRICT _PARALLEL; 4217 4218-- PostGIS equivalent function: Centroid(geometry) 4219CREATE OR REPLACE FUNCTION ST_Centroid(geometry) 4220 RETURNS geometry 4221 AS 'MODULE_PATHNAME', 'centroid' 4222 LANGUAGE 'c' IMMUTABLE STRICT _PARALLEL 4223 COST 1; -- reset cost, see #3675 4224 4225-- Availability: 2.3.0 4226CREATE OR REPLACE FUNCTION ST_GeometricMedian(g geometry, tolerance float8 DEFAULT NULL, max_iter int DEFAULT 10000, fail_if_not_converged boolean DEFAULT false) 4227 RETURNS geometry 4228 AS 'MODULE_PATHNAME', 'ST_GeometricMedian' 4229 LANGUAGE 'c' IMMUTABLE _PARALLEL; 4230 4231-- PostGIS equivalent function: IsRing(geometry) 4232CREATE OR REPLACE FUNCTION ST_IsRing(geometry) 4233 RETURNS boolean 4234 AS 'MODULE_PATHNAME', 'isring' 4235 LANGUAGE 'c' IMMUTABLE STRICT _PARALLEL; 4236 4237-- PostGIS equivalent function: PointOnSurface(geometry) 4238CREATE OR REPLACE FUNCTION ST_PointOnSurface(geometry) 4239 RETURNS geometry 4240 AS 'MODULE_PATHNAME', 'pointonsurface' 4241 LANGUAGE 'c' IMMUTABLE STRICT _PARALLEL 4242 COST 1; -- reset cost, see #3675 4243 4244-- PostGIS equivalent function: IsSimple(geometry) 4245CREATE OR REPLACE FUNCTION ST_IsSimple(geometry) 4246 RETURNS boolean 4247 AS 'MODULE_PATHNAME', 'issimple' 4248 LANGUAGE 'c' IMMUTABLE STRICT _PARALLEL 4249 COST 25; 4250 4251-- Availability: 2.0.0 4252CREATE OR REPLACE FUNCTION ST_IsCollection(geometry) 4253 RETURNS boolean 4254 AS 'MODULE_PATHNAME', 'ST_IsCollection' 4255 LANGUAGE 'c' IMMUTABLE STRICT _PARALLEL 4256 COST 5; 4257 4258-- Availability: 1.5.0 4259CREATE OR REPLACE FUNCTION _ST_Equals(geom1 geometry, geom2 geometry) 4260 RETURNS boolean 4261 AS 'MODULE_PATHNAME','ST_Equals' 4262 LANGUAGE 'c' IMMUTABLE STRICT _PARALLEL 4263 COST 100; --Guessed cost 4264 4265-- Availability: 1.2.1 4266CREATE OR REPLACE FUNCTION ST_Equals(geom1 geometry, geom2 geometry) 4267 RETURNS boolean 4268 AS 'SELECT $1 OPERATOR(@extschema@.~=) $2 AND @extschema@._ST_Equals($1,$2)' 4269 LANGUAGE 'sql' IMMUTABLE _PARALLEL; 4270 4271-- Deprecation in 1.2.3 4272-- TODO: drop in 2.0.0 ! 4273CREATE OR REPLACE FUNCTION Equals(geom1 geometry, geom2 geometry) 4274 RETURNS boolean 4275 AS 'MODULE_PATHNAME','ST_Equals' 4276 LANGUAGE 'c' IMMUTABLE STRICT _PARALLEL; 4277 4278----------------------------------------------------------------------- 4279-- GML & KML INPUT 4280----------------------------------------------------------------------- 4281CREATE OR REPLACE FUNCTION _ST_GeomFromGML(text, int4) 4282 RETURNS geometry 4283 AS 'MODULE_PATHNAME','geom_from_gml' 4284 LANGUAGE 'c' IMMUTABLE _PARALLEL; 4285 4286-- Availability: 2.0.0 4287CREATE OR REPLACE FUNCTION ST_GeomFromGML(text, int4) 4288 RETURNS geometry 4289 AS 'MODULE_PATHNAME','geom_from_gml' 4290 LANGUAGE 'c' IMMUTABLE STRICT _PARALLEL; 4291 4292-- Availability: 1.5.0 4293CREATE OR REPLACE FUNCTION ST_GeomFromGML(text) 4294 RETURNS geometry 4295 AS 'SELECT @extschema@._ST_GeomFromGML($1, 0)' 4296 LANGUAGE 'sql' IMMUTABLE STRICT _PARALLEL; 4297 4298-- Availability: 1.5.0 4299CREATE OR REPLACE FUNCTION ST_GMLToSQL(text) 4300 RETURNS geometry 4301 AS 'SELECT @extschema@._ST_GeomFromGML($1, 0)' 4302 LANGUAGE 'sql' IMMUTABLE STRICT _PARALLEL; 4303 4304-- Availability: 2.0.0 4305CREATE OR REPLACE FUNCTION ST_GMLToSQL(text, int4) 4306 RETURNS geometry 4307 AS 'MODULE_PATHNAME','geom_from_gml' 4308 LANGUAGE 'c' IMMUTABLE STRICT _PARALLEL; 4309 4310-- Availability: 1.5.0 4311CREATE OR REPLACE FUNCTION ST_GeomFromKML(text) 4312 RETURNS geometry 4313 AS 'MODULE_PATHNAME','geom_from_kml' 4314 LANGUAGE 'c' IMMUTABLE STRICT _PARALLEL; 4315 4316----------------------------------------------------------------------- 4317-- GEOJSON INPUT 4318----------------------------------------------------------------------- 4319-- Availability: 2.0.0 4320CREATE OR REPLACE FUNCTION ST_GeomFromGeoJson(text) 4321 RETURNS geometry 4322 AS 'MODULE_PATHNAME','geom_from_geojson' 4323 LANGUAGE 'c' IMMUTABLE STRICT _PARALLEL; 4324 4325-- Availability: 2.5.0 4326CREATE OR REPLACE FUNCTION ST_GeomFromGeoJson(json) 4327 RETURNS geometry 4328 AS 'SELECT @extschema@.ST_GeomFromGeoJson($1::text)' 4329 LANGUAGE 'sql' IMMUTABLE STRICT _PARALLEL; 4330 4331#if POSTGIS_PGSQL_VERSION >= 94 4332-- Availability: 2.5.0 4333CREATE OR REPLACE FUNCTION ST_GeomFromGeoJson(jsonb) 4334 RETURNS geometry 4335 AS 'SELECT @extschema@.ST_GeomFromGeoJson($1::text)' 4336 LANGUAGE 'sql' IMMUTABLE STRICT _PARALLEL; 4337#endif 4338 4339-- Availability: 2.0.0 4340CREATE OR REPLACE FUNCTION postgis_libjson_version() 4341 RETURNS text 4342 AS 'MODULE_PATHNAME','postgis_libjson_version' 4343 LANGUAGE 'c' IMMUTABLE STRICT _PARALLEL; 4344 4345---------------------------------------------------------------------- 4346-- ENCODED POLYLINE INPUT 4347----------------------------------------------------------------------- 4348-- Availability: 2.2.0 4349-- ST_LineFromEncodedPolyline(polyline text, precision int4) 4350CREATE OR REPLACE FUNCTION ST_LineFromEncodedPolyline(text, int4 DEFAULT 5) 4351 RETURNS geometry 4352 AS 'MODULE_PATHNAME','line_from_encoded_polyline' 4353 LANGUAGE 'c' IMMUTABLE STRICT _PARALLEL; 4354 4355------------------------------------------------------------------------ 4356 4357---------------------------------------------------------------------- 4358-- ENCODED POLYLINE OUTPUT 4359----------------------------------------------------------------------- 4360-- Availability: 2.2.0 4361-- ST_AsEncodedPolyline(geom geometry, precision int4) 4362CREATE OR REPLACE FUNCTION ST_AsEncodedPolyline(geom geometry, int4 DEFAULT 5) 4363 RETURNS TEXT 4364 AS 'MODULE_PATHNAME','LWGEOM_asEncodedPolyline' 4365 LANGUAGE 'c' IMMUTABLE STRICT _PARALLEL; 4366 4367------------------------------------------------------------------------ 4368 4369----------------------------------------------------------------------- 4370-- SVG OUTPUT 4371----------------------------------------------------------------------- 4372-- Availability: 1.2.2 4373-- Changed: 2.0.0 changed to use default args and allow calling by named args 4374CREATE OR REPLACE FUNCTION ST_AsSVG(geom geometry,rel int4 DEFAULT 0,maxdecimaldigits int4 DEFAULT 15) 4375 RETURNS TEXT 4376 AS 'MODULE_PATHNAME','LWGEOM_asSVG' 4377 LANGUAGE 'c' IMMUTABLE STRICT _PARALLEL 4378 COST 1000; 4379 4380----------------------------------------------------------------------- 4381-- GML OUTPUT 4382----------------------------------------------------------------------- 4383-- _ST_AsGML(version, geom, precision, option, prefix, id) 4384CREATE OR REPLACE FUNCTION _ST_AsGML(int4, geometry, int4, int4, text, text) 4385 RETURNS TEXT 4386 AS 'MODULE_PATHNAME','LWGEOM_asGML' 4387 LANGUAGE 'c' IMMUTABLE _PARALLEL 4388 COST 2500; 4389 4390-- ST_AsGML(version, geom) / precision=15 4391-- Availability: 1.3.2 4392-- ST_AsGML(version, geom, precision) 4393-- Availability: 1.3.2 4394 4395-- ST_AsGML (geom, precision, option) / version=2 4396-- Availability: 1.4.0 4397-- Changed: 2.0.0 to have default args 4398CREATE OR REPLACE FUNCTION ST_AsGML(geom geometry, maxdecimaldigits int4 DEFAULT 15, options int4 DEFAULT 0) 4399 RETURNS TEXT 4400 AS $$ SELECT @extschema@._ST_AsGML(2, $1, $2, $3, null, null); $$ 4401 LANGUAGE 'sql' IMMUTABLE STRICT _PARALLEL; 4402 4403-- ST_AsGML(version, geom, precision, option) 4404-- Availability: 1.4.0 4405-- ST_AsGML(version, geom, precision, option, prefix) 4406-- Availability: 2.0.0 4407-- Changed: 2.0.0 to use default and named args 4408-- ST_AsGML(version, geom, precision, option, prefix, id) 4409-- Availability: 2.1.0 4410CREATE OR REPLACE FUNCTION ST_AsGML(version int4, geom geometry, maxdecimaldigits int4 DEFAULT 15, options int4 DEFAULT 0, nprefix text DEFAULT null, id text DEFAULT null) 4411 RETURNS TEXT 4412 AS $$ SELECT @extschema@._ST_AsGML($1, $2, $3, $4, $5, $6); $$ 4413 LANGUAGE 'sql' IMMUTABLE _PARALLEL; 4414 4415----------------------------------------------------------------------- 4416-- KML OUTPUT 4417----------------------------------------------------------------------- 4418-- _ST_AsKML(version, geom, precision, nprefix) 4419CREATE OR REPLACE FUNCTION _ST_AsKML(int4,geometry, int4, text) 4420 RETURNS TEXT 4421 AS 'MODULE_PATHNAME','LWGEOM_asKML' 4422 LANGUAGE 'c' IMMUTABLE _PARALLEL 4423 COST 5000; 4424 4425-- Availability: 1.2.2 4426-- Changed: 2.0.0 to use default args and allow named args 4427CREATE OR REPLACE FUNCTION ST_AsKML(geom geometry, maxdecimaldigits int4 DEFAULT 15) 4428 RETURNS TEXT 4429 AS $$ SELECT @extschema@._ST_AsKML(2, ST_Transform($1,4326), $2, null); $$ 4430 LANGUAGE 'sql' IMMUTABLE STRICT _PARALLEL; 4431 4432-- ST_AsKML(version, geom, precision, text) 4433-- Availability: 2.0.0 4434-- Changed: 2.0.0 allows default args and got rid of other permutations 4435CREATE OR REPLACE FUNCTION ST_AsKML(version int4, geom geometry, maxdecimaldigits int4 DEFAULT 15, nprefix text DEFAULT null) 4436 RETURNS TEXT 4437 AS $$ SELECT @extschema@._ST_AsKML($1, @extschema@.ST_Transform($2,4326), $3, $4); $$ 4438 LANGUAGE 'sql' IMMUTABLE _PARALLEL; 4439 4440----------------------------------------------------------------------- 4441-- GEOJSON OUTPUT 4442-- Availability: 1.3.4 4443----------------------------------------------------------------------- 4444 4445-- ST_AsGeoJson(geom, precision, options) / version=1 4446-- Changed 2.0.0 to use default args and named args 4447CREATE OR REPLACE FUNCTION ST_AsGeoJson(geom geometry, maxdecimaldigits int4 DEFAULT 15, options int4 DEFAULT 0) 4448 RETURNS TEXT 4449 AS 'MODULE_PATHNAME','LWGEOM_asGeoJson' 4450 LANGUAGE 'c' IMMUTABLE STRICT _PARALLEL 4451 COST 1000; 4452 4453-- _ST_AsGeoJson(version, geom, precision, options) 4454CREATE OR REPLACE FUNCTION _ST_AsGeoJson(int4, geometry, int4, int4) 4455 RETURNS TEXT 4456 AS $$ SELECT @extschema@.ST_AsGeoJson($2::@extschema@.geometry, $3::int4, $4::int4); $$ 4457 LANGUAGE 'sql' IMMUTABLE STRICT _PARALLEL; 4458 4459-- ST_AsGeoJson(version, geom, precision,options) 4460-- Changed 2.0.0 to use default args and named args 4461CREATE OR REPLACE FUNCTION ST_AsGeoJson(gj_version int4, geom geometry, maxdecimaldigits int4 DEFAULT 15, options int4 DEFAULT 0) 4462 RETURNS TEXT 4463 AS $$ SELECT @extschema@.ST_AsGeoJson($2::@extschema@.geometry, $3::int4, $4::int4); $$ 4464 LANGUAGE 'sql' IMMUTABLE STRICT _PARALLEL; 4465 4466----------------------------------------------------------------------- 4467-- Mapbox Vector Tile OUTPUT 4468-- Availability: 2.4.0 4469----------------------------------------------------------------------- 4470 4471-- Availability: 2.4.0 4472CREATE OR REPLACE FUNCTION pgis_asmvt_transfn(internal, anyelement) 4473 RETURNS internal 4474 AS 'MODULE_PATHNAME', 'pgis_asmvt_transfn' 4475 LANGUAGE 'c' IMMUTABLE _PARALLEL; 4476 4477-- Availability: 2.4.0 4478CREATE OR REPLACE FUNCTION pgis_asmvt_transfn(internal, anyelement, text) 4479 RETURNS internal 4480 AS 'MODULE_PATHNAME', 'pgis_asmvt_transfn' 4481 LANGUAGE 'c' IMMUTABLE _PARALLEL; 4482 4483-- Availability: 2.4.0 4484CREATE OR REPLACE FUNCTION pgis_asmvt_transfn(internal, anyelement, text, int4) 4485 RETURNS internal 4486 AS 'MODULE_PATHNAME', 'pgis_asmvt_transfn' 4487 LANGUAGE 'c' IMMUTABLE _PARALLEL; 4488 4489-- Availability: 2.4.0 4490CREATE OR REPLACE FUNCTION pgis_asmvt_transfn(internal, anyelement, text, int4, text) 4491 RETURNS internal 4492 AS 'MODULE_PATHNAME', 'pgis_asmvt_transfn' 4493 LANGUAGE 'c' IMMUTABLE _PARALLEL; 4494 4495-- Availability: 2.4.0 4496CREATE OR REPLACE FUNCTION pgis_asmvt_finalfn(internal) 4497 RETURNS bytea 4498 AS 'MODULE_PATHNAME', 'pgis_asmvt_finalfn' 4499 LANGUAGE 'c' IMMUTABLE _PARALLEL; 4500 4501-- Availability: 2.5.0 4502CREATE OR REPLACE FUNCTION pgis_asmvt_combinefn(internal, internal) 4503 RETURNS internal 4504 AS 'MODULE_PATHNAME', 'pgis_asmvt_combinefn' 4505 LANGUAGE 'c' IMMUTABLE _PARALLEL; 4506 4507-- Availability: 2.5.0 4508CREATE OR REPLACE FUNCTION pgis_asmvt_serialfn(internal) 4509 RETURNS bytea 4510 AS 'MODULE_PATHNAME', 'pgis_asmvt_serialfn' 4511 LANGUAGE 'c' IMMUTABLE _PARALLEL; 4512 4513-- Availability: 2.5.0 4514CREATE OR REPLACE FUNCTION pgis_asmvt_deserialfn(bytea, internal) 4515 RETURNS internal 4516 AS 'MODULE_PATHNAME', 'pgis_asmvt_deserialfn' 4517 LANGUAGE 'c' IMMUTABLE _PARALLEL; 4518 4519-- Availability: 2.4.0 4520-- Changed: 2.5.0 4521CREATE AGGREGATE ST_AsMVT(anyelement) 4522( 4523 sfunc = pgis_asmvt_transfn, 4524 stype = internal, 4525#if POSTGIS_PGSQL_VERSION >= 96 4526 parallel = safe, 4527 serialfunc = pgis_asmvt_serialfn, 4528 deserialfunc = pgis_asmvt_deserialfn, 4529 combinefunc = pgis_asmvt_combinefn, 4530#endif 4531 finalfunc = pgis_asmvt_finalfn 4532); 4533 4534-- Availability: 2.4.0 4535-- Changed: 2.5.0 4536CREATE AGGREGATE ST_AsMVT(anyelement, text) 4537( 4538 sfunc = pgis_asmvt_transfn, 4539 stype = internal, 4540#if POSTGIS_PGSQL_VERSION >= 96 4541 parallel = safe, 4542 serialfunc = pgis_asmvt_serialfn, 4543 deserialfunc = pgis_asmvt_deserialfn, 4544 combinefunc = pgis_asmvt_combinefn, 4545#endif 4546 finalfunc = pgis_asmvt_finalfn 4547); 4548 4549-- Availability: 2.4.0 4550-- Changed: 2.5.0 4551CREATE AGGREGATE ST_AsMVT(anyelement, text, int4) 4552( 4553 sfunc = pgis_asmvt_transfn, 4554 stype = internal, 4555#if POSTGIS_PGSQL_VERSION >= 96 4556 parallel = safe, 4557 serialfunc = pgis_asmvt_serialfn, 4558 deserialfunc = pgis_asmvt_deserialfn, 4559 combinefunc = pgis_asmvt_combinefn, 4560#endif 4561 finalfunc = pgis_asmvt_finalfn 4562); 4563 4564-- Availability: 2.4.0 4565-- Changed: 2.5.0 4566CREATE AGGREGATE ST_AsMVT(anyelement, text, int4, text) 4567( 4568 sfunc = pgis_asmvt_transfn, 4569 stype = internal, 4570#if POSTGIS_PGSQL_VERSION >= 96 4571 parallel = safe, 4572 serialfunc = pgis_asmvt_serialfn, 4573 deserialfunc = pgis_asmvt_deserialfn, 4574 combinefunc = pgis_asmvt_combinefn, 4575#endif 4576 finalfunc = pgis_asmvt_finalfn 4577); 4578 4579-- Availability: 2.4.0 4580CREATE OR REPLACE FUNCTION ST_AsMVTGeom(geom geometry, bounds box2d, extent int4 default 4096, buffer int4 default 256, clip_geom bool default true) 4581 RETURNS geometry 4582 AS 'MODULE_PATHNAME','ST_AsMVTGeom' 4583 LANGUAGE 'c' IMMUTABLE _PARALLEL; 4584 4585-- Availability: 2.4.0 4586CREATE OR REPLACE FUNCTION postgis_libprotobuf_version() 4587 RETURNS text 4588 AS 'MODULE_PATHNAME','postgis_libprotobuf_version' 4589 LANGUAGE 'c' IMMUTABLE STRICT; 4590 4591----------------------------------------------------------------------- 4592-- GEOBUF OUTPUT 4593-- Availability: 2.4.0 4594----------------------------------------------------------------------- 4595 4596-- Availability: 2.4.0 4597CREATE OR REPLACE FUNCTION pgis_asgeobuf_transfn(internal, anyelement) 4598 RETURNS internal 4599 AS 'MODULE_PATHNAME', 'pgis_asgeobuf_transfn' 4600 LANGUAGE 'c' IMMUTABLE _PARALLEL; 4601 4602-- Availability: 2.4.0 4603CREATE OR REPLACE FUNCTION pgis_asgeobuf_transfn(internal, anyelement, text) 4604 RETURNS internal 4605 AS 'MODULE_PATHNAME', 'pgis_asgeobuf_transfn' 4606 LANGUAGE 'c' IMMUTABLE _PARALLEL; 4607 4608-- Availability: 2.4.0 4609CREATE OR REPLACE FUNCTION pgis_asgeobuf_finalfn(internal) 4610 RETURNS bytea 4611 AS 'MODULE_PATHNAME', 'pgis_asgeobuf_finalfn' 4612 LANGUAGE 'c' IMMUTABLE _PARALLEL; 4613 4614-- Availability: 2.4.0 4615CREATE AGGREGATE ST_AsGeobuf(anyelement) 4616( 4617 sfunc = pgis_asgeobuf_transfn, 4618 stype = internal, 4619#if POSTGIS_PGSQL_VERSION >= 96 4620 parallel = safe, 4621#endif 4622 finalfunc = pgis_asgeobuf_finalfn 4623); 4624 4625-- Availability: 2.4.0 4626CREATE AGGREGATE ST_AsGeobuf(anyelement, text) 4627( 4628 sfunc = pgis_asgeobuf_transfn, 4629 stype = internal, 4630#if POSTGIS_PGSQL_VERSION >= 96 4631 parallel = safe, 4632#endif 4633 finalfunc = pgis_asgeobuf_finalfn 4634); 4635 4636------------------------------------------------------------------------ 4637-- GeoHash (geohash.org) 4638------------------------------------------------------------------------ 4639 4640-- Availability 1.4.0 4641-- Changed 2.0.0 to use default args and named args 4642CREATE OR REPLACE FUNCTION ST_GeoHash(geom geometry, maxchars int4 DEFAULT 0) 4643 RETURNS TEXT 4644 AS 'MODULE_PATHNAME', 'ST_GeoHash' 4645 LANGUAGE 'c' IMMUTABLE STRICT _PARALLEL; 4646 4647----------------------------------------------------------------------- 4648-- GeoHash input 4649-- Availability: 2.0.? 4650----------------------------------------------------------------------- 4651-- ST_Box2dFromGeoHash(geohash text, precision int4) 4652CREATE OR REPLACE FUNCTION ST_Box2dFromGeoHash(text, int4 DEFAULT NULL) 4653 RETURNS box2d 4654 AS 'MODULE_PATHNAME','box2d_from_geohash' 4655 LANGUAGE 'c' IMMUTABLE _PARALLEL; 4656 4657-- ST_PointFromGeoHash(geohash text, precision int4) 4658CREATE OR REPLACE FUNCTION ST_PointFromGeoHash(text, int4 DEFAULT NULL) 4659 RETURNS geometry 4660 AS 'MODULE_PATHNAME','point_from_geohash' 4661 LANGUAGE 'c' IMMUTABLE _PARALLEL; 4662 4663-- ST_GeomFromGeoHash(geohash text, precision int4) 4664CREATE OR REPLACE FUNCTION ST_GeomFromGeoHash(text, int4 DEFAULT NULL) 4665 RETURNS geometry 4666 AS $$ SELECT CAST(@extschema@.ST_Box2dFromGeoHash($1, $2) AS geometry); $$ 4667 LANGUAGE 'sql' IMMUTABLE _PARALLEL; 4668 4669------------------------------------------------------------------------ 4670-- OGC defined 4671------------------------------------------------------------------------ 4672-- PostGIS equivalent function: NumPoints(geometry) 4673CREATE OR REPLACE FUNCTION ST_NumPoints(geometry) 4674 RETURNS int4 4675 AS 'MODULE_PATHNAME', 'LWGEOM_numpoints_linestring' 4676 LANGUAGE 'c' IMMUTABLE STRICT _PARALLEL; 4677 4678-- PostGIS equivalent function: NumGeometries(geometry) 4679CREATE OR REPLACE FUNCTION ST_NumGeometries(geometry) 4680 RETURNS int4 4681 AS 'MODULE_PATHNAME', 'LWGEOM_numgeometries_collection' 4682 LANGUAGE 'c' IMMUTABLE STRICT _PARALLEL; 4683 4684-- PostGIS equivalent function: GeometryN(geometry) 4685CREATE OR REPLACE FUNCTION ST_GeometryN(geometry,integer) 4686 RETURNS geometry 4687 AS 'MODULE_PATHNAME', 'LWGEOM_geometryn_collection' 4688 LANGUAGE 'c' IMMUTABLE STRICT _PARALLEL; 4689 4690-- PostGIS equivalent function: Dimension(geometry) 4691CREATE OR REPLACE FUNCTION ST_Dimension(geometry) 4692 RETURNS int4 4693 AS 'MODULE_PATHNAME', 'LWGEOM_dimension' 4694 LANGUAGE 'c' IMMUTABLE STRICT _PARALLEL 4695 COST 10; 4696 4697-- PostGIS equivalent function: ExteriorRing(geometry) 4698CREATE OR REPLACE FUNCTION ST_ExteriorRing(geometry) 4699 RETURNS geometry 4700 AS 'MODULE_PATHNAME','LWGEOM_exteriorring_polygon' 4701 LANGUAGE 'c' IMMUTABLE STRICT _PARALLEL; 4702 4703-- PostGIS equivalent function: NumInteriorRings(geometry) 4704CREATE OR REPLACE FUNCTION ST_NumInteriorRings(geometry) 4705 RETURNS integer 4706 AS 'MODULE_PATHNAME','LWGEOM_numinteriorrings_polygon' 4707 LANGUAGE 'c' IMMUTABLE STRICT _PARALLEL; 4708 4709-- Availability: 1.2.2 4710CREATE OR REPLACE FUNCTION ST_NumInteriorRing(geometry) 4711 RETURNS integer 4712 AS 'MODULE_PATHNAME','LWGEOM_numinteriorrings_polygon' 4713 LANGUAGE 'c' IMMUTABLE STRICT _PARALLEL; 4714 4715-- PostGIS equivalent function: InteriorRingN(geometry) 4716CREATE OR REPLACE FUNCTION ST_InteriorRingN(geometry,integer) 4717 RETURNS geometry 4718 AS 'MODULE_PATHNAME','LWGEOM_interiorringn_polygon' 4719 LANGUAGE 'c' IMMUTABLE STRICT _PARALLEL; 4720 4721-- Deprecation in 1.2.3 -- this should not be deprecated (2011-01-04 robe) 4722CREATE OR REPLACE FUNCTION GeometryType(geometry) 4723 RETURNS text 4724 AS 'MODULE_PATHNAME', 'LWGEOM_getTYPE' 4725 LANGUAGE 'c' IMMUTABLE STRICT _PARALLEL 4726 COST 10; -- COST guessed from ST_GeometryType(geometry) 4727 4728-- Not quite equivalent to GeometryType 4729CREATE OR REPLACE FUNCTION ST_GeometryType(geometry) 4730 RETURNS text 4731 AS 'MODULE_PATHNAME', 'geometry_geometrytype' 4732 LANGUAGE 'c' IMMUTABLE STRICT _PARALLEL 4733 COST 10; 4734 4735-- PostGIS equivalent function: PointN(geometry,integer) 4736CREATE OR REPLACE FUNCTION ST_PointN(geometry,integer) 4737 RETURNS geometry 4738 AS 'MODULE_PATHNAME','LWGEOM_pointn_linestring' 4739 LANGUAGE 'c' IMMUTABLE STRICT _PARALLEL; 4740 4741-- Availability: 2.0.0 4742CREATE OR REPLACE FUNCTION ST_NumPatches(geometry) 4743 RETURNS int4 4744 AS ' 4745 SELECT CASE WHEN @extschema@.ST_GeometryType($1) = ''ST_PolyhedralSurface'' 4746 THEN @extschema@.ST_NumGeometries($1) 4747 ELSE NULL END 4748 ' 4749 LANGUAGE 'sql' IMMUTABLE STRICT _PARALLEL; 4750 4751-- Availability: 2.0.0 4752CREATE OR REPLACE FUNCTION ST_PatchN(geometry, integer) 4753 RETURNS geometry 4754 AS ' 4755 SELECT CASE WHEN @extschema@.ST_GeometryType($1) = ''ST_PolyhedralSurface'' 4756 THEN @extschema@.ST_GeometryN($1, $2) 4757 ELSE NULL END 4758 ' 4759 LANGUAGE 'sql' IMMUTABLE STRICT _PARALLEL; 4760 4761-- PostGIS equivalent function of old StartPoint(geometry)) 4762CREATE OR REPLACE FUNCTION ST_StartPoint(geometry) 4763 RETURNS geometry 4764 AS 'MODULE_PATHNAME', 'LWGEOM_startpoint_linestring' 4765 LANGUAGE 'c' IMMUTABLE STRICT _PARALLEL; 4766 4767-- PostGIS equivalent function of old EndPoint(geometry) 4768CREATE OR REPLACE FUNCTION ST_EndPoint(geometry) 4769 RETURNS geometry 4770 AS 'MODULE_PATHNAME', 'LWGEOM_endpoint_linestring' 4771 LANGUAGE 'c' IMMUTABLE STRICT _PARALLEL; 4772 4773-- PostGIS equivalent function: IsClosed(geometry) 4774CREATE OR REPLACE FUNCTION ST_IsClosed(geometry) 4775 RETURNS boolean 4776 AS 'MODULE_PATHNAME', 'LWGEOM_isclosed' 4777 LANGUAGE 'c' IMMUTABLE STRICT _PARALLEL 4778 COST 10; 4779 4780-- PostGIS equivalent function: IsEmpty(geometry) 4781CREATE OR REPLACE FUNCTION ST_IsEmpty(geometry) 4782 RETURNS boolean 4783 AS 'MODULE_PATHNAME', 'LWGEOM_isempty' 4784 LANGUAGE 'c' IMMUTABLE STRICT _PARALLEL 4785 COST 10; 4786 4787-- Availability: 1.2.2 4788CREATE OR REPLACE FUNCTION ST_AsBinary(geometry,text) 4789 RETURNS bytea 4790 AS 'MODULE_PATHNAME','LWGEOM_asBinary' 4791 LANGUAGE 'c' IMMUTABLE STRICT _PARALLEL 4792 COST 10; 4793 4794-- PostGIS equivalent of old function: AsBinary(geometry) 4795CREATE OR REPLACE FUNCTION ST_AsBinary(geometry) 4796 RETURNS bytea 4797 AS 'MODULE_PATHNAME','LWGEOM_asBinary' 4798 LANGUAGE 'c' IMMUTABLE STRICT _PARALLEL 4799 COST 10; 4800 4801-- PostGIS equivalent function: AsText(geometry) 4802CREATE OR REPLACE FUNCTION ST_AsText(geometry) 4803 RETURNS TEXT 4804 AS 'MODULE_PATHNAME','LWGEOM_asText' 4805 LANGUAGE 'c' IMMUTABLE STRICT _PARALLEL 4806 COST 750; --guess 4807 4808-- Availability: 2.5.0 4809-- PostGIS equivalent function: AsText(geometry, int4) 4810CREATE OR REPLACE FUNCTION ST_AsText(geometry, int4) 4811 RETURNS TEXT 4812 AS 'MODULE_PATHNAME','LWGEOM_asText' 4813 LANGUAGE 'c' IMMUTABLE STRICT; 4814 4815-- Availability: 1.2.2 4816CREATE OR REPLACE FUNCTION ST_GeometryFromText(text) 4817 RETURNS geometry 4818 AS 'MODULE_PATHNAME','LWGEOM_from_text' 4819 LANGUAGE 'c' IMMUTABLE STRICT _PARALLEL; 4820 4821-- Availability: 1.2.2 4822CREATE OR REPLACE FUNCTION ST_GeometryFromText(text, int4) 4823 RETURNS geometry 4824 AS 'MODULE_PATHNAME','LWGEOM_from_text' 4825 LANGUAGE 'c' IMMUTABLE STRICT _PARALLEL; 4826 4827-- Availability: 1.2.2 4828CREATE OR REPLACE FUNCTION ST_GeomFromText(text) 4829 RETURNS geometry 4830 AS 'MODULE_PATHNAME','LWGEOM_from_text' 4831 LANGUAGE 'c' IMMUTABLE STRICT _PARALLEL; 4832 4833-- PostGIS equivalent function: ST_GeometryFromText(text, int4) 4834CREATE OR REPLACE FUNCTION ST_GeomFromText(text, int4) 4835 RETURNS geometry 4836 AS 'MODULE_PATHNAME','LWGEOM_from_text' 4837 LANGUAGE 'c' IMMUTABLE STRICT _PARALLEL; 4838 4839-- PostGIS equivalent function: ST_GeometryFromText(text) 4840-- SQL/MM alias for ST_GeomFromText 4841CREATE OR REPLACE FUNCTION ST_WKTToSQL(text) 4842 RETURNS geometry 4843 AS 'MODULE_PATHNAME','LWGEOM_from_text' 4844 LANGUAGE 'c' IMMUTABLE STRICT _PARALLEL; 4845 4846-- Availability: 1.2.2 4847CREATE OR REPLACE FUNCTION ST_PointFromText(text) 4848 RETURNS geometry 4849 AS ' 4850 SELECT CASE WHEN @extschema@.geometrytype(@extschema@.ST_GeomFromText($1)) = ''POINT'' 4851 THEN @extschema@.ST_GeomFromText($1) 4852 ELSE NULL END 4853 ' 4854 LANGUAGE 'sql' IMMUTABLE STRICT _PARALLEL; 4855 4856-- PostGIS equivalent function: PointFromText(text, int4) 4857-- TODO: improve this ... by not duplicating constructor time. 4858CREATE OR REPLACE FUNCTION ST_PointFromText(text, int4) 4859 RETURNS geometry 4860 AS ' 4861 SELECT CASE WHEN @extschema@.geometrytype(@extschema@.ST_GeomFromText($1, $2)) = ''POINT'' 4862 THEN @extschema@.ST_GeomFromText($1, $2) 4863 ELSE NULL END 4864 ' 4865 LANGUAGE 'sql' IMMUTABLE STRICT _PARALLEL; 4866 4867-- Availability: 1.2.2 4868CREATE OR REPLACE FUNCTION ST_LineFromText(text) 4869 RETURNS geometry 4870 AS ' 4871 SELECT CASE WHEN @extschema@.geometrytype(@extschema@.ST_GeomFromText($1)) = ''LINESTRING'' 4872 THEN @extschema@.ST_GeomFromText($1) 4873 ELSE NULL END 4874 ' 4875 LANGUAGE 'sql' IMMUTABLE STRICT _PARALLEL; 4876 4877-- PostGIS equivalent function: LineFromText(text, int4) 4878CREATE OR REPLACE FUNCTION ST_LineFromText(text, int4) 4879 RETURNS geometry 4880 AS ' 4881 SELECT CASE WHEN @extschema@.geometrytype(@extschema@.ST_GeomFromText($1, $2)) = ''LINESTRING'' 4882 THEN @extschema@.ST_GeomFromText($1,$2) 4883 ELSE NULL END 4884 ' 4885 LANGUAGE 'sql' IMMUTABLE STRICT _PARALLEL; 4886 4887-- Availability: 1.2.2 4888CREATE OR REPLACE FUNCTION ST_PolyFromText(text) 4889 RETURNS geometry 4890 AS ' 4891 SELECT CASE WHEN @extschema@.geometrytype(@extschema@.ST_GeomFromText($1)) = ''POLYGON'' 4892 THEN @extschema@.ST_GeomFromText($1) 4893 ELSE NULL END 4894 ' 4895 LANGUAGE 'sql' IMMUTABLE STRICT _PARALLEL; 4896 4897-- PostGIS equivalent function: ST_PolygonFromText(text, int4) 4898CREATE OR REPLACE FUNCTION ST_PolyFromText(text, int4) 4899 RETURNS geometry 4900 AS ' 4901 SELECT CASE WHEN @extschema@.geometrytype(@extschema@.ST_GeomFromText($1, $2)) = ''POLYGON'' 4902 THEN @extschema@.ST_GeomFromText($1, $2) 4903 ELSE NULL END 4904 ' 4905 LANGUAGE 'sql' IMMUTABLE STRICT _PARALLEL; 4906 4907-- Availability: 1.2.2 4908CREATE OR REPLACE FUNCTION ST_PolygonFromText(text, int4) 4909 RETURNS geometry 4910 AS 'SELECT @extschema@.ST_PolyFromText($1, $2)' 4911 LANGUAGE 'sql' IMMUTABLE STRICT _PARALLEL; 4912 4913-- Availability: 1.2.2 4914CREATE OR REPLACE FUNCTION ST_PolygonFromText(text) 4915 RETURNS geometry 4916 AS 'SELECT @extschema@.ST_PolyFromText($1)' 4917 LANGUAGE 'sql' IMMUTABLE STRICT _PARALLEL; 4918 4919-- PostGIS equivalent function: MLineFromText(text, int4) 4920CREATE OR REPLACE FUNCTION ST_MLineFromText(text, int4) 4921 RETURNS geometry 4922 AS ' 4923 SELECT CASE 4924 WHEN @extschema@.geometrytype(@extschema@.ST_GeomFromText($1, $2)) = ''MULTILINESTRING'' 4925 THEN @extschema@.ST_GeomFromText($1,$2) 4926 ELSE NULL END 4927 ' 4928 LANGUAGE 'sql' IMMUTABLE STRICT _PARALLEL; 4929 4930-- Availability: 1.2.2 4931CREATE OR REPLACE FUNCTION ST_MLineFromText(text) 4932 RETURNS geometry 4933 AS ' 4934 SELECT CASE WHEN @extschema@.geometrytype(@extschema@.ST_GeomFromText($1)) = ''MULTILINESTRING'' 4935 THEN @extschema@.ST_GeomFromText($1) 4936 ELSE NULL END 4937 ' 4938 LANGUAGE 'sql' IMMUTABLE STRICT _PARALLEL; 4939 4940-- Availability: 1.2.2 4941CREATE OR REPLACE FUNCTION ST_MultiLineStringFromText(text) 4942 RETURNS geometry 4943 AS 'SELECT @extschema@.ST_MLineFromText($1)' 4944 LANGUAGE 'sql' IMMUTABLE STRICT _PARALLEL; 4945 4946-- Availability: 1.2.2 4947CREATE OR REPLACE FUNCTION ST_MultiLineStringFromText(text, int4) 4948 RETURNS geometry 4949 AS 'SELECT @extschema@.ST_MLineFromText($1, $2)' 4950 LANGUAGE 'sql' IMMUTABLE STRICT _PARALLEL; 4951 4952-- PostGIS equivalent function: MPointFromText(text, int4) 4953CREATE OR REPLACE FUNCTION ST_MPointFromText(text, int4) 4954 RETURNS geometry 4955 AS ' 4956 SELECT CASE WHEN @extschema@.geometrytype(@extschema@.ST_GeomFromText($1, $2)) = ''MULTIPOINT'' 4957 THEN ST_GeomFromText($1, $2) 4958 ELSE NULL END 4959 ' 4960 LANGUAGE 'sql' IMMUTABLE STRICT _PARALLEL; 4961 4962-- Availability: 1.2.2 4963CREATE OR REPLACE FUNCTION ST_MPointFromText(text) 4964 RETURNS geometry 4965 AS ' 4966 SELECT CASE WHEN @extschema@.geometrytype(@extschema@.ST_GeomFromText($1)) = ''MULTIPOINT'' 4967 THEN @extschema@.ST_GeomFromText($1) 4968 ELSE NULL END 4969 ' 4970 LANGUAGE 'sql' IMMUTABLE STRICT _PARALLEL; 4971 4972-- Availability: 1.2.2 4973CREATE OR REPLACE FUNCTION ST_MultiPointFromText(text) 4974 RETURNS geometry 4975 AS 'SELECT @extschema@.ST_MPointFromText($1)' 4976 LANGUAGE 'sql' IMMUTABLE STRICT _PARALLEL; 4977 4978-- PostGIS equivalent function: MPolyFromText(text, int4) 4979CREATE OR REPLACE FUNCTION ST_MPolyFromText(text, int4) 4980 RETURNS geometry 4981 AS ' 4982 SELECT CASE WHEN @extschema@.geometrytype(@extschema@.ST_GeomFromText($1, $2)) = ''MULTIPOLYGON'' 4983 THEN @extschema@.ST_GeomFromText($1,$2) 4984 ELSE NULL END 4985 ' 4986 LANGUAGE 'sql' IMMUTABLE STRICT _PARALLEL; 4987 4988--Availability: 1.2.2 4989CREATE OR REPLACE FUNCTION ST_MPolyFromText(text) 4990 RETURNS geometry 4991 AS ' 4992 SELECT CASE WHEN @extschema@.geometrytype(@extschema@.ST_GeomFromText($1)) = ''MULTIPOLYGON'' 4993 THEN @extschema@.ST_GeomFromText($1) 4994 ELSE NULL END 4995 ' 4996 LANGUAGE 'sql' IMMUTABLE STRICT _PARALLEL; 4997 4998-- Availability: 1.2.2 4999CREATE OR REPLACE FUNCTION ST_MultiPolygonFromText(text, int4) 5000 RETURNS geometry 5001 AS 'SELECT @extschema@.ST_MPolyFromText($1, $2)' 5002 LANGUAGE 'sql' IMMUTABLE STRICT _PARALLEL; 5003 5004-- Availability: 1.2.2 5005CREATE OR REPLACE FUNCTION ST_MultiPolygonFromText(text) 5006 RETURNS geometry 5007 AS 'SELECT @extschema@.ST_MPolyFromText($1)' 5008 LANGUAGE 'sql' IMMUTABLE STRICT _PARALLEL; 5009 5010-- Availability: 1.2.2 5011CREATE OR REPLACE FUNCTION ST_GeomCollFromText(text, int4) 5012 RETURNS geometry 5013 AS ' 5014 SELECT CASE 5015 WHEN @extschema@.geometrytype(@extschema@.ST_GeomFromText($1, $2)) = ''GEOMETRYCOLLECTION'' 5016 THEN @extschema@.ST_GeomFromText($1,$2) 5017 ELSE NULL END 5018 ' 5019 LANGUAGE 'sql' IMMUTABLE STRICT _PARALLEL; 5020 5021-- Availability: 1.2.2 5022CREATE OR REPLACE FUNCTION ST_GeomCollFromText(text) 5023 RETURNS geometry 5024 AS ' 5025 SELECT CASE 5026 WHEN @extschema@.geometrytype(@extschema@.ST_GeomFromText($1)) = ''GEOMETRYCOLLECTION'' 5027 THEN @extschema@.ST_GeomFromText($1) 5028 ELSE NULL END 5029 ' 5030 LANGUAGE 'sql' IMMUTABLE STRICT _PARALLEL; 5031 5032-- Availability: 1.2.2 5033CREATE OR REPLACE FUNCTION ST_GeomFromWKB(bytea) 5034 RETURNS geometry 5035 AS 'MODULE_PATHNAME','LWGEOM_from_WKB' 5036 LANGUAGE 'c' IMMUTABLE STRICT _PARALLEL; 5037 5038-- PostGIS equivalent function: GeomFromWKB(bytea, int) 5039CREATE OR REPLACE FUNCTION ST_GeomFromWKB(bytea, int) 5040 RETURNS geometry 5041 AS 'SELECT @extschema@.ST_SetSRID(@extschema@.ST_GeomFromWKB($1), $2)' 5042 LANGUAGE 'sql' IMMUTABLE STRICT _PARALLEL; 5043 5044-- PostGIS equivalent function: PointFromWKB(bytea, int) 5045CREATE OR REPLACE FUNCTION ST_PointFromWKB(bytea, int) 5046 RETURNS geometry 5047 AS ' 5048 SELECT CASE WHEN @extschema@.geometrytype(@extschema@.ST_GeomFromWKB($1, $2)) = ''POINT'' 5049 THEN @extschema@.ST_GeomFromWKB($1, $2) 5050 ELSE NULL END 5051 ' 5052 LANGUAGE 'sql' IMMUTABLE STRICT _PARALLEL; 5053 5054-- Availability: 1.2.2 5055CREATE OR REPLACE FUNCTION ST_PointFromWKB(bytea) 5056 RETURNS geometry 5057 AS ' 5058 SELECT CASE WHEN @extschema@.geometrytype(@extschema@.ST_GeomFromWKB($1)) = ''POINT'' 5059 THEN @extschema@.ST_GeomFromWKB($1) 5060 ELSE NULL END 5061 ' 5062 LANGUAGE 'sql' IMMUTABLE STRICT _PARALLEL; 5063 5064-- PostGIS equivalent function: LineFromWKB(bytea, int) 5065CREATE OR REPLACE FUNCTION ST_LineFromWKB(bytea, int) 5066 RETURNS geometry 5067 AS ' 5068 SELECT CASE WHEN @extschema@.geometrytype(@extschema@.ST_GeomFromWKB($1, $2)) = ''LINESTRING'' 5069 THEN @extschema@.ST_GeomFromWKB($1, $2) 5070 ELSE NULL END 5071 ' 5072 LANGUAGE 'sql' IMMUTABLE STRICT _PARALLEL; 5073 5074-- Availability: 1.2.2 5075CREATE OR REPLACE FUNCTION ST_LineFromWKB(bytea) 5076 RETURNS geometry 5077 AS ' 5078 SELECT CASE WHEN @extschema@.geometrytype(@extschema@.ST_GeomFromWKB($1)) = ''LINESTRING'' 5079 THEN @extschema@.ST_GeomFromWKB($1) 5080 ELSE NULL END 5081 ' 5082 LANGUAGE 'sql' IMMUTABLE STRICT _PARALLEL; 5083 5084-- Availability: 1.2.2 5085CREATE OR REPLACE FUNCTION ST_LinestringFromWKB(bytea, int) 5086 RETURNS geometry 5087 AS ' 5088 SELECT CASE WHEN @extschema@.geometrytype(@extschema@.ST_GeomFromWKB($1, $2)) = ''LINESTRING'' 5089 THEN @extschema@.ST_GeomFromWKB($1, $2) 5090 ELSE NULL END 5091 ' 5092 LANGUAGE 'sql' IMMUTABLE STRICT _PARALLEL; 5093 5094-- Availability: 1.2.2 5095CREATE OR REPLACE FUNCTION ST_LinestringFromWKB(bytea) 5096 RETURNS geometry 5097 AS ' 5098 SELECT CASE WHEN @extschema@.geometrytype(@extschema@.ST_GeomFromWKB($1)) = ''LINESTRING'' 5099 THEN @extschema@.ST_GeomFromWKB($1) 5100 ELSE NULL END 5101 ' 5102 LANGUAGE 'sql' IMMUTABLE STRICT _PARALLEL; 5103 5104-- PostGIS equivalent function: PolyFromWKB(text, int) 5105CREATE OR REPLACE FUNCTION ST_PolyFromWKB(bytea, int) 5106 RETURNS geometry 5107 AS ' 5108 SELECT CASE WHEN @extschema@.geometrytype(@extschema@.ST_GeomFromWKB($1, $2)) = ''POLYGON'' 5109 THEN @extschema@.ST_GeomFromWKB($1, $2) 5110 ELSE NULL END 5111 ' 5112 LANGUAGE 'sql' IMMUTABLE STRICT _PARALLEL; 5113 5114-- Availability: 1.2.2 5115CREATE OR REPLACE FUNCTION ST_PolyFromWKB(bytea) 5116 RETURNS geometry 5117 AS ' 5118 SELECT CASE WHEN @extschema@.geometrytype(@extschema@.ST_GeomFromWKB($1)) = ''POLYGON'' 5119 THEN @extschema@.ST_GeomFromWKB($1) 5120 ELSE NULL END 5121 ' 5122 LANGUAGE 'sql' IMMUTABLE STRICT _PARALLEL; 5123 5124-- Availability: 1.2.2 5125CREATE OR REPLACE FUNCTION ST_PolygonFromWKB(bytea, int) 5126 RETURNS geometry 5127 AS ' 5128 SELECT CASE WHEN @extschema@.geometrytype(@extschema@.ST_GeomFromWKB($1,$2)) = ''POLYGON'' 5129 THEN @extschema@.ST_GeomFromWKB($1, $2) 5130 ELSE NULL END 5131 ' 5132 LANGUAGE 'sql' IMMUTABLE STRICT _PARALLEL; 5133 5134-- Availability: 1.2.2 5135CREATE OR REPLACE FUNCTION ST_PolygonFromWKB(bytea) 5136 RETURNS geometry 5137 AS ' 5138 SELECT CASE WHEN @extschema@.geometrytype(@extschema@.ST_GeomFromWKB($1)) = ''POLYGON'' 5139 THEN @extschema@.ST_GeomFromWKB($1) 5140 ELSE NULL END 5141 ' 5142 LANGUAGE 'sql' IMMUTABLE STRICT _PARALLEL; 5143 5144-- PostGIS equivalent function: MPointFromWKB(text, int) 5145CREATE OR REPLACE FUNCTION ST_MPointFromWKB(bytea, int) 5146 RETURNS geometry 5147 AS ' 5148 SELECT CASE WHEN @extschema@.geometrytype(@extschema@.ST_GeomFromWKB($1, $2)) = ''MULTIPOINT'' 5149 THEN @extschema@.ST_GeomFromWKB($1, $2) 5150 ELSE NULL END 5151 ' 5152 LANGUAGE 'sql' IMMUTABLE STRICT _PARALLEL; 5153 5154-- Availability: 1.2.2 5155CREATE OR REPLACE FUNCTION ST_MPointFromWKB(bytea) 5156 RETURNS geometry 5157 AS ' 5158 SELECT CASE WHEN @extschema@.geometrytype(@extschema@.ST_GeomFromWKB($1)) = ''MULTIPOINT'' 5159 THEN @extschema@.ST_GeomFromWKB($1) 5160 ELSE NULL END 5161 ' 5162 LANGUAGE 'sql' IMMUTABLE STRICT _PARALLEL; 5163 5164-- Availability: 1.2.2 5165CREATE OR REPLACE FUNCTION ST_MultiPointFromWKB(bytea, int) 5166 RETURNS geometry 5167 AS ' 5168 SELECT CASE WHEN @extschema@.geometrytype(@extschema@.ST_GeomFromWKB($1,$2)) = ''MULTIPOINT'' 5169 THEN @extschema@.ST_GeomFromWKB($1, $2) 5170 ELSE NULL END 5171 ' 5172 LANGUAGE 'sql' IMMUTABLE STRICT _PARALLEL; 5173 5174-- Availability: 1.2.2 5175CREATE OR REPLACE FUNCTION ST_MultiPointFromWKB(bytea) 5176 RETURNS geometry 5177 AS ' 5178 SELECT CASE WHEN @extschema@.geometrytype(@extschema@.ST_GeomFromWKB($1)) = ''MULTIPOINT'' 5179 THEN @extschema@.ST_GeomFromWKB($1) 5180 ELSE NULL END 5181 ' 5182 LANGUAGE 'sql' IMMUTABLE STRICT _PARALLEL; 5183 5184-- Availability: 1.2.2 5185CREATE OR REPLACE FUNCTION ST_MultiLineFromWKB(bytea) 5186 RETURNS geometry 5187 AS ' 5188 SELECT CASE WHEN @extschema@.geometrytype(@extschema@.ST_GeomFromWKB($1)) = ''MULTILINESTRING'' 5189 THEN @extschema@.ST_GeomFromWKB($1) 5190 ELSE NULL END 5191 ' 5192 LANGUAGE 'sql' IMMUTABLE STRICT _PARALLEL; 5193 5194-- PostGIS equivalent function: MLineFromWKB(text, int) 5195CREATE OR REPLACE FUNCTION ST_MLineFromWKB(bytea, int) 5196 RETURNS geometry 5197 AS ' 5198 SELECT CASE WHEN @extschema@.geometrytype(@extschema@.ST_GeomFromWKB($1, $2)) = ''MULTILINESTRING'' 5199 THEN @extschema@.ST_GeomFromWKB($1, $2) 5200 ELSE NULL END 5201 ' 5202 LANGUAGE 'sql' IMMUTABLE STRICT _PARALLEL; 5203 5204-- Availability: 1.2.2 5205CREATE OR REPLACE FUNCTION ST_MLineFromWKB(bytea) 5206 RETURNS geometry 5207 AS ' 5208 SELECT CASE WHEN @extschema@.geometrytype(@extschema@.ST_GeomFromWKB($1)) = ''MULTILINESTRING'' 5209 THEN @extschema@.ST_GeomFromWKB($1) 5210 ELSE NULL END 5211 ' 5212 LANGUAGE 'sql' IMMUTABLE STRICT _PARALLEL; 5213 5214-- Availability: 1.2.2 5215-- PostGIS equivalent function: MPolyFromWKB(bytea, int) 5216CREATE OR REPLACE FUNCTION ST_MPolyFromWKB(bytea, int) 5217 RETURNS geometry 5218 AS ' 5219 SELECT CASE WHEN @extschema@.geometrytype(@extschema@.ST_GeomFromWKB($1, $2)) = ''MULTIPOLYGON'' 5220 THEN @extschema@.ST_GeomFromWKB($1, $2) 5221 ELSE NULL END 5222 ' 5223 LANGUAGE 'sql' IMMUTABLE STRICT _PARALLEL; 5224 5225-- Availability: 1.2.2 5226CREATE OR REPLACE FUNCTION ST_MPolyFromWKB(bytea) 5227 RETURNS geometry 5228 AS ' 5229 SELECT CASE WHEN @extschema@.geometrytype(@extschema@.ST_GeomFromWKB($1)) = ''MULTIPOLYGON'' 5230 THEN @extschema@.ST_GeomFromWKB($1) 5231 ELSE NULL END 5232 ' 5233 LANGUAGE 'sql' IMMUTABLE STRICT _PARALLEL; 5234 5235-- Availability: 1.2.2 5236CREATE OR REPLACE FUNCTION ST_MultiPolyFromWKB(bytea, int) 5237 RETURNS geometry 5238 AS ' 5239 SELECT CASE WHEN @extschema@.geometrytype(@extschema@.ST_GeomFromWKB($1, $2)) = ''MULTIPOLYGON'' 5240 THEN @extschema@.ST_GeomFromWKB($1, $2) 5241 ELSE NULL END 5242 ' 5243 LANGUAGE 'sql' IMMUTABLE STRICT _PARALLEL; 5244 5245-- Availability: 1.2.2 5246CREATE OR REPLACE FUNCTION ST_MultiPolyFromWKB(bytea) 5247 RETURNS geometry 5248 AS ' 5249 SELECT CASE WHEN @extschema@.geometrytype(@extschema@.ST_GeomFromWKB($1)) = ''MULTIPOLYGON'' 5250 THEN @extschema@.ST_GeomFromWKB($1) 5251 ELSE NULL END 5252 ' 5253 LANGUAGE 'sql' IMMUTABLE STRICT _PARALLEL; 5254 5255-- Availability: 1.2.2 5256CREATE OR REPLACE FUNCTION ST_GeomCollFromWKB(bytea, int) 5257 RETURNS geometry 5258 AS ' 5259 SELECT CASE 5260 WHEN @extschema@.geometrytype(@extschema@.ST_GeomFromWKB($1, $2)) = ''GEOMETRYCOLLECTION'' 5261 THEN @extschema@.ST_GeomFromWKB($1, $2) 5262 ELSE NULL END 5263 ' 5264 LANGUAGE 'sql' IMMUTABLE STRICT _PARALLEL; 5265 5266-- Availability: 1.2.2 5267CREATE OR REPLACE FUNCTION ST_GeomCollFromWKB(bytea) 5268 RETURNS geometry 5269 AS ' 5270 SELECT CASE 5271 WHEN @extschema@.geometrytype(@extschema@.ST_GeomFromWKB($1)) = ''GEOMETRYCOLLECTION'' 5272 THEN @extschema@.ST_GeomFromWKB($1) 5273 ELSE NULL END 5274 ' 5275 LANGUAGE 'sql' IMMUTABLE STRICT _PARALLEL; 5276 5277--New functions 5278 5279-- Maximum distance between linestrings. 5280 5281-- Availability: 1.5.0 5282CREATE OR REPLACE FUNCTION _ST_MaxDistance(geom1 geometry, geom2 geometry) 5283 RETURNS float8 5284 AS 'MODULE_PATHNAME', 'LWGEOM_maxdistance2d_linestring' 5285 LANGUAGE 'c' IMMUTABLE STRICT _PARALLEL; 5286 5287-- Availability: 1.5.0 5288CREATE OR REPLACE FUNCTION ST_MaxDistance(geom1 geometry, geom2 geometry) 5289 RETURNS float8 5290 AS 'SELECT @extschema@._ST_MaxDistance(@extschema@.ST_ConvexHull($1), @extschema@.ST_ConvexHull($2))' 5291 LANGUAGE 'sql' IMMUTABLE STRICT _PARALLEL; 5292 5293CREATE OR REPLACE FUNCTION ST_ClosestPoint(geom1 geometry, geom2 geometry) 5294 RETURNS geometry 5295 AS 'MODULE_PATHNAME', 'LWGEOM_closestpoint' 5296 LANGUAGE 'c' IMMUTABLE STRICT _PARALLEL; 5297 5298CREATE OR REPLACE FUNCTION ST_ShortestLine(geom1 geometry, geom2 geometry) 5299 RETURNS geometry 5300 AS 'MODULE_PATHNAME', 'LWGEOM_shortestline2d' 5301 LANGUAGE 'c' IMMUTABLE STRICT _PARALLEL; 5302 5303CREATE OR REPLACE FUNCTION _ST_LongestLine(geom1 geometry, geom2 geometry) 5304 RETURNS geometry 5305 AS 'MODULE_PATHNAME', 'LWGEOM_longestline2d' 5306 LANGUAGE 'c' IMMUTABLE STRICT _PARALLEL; 5307 5308CREATE OR REPLACE FUNCTION ST_LongestLine(geom1 geometry, geom2 geometry) 5309 RETURNS geometry 5310 AS 'SELECT @extschema@._ST_LongestLine(@extschema@.ST_ConvexHull($1), @extschema@.ST_ConvexHull($2))' 5311 LANGUAGE 'sql' IMMUTABLE STRICT _PARALLEL; 5312 5313CREATE OR REPLACE FUNCTION _ST_DFullyWithin(geom1 geometry, geom2 geometry,float8) 5314 RETURNS boolean 5315 AS 'MODULE_PATHNAME', 'LWGEOM_dfullywithin' 5316 LANGUAGE 'c' IMMUTABLE STRICT _PARALLEL; 5317 5318CREATE OR REPLACE FUNCTION ST_DFullyWithin(geom1 geometry, geom2 geometry, float8) 5319 RETURNS boolean 5320 AS 'SELECT $1 OPERATOR(@extschema@.&&) @extschema@.ST_Expand($2,$3) AND $2 OPERATOR(@extschema@.&&) @extschema@.ST_Expand($1,$3) AND @extschema@._ST_DFullyWithin(@extschema@.ST_ConvexHull($1), @extschema@.ST_ConvexHull($2), $3)' 5321 LANGUAGE 'sql' IMMUTABLE; 5322 5323-- Availability: 2.2.0 5324CREATE OR REPLACE FUNCTION ST_SwapOrdinates(geom geometry, ords cstring) 5325 RETURNS geometry 5326 AS 'MODULE_PATHNAME', 'ST_SwapOrdinates' 5327 LANGUAGE 'c' IMMUTABLE STRICT _PARALLEL 5328 COST 1; -- reset cost, see #3675 5329 5330-- NOTE: same as ST_SwapOrdinates(geometry, 'xy') 5331-- but slightly faster in that it doesn't need to parse ordinate 5332-- spec strings 5333CREATE OR REPLACE FUNCTION ST_FlipCoordinates(geometry) 5334 RETURNS geometry 5335 AS 'MODULE_PATHNAME', 'ST_FlipCoordinates' 5336 LANGUAGE 'c' IMMUTABLE STRICT _PARALLEL 5337 COST 1; -- reset cost, see #3675 5338 5339-- 5340-- SFSQL 1.1 5341-- 5342-- BdPolyFromText(multiLineStringTaggedText String, SRID Integer): Polygon 5343-- 5344-- Construct a Polygon given an arbitrary 5345-- collection of closed linestrings as a 5346-- MultiLineString text representation. 5347-- 5348-- This is a PLPGSQL function rather then an SQL function 5349-- To avoid double call of BuildArea (one to get GeometryType 5350-- and another to actual return, in a CASE WHEN construct). 5351-- Also, we profit from plpgsql to RAISE exceptions. 5352-- 5353 5354-- Availability: 1.2.2 5355CREATE OR REPLACE FUNCTION ST_BdPolyFromText(text, integer) 5356RETURNS geometry 5357AS $$ 5358DECLARE 5359 geomtext alias for $1; 5360 srid alias for $2; 5361 mline @extschema@.geometry; 5362 geom @extschema@.geometry; 5363BEGIN 5364 mline := @extschema@.ST_MultiLineStringFromText(geomtext, srid); 5365 5366 IF mline IS NULL 5367 THEN 5368 RAISE EXCEPTION 'Input is not a MultiLinestring'; 5369 END IF; 5370 5371 geom := @extschema@.ST_BuildArea(mline); 5372 5373 IF @extschema@.GeometryType(geom) != 'POLYGON' 5374 THEN 5375 RAISE EXCEPTION 'Input returns more then a single polygon, try using BdMPolyFromText instead'; 5376 END IF; 5377 5378 RETURN geom; 5379END; 5380$$ 5381LANGUAGE 'plpgsql' IMMUTABLE STRICT _PARALLEL; 5382 5383-- 5384-- SFSQL 1.1 5385-- 5386-- BdMPolyFromText(multiLineStringTaggedText String, SRID Integer): MultiPolygon 5387-- 5388-- Construct a MultiPolygon given an arbitrary 5389-- collection of closed linestrings as a 5390-- MultiLineString text representation. 5391-- 5392-- This is a PLPGSQL function rather then an SQL function 5393-- To raise an exception in case of invalid input. 5394-- 5395 5396-- Availability: 1.2.2 5397CREATE OR REPLACE FUNCTION ST_BdMPolyFromText(text, integer) 5398RETURNS geometry 5399AS $$ 5400DECLARE 5401 geomtext alias for $1; 5402 srid alias for $2; 5403 mline @extschema@.geometry; 5404 geom @extschema@.geometry; 5405BEGIN 5406 mline := @extschema@.ST_MultiLineStringFromText(geomtext, srid); 5407 5408 IF mline IS NULL 5409 THEN 5410 RAISE EXCEPTION 'Input is not a MultiLinestring'; 5411 END IF; 5412 5413 geom := @extschema@.ST_Multi(@extschema@.ST_BuildArea(mline)); 5414 5415 RETURN geom; 5416END; 5417$$ 5418LANGUAGE 'plpgsql' IMMUTABLE STRICT _PARALLEL; 5419 5420/* Should we include the .sql directly here ? */ 5421#include "long_xact.sql.in" 5422#include "geography.sql.in" 5423 5424-- Availability: 2.2.0 5425CREATE OR REPLACE FUNCTION ST_DistanceSphere(geom1 geometry, geom2 geometry) 5426 RETURNS FLOAT8 5427 AS $$ 5428 select @extschema@.ST_distance( @extschema@.geography($1), @extschema@.geography($2),false) 5429 $$ 5430 LANGUAGE 'sql' IMMUTABLE STRICT _PARALLEL 5431 COST 300; 5432 5433-- Availability: 1.2.2 5434-- Deprecation in 2.2.0 5435CREATE OR REPLACE FUNCTION ST_distance_sphere(geom1 geometry, geom2 geometry) 5436 RETURNS FLOAT8 AS 5437 $$ SELECT @extschema@._postgis_deprecate('ST_Distance_Sphere', 'ST_DistanceSphere', '2.2.0'); 5438 SELECT @extschema@.ST_DistanceSphere($1,$2); 5439 $$ 5440 LANGUAGE 'sql' IMMUTABLE STRICT _PARALLEL 5441 COST 300; 5442 5443--------------------------------------------------------------- 5444-- GEOMETRY_COLUMNS view support functions 5445--------------------------------------------------------------- 5446-- New helper function so we can keep list of valid geometry types in one place -- 5447-- Maps old names to pramsey beautiful names but can take old name or new name as input 5448-- By default returns new name but can be overridden to return old name for old constraint like support 5449CREATE OR REPLACE FUNCTION postgis_type_name(geomname varchar, coord_dimension integer, use_new_name boolean DEFAULT true) 5450 RETURNS varchar 5451AS 5452$$ 5453 SELECT CASE WHEN $3 THEN new_name ELSE old_name END As geomname 5454 FROM 5455 ( VALUES 5456 ('GEOMETRY', 'Geometry', 2), 5457 ('GEOMETRY', 'GeometryZ', 3), 5458 ('GEOMETRYM', 'GeometryM', 3), 5459 ('GEOMETRY', 'GeometryZM', 4), 5460 5461 ('GEOMETRYCOLLECTION', 'GeometryCollection', 2), 5462 ('GEOMETRYCOLLECTION', 'GeometryCollectionZ', 3), 5463 ('GEOMETRYCOLLECTIONM', 'GeometryCollectionM', 3), 5464 ('GEOMETRYCOLLECTION', 'GeometryCollectionZM', 4), 5465 5466 ('POINT', 'Point', 2), 5467 ('POINT', 'PointZ', 3), 5468 ('POINTM','PointM', 3), 5469 ('POINT', 'PointZM', 4), 5470 5471 ('MULTIPOINT','MultiPoint', 2), 5472 ('MULTIPOINT','MultiPointZ', 3), 5473 ('MULTIPOINTM','MultiPointM', 3), 5474 ('MULTIPOINT','MultiPointZM', 4), 5475 5476 ('POLYGON', 'Polygon', 2), 5477 ('POLYGON', 'PolygonZ', 3), 5478 ('POLYGONM', 'PolygonM', 3), 5479 ('POLYGON', 'PolygonZM', 4), 5480 5481 ('MULTIPOLYGON', 'MultiPolygon', 2), 5482 ('MULTIPOLYGON', 'MultiPolygonZ', 3), 5483 ('MULTIPOLYGONM', 'MultiPolygonM', 3), 5484 ('MULTIPOLYGON', 'MultiPolygonZM', 4), 5485 5486 ('MULTILINESTRING', 'MultiLineString', 2), 5487 ('MULTILINESTRING', 'MultiLineStringZ', 3), 5488 ('MULTILINESTRINGM', 'MultiLineStringM', 3), 5489 ('MULTILINESTRING', 'MultiLineStringZM', 4), 5490 5491 ('LINESTRING', 'LineString', 2), 5492 ('LINESTRING', 'LineStringZ', 3), 5493 ('LINESTRINGM', 'LineStringM', 3), 5494 ('LINESTRING', 'LineStringZM', 4), 5495 5496 ('CIRCULARSTRING', 'CircularString', 2), 5497 ('CIRCULARSTRING', 'CircularStringZ', 3), 5498 ('CIRCULARSTRINGM', 'CircularStringM' ,3), 5499 ('CIRCULARSTRING', 'CircularStringZM', 4), 5500 5501 ('COMPOUNDCURVE', 'CompoundCurve', 2), 5502 ('COMPOUNDCURVE', 'CompoundCurveZ', 3), 5503 ('COMPOUNDCURVEM', 'CompoundCurveM', 3), 5504 ('COMPOUNDCURVE', 'CompoundCurveZM', 4), 5505 5506 ('CURVEPOLYGON', 'CurvePolygon', 2), 5507 ('CURVEPOLYGON', 'CurvePolygonZ', 3), 5508 ('CURVEPOLYGONM', 'CurvePolygonM', 3), 5509 ('CURVEPOLYGON', 'CurvePolygonZM', 4), 5510 5511 ('MULTICURVE', 'MultiCurve', 2), 5512 ('MULTICURVE', 'MultiCurveZ', 3), 5513 ('MULTICURVEM', 'MultiCurveM', 3), 5514 ('MULTICURVE', 'MultiCurveZM', 4), 5515 5516 ('MULTISURFACE', 'MultiSurface', 2), 5517 ('MULTISURFACE', 'MultiSurfaceZ', 3), 5518 ('MULTISURFACEM', 'MultiSurfaceM', 3), 5519 ('MULTISURFACE', 'MultiSurfaceZM', 4), 5520 5521 ('POLYHEDRALSURFACE', 'PolyhedralSurface', 2), 5522 ('POLYHEDRALSURFACE', 'PolyhedralSurfaceZ', 3), 5523 ('POLYHEDRALSURFACEM', 'PolyhedralSurfaceM', 3), 5524 ('POLYHEDRALSURFACE', 'PolyhedralSurfaceZM', 4), 5525 5526 ('TRIANGLE', 'Triangle', 2), 5527 ('TRIANGLE', 'TriangleZ', 3), 5528 ('TRIANGLEM', 'TriangleM', 3), 5529 ('TRIANGLE', 'TriangleZM', 4), 5530 5531 ('TIN', 'Tin', 2), 5532 ('TIN', 'TinZ', 3), 5533 ('TINM', 'TinM', 3), 5534 ('TIN', 'TinZM', 4) ) 5535 As g(old_name, new_name, coord_dimension) 5536 WHERE (upper(old_name) = upper($1) OR upper(new_name) = upper($1)) 5537 AND coord_dimension = $2; 5538$$ 5539LANGUAGE 'sql' IMMUTABLE STRICT _PARALLEL COST 200; 5540 5541-- Availability: 2.0.0 5542-- Deprecation in 2.2.0 5543CREATE OR REPLACE FUNCTION postgis_constraint_srid(geomschema text, geomtable text, geomcolumn text) RETURNS integer AS 5544$$ 5545SELECT replace(replace(split_part(s.consrc, ' = ', 2), ')', ''), '(', '')::integer 5546 FROM pg_class c, pg_namespace n, pg_attribute a 5547 , (SELECT connamespace, conrelid, conkey, pg_get_constraintdef(oid) As consrc 5548 FROM pg_constraint) AS s 5549 WHERE n.nspname = $1 5550 AND c.relname = $2 5551 AND a.attname = $3 5552 AND a.attrelid = c.oid 5553 AND s.connamespace = n.oid 5554 AND s.conrelid = c.oid 5555 AND a.attnum = ANY (s.conkey) 5556 AND s.consrc LIKE '%srid(% = %'; 5557$$ 5558LANGUAGE 'sql' STABLE STRICT _PARALLEL; 5559 5560-- Availability: 2.0.0 5561-- Undeprecated 2.5.2 needed by UpdateGeometrySRID 5562CREATE OR REPLACE FUNCTION postgis_constraint_dims(geomschema text, geomtable text, geomcolumn text) RETURNS integer AS 5563$$ 5564SELECT replace(split_part(s.consrc, ' = ', 2), ')', '')::integer 5565 5566 FROM pg_class c, pg_namespace n, pg_attribute a 5567 , (SELECT connamespace, conrelid, conkey, pg_get_constraintdef(oid) As consrc 5568 FROM pg_constraint) AS s 5569 WHERE n.nspname = $1 5570 AND c.relname = $2 5571 AND a.attname = $3 5572 AND a.attrelid = c.oid 5573 AND s.connamespace = n.oid 5574 AND s.conrelid = c.oid 5575 AND a.attnum = ANY (s.conkey) 5576 AND s.consrc LIKE '%ndims(% = %'; 5577$$ 5578LANGUAGE 'sql' STABLE STRICT _PARALLEL; 5579 5580-- support function to pull out geometry type from constraint check 5581-- will return pretty name instead of ugly name 5582-- Availability: 2.0.0 5583-- Undeprecated 5584-- Changed: 2.5.2 replace use of pg_constraint.consrc with pg_get_constraintdef, consrc removed pg12 5585CREATE OR REPLACE FUNCTION postgis_constraint_type(geomschema text, geomtable text, geomcolumn text) RETURNS varchar AS 5586$$ 5587SELECT replace(split_part(s.consrc, '''', 2), ')', '')::varchar 5588 5589 FROM pg_class c, pg_namespace n, pg_attribute a 5590 , (SELECT connamespace, conrelid, conkey, pg_get_constraintdef(oid) As consrc 5591 FROM pg_constraint) AS s 5592 WHERE n.nspname = $1 5593 AND c.relname = $2 5594 AND a.attname = $3 5595 AND a.attrelid = c.oid 5596 AND s.connamespace = n.oid 5597 AND s.conrelid = c.oid 5598 AND a.attnum = ANY (s.conkey) 5599 AND s.consrc LIKE '%geometrytype(% = %'; 5600$$ 5601LANGUAGE 'sql' STABLE STRICT _PARALLEL; 5602 5603-- Availability: 2.0.0 5604-- Changed: 2.1.8 significant performance improvement for constraint based columns 5605-- Changed: 2.2.0 get rid of schema, table, column cast to improve performance 5606-- Changed: 2.4.0 List also Parent partitioned tables 5607-- Changed: 2.5.2 replace use of pg_constraint.consrc with pg_get_constraintdef, consrc removed pg12 5608CREATE OR REPLACE VIEW geometry_columns AS 5609 SELECT current_database()::character varying(256) AS f_table_catalog, 5610 n.nspname AS f_table_schema, 5611 c.relname AS f_table_name, 5612 a.attname AS f_geometry_column, 5613 COALESCE(postgis_typmod_dims(a.atttypmod), sn.ndims, 2) AS coord_dimension, 5614 COALESCE(NULLIF(postgis_typmod_srid(a.atttypmod), 0), sr.srid, 0) AS srid, 5615 replace(replace(COALESCE(NULLIF(upper(postgis_typmod_type(a.atttypmod)), 'GEOMETRY'::text), st.type, 'GEOMETRY'::text), 'ZM'::text, ''::text), 'Z'::text, ''::text)::character varying(30) AS type 5616 FROM pg_class c 5617 JOIN pg_attribute a ON a.attrelid = c.oid AND NOT a.attisdropped 5618 JOIN pg_namespace n ON c.relnamespace = n.oid 5619 JOIN pg_type t ON a.atttypid = t.oid 5620 LEFT JOIN ( SELECT s.connamespace, 5621 s.conrelid, 5622 s.conkey, replace(split_part(s.consrc, ''''::text, 2), ')'::text, ''::text) As type 5623 FROM (SELECT connamespace, conrelid, conkey, pg_get_constraintdef(oid) As consrc 5624 FROM pg_constraint) AS s 5625 WHERE s.consrc ~~* '%geometrytype(% = %'::text 5626 5627) st ON st.connamespace = n.oid AND st.conrelid = c.oid AND (a.attnum = ANY (st.conkey)) 5628 LEFT JOIN ( SELECT s.connamespace, 5629 s.conrelid, 5630 s.conkey, replace(split_part(s.consrc, ' = '::text, 2), ')'::text, ''::text)::integer As ndims 5631 FROM (SELECT connamespace, conrelid, conkey, pg_get_constraintdef(oid) As consrc 5632 FROM pg_constraint) AS s 5633 WHERE s.consrc ~~* '%ndims(% = %'::text 5634 5635) sn ON sn.connamespace = n.oid AND sn.conrelid = c.oid AND (a.attnum = ANY (sn.conkey)) 5636 LEFT JOIN ( SELECT s.connamespace, 5637 s.conrelid, 5638 s.conkey, replace(replace(split_part(s.consrc, ' = '::text, 2), ')'::text, ''::text), '('::text, ''::text)::integer As srid 5639 FROM (SELECT connamespace, conrelid, conkey, pg_get_constraintdef(oid) As consrc 5640 FROM pg_constraint) AS s 5641 WHERE s.consrc ~~* '%srid(% = %'::text 5642 5643) sr ON sr.connamespace = n.oid AND sr.conrelid = c.oid AND (a.attnum = ANY (sr.conkey)) 5644 WHERE (c.relkind = ANY (ARRAY['r'::"char", 'v'::"char", 'm'::"char", 'f'::"char", 'p'::"char"])) 5645 AND NOT c.relname = 'raster_columns'::name AND t.typname = 'geometry'::name 5646 AND NOT pg_is_other_temp_schema(c.relnamespace) AND has_table_privilege(c.oid, 'SELECT'::text); 5647 5648-- TODO: support RETURNING and raise a WARNING 5649CREATE OR REPLACE RULE geometry_columns_insert AS 5650 ON INSERT TO geometry_columns 5651 DO INSTEAD NOTHING; 5652 5653-- TODO: raise a WARNING 5654CREATE OR REPLACE RULE geometry_columns_update AS 5655 ON UPDATE TO geometry_columns 5656 DO INSTEAD NOTHING; 5657 5658-- TODO: raise a WARNING 5659CREATE OR REPLACE RULE geometry_columns_delete AS 5660 ON DELETE TO geometry_columns 5661 DO INSTEAD NOTHING; 5662 5663--------------------------------------------------------------- 5664-- 3D-functions 5665--------------------------------------------------------------- 5666 5667CREATE OR REPLACE FUNCTION ST_3DDistance(geom1 geometry, geom2 geometry) 5668 RETURNS float8 5669 AS 'MODULE_PATHNAME', 'distance3d' 5670 LANGUAGE 'c' IMMUTABLE STRICT _PARALLEL 5671 COST 100; 5672 5673CREATE OR REPLACE FUNCTION ST_3DMaxDistance(geom1 geometry, geom2 geometry) 5674 RETURNS float8 5675 AS 'MODULE_PATHNAME', 'LWGEOM_maxdistance3d' 5676 LANGUAGE 'c' IMMUTABLE STRICT _PARALLEL 5677 COST 100; 5678 5679CREATE OR REPLACE FUNCTION ST_3DClosestPoint(geom1 geometry, geom2 geometry) 5680 RETURNS geometry 5681 AS 'MODULE_PATHNAME', 'LWGEOM_closestpoint3d' 5682 LANGUAGE 'c' IMMUTABLE STRICT _PARALLEL 5683 COST 1; -- reset cost, see #3675 5684 5685CREATE OR REPLACE FUNCTION ST_3DShortestLine(geom1 geometry, geom2 geometry) 5686 RETURNS geometry 5687 AS 'MODULE_PATHNAME', 'LWGEOM_shortestline3d' 5688 LANGUAGE 'c' IMMUTABLE STRICT _PARALLEL 5689 COST 1; -- reset cost, see #3675 5690 5691CREATE OR REPLACE FUNCTION ST_3DLongestLine(geom1 geometry, geom2 geometry) 5692 RETURNS geometry 5693 AS 'MODULE_PATHNAME', 'LWGEOM_longestline3d' 5694 LANGUAGE 'c' IMMUTABLE STRICT _PARALLEL 5695 COST 1; -- reset cost, see #3675 5696 5697CREATE OR REPLACE FUNCTION _ST_3DDWithin(geom1 geometry, geom2 geometry,float8) 5698 RETURNS boolean 5699 AS 'MODULE_PATHNAME', 'LWGEOM_dwithin3d' 5700 LANGUAGE 'c' IMMUTABLE STRICT _PARALLEL 5701 COST 100; 5702 5703CREATE OR REPLACE FUNCTION ST_3DDWithin(geom1 geometry, geom2 geometry,float8) 5704 RETURNS boolean 5705 AS 'SELECT $1 OPERATOR(@extschema@.&&) @extschema@.ST_Expand($2,$3) AND $2 OPERATOR(@extschema@.&&) @extschema@.ST_Expand($1,$3) AND @extschema@._ST_3DDWithin($1, $2, $3)' 5706 LANGUAGE 'sql' IMMUTABLE _PARALLEL 5707 COST 100; 5708 5709CREATE OR REPLACE FUNCTION _ST_3DDFullyWithin(geom1 geometry, geom2 geometry,float8) 5710 RETURNS boolean 5711 AS 'MODULE_PATHNAME', 'LWGEOM_dfullywithin3d' 5712 LANGUAGE 'c' IMMUTABLE STRICT _PARALLEL 5713 COST 100; 5714 5715CREATE OR REPLACE FUNCTION ST_3DDFullyWithin(geom1 geometry, geom2 geometry,float8) 5716 RETURNS boolean 5717 AS 'SELECT $1 OPERATOR(@extschema@.&&) @extschema@.ST_Expand($2,$3) AND $2 OPERATOR(@extschema@.&&) @extschema@.ST_Expand($1,$3) AND @extschema@._ST_3DDFullyWithin($1, $2, $3)' 5718 LANGUAGE 'sql' IMMUTABLE _PARALLEL 5719 COST 100; 5720 5721CREATE OR REPLACE FUNCTION _ST_3DIntersects(geom1 geometry, geom2 geometry) 5722 RETURNS boolean 5723 AS 'MODULE_PATHNAME','intersects3d' 5724 LANGUAGE 'c' IMMUTABLE STRICT _PARALLEL 5725 COST 100; 5726 5727CREATE OR REPLACE FUNCTION ST_3DIntersects(geom1 geometry, geom2 geometry) 5728 RETURNS boolean 5729 AS 'SELECT $1 OPERATOR(@extschema@.&&) $2 AND @extschema@._ST_3DIntersects($1, $2)' 5730 LANGUAGE 'sql' IMMUTABLE _PARALLEL 5731 COST 100; 5732 5733--------------------------------------------------------------- 5734-- SQL-MM 5735--------------------------------------------------------------- 5736-- PostGIS equivalent function: ST_ndims(geometry) 5737CREATE OR REPLACE FUNCTION ST_CoordDim(Geometry geometry) 5738 RETURNS smallint 5739 AS 'MODULE_PATHNAME', 'LWGEOM_ndims' 5740 LANGUAGE 'c' IMMUTABLE STRICT _PARALLEL 5741 COST 5; 5742-- 5743-- SQL-MM 5744-- 5745-- ST_CurveToLine(Geometry geometry, Tolerance float8, ToleranceType integer, Flags integer) 5746-- 5747-- Converts a given geometry to a linear geometry. Each curveed 5748-- geometry or segment is converted into a linear approximation using 5749-- the given tolerance. 5750-- 5751-- Semantic of tolerance depends on the `toltype` argument, which can be: 5752-- 0: Tolerance is number of segments per quadrant 5753-- 1: Tolerance is max distance between curve and line 5754-- 2: Tolerance is max angle between radii defining line vertices 5755-- 5756-- Supported flags: 5757-- 1: Symmetric output (result in same vertices when inverting the curve) 5758-- 5759-- Availability: 2.4.0 5760-- 5761CREATE OR REPLACE FUNCTION ST_CurveToLine(geom geometry, tol float8 DEFAULT 32, toltype integer DEFAULT 0, flags integer DEFAULT 0) 5762 RETURNS geometry 5763 AS 'MODULE_PATHNAME', 'ST_CurveToLine' 5764 LANGUAGE 'c' IMMUTABLE STRICT _PARALLEL; 5765 5766CREATE OR REPLACE FUNCTION ST_HasArc(Geometry geometry) 5767 RETURNS boolean 5768 AS 'MODULE_PATHNAME', 'LWGEOM_has_arc' 5769 LANGUAGE 'c' IMMUTABLE STRICT _PARALLEL; 5770 5771CREATE OR REPLACE FUNCTION ST_LineToCurve(Geometry geometry) 5772 RETURNS geometry 5773 AS 'MODULE_PATHNAME', 'LWGEOM_line_desegmentize' 5774 LANGUAGE 'c' IMMUTABLE STRICT _PARALLEL; 5775 5776-- Availability: 1.5.0 5777CREATE OR REPLACE FUNCTION _ST_OrderingEquals(GeometryA geometry, GeometryB geometry) 5778 RETURNS boolean 5779 AS 'MODULE_PATHNAME', 'LWGEOM_same' 5780 LANGUAGE 'c' IMMUTABLE STRICT _PARALLEL 5781 COST 100; 5782 5783-- Availability: 1.3.0 5784CREATE OR REPLACE FUNCTION ST_OrderingEquals(GeometryA geometry, GeometryB geometry) 5785 RETURNS boolean 5786 AS $$ 5787 SELECT $1 OPERATOR(@extschema@.~=) $2 AND @extschema@._ST_OrderingEquals($1, $2) 5788 $$ 5789 LANGUAGE 'sql' IMMUTABLE _PARALLEL; 5790 5791------------------------------------------------------------------------------- 5792-- SQL/MM - SQL Functions on type ST_Point 5793------------------------------------------------------------------------------- 5794 5795-- PostGIS equivalent function: ST_MakePoint(XCoordinate float8,YCoordinate float8) 5796CREATE OR REPLACE FUNCTION ST_Point(float8, float8) 5797 RETURNS geometry 5798 AS 'MODULE_PATHNAME', 'LWGEOM_makepoint' 5799 LANGUAGE 'c' IMMUTABLE STRICT _PARALLEL; 5800 5801-- PostGIS equivalent function: ST_MakePolygon(Geometry geometry) 5802CREATE OR REPLACE FUNCTION ST_Polygon(geometry, int) 5803 RETURNS geometry 5804 AS $$ 5805 SELECT @extschema@.ST_SetSRID(@extschema@.ST_MakePolygon($1), $2) 5806 $$ 5807 LANGUAGE 'sql' IMMUTABLE STRICT _PARALLEL; 5808 5809-- PostGIS equivalent function: GeomFromWKB(WKB bytea)) 5810-- Note: Defaults to an SRID=-1, not 0 as per SQL/MM specs. 5811CREATE OR REPLACE FUNCTION ST_WKBToSQL(WKB bytea) 5812 RETURNS geometry 5813 AS 'MODULE_PATHNAME','LWGEOM_from_WKB' 5814 LANGUAGE 'c' IMMUTABLE STRICT _PARALLEL; 5815 5816--- 5817-- Linear referencing functions 5818--- 5819-- Availability: 2.0.0 5820CREATE OR REPLACE FUNCTION ST_LocateBetween(Geometry geometry, FromMeasure float8, ToMeasure float8, LeftRightOffset float8 default 0.0) 5821 RETURNS geometry 5822 AS 'MODULE_PATHNAME', 'ST_LocateBetween' 5823 LANGUAGE 'c' IMMUTABLE STRICT _PARALLEL; 5824 5825-- Availability: 2.0.0 5826CREATE OR REPLACE FUNCTION ST_LocateAlong(Geometry geometry, Measure float8, LeftRightOffset float8 default 0.0) 5827 RETURNS geometry 5828 AS 'MODULE_PATHNAME', 'ST_LocateAlong' 5829 LANGUAGE 'c' IMMUTABLE STRICT _PARALLEL; 5830 5831-- Only accepts LINESTRING as parameters. 5832-- Availability: 1.4.0 5833CREATE OR REPLACE FUNCTION ST_LocateBetweenElevations(Geometry geometry, FromElevation float8, ToElevation float8) 5834 RETURNS geometry 5835 AS 'MODULE_PATHNAME', 'ST_LocateBetweenElevations' 5836 LANGUAGE 'c' IMMUTABLE STRICT _PARALLEL; 5837 5838-- Availability: 2.0.0 5839CREATE OR REPLACE FUNCTION ST_InterpolatePoint(Line geometry, Point geometry) 5840 RETURNS float8 5841 AS 'MODULE_PATHNAME', 'ST_InterpolatePoint' 5842 LANGUAGE 'c' IMMUTABLE STRICT _PARALLEL; 5843 5844-- moved to separate file cause its invovled 5845#include "postgis_brin.sql.in" 5846 5847--------------------------------------------------------------- 5848-- USER CONTRIBUTED 5849--------------------------------------------------------------- 5850 5851-- ST_ConcaveHull and Helper functions starts here -- 5852----------------------------------------------------------------------- 5853-- Contributed by Regina Obe and Leo Hsu 5854-- Availability: 2.0.0 5855-- Changed: 2.5.0 5856----------------------------------------------------------------------- 5857CREATE OR REPLACE FUNCTION _st_concavehull(param_inputgeom geometry) 5858 RETURNS geometry AS 5859$$ 5860 DECLARE 5861 vexhull @extschema@.geometry; 5862 var_resultgeom @extschema@.geometry; 5863 var_inputgeom @extschema@.geometry; 5864 vexring @extschema@.geometry; 5865 cavering @extschema@.geometry; 5866 cavept @extschema@.geometry[]; 5867 seglength double precision; 5868 var_tempgeom @extschema@.geometry; 5869 scale_factor float := 1; 5870 i integer; 5871 BEGIN 5872 -- First compute the ConvexHull of the geometry 5873 vexhull := @extschema@.ST_ConvexHull(param_inputgeom); 5874 var_inputgeom := param_inputgeom; 5875 --A point really has no concave hull 5876 IF @extschema@.ST_GeometryType(vexhull) = 'ST_Point' OR @extschema@.ST_GeometryType(vexHull) = 'ST_LineString' THEN 5877 RETURN vexhull; 5878 END IF; 5879 5880 -- convert the hull perimeter to a linestring so we can manipulate individual points 5881 vexring := CASE WHEN @extschema@.ST_GeometryType(vexhull) = 'ST_LineString' THEN vexhull ELSE @extschema@.ST_ExteriorRing(vexhull) END; 5882 IF abs(@extschema@.ST_X(@extschema@.ST_PointN(vexring,1))) < 1 THEN --scale the geometry to prevent stupid precision errors - not sure it works so make low for now 5883 scale_factor := 100; 5884 vexring := @extschema@.ST_Scale(vexring, scale_factor,scale_factor); 5885 var_inputgeom := @extschema@.ST_Scale(var_inputgeom, scale_factor, scale_factor); 5886 --RAISE NOTICE 'Scaling'; 5887 END IF; 5888 seglength := @extschema@.ST_Length(vexring)/least(@extschema@.ST_NPoints(vexring)*2,1000) ; 5889 5890 vexring := @extschema@.ST_Segmentize(vexring, seglength); 5891 -- find the point on the original geom that is closest to each point of the convex hull and make a new linestring out of it. 5892 cavering := @extschema@.ST_Collect( 5893 ARRAY( 5894 5895 SELECT 5896 @extschema@.ST_ClosestPoint(var_inputgeom, pt ) As the_geom 5897 FROM ( 5898 SELECT @extschema@.ST_PointN(vexring, n ) As pt, n 5899 FROM 5900 generate_series(1, @extschema@.ST_NPoints(vexring) ) As n 5901 ) As pt 5902 5903 ) 5904 ) 5905 ; 5906 5907 var_resultgeom := @extschema@.ST_MakeLine(geom) 5908 FROM @extschema@.ST_Dump(cavering) As foo; 5909 5910 IF @extschema@.ST_IsSimple(var_resultgeom) THEN 5911 var_resultgeom := @extschema@.ST_MakePolygon(var_resultgeom); 5912 --RAISE NOTICE 'is Simple: %', var_resultgeom; 5913 ELSE /** will not result in a valid polygon -- just return convex hull **/ 5914 --RAISE NOTICE 'is not Simple: %', var_resultgeom; 5915 var_resultgeom := @extschema@.ST_ConvexHull(var_resultgeom); 5916 END IF; 5917 5918 IF scale_factor > 1 THEN -- scale the result back 5919 var_resultgeom := @extschema@.ST_Scale(var_resultgeom, 1/scale_factor, 1/scale_factor); 5920 END IF; 5921 5922 -- make sure result covers original (#3638) 5923 -- Using ST_UnaryUnion since SFCGAL doesn't replace with its own implementation 5924 -- and SFCGAL one chokes for some reason 5925 var_resultgeom := @extschema@.ST_UnaryUnion(@extschema@.ST_Collect(param_inputgeom, var_resultgeom) ); 5926 RETURN var_resultgeom; 5927 5928 END; 5929$$ 5930 LANGUAGE plpgsql IMMUTABLE STRICT _PARALLEL; 5931 5932-- Availability: 2.0.0 5933-- Changed: 2.5.5 5934-- Changed: 2.5.0 5935CREATE OR REPLACE FUNCTION ST_ConcaveHull(param_geom geometry, param_pctconvex float, param_allow_holes boolean DEFAULT false) RETURNS geometry AS 5936$$ 5937 DECLARE 5938 var_convhull @extschema@.geometry := @extschema@.ST_ForceSFS(@extschema@.ST_ConvexHull(param_geom)); 5939 var_param_geom @extschema@.geometry := @extschema@.ST_ForceSFS(param_geom); 5940 var_initarea float := @extschema@.ST_Area(var_convhull); 5941 var_newarea float := var_initarea; 5942 var_div integer := 6; /** this is the 1/var_div is the percent increase we will allow per geometry to keep speed decent **/ 5943 var_tempgeom @extschema@.geometry; 5944 var_tempgeom2 @extschema@.geometry; 5945 var_cent @extschema@.geometry; 5946 var_geoms @extschema@.geometry[4]; /** We will cut the current geometry into 4 triangular quadrants along the centroid/extent **/ 5947 var_enline @extschema@.geometry; 5948 var_resultgeom @extschema@.geometry; 5949 var_atempgeoms @extschema@.geometry[]; 5950 var_buf float := 1; /** tolerance so that geometries that are right on the extent don't get accidentally clipped off **/ 5951 BEGIN 5952 -- We start with convex hull as our base 5953 var_resultgeom := var_convhull; 5954 5955 IF param_pctconvex = 1 THEN 5956 -- this is the same as asking for the convex hull 5957 return var_resultgeom; 5958 ELSIF @extschema@.ST_GeometryType(var_param_geom) = 'ST_Polygon' THEN -- it is as concave as it is going to get 5959 IF param_allow_holes THEN -- leave the holes 5960 RETURN var_param_geom; 5961 ELSE -- remove the holes 5962 var_resultgeom := @extschema@.ST_MakePolygon(@extschema@.ST_ExteriorRing(var_param_geom)); 5963 RETURN var_resultgeom; 5964 END IF; 5965 END IF; 5966 IF @extschema@.ST_Dimension(var_resultgeom) > 1 AND param_pctconvex BETWEEN 0 and 0.99 THEN 5967 -- get linestring that forms envelope of geometry 5968 var_enline := @extschema@.ST_Boundary(@extschema@.ST_Envelope(var_param_geom)); 5969 var_buf := @extschema@.ST_Length(var_enline)/1000.0; 5970 IF @extschema@.ST_GeometryType(var_param_geom) = 'ST_MultiPoint' AND @extschema@.ST_NumGeometries(var_param_geom) BETWEEN 4 and 200 THEN 5971 -- we make polygons out of points since they are easier to cave in. 5972 -- Note we limit to between 4 and 200 points because this process is slow and gets quadratically slow 5973 var_buf := sqrt(@extschema@.ST_Area(var_convhull)*0.8/(@extschema@.ST_NumGeometries(var_param_geom)*@extschema@.ST_NumGeometries(var_param_geom))); 5974 var_atempgeoms := ARRAY(SELECT geom FROM @extschema@.ST_DumpPoints(var_param_geom)); 5975 -- 5 and 10 and just fudge factors 5976 var_tempgeom := @extschema@.ST_Union(ARRAY(SELECT geom 5977 FROM ( 5978 -- fuse near neighbors together 5979 SELECT DISTINCT ON (i) i, @extschema@.ST_Distance(var_atempgeoms[i],var_atempgeoms[j]), @extschema@.ST_Buffer(@extschema@.ST_MakeLine(var_atempgeoms[i], var_atempgeoms[j]) , var_buf*5, 'quad_segs=3') As geom 5980 FROM generate_series(1,array_upper(var_atempgeoms, 1)) As i 5981 INNER JOIN generate_series(1,array_upper(var_atempgeoms, 1)) As j 5982 ON ( 5983 NOT @extschema@.ST_Intersects(var_atempgeoms[i],var_atempgeoms[j]) 5984 AND @extschema@.ST_DWithin(var_atempgeoms[i],var_atempgeoms[j], var_buf*10) 5985 ) 5986 UNION ALL 5987 -- catch the ones with no near neighbors 5988 SELECT i, 0, @extschema@.ST_Buffer(var_atempgeoms[i] , var_buf*10, 'quad_segs=3') As geom 5989 FROM generate_series(1,array_upper(var_atempgeoms, 1)) As i 5990 LEFT JOIN generate_series(ceiling(array_upper(var_atempgeoms,1)/2)::integer,array_upper(var_atempgeoms, 1)) As j 5991 ON ( 5992 NOT @extschema@.ST_Intersects(var_atempgeoms[i],var_atempgeoms[j]) 5993 AND @extschema@.ST_DWithin(var_atempgeoms[i],var_atempgeoms[j], var_buf*10) 5994 ) 5995 WHERE j IS NULL 5996 ORDER BY 1, 2 5997 ) As foo ) ); 5998 IF @extschema@.ST_IsValid(var_tempgeom) AND @extschema@.ST_GeometryType(var_tempgeom) = 'ST_Polygon' THEN 5999 var_tempgeom := @extschema@.ST_ForceSFS(@extschema@.ST_Intersection(var_tempgeom, var_convhull)); 6000 IF param_allow_holes THEN 6001 var_param_geom := var_tempgeom; 6002 ELSIF @extschema@.ST_GeometryType(var_tempgeom) = 'ST_Polygon' THEN 6003 var_param_geom := @extschema@.ST_ForceSFS(@extschema@.ST_MakePolygon(@extschema@.ST_ExteriorRing(var_tempgeom))); 6004 ELSE 6005 var_param_geom := @extschema@.ST_ForceSFS(@extschema@.ST_ConvexHull(var_param_geom)); 6006 END IF; 6007 -- make sure result covers original (#3638) 6008 var_param_geom := @extschema@.ST_Union(param_geom, var_param_geom); 6009 return var_param_geom; 6010 ELSIF @extschema@.ST_IsValid(var_tempgeom) THEN 6011 var_param_geom := @extschema@.ST_ForceSFS(@extschema@.ST_Intersection(var_tempgeom, var_convhull)); 6012 END IF; 6013 END IF; 6014 6015 IF @extschema@.ST_GeometryType(var_param_geom) = 'ST_Polygon' THEN 6016 IF NOT param_allow_holes THEN 6017 var_param_geom := @extschema@.ST_ForceSFS(@extschema@.ST_MakePolygon(@extschema@.ST_ExteriorRing(var_param_geom))); 6018 END IF; 6019 -- make sure result covers original (#3638) 6020 --var_param_geom := @extschema@.ST_Union(param_geom, var_param_geom); 6021 return var_param_geom; 6022 END IF; 6023 var_cent := @extschema@.ST_Centroid(var_param_geom); 6024 IF (@extschema@.ST_XMax(var_enline) - @extschema@.ST_XMin(var_enline) ) > var_buf AND (@extschema@.ST_YMax(var_enline) - @extschema@.ST_YMin(var_enline) ) > var_buf THEN 6025 IF @extschema@.ST_Dwithin(@extschema@.ST_Centroid(var_convhull) , @extschema@.ST_Centroid(@extschema@.ST_Envelope(var_param_geom)), var_buf/2) THEN 6026 -- If the geometric dimension is > 1 and the object is symettric (cutting at centroid will not work -- offset a bit) 6027 var_cent := @extschema@.ST_Translate(var_cent, (@extschema@.ST_XMax(var_enline) - @extschema@.ST_XMin(var_enline))/1000, (@extschema@.ST_YMAX(var_enline) - @extschema@.ST_YMin(var_enline))/1000); 6028 ELSE 6029 -- uses closest point on geometry to centroid. I can't explain why we are doing this 6030 var_cent := @extschema@.ST_ClosestPoint(var_param_geom,var_cent); 6031 END IF; 6032 IF @extschema@.ST_DWithin(var_cent, var_enline,var_buf) THEN 6033 var_cent := @extschema@.ST_centroid(@extschema@.ST_Envelope(var_param_geom)); 6034 END IF; 6035 -- break envelope into 4 triangles about the centroid of the geometry and returned the clipped geometry in each quadrant 6036 FOR i in 1 .. 4 LOOP 6037 var_geoms[i] := @extschema@.ST_MakePolygon(@extschema@.ST_MakeLine(ARRAY[@extschema@.ST_PointN(var_enline,i), @extschema@.ST_PointN(var_enline,i+1), var_cent, @extschema@.ST_PointN(var_enline,i)])); 6038 var_geoms[i] := @extschema@.ST_ForceSFS(@extschema@.ST_Intersection(var_param_geom, @extschema@.ST_Buffer(var_geoms[i],var_buf))); 6039 IF @extschema@.ST_IsValid(var_geoms[i]) THEN 6040 6041 ELSE 6042 var_geoms[i] := @extschema@.ST_BuildArea(@extschema@.ST_MakeLine(ARRAY[@extschema@.ST_PointN(var_enline,i), @extschema@.ST_PointN(var_enline,i+1), var_cent, @extschema@.ST_PointN(var_enline,i)])); 6043 END IF; 6044 END LOOP; 6045 var_tempgeom := @extschema@.ST_Union(ARRAY[@extschema@.ST_ConvexHull(var_geoms[1]), @extschema@.ST_ConvexHull(var_geoms[2]) , @extschema@.ST_ConvexHull(var_geoms[3]), @extschema@.ST_ConvexHull(var_geoms[4])]); 6046 --RAISE NOTICE 'Curr vex % ', @extschema@.ST_AsText(var_tempgeom); 6047 IF @extschema@.ST_Area(var_tempgeom) <= var_newarea AND @extschema@.ST_IsValid(var_tempgeom) THEN --AND @extschema@.ST_GeometryType(var_tempgeom) ILIKE '%Polygon' 6048 6049 var_tempgeom := @extschema@.ST_Buffer(@extschema@.ST_ConcaveHull(var_geoms[1],least(param_pctconvex + param_pctconvex/var_div),true),var_buf, 'quad_segs=2'); 6050 FOR i IN 1 .. 4 LOOP 6051 var_geoms[i] := @extschema@.ST_Buffer(@extschema@.ST_ConcaveHull(var_geoms[i],least(param_pctconvex + param_pctconvex/var_div),true), var_buf, 'quad_segs=2'); 6052 IF @extschema@.ST_IsValid(var_geoms[i]) Then 6053 var_tempgeom := @extschema@.ST_Union(var_tempgeom, var_geoms[i]); 6054 ELSE 6055 RAISE NOTICE 'Not valid % %', i, @extschema@.ST_AsText(var_tempgeom); 6056 var_tempgeom := @extschema@.ST_Union(var_tempgeom, @extschema@.ST_ConvexHull(var_geoms[i])); 6057 END IF; 6058 END LOOP; 6059 6060 --RAISE NOTICE 'Curr concave % ', @extschema@.ST_AsText(var_tempgeom); 6061 IF @extschema@.ST_IsValid(var_tempgeom) THEN 6062 var_resultgeom := var_tempgeom; 6063 END IF; 6064 var_newarea := @extschema@.ST_Area(var_resultgeom); 6065 ELSIF @extschema@.ST_IsValid(var_tempgeom) THEN 6066 var_resultgeom := var_tempgeom; 6067 END IF; 6068 6069 IF @extschema@.ST_NumGeometries(var_resultgeom) > 1 THEN 6070 var_tempgeom := @extschema@._ST_ConcaveHull(var_resultgeom); 6071 IF @extschema@.ST_IsValid(var_tempgeom) AND @extschema@.ST_GeometryType(var_tempgeom) ILIKE 'ST_Polygon' THEN 6072 var_resultgeom := var_tempgeom; 6073 ELSE 6074 var_resultgeom := @extschema@.ST_Buffer(var_tempgeom,var_buf, 'quad_segs=2'); 6075 END IF; 6076 END IF; 6077 IF param_allow_holes = false THEN 6078 -- only keep exterior ring since we do not want holes 6079 var_resultgeom := @extschema@.ST_MakePolygon(@extschema@.ST_ExteriorRing(var_resultgeom)); 6080 END IF; 6081 ELSE 6082 var_resultgeom := @extschema@.ST_Buffer(var_resultgeom,var_buf); 6083 END IF; 6084 var_resultgeom := @extschema@.ST_ForceSFS(@extschema@.ST_Intersection(var_resultgeom, @extschema@.ST_ConvexHull(var_param_geom))); 6085 ELSE 6086 -- dimensions are too small to cut 6087 var_resultgeom := @extschema@._ST_ConcaveHull(var_param_geom); 6088 END IF; 6089 6090 RETURN var_resultgeom; 6091 END; 6092$$ 6093LANGUAGE 'plpgsql' IMMUTABLE STRICT _PARALLEL; 6094-- ST_ConcaveHull and Helper functions end here -- 6095 6096----------------------------------------------------------------------- 6097-- X3D OUTPUT 6098----------------------------------------------------------------------- 6099-- _ST_AsX3D(version, geom, precision, option, attribs) 6100CREATE OR REPLACE FUNCTION _ST_AsX3D(int4, geometry, int4, int4, text) 6101 RETURNS TEXT 6102 AS 'MODULE_PATHNAME','LWGEOM_asX3D' 6103 LANGUAGE 'c' IMMUTABLE _PARALLEL; 6104 6105-- ST_AsX3D(geom, precision, options) 6106CREATE OR REPLACE FUNCTION ST_AsX3D(geom geometry, maxdecimaldigits integer DEFAULT 15, options integer DEFAULT 0) 6107 RETURNS TEXT 6108 AS $$SELECT @extschema@._ST_AsX3D(3,$1,$2,$3,'');$$ 6109 LANGUAGE 'sql' IMMUTABLE _PARALLEL; 6110 6111----------------------------------------------------------------------- 6112-- ST_Angle 6113----------------------------------------------------------------------- 6114-- Availability: 2.3.0 6115-- has to be here because need ST_StartPoint 6116CREATE OR REPLACE FUNCTION ST_Angle(line1 geometry, line2 geometry) 6117 RETURNS float8 AS 'SELECT ST_Angle(St_StartPoint($1), ST_EndPoint($1), St_StartPoint($2), ST_EndPoint($2))' 6118 LANGUAGE 'sql' IMMUTABLE STRICT; 6119 6120-- make views and spatial_ref_sys public viewable -- 6121GRANT SELECT ON TABLE geography_columns TO public; 6122GRANT SELECT ON TABLE geometry_columns TO public; 6123GRANT SELECT ON TABLE spatial_ref_sys TO public; 6124 6125 6126-- moved to separate file cause its invovled 6127#include "postgis_spgist.sql.in" 6128 6129COMMIT; 6130