Permalink
Cannot retrieve contributors at this time
Name already in use
A tag already exists with the provided branch name. Many Git commands accept both tag and branch names, so creating this branch may cause unexpected behavior. Are you sure you want to create this branch?
postgis/postgis/postgis.sql.in
Go to fileThis commit does not belong to any branch on this repository, and may belong to a fork outside of the repository.
6517 lines (5675 sloc)
215 KB
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
-- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - | |
-- | |
-- | |
-- PostGIS - Spatial Types for PostgreSQL | |
-- http://postgis.net | |
-- Copyright 2001-2003 Refractions Research Inc. | |
-- | |
-- This is free software; you can redistribute and/or modify it under | |
-- the terms of the GNU General Public Licence. See the COPYING file. | |
-- | |
-- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - | |
-- | |
-- WARNING: Any change in this file must be evaluated for compatibility. | |
-- Changes cleanly handled by postgis_upgrade.sql are fine, | |
-- other changes will require a bump in Major version. | |
-- Currently only function replaceble by CREATE OR REPLACE | |
-- are cleanly handled. | |
-- | |
-- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - | |
#include "sqldefines.h" | |
-- INSTALL VERSION: POSTGIS_LIB_VERSION | |
BEGIN; | |
SET LOCAL client_min_messages TO warning; | |
DO $$ | |
DECLARE | |
pgver text; | |
BEGIN | |
SELECT substring(version(), 'PostgreSQL ([0-9\.]+)') INTO pgver; | |
IF POSTGIS_PGSQL_VERSION::text != ( 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 | |
FROM substring(version(), 'PostgreSQL ([0-9\.]+)') AS s ) | |
THEN | |
RAISE EXCEPTION 'PostGIS built for PostgreSQL % cannot be loaded in PostgreSQL %', | |
POSTGIS_PGSQL_HR_VERSION, pgver; | |
END IF; | |
END; | |
$$; | |
-- Check that no other postgis is installed | |
DO $$ | |
DECLARE | |
rec RECORD; | |
BEGIN | |
FOR rec IN | |
SELECT n.nspname, p.proname FROM pg_proc p, pg_namespace n | |
WHERE p.proname = 'postgis_version' | |
AND p.pronamespace = n.oid | |
LOOP | |
RAISE EXCEPTION 'PostGIS is already installed in schema ''%''', rec.nspname; | |
END LOOP; | |
END | |
$$ LANGUAGE 'plpgsql'; | |
-- Let the user know about a deprecated signature and its new name, if any | |
CREATE OR REPLACE FUNCTION _postgis_deprecate(oldname text, newname text, version text) | |
RETURNS void AS | |
$$ | |
DECLARE | |
curver_text text; | |
BEGIN | |
-- | |
-- Raises a NOTICE if it was deprecated in this version, | |
-- a WARNING if in a previous version (only up to minor version checked) | |
-- | |
curver_text := POSTGIS_LIB_VERSION; | |
IF split_part(curver_text,'.',1)::int > split_part(version,'.',1)::int OR | |
( split_part(curver_text,'.',1) = split_part(version,'.',1) AND | |
split_part(curver_text,'.',2) != split_part(version,'.',2) ) | |
THEN | |
RAISE WARNING '% signature was deprecated in %. Please use %', oldname, version, newname; | |
ELSE | |
RAISE DEBUG '% signature was deprecated in %. Please use %', oldname, version, newname; | |
END IF; | |
END; | |
$$ LANGUAGE 'plpgsql' IMMUTABLE STRICT _COST_MEDIUM; | |
------------------------------------------------------------------- | |
-- SPHEROID TYPE | |
------------------------------------------------------------------- | |
CREATE OR REPLACE FUNCTION spheroid_in(cstring) | |
RETURNS spheroid | |
AS 'MODULE_PATHNAME','ellipsoid_in' | |
LANGUAGE 'c' IMMUTABLE STRICT PARALLEL SAFE; | |
CREATE OR REPLACE FUNCTION spheroid_out(spheroid) | |
RETURNS cstring | |
AS 'MODULE_PATHNAME','ellipsoid_out' | |
LANGUAGE 'c' IMMUTABLE STRICT PARALLEL SAFE; | |
-- Availability: 0.5.0 | |
CREATE TYPE spheroid ( | |
alignment = double, | |
internallength = 65, | |
input = spheroid_in, | |
output = spheroid_out | |
); | |
------------------------------------------------------------------- | |
-- GEOMETRY TYPE (lwgeom) | |
------------------------------------------------------------------- | |
CREATE OR REPLACE FUNCTION geometry_in(cstring) | |
RETURNS geometry | |
AS 'MODULE_PATHNAME','LWGEOM_in' | |
LANGUAGE 'c' IMMUTABLE STRICT PARALLEL SAFE; | |
CREATE OR REPLACE FUNCTION geometry_out(geometry) | |
RETURNS cstring | |
AS 'MODULE_PATHNAME','LWGEOM_out' | |
LANGUAGE 'c' IMMUTABLE STRICT PARALLEL SAFE; | |
-- Availability: 2.0.0 | |
CREATE OR REPLACE FUNCTION geometry_typmod_in(cstring[]) | |
RETURNS integer | |
AS 'MODULE_PATHNAME','geometry_typmod_in' | |
LANGUAGE 'c' IMMUTABLE STRICT PARALLEL SAFE; | |
-- Availability: 2.0.0 | |
CREATE OR REPLACE FUNCTION geometry_typmod_out(integer) | |
RETURNS cstring | |
AS 'MODULE_PATHNAME','postgis_typmod_out' | |
LANGUAGE 'c' IMMUTABLE STRICT PARALLEL SAFE; | |
CREATE OR REPLACE FUNCTION geometry_analyze(internal) | |
RETURNS bool | |
AS 'MODULE_PATHNAME', 'gserialized_analyze_nd' | |
LANGUAGE 'c' VOLATILE STRICT; | |
CREATE OR REPLACE FUNCTION geometry_recv(internal) | |
RETURNS geometry | |
AS 'MODULE_PATHNAME','LWGEOM_recv' | |
LANGUAGE 'c' IMMUTABLE STRICT PARALLEL SAFE; | |
CREATE OR REPLACE FUNCTION geometry_send(geometry) | |
RETURNS bytea | |
AS 'MODULE_PATHNAME','LWGEOM_send' | |
LANGUAGE 'c' IMMUTABLE STRICT PARALLEL SAFE; | |
-- Availability: 0.1.0 | |
CREATE TYPE geometry ( | |
internallength = variable, | |
input = geometry_in, | |
output = geometry_out, | |
send = geometry_send, | |
receive = geometry_recv, | |
typmod_in = geometry_typmod_in, | |
typmod_out = geometry_typmod_out, | |
delimiter = ':', | |
alignment = double, | |
analyze = geometry_analyze, | |
storage = main | |
); | |
-- Availability: 2.0.0 | |
-- Special cast for enforcing the typmod restrictions | |
CREATE OR REPLACE FUNCTION geometry(geometry, integer, boolean) | |
RETURNS geometry | |
AS 'MODULE_PATHNAME','geometry_enforce_typmod' | |
LANGUAGE 'c' IMMUTABLE STRICT PARALLEL SAFE; | |
-- Availability: 2.0.0 | |
CREATE CAST (geometry AS geometry) WITH FUNCTION geometry(geometry, integer, boolean) AS IMPLICIT; | |
-- Availability: 2.1.0 | |
CREATE OR REPLACE FUNCTION geometry(point) | |
RETURNS geometry | |
AS 'MODULE_PATHNAME','point_to_geometry' | |
LANGUAGE 'c' IMMUTABLE STRICT PARALLEL SAFE; | |
-- Availability: 2.1.0 | |
CREATE OR REPLACE FUNCTION point(geometry) | |
RETURNS point | |
AS 'MODULE_PATHNAME','geometry_to_point' | |
LANGUAGE 'c' IMMUTABLE STRICT PARALLEL SAFE; | |
-- Availability: 2.1.0 | |
CREATE OR REPLACE FUNCTION geometry(path) | |
RETURNS geometry | |
AS 'MODULE_PATHNAME','path_to_geometry' | |
LANGUAGE 'c' IMMUTABLE STRICT PARALLEL SAFE; | |
-- Availability: 2.1.0 | |
CREATE OR REPLACE FUNCTION path(geometry) | |
RETURNS path | |
AS 'MODULE_PATHNAME','geometry_to_path' | |
LANGUAGE 'c' IMMUTABLE STRICT PARALLEL SAFE; | |
-- Availability: 2.1.0 | |
CREATE OR REPLACE FUNCTION geometry(polygon) | |
RETURNS geometry | |
AS 'MODULE_PATHNAME','polygon_to_geometry' | |
LANGUAGE 'c' IMMUTABLE STRICT PARALLEL SAFE; | |
-- Availability: 2.1.0 | |
CREATE OR REPLACE FUNCTION polygon(geometry) | |
RETURNS polygon | |
AS 'MODULE_PATHNAME','geometry_to_polygon' | |
LANGUAGE 'c' IMMUTABLE STRICT PARALLEL SAFE; | |
CREATE CAST (geometry AS point) WITH FUNCTION point(geometry); | |
CREATE CAST (point AS geometry) WITH FUNCTION geometry(point); | |
CREATE CAST (geometry AS path) WITH FUNCTION path(geometry); | |
CREATE CAST (path AS geometry) WITH FUNCTION geometry(path); | |
CREATE CAST (geometry AS polygon) WITH FUNCTION polygon(geometry); | |
CREATE CAST (polygon AS geometry) WITH FUNCTION geometry(polygon); | |
------------------------------------------------------------------- | |
-- BOX3D TYPE | |
-- Point coordinate data access | |
------------------------------------------- | |
-- PostGIS equivalent function: X(geometry) | |
CREATE OR REPLACE FUNCTION ST_X(geometry) | |
RETURNS float8 | |
AS 'MODULE_PATHNAME','LWGEOM_x_point' | |
LANGUAGE 'c' IMMUTABLE STRICT | |
PARALLEL SAFE _COST_DEFAULT; | |
-- PostGIS equivalent function: Y(geometry) | |
CREATE OR REPLACE FUNCTION ST_Y(geometry) | |
RETURNS float8 | |
AS 'MODULE_PATHNAME','LWGEOM_y_point' | |
LANGUAGE 'c' IMMUTABLE STRICT | |
PARALLEL SAFE _COST_DEFAULT; | |
-- Availability: 1.2.2 | |
CREATE OR REPLACE FUNCTION ST_Z(geometry) | |
RETURNS float8 | |
AS 'MODULE_PATHNAME','LWGEOM_z_point' | |
LANGUAGE 'c' IMMUTABLE STRICT | |
PARALLEL SAFE _COST_DEFAULT; | |
-- Availability: 1.2.2 | |
CREATE OR REPLACE FUNCTION ST_M(geometry) | |
RETURNS float8 | |
AS 'MODULE_PATHNAME','LWGEOM_m_point' | |
LANGUAGE 'c' IMMUTABLE STRICT | |
PARALLEL SAFE _COST_DEFAULT; | |
------------------------------------------------------------------- | |
CREATE OR REPLACE FUNCTION box3d_in(cstring) | |
RETURNS box3d | |
AS 'MODULE_PATHNAME', 'BOX3D_in' | |
LANGUAGE 'c' IMMUTABLE STRICT PARALLEL SAFE; | |
CREATE OR REPLACE FUNCTION box3d_out(box3d) | |
RETURNS cstring | |
AS 'MODULE_PATHNAME', 'BOX3D_out' | |
LANGUAGE 'c' IMMUTABLE STRICT PARALLEL SAFE; | |
-- Availability: 0.1.0 | |
CREATE TYPE box3d ( | |
alignment = double, | |
internallength = 52, | |
input = box3d_in, | |
output = box3d_out | |
); | |
----------------------------------------------------------------------- | |
-- BOX2D | |
----------------------------------------------------------------------- | |
CREATE OR REPLACE FUNCTION box2d_in(cstring) | |
RETURNS box2d | |
AS 'MODULE_PATHNAME','BOX2D_in' | |
LANGUAGE 'c' IMMUTABLE STRICT PARALLEL SAFE; | |
CREATE OR REPLACE FUNCTION box2d_out(box2d) | |
RETURNS cstring | |
AS 'MODULE_PATHNAME','BOX2D_out' | |
LANGUAGE 'c' IMMUTABLE STRICT PARALLEL SAFE; | |
-- Availability: 0.8.2 | |
CREATE TYPE box2d ( | |
internallength = 65, | |
input = box2d_in, | |
output = box2d_out, | |
storage = plain | |
); | |
------------------------------------------------------------------- | |
-- BOX2DF TYPE (INTERNAL ONLY) | |
------------------------------------------------------------------- | |
-- | |
-- Box2Df type is used by the GiST index bindings. | |
-- In/out functions are stubs, as all access should be internal. | |
--- | |
-- Availability: 2.0.0 | |
CREATE OR REPLACE FUNCTION box2df_in(cstring) | |
RETURNS box2df | |
AS 'MODULE_PATHNAME','box2df_in' | |
LANGUAGE 'c' IMMUTABLE STRICT PARALLEL SAFE; | |
-- Availability: 2.0.0 | |
CREATE OR REPLACE FUNCTION box2df_out(box2df) | |
RETURNS cstring | |
AS 'MODULE_PATHNAME','box2df_out' | |
LANGUAGE 'c' IMMUTABLE STRICT PARALLEL SAFE; | |
-- Availability: 2.0.0 | |
CREATE TYPE box2df ( | |
internallength = 16, | |
input = box2df_in, | |
output = box2df_out, | |
storage = plain, | |
alignment = double | |
); | |
------------------------------------------------------------------- | |
-- GIDX TYPE (INTERNAL ONLY) | |
------------------------------------------------------------------- | |
-- | |
-- GIDX type is used by the N-D and GEOGRAPHY GiST index bindings. | |
-- In/out functions are stubs, as all access should be internal. | |
--- | |
-- Availability: 1.5.0 | |
CREATE OR REPLACE FUNCTION gidx_in(cstring) | |
RETURNS gidx | |
AS 'MODULE_PATHNAME','gidx_in' | |
LANGUAGE 'c' IMMUTABLE STRICT PARALLEL SAFE; | |
-- Availability: 1.5.0 | |
CREATE OR REPLACE FUNCTION gidx_out(gidx) | |
RETURNS cstring | |
AS 'MODULE_PATHNAME','gidx_out' | |
LANGUAGE 'c' IMMUTABLE STRICT PARALLEL SAFE; | |
-- Availability: 1.5.0 | |
CREATE TYPE gidx ( | |
internallength = variable, | |
input = gidx_in, | |
output = gidx_out, | |
storage = plain, | |
alignment = double | |
); | |
------------------------------------------------------------------- | |
-- BTREE indexes | |
------------------------------------------------------------------- | |
CREATE OR REPLACE FUNCTION geometry_lt(geom1 geometry, geom2 geometry) | |
RETURNS bool | |
AS 'MODULE_PATHNAME', 'lwgeom_lt' | |
LANGUAGE 'c' IMMUTABLE STRICT PARALLEL SAFE | |
_COST_DEFAULT; | |
CREATE OR REPLACE FUNCTION geometry_le(geom1 geometry, geom2 geometry) | |
RETURNS bool | |
AS 'MODULE_PATHNAME', 'lwgeom_le' | |
LANGUAGE 'c' IMMUTABLE STRICT PARALLEL SAFE | |
_COST_DEFAULT; | |
CREATE OR REPLACE FUNCTION geometry_gt(geom1 geometry, geom2 geometry) | |
RETURNS bool | |
AS 'MODULE_PATHNAME', 'lwgeom_gt' | |
LANGUAGE 'c' IMMUTABLE STRICT PARALLEL SAFE | |
_COST_DEFAULT; | |
CREATE OR REPLACE FUNCTION geometry_ge(geom1 geometry, geom2 geometry) | |
RETURNS bool | |
AS 'MODULE_PATHNAME', 'lwgeom_ge' | |
LANGUAGE 'c' IMMUTABLE STRICT PARALLEL SAFE | |
_COST_DEFAULT; | |
CREATE OR REPLACE FUNCTION geometry_eq(geom1 geometry, geom2 geometry) | |
RETURNS bool | |
AS 'MODULE_PATHNAME', 'lwgeom_eq' | |
LANGUAGE 'c' IMMUTABLE STRICT PARALLEL SAFE | |
_COST_DEFAULT; | |
CREATE OR REPLACE FUNCTION geometry_cmp(geom1 geometry, geom2 geometry) | |
RETURNS integer | |
AS 'MODULE_PATHNAME', 'lwgeom_cmp' | |
LANGUAGE 'c' IMMUTABLE STRICT PARALLEL SAFE | |
_COST_DEFAULT; | |
-- Availability: 3.0.0 | |
CREATE OR REPLACE FUNCTION geometry_sortsupport(internal) | |
RETURNS void | |
AS 'MODULE_PATHNAME', 'lwgeom_sortsupport' | |
LANGUAGE 'c' IMMUTABLE STRICT PARALLEL SAFE; | |
-- | |
-- Sorting operators for Btree | |
-- | |
-- Availability: 0.9.0 | |
CREATE OPERATOR < ( | |
LEFTARG = geometry, RIGHTARG = geometry, PROCEDURE = geometry_lt, | |
COMMUTATOR = '>', NEGATOR = '>=', | |
RESTRICT = contsel, JOIN = contjoinsel | |
); | |
-- Availability: 0.9.0 | |
CREATE OPERATOR <= ( | |
LEFTARG = geometry, RIGHTARG = geometry, PROCEDURE = geometry_le, | |
COMMUTATOR = '>=', NEGATOR = '>', | |
RESTRICT = contsel, JOIN = contjoinsel | |
); | |
-- Availability: 0.9.0 | |
CREATE OPERATOR = ( | |
LEFTARG = geometry, RIGHTARG = geometry, PROCEDURE = geometry_eq, | |
COMMUTATOR = '=', -- we might implement a faster negator here | |
RESTRICT = contsel, JOIN = contjoinsel, HASHES, MERGES | |
); | |
-- Availability: 0.9.0 | |
CREATE OPERATOR >= ( | |
LEFTARG = geometry, RIGHTARG = geometry, PROCEDURE = geometry_ge, | |
COMMUTATOR = '<=', NEGATOR = '<', | |
RESTRICT = contsel, JOIN = contjoinsel | |
); | |
-- Availability: 0.9.0 | |
CREATE OPERATOR > ( | |
LEFTARG = geometry, RIGHTARG = geometry, PROCEDURE = geometry_gt, | |
COMMUTATOR = '<', NEGATOR = '<=', | |
RESTRICT = contsel, JOIN = contjoinsel | |
); | |
-- Availability: 0.9.0 | |
CREATE OPERATOR CLASS btree_geometry_ops | |
DEFAULT FOR TYPE geometry USING btree AS | |
OPERATOR 1 < , | |
OPERATOR 2 <= , | |
OPERATOR 3 = , | |
OPERATOR 4 >= , | |
OPERATOR 5 > , | |
FUNCTION 1 geometry_cmp (geom1 geometry, geom2 geometry), | |
-- Availability: 3.0.0 | |
FUNCTION 2 geometry_sortsupport(internal); | |
-- | |
-- Sorting operators for Btree | |
-- | |
-- Availability: 2.5.0 | |
CREATE OR REPLACE FUNCTION geometry_hash(geometry) | |
RETURNS integer | |
AS 'MODULE_PATHNAME','lwgeom_hash' | |
LANGUAGE 'c' STRICT IMMUTABLE PARALLEL SAFE; | |
-- Availability: 2.5.0 | |
CREATE OPERATOR CLASS hash_geometry_ops | |
DEFAULT FOR TYPE geometry USING hash AS | |
OPERATOR 1 = , | |
FUNCTION 1 geometry_hash(geometry); | |
----------------------------------------------------------------------------- | |
-- GiST 2D GEOMETRY-over-GSERIALIZED INDEX | |
----------------------------------------------------------------------------- | |
-- ---------- ---------- ---------- ---------- ---------- ---------- ---------- | |
-- GiST Support Functions | |
-- ---------- ---------- ---------- ---------- ---------- ---------- ---------- | |
-- Availability: 2.0.0 | |
CREATE OR REPLACE FUNCTION geometry_gist_distance_2d(internal,geometry,int4) | |
RETURNS float8 | |
AS 'MODULE_PATHNAME' ,'gserialized_gist_distance_2d' | |
LANGUAGE 'c' PARALLEL SAFE; | |
-- Availability: 2.0.0 | |
CREATE OR REPLACE FUNCTION geometry_gist_consistent_2d(internal,geometry,int4) | |
RETURNS bool | |
AS 'MODULE_PATHNAME' ,'gserialized_gist_consistent_2d' | |
LANGUAGE 'c' PARALLEL SAFE; | |
-- Availability: 2.0.0 | |
CREATE OR REPLACE FUNCTION geometry_gist_compress_2d(internal) | |
RETURNS internal | |
AS 'MODULE_PATHNAME','gserialized_gist_compress_2d' | |
LANGUAGE 'c' PARALLEL SAFE; | |
-- Availability: 2.0.0 | |
CREATE OR REPLACE FUNCTION geometry_gist_penalty_2d(internal,internal,internal) | |
RETURNS internal | |
AS 'MODULE_PATHNAME' ,'gserialized_gist_penalty_2d' | |
LANGUAGE 'c' PARALLEL SAFE; | |
-- Availability: 2.0.0 | |
CREATE OR REPLACE FUNCTION geometry_gist_picksplit_2d(internal, internal) | |
RETURNS internal | |
AS 'MODULE_PATHNAME' ,'gserialized_gist_picksplit_2d' | |
LANGUAGE 'c' PARALLEL SAFE; | |
-- Availability: 2.0.0 | |
CREATE OR REPLACE FUNCTION geometry_gist_union_2d(bytea, internal) | |
RETURNS internal | |
AS 'MODULE_PATHNAME' ,'gserialized_gist_union_2d' | |
LANGUAGE 'c' PARALLEL SAFE; | |
-- Availability: 2.0.0 | |
CREATE OR REPLACE FUNCTION geometry_gist_same_2d(geom1 geometry, geom2 geometry, internal) | |
RETURNS internal | |
AS 'MODULE_PATHNAME' ,'gserialized_gist_same_2d' | |
LANGUAGE 'c' PARALLEL SAFE; | |
-- Availability: 2.0.0 | |
CREATE OR REPLACE FUNCTION geometry_gist_decompress_2d(internal) | |
RETURNS internal | |
AS 'MODULE_PATHNAME' ,'gserialized_gist_decompress_2d' | |
LANGUAGE 'c' PARALLEL SAFE; | |
----------------------------------------------------------------------------- | |
-- Availability: 2.1.0 | |
-- Given a table, column and query geometry, returns the estimate of what proportion | |
-- of the table would be returned by a query using the &&/&&& operators. The mode | |
-- changes whether the estimate is in x/y only or in all available dimensions. | |
CREATE OR REPLACE FUNCTION _postgis_selectivity(tbl regclass, att_name text, geom geometry, mode text default '2') | |
RETURNS float8 | |
AS 'MODULE_PATHNAME', '_postgis_gserialized_sel' | |
LANGUAGE 'c' STRICT PARALLEL SAFE; | |
-- Availability: 2.1.0 | |
-- Given a two tables and columns, returns estimate of the proportion of rows | |
-- a &&/&&& join will return relative to the number of rows an unconstrained | |
-- table join would return. Mode flips result between evaluation in x/y only | |
-- and evaluation in all available dimensions. | |
CREATE OR REPLACE FUNCTION _postgis_join_selectivity(regclass, text, regclass, text, text default '2') | |
RETURNS float8 | |
AS 'MODULE_PATHNAME', '_postgis_gserialized_joinsel' | |
LANGUAGE 'c' STRICT PARALLEL SAFE; | |
-- Availability: 2.1.0 | |
-- Given a table and a column, returns the statistics information stored by | |
-- PostgreSQL, in a JSON text form. Mode determines whether the 2D statistics | |
-- or the ND statistics are returned. | |
CREATE OR REPLACE FUNCTION _postgis_stats(tbl regclass, att_name text, text default '2') | |
RETURNS text | |
AS 'MODULE_PATHNAME', '_postgis_gserialized_stats' | |
LANGUAGE 'c' STRICT PARALLEL SAFE; | |
-- Availability: 2.5.0 | |
-- Given a table and a column, returns the extent of all boxes in the | |
-- first page of the index (the head of the index) | |
CREATE OR REPLACE FUNCTION _postgis_index_extent(tbl regclass, col text) | |
RETURNS box2d | |
AS 'MODULE_PATHNAME','_postgis_gserialized_index_extent' | |
LANGUAGE 'c' STABLE STRICT; | |
-- Availability: 2.1.0 | |
CREATE OR REPLACE FUNCTION gserialized_gist_sel_2d (internal, oid, internal, int4) | |
RETURNS float8 | |
AS 'MODULE_PATHNAME', 'gserialized_gist_sel_2d' | |
LANGUAGE 'c' PARALLEL SAFE; | |
-- Availability: 2.1.0 | |
CREATE OR REPLACE FUNCTION gserialized_gist_sel_nd (internal, oid, internal, int4) | |
RETURNS float8 | |
AS 'MODULE_PATHNAME', 'gserialized_gist_sel_nd' | |
LANGUAGE 'c' PARALLEL SAFE; | |
-- Availability: 2.1.0 | |
CREATE OR REPLACE FUNCTION gserialized_gist_joinsel_2d (internal, oid, internal, smallint) | |
RETURNS float8 | |
AS 'MODULE_PATHNAME', 'gserialized_gist_joinsel_2d' | |
LANGUAGE 'c' PARALLEL SAFE; | |
-- Availability: 2.1.0 | |
CREATE OR REPLACE FUNCTION gserialized_gist_joinsel_nd (internal, oid, internal, smallint) | |
RETURNS float8 | |
AS 'MODULE_PATHNAME', 'gserialized_gist_joinsel_nd' | |
LANGUAGE 'c' PARALLEL SAFE; | |
----------------------------------------------------------------------------- | |
-- GEOMETRY Operators | |
----------------------------------------------------------------------------- | |
-- ---------- ---------- ---------- ---------- ---------- ---------- ---------- | |
-- 2D GEOMETRY Operators | |
-- ---------- ---------- ---------- ---------- ---------- ---------- ---------- | |
-- Availability: 2.0.0 | |
CREATE OR REPLACE FUNCTION geometry_overlaps(geom1 geometry, geom2 geometry) | |
RETURNS boolean | |
AS 'MODULE_PATHNAME' ,'gserialized_overlaps_2d' | |
LANGUAGE 'c' IMMUTABLE STRICT PARALLEL SAFE | |
_COST_DEFAULT; | |
-- Availability: 0.1.0 | |
-- Changed: 2.0.0 use gserialized selectivity estimators | |
CREATE OPERATOR && ( | |
LEFTARG = geometry, RIGHTARG = geometry, PROCEDURE = geometry_overlaps, | |
COMMUTATOR = '&&', | |
RESTRICT = gserialized_gist_sel_2d, | |
JOIN = gserialized_gist_joinsel_2d | |
); | |
-- Availability: 2.0.0 | |
CREATE OR REPLACE FUNCTION geometry_same(geom1 geometry, geom2 geometry) | |
RETURNS boolean | |
AS 'MODULE_PATHNAME' ,'gserialized_same_2d' | |
LANGUAGE 'c' IMMUTABLE STRICT PARALLEL SAFE | |
_COST_DEFAULT; | |
-- Availability: 0.1.0 | |
CREATE OPERATOR ~= ( | |
LEFTARG = geometry, RIGHTARG = geometry, PROCEDURE = geometry_same, | |
RESTRICT = contsel, JOIN = contjoinsel | |
); | |
-- As of 2.2.0 this no longer returns the centroid/centroid distance, it | |
-- returns the actual distance, to support the 'recheck' functionality | |
-- enabled in the KNN operator | |
-- Availability: 2.0.0 | |
CREATE OR REPLACE FUNCTION geometry_distance_centroid(geom1 geometry, geom2 geometry) | |
RETURNS float8 | |
AS 'MODULE_PATHNAME', 'ST_Distance' | |
LANGUAGE 'c' IMMUTABLE STRICT PARALLEL SAFE | |
_COST_MEDIUM; | |
-- Availability: 2.0.0 | |
CREATE OR REPLACE FUNCTION geometry_distance_box(geom1 geometry, geom2 geometry) | |
RETURNS float8 | |
AS 'MODULE_PATHNAME', 'gserialized_distance_box_2d' | |
LANGUAGE 'c' IMMUTABLE STRICT PARALLEL SAFE | |
_COST_DEFAULT; | |
-- Availability: 2.0.0 | |
CREATE OPERATOR <-> ( | |
LEFTARG = geometry, RIGHTARG = geometry, PROCEDURE = geometry_distance_centroid, | |
COMMUTATOR = '<->' | |
); | |
-- Availability: 2.0.0 | |
CREATE OPERATOR <#> ( | |
LEFTARG = geometry, RIGHTARG = geometry, PROCEDURE = geometry_distance_box, | |
COMMUTATOR = '<#>' | |
); | |
-- Availability: 2.0.0 | |
CREATE OR REPLACE FUNCTION geometry_contains(geom1 geometry, geom2 geometry) | |
RETURNS bool | |
AS 'MODULE_PATHNAME', 'gserialized_contains_2d' | |
LANGUAGE 'c' IMMUTABLE STRICT PARALLEL SAFE | |
_COST_DEFAULT; | |
-- Availability: 2.0.0 | |
CREATE OR REPLACE FUNCTION geometry_within(geom1 geometry, geom2 geometry) | |
RETURNS bool | |
AS 'MODULE_PATHNAME', 'gserialized_within_2d' | |
LANGUAGE 'c' IMMUTABLE STRICT PARALLEL SAFE | |
_COST_DEFAULT; | |
-- Availability: 0.1.0 | |
CREATE OPERATOR @ ( | |
LEFTARG = geometry, RIGHTARG = geometry, PROCEDURE = geometry_within, | |
COMMUTATOR = '~', | |
RESTRICT = contsel, JOIN = contjoinsel | |
); | |
-- Availability: 0.1.0 | |
CREATE OPERATOR ~ ( | |
LEFTARG = geometry, RIGHTARG = geometry, PROCEDURE = geometry_contains, | |
COMMUTATOR = '@', | |
RESTRICT = contsel, JOIN = contjoinsel | |
); | |
-- Availability: 2.0.0 | |
CREATE OR REPLACE FUNCTION geometry_left(geom1 geometry, geom2 geometry) | |
RETURNS bool | |
AS 'MODULE_PATHNAME', 'gserialized_left_2d' | |
LANGUAGE 'c' IMMUTABLE STRICT PARALLEL SAFE | |
_COST_DEFAULT; | |
-- Availability: 0.1.0 | |
CREATE OPERATOR << ( | |
LEFTARG = geometry, RIGHTARG = geometry, PROCEDURE = geometry_left, | |
COMMUTATOR = '>>', | |
RESTRICT = positionsel, JOIN = positionjoinsel | |
); | |
-- Availability: 2.0.0 | |
CREATE OR REPLACE FUNCTION geometry_overleft(geom1 geometry, geom2 geometry) | |
RETURNS bool | |
AS 'MODULE_PATHNAME', 'gserialized_overleft_2d' | |
LANGUAGE 'c' IMMUTABLE STRICT PARALLEL SAFE | |
_COST_DEFAULT; | |
-- Availability: 0.1.0 | |
CREATE OPERATOR &< ( | |
LEFTARG = geometry, RIGHTARG = geometry, PROCEDURE = geometry_overleft, | |
RESTRICT = positionsel, JOIN = positionjoinsel | |
); | |
-- Availability: 2.0.0 | |
CREATE OR REPLACE FUNCTION geometry_below(geom1 geometry, geom2 geometry) | |
RETURNS bool | |
AS 'MODULE_PATHNAME', 'gserialized_below_2d' | |
LANGUAGE 'c' IMMUTABLE STRICT PARALLEL SAFE | |
_COST_DEFAULT; | |
-- Availability: 0.1.0 | |
CREATE OPERATOR <<| ( | |
LEFTARG = geometry, RIGHTARG = geometry, PROCEDURE = geometry_below, | |
COMMUTATOR = '|>>', | |
RESTRICT = positionsel, JOIN = positionjoinsel | |
); | |
-- Availability: 2.0.0 | |
CREATE OR REPLACE FUNCTION geometry_overbelow(geom1 geometry, geom2 geometry) | |
RETURNS bool | |
AS 'MODULE_PATHNAME', 'gserialized_overbelow_2d' | |
LANGUAGE 'c' IMMUTABLE STRICT PARALLEL SAFE | |
_COST_DEFAULT; | |
-- Availability: 0.1.0 | |
CREATE OPERATOR &<| ( | |
LEFTARG = geometry, RIGHTARG = geometry, PROCEDURE = geometry_overbelow, | |
RESTRICT = positionsel, JOIN = positionjoinsel | |
); | |
-- Availability: 2.0.0 | |
CREATE OR REPLACE FUNCTION geometry_overright(geom1 geometry, geom2 geometry) | |
RETURNS bool | |
AS 'MODULE_PATHNAME', 'gserialized_overright_2d' | |
LANGUAGE 'c' IMMUTABLE STRICT PARALLEL SAFE | |
_COST_DEFAULT; | |
-- Availability: 0.1.0 | |
CREATE OPERATOR &> ( | |
LEFTARG = geometry, RIGHTARG = geometry, PROCEDURE = geometry_overright, | |
RESTRICT = positionsel, JOIN = positionjoinsel | |
); | |
-- Availability: 2.0.0 | |
CREATE OR REPLACE FUNCTION geometry_right(geom1 geometry, geom2 geometry) | |
RETURNS bool | |
AS 'MODULE_PATHNAME', 'gserialized_right_2d' | |
LANGUAGE 'c' IMMUTABLE STRICT PARALLEL SAFE | |
_COST_DEFAULT; | |
-- Availability: 0.1.0 | |
CREATE OPERATOR >> ( | |
LEFTARG = geometry, RIGHTARG = geometry, PROCEDURE = geometry_right, | |
COMMUTATOR = '<<', | |
RESTRICT = positionsel, JOIN = positionjoinsel | |
); | |
-- Availability: 2.0.0 | |
CREATE OR REPLACE FUNCTION geometry_overabove(geom1 geometry, geom2 geometry) | |
RETURNS bool | |
AS 'MODULE_PATHNAME', 'gserialized_overabove_2d' | |
LANGUAGE 'c' IMMUTABLE STRICT PARALLEL SAFE | |
_COST_DEFAULT; | |
-- Availability: 0.1.0 | |
CREATE OPERATOR |&> ( | |
LEFTARG = geometry, RIGHTARG = geometry, PROCEDURE = geometry_overabove, | |
RESTRICT = positionsel, JOIN = positionjoinsel | |
); | |
-- Availability: 2.0.0 | |
CREATE OR REPLACE FUNCTION geometry_above(geom1 geometry, geom2 geometry) | |
RETURNS bool | |
AS 'MODULE_PATHNAME', 'gserialized_above_2d' | |
LANGUAGE 'c' IMMUTABLE STRICT PARALLEL SAFE | |
_COST_DEFAULT; | |
-- Availability: 0.1.0 | |
CREATE OPERATOR |>> ( | |
LEFTARG = geometry, RIGHTARG = geometry, PROCEDURE = geometry_above, | |
COMMUTATOR = '<<|', | |
RESTRICT = positionsel, JOIN = positionjoinsel | |
); | |
-- Availability: 2.0.0 | |
CREATE OPERATOR CLASS gist_geometry_ops_2d | |
DEFAULT FOR TYPE geometry USING GIST AS | |
STORAGE box2df, | |
OPERATOR 1 << , | |
OPERATOR 2 &< , | |
OPERATOR 3 && , | |
OPERATOR 4 &> , | |
OPERATOR 5 >> , | |
OPERATOR 6 ~= , | |
OPERATOR 7 ~ , | |
OPERATOR 8 @ , | |
OPERATOR 9 &<| , | |
OPERATOR 10 <<| , | |
OPERATOR 11 |>> , | |
OPERATOR 12 |&> , | |
OPERATOR 13 <-> FOR ORDER BY pg_catalog.float_ops, | |
OPERATOR 14 <#> FOR ORDER BY pg_catalog.float_ops, | |
FUNCTION 8 geometry_gist_distance_2d (internal, geometry, int4), | |
FUNCTION 1 geometry_gist_consistent_2d (internal, geometry, int4), | |
FUNCTION 2 geometry_gist_union_2d (bytea, internal), | |
FUNCTION 3 geometry_gist_compress_2d (internal), | |
FUNCTION 4 geometry_gist_decompress_2d (internal), | |
FUNCTION 5 geometry_gist_penalty_2d (internal, internal, internal), | |
FUNCTION 6 geometry_gist_picksplit_2d (internal, internal), | |
FUNCTION 7 geometry_gist_same_2d (geom1 geometry, geom2 geometry, internal); | |
----------------------------------------------------------------------------- | |
-- GiST ND GEOMETRY-over-GSERIALIZED | |
----------------------------------------------------------------------------- | |
-- ---------- ---------- ---------- ---------- ---------- ---------- ---------- | |
-- GiST Support Functions | |
-- ---------- ---------- ---------- ---------- ---------- ---------- ---------- | |
-- Availability: 2.0.0 | |
CREATE OR REPLACE FUNCTION geometry_gist_consistent_nd(internal,geometry,int4) | |
RETURNS bool | |
AS 'MODULE_PATHNAME' ,'gserialized_gist_consistent' | |
LANGUAGE 'c' PARALLEL SAFE | |
_COST_DEFAULT; | |
-- Availability: 2.0.0 | |
CREATE OR REPLACE FUNCTION geometry_gist_compress_nd(internal) | |
RETURNS internal | |
AS 'MODULE_PATHNAME','gserialized_gist_compress' | |
LANGUAGE 'c' PARALLEL SAFE | |
_COST_DEFAULT; | |
-- Availability: 2.0.0 | |
CREATE OR REPLACE FUNCTION geometry_gist_penalty_nd(internal,internal,internal) | |
RETURNS internal | |
AS 'MODULE_PATHNAME' ,'gserialized_gist_penalty' | |
LANGUAGE 'c' PARALLEL SAFE | |
_COST_DEFAULT; | |
-- Availability: 2.0.0 | |
CREATE OR REPLACE FUNCTION geometry_gist_picksplit_nd(internal, internal) | |
RETURNS internal | |
AS 'MODULE_PATHNAME' ,'gserialized_gist_picksplit' | |
LANGUAGE 'c' PARALLEL SAFE | |
_COST_DEFAULT; | |
-- Availability: 2.0.0 | |
CREATE OR REPLACE FUNCTION geometry_gist_union_nd(bytea, internal) | |
RETURNS internal | |
AS 'MODULE_PATHNAME' ,'gserialized_gist_union' | |
LANGUAGE 'c' PARALLEL SAFE | |
_COST_DEFAULT; | |
-- Availability: 2.0.0 | |
CREATE OR REPLACE FUNCTION geometry_gist_same_nd(geometry, geometry, internal) | |
RETURNS internal | |
AS 'MODULE_PATHNAME' ,'gserialized_gist_same' | |
LANGUAGE 'c' PARALLEL SAFE | |
_COST_DEFAULT; | |
-- Availability: 2.0.0 | |
CREATE OR REPLACE FUNCTION geometry_gist_decompress_nd(internal) | |
RETURNS internal | |
AS 'MODULE_PATHNAME' ,'gserialized_gist_decompress' | |
LANGUAGE 'c' PARALLEL SAFE | |
_COST_DEFAULT; | |
-- ---------- ---------- ---------- ---------- ---------- ---------- ---------- | |
-- N-D GEOMETRY Operators | |
-- ---------- ---------- ---------- ---------- ---------- ---------- ---------- | |
-- Availability: 2.0.0 | |
CREATE OR REPLACE FUNCTION geometry_overlaps_nd(geometry, geometry) | |
RETURNS boolean | |
AS 'MODULE_PATHNAME' ,'gserialized_overlaps' | |
LANGUAGE 'c' IMMUTABLE STRICT PARALLEL SAFE | |
_COST_DEFAULT; | |
-- Availability: 2.0.0 | |
CREATE OPERATOR &&& ( | |
LEFTARG = geometry, RIGHTARG = geometry, PROCEDURE = geometry_overlaps_nd, | |
COMMUTATOR = '&&&', | |
RESTRICT = gserialized_gist_sel_nd, | |
JOIN = gserialized_gist_joinsel_nd | |
); | |
-- Availability: 3.0.0 | |
CREATE OR REPLACE FUNCTION geometry_contains_nd(geometry, geometry) | |
RETURNS boolean | |
AS 'MODULE_PATHNAME' ,'gserialized_contains' | |
LANGUAGE 'c' IMMUTABLE STRICT PARALLEL SAFE | |
_COST_DEFAULT; | |
-- Availability: 3.0.0 | |
CREATE OPERATOR ~~ ( | |
LEFTARG = geometry, RIGHTARG = geometry, PROCEDURE = geometry_contains_nd, | |
COMMUTATOR = '@@', | |
RESTRICT = gserialized_gist_sel_nd, | |
JOIN = gserialized_gist_joinsel_nd | |
); | |
-- Availability: 3.0.0 | |
CREATE OR REPLACE FUNCTION geometry_within_nd(geometry, geometry) | |
RETURNS boolean | |
AS 'MODULE_PATHNAME' ,'gserialized_within' | |
LANGUAGE 'c' IMMUTABLE STRICT PARALLEL SAFE | |
_COST_DEFAULT; | |
-- Availability: 3.0.0 | |
CREATE OPERATOR @@ ( | |
LEFTARG = geometry, RIGHTARG = geometry, PROCEDURE = geometry_within_nd, | |
COMMUTATOR = '~~', | |
RESTRICT = gserialized_gist_sel_nd, | |
JOIN = gserialized_gist_joinsel_nd | |
); | |
-- Availability: 3.0.0 | |
CREATE OR REPLACE FUNCTION geometry_same_nd(geometry, geometry) | |
RETURNS boolean | |
AS 'MODULE_PATHNAME' ,'gserialized_same' | |
LANGUAGE 'c' IMMUTABLE STRICT PARALLEL SAFE | |
_COST_DEFAULT; | |
-- Availability: 3.0.0 | |
CREATE OPERATOR ~~= ( | |
LEFTARG = geometry, RIGHTARG = geometry, PROCEDURE = geometry_same_nd, | |
COMMUTATOR = '~~=', | |
RESTRICT = gserialized_gist_sel_nd, | |
JOIN = gserialized_gist_joinsel_nd | |
); | |
-- Availability: 2.2.0 | |
CREATE OR REPLACE FUNCTION geometry_distance_centroid_nd(geometry,geometry) | |
RETURNS float8 | |
AS 'MODULE_PATHNAME', 'gserialized_distance_nd' | |
LANGUAGE 'c' IMMUTABLE STRICT PARALLEL SAFE; | |
-- Availability: 2.2.0 | |
CREATE OPERATOR <<->> ( | |
LEFTARG = geometry, RIGHTARG = geometry, | |
PROCEDURE = geometry_distance_centroid_nd, | |
COMMUTATOR = '<<->>' | |
); | |
-- | |
-- This is for use with |=| operator, which does not directly use | |
-- ST_DistanceCPA just in case it'll ever need to change behavior | |
-- (operators definition cannot be altered) | |
-- | |
-- Availability: 2.2.0 | |
CREATE OR REPLACE FUNCTION geometry_distance_cpa(geometry, geometry) | |
RETURNS float8 | |
AS 'MODULE_PATHNAME', 'ST_DistanceCPA' | |
LANGUAGE 'c' IMMUTABLE STRICT PARALLEL SAFE | |
_COST_HIGH; | |
-- Availability: 2.2.0 | |
CREATE OPERATOR |=| ( | |
LEFTARG = geometry, RIGHTARG = geometry, | |
PROCEDURE = geometry_distance_cpa, | |
COMMUTATOR = '|=|' | |
); | |
-- Availability: 2.2.0 | |
CREATE OR REPLACE FUNCTION geometry_gist_distance_nd(internal,geometry,int4) | |
RETURNS float8 | |
AS 'MODULE_PATHNAME', 'gserialized_gist_distance' | |
LANGUAGE 'c' PARALLEL SAFE | |
_COST_DEFAULT; | |
-- Availability: 2.0.0 | |
CREATE OPERATOR CLASS gist_geometry_ops_nd | |
FOR TYPE geometry USING GIST AS | |
STORAGE gidx, | |
OPERATOR 3 &&& , | |
-- Availability: 3.0.0 | |
OPERATOR 6 ~~= , | |
-- Availability: 3.0.0 | |
OPERATOR 7 ~~ , | |
-- Availability: 3.0.0 | |
OPERATOR 8 @@ , | |
-- Availability: 2.2.0 | |
OPERATOR 13 <<->> FOR ORDER BY pg_catalog.float_ops, | |
-- Availability: 2.2.0 | |
OPERATOR 20 |=| FOR ORDER BY pg_catalog.float_ops, | |
-- Availability: 2.2.0 | |
FUNCTION 8 geometry_gist_distance_nd (internal, geometry, int4), | |
FUNCTION 1 geometry_gist_consistent_nd (internal, geometry, int4), | |
FUNCTION 2 geometry_gist_union_nd (bytea, internal), | |
FUNCTION 3 geometry_gist_compress_nd (internal), | |
FUNCTION 4 geometry_gist_decompress_nd (internal), | |
FUNCTION 5 geometry_gist_penalty_nd (internal, internal, internal), | |
FUNCTION 6 geometry_gist_picksplit_nd (internal, internal), | |
FUNCTION 7 geometry_gist_same_nd (geometry, geometry, internal); | |
-- Availability: 2.2.0 | |
CREATE OR REPLACE FUNCTION ST_ShiftLongitude(geometry) | |
RETURNS geometry | |
AS 'MODULE_PATHNAME', 'LWGEOM_longitude_shift' | |
LANGUAGE 'c' IMMUTABLE STRICT PARALLEL SAFE | |
_COST_LOW; | |
-- Availability: 2.3.0 | |
CREATE OR REPLACE FUNCTION ST_WrapX(geom geometry, wrap float8, move float8) | |
RETURNS geometry | |
AS 'MODULE_PATHNAME', 'ST_WrapX' | |
LANGUAGE 'c' IMMUTABLE STRICT PARALLEL SAFE | |
_COST_LOW; | |
----------------------------------------------------------------------------- | |
-- BOX3D FUNCTIONS | |
----------------------------------------------------------------------------- | |
-- Availability: 1.2.2 | |
CREATE OR REPLACE FUNCTION ST_XMin(box3d) | |
RETURNS FLOAT8 | |
AS 'MODULE_PATHNAME','BOX3D_xmin' | |
LANGUAGE 'c' IMMUTABLE STRICT PARALLEL SAFE | |
_COST_DEFAULT; | |
-- Availability: 1.2.2 | |
CREATE OR REPLACE FUNCTION ST_YMin(box3d) | |
RETURNS FLOAT8 | |
AS 'MODULE_PATHNAME','BOX3D_ymin' | |
LANGUAGE 'c' IMMUTABLE STRICT PARALLEL SAFE | |
_COST_DEFAULT; | |
-- Availability: 1.2.2 | |
CREATE OR REPLACE FUNCTION ST_ZMin(box3d) | |
RETURNS FLOAT8 | |
AS 'MODULE_PATHNAME','BOX3D_zmin' | |
LANGUAGE 'c' IMMUTABLE STRICT PARALLEL SAFE | |
_COST_DEFAULT; | |
-- Availability: 1.2.2 | |
CREATE OR REPLACE FUNCTION ST_XMax(box3d) | |
RETURNS FLOAT8 | |
AS 'MODULE_PATHNAME','BOX3D_xmax' | |
LANGUAGE 'c' IMMUTABLE STRICT PARALLEL SAFE | |
_COST_DEFAULT; | |
-- Availability: 1.2.2 | |
CREATE OR REPLACE FUNCTION ST_YMax(box3d) | |
RETURNS FLOAT8 | |
AS 'MODULE_PATHNAME','BOX3D_ymax' | |
LANGUAGE 'c' IMMUTABLE STRICT PARALLEL SAFE | |
_COST_DEFAULT; | |
-- Availability: 1.2.2 | |
CREATE OR REPLACE FUNCTION ST_ZMax(box3d) | |
RETURNS FLOAT8 | |
AS 'MODULE_PATHNAME','BOX3D_zmax' | |
LANGUAGE 'c' IMMUTABLE STRICT PARALLEL SAFE | |
_COST_DEFAULT; | |
----------------------------------------------------------------------------- | |
-- BOX2D FUNCTIONS | |
----------------------------------------------------------------------------- | |
-- Availability: 1.2.2 | |
CREATE OR REPLACE FUNCTION ST_Expand(box2d,float8) | |
RETURNS box2d | |
AS 'MODULE_PATHNAME', 'BOX2D_expand' | |
LANGUAGE 'c' IMMUTABLE STRICT PARALLEL SAFE | |
_COST_DEFAULT; | |
-- Availability: 2.3.0 | |
CREATE OR REPLACE FUNCTION ST_Expand(box box2d, dx float8, dy float8) | |
RETURNS box2d | |
AS 'MODULE_PATHNAME', 'BOX2D_expand' | |
LANGUAGE 'c' IMMUTABLE STRICT PARALLEL SAFE | |
_COST_DEFAULT; | |
-- Availability: 1.5.0 | |
CREATE OR REPLACE FUNCTION postgis_getbbox(geometry) | |
RETURNS box2d | |
AS 'MODULE_PATHNAME','LWGEOM_to_BOX2DF' | |
LANGUAGE 'c' IMMUTABLE STRICT PARALLEL SAFE | |
_COST_DEFAULT; | |
-- Availability: 1.2.2 | |
CREATE OR REPLACE FUNCTION ST_MakeBox2d(geom1 geometry, geom2 geometry) | |
RETURNS box2d | |
AS 'MODULE_PATHNAME', 'BOX2D_construct' | |
LANGUAGE 'c' IMMUTABLE STRICT PARALLEL SAFE | |
_COST_DEFAULT; | |
----------------------------------------------------------------------- | |
-- ST_ESTIMATED_EXTENT( <schema name>, <table name>, <column name> ) | |
----------------------------------------------------------------------- | |
-- Availability: 2.3.0 | |
CREATE OR REPLACE FUNCTION ST_EstimatedExtent(text,text,text,boolean) RETURNS box2d AS | |
'MODULE_PATHNAME', 'gserialized_estimated_extent' | |
LANGUAGE 'c' STABLE STRICT SECURITY DEFINER; | |
-- Availability: 2.1.0 | |
CREATE OR REPLACE FUNCTION ST_EstimatedExtent(text,text,text) RETURNS box2d AS | |
'MODULE_PATHNAME', 'gserialized_estimated_extent' | |
LANGUAGE 'c' STABLE STRICT SECURITY DEFINER; | |
----------------------------------------------------------------------- | |
-- ST_ESTIMATED_EXTENT( <table name>, <column name> ) | |
----------------------------------------------------------------------- | |
-- Availability: 2.1.0 | |
CREATE OR REPLACE FUNCTION ST_EstimatedExtent(text,text) RETURNS box2d AS | |
'MODULE_PATHNAME', 'gserialized_estimated_extent' | |
LANGUAGE 'c' STABLE STRICT SECURITY DEFINER; | |
----------------------------------------------------------------------- | |
-- FIND_EXTENT( <schema name>, <table name>, <column name> ) | |
----------------------------------------------------------------------- | |
-- Availability: 2.2.0 | |
CREATE OR REPLACE FUNCTION ST_FindExtent(text,text,text) RETURNS box2d AS | |
$$ | |
DECLARE | |
schemaname alias for $1; | |
tablename alias for $2; | |
columnname alias for $3; | |
myrec RECORD; | |
BEGIN | |
FOR myrec IN EXECUTE 'SELECT @extschema@.ST_Extent("' || columnname || '") As extent FROM "' || schemaname || '"."' || tablename || '"' LOOP | |
return myrec.extent; | |
END LOOP; | |
END; | |
$$ | |
LANGUAGE 'plpgsql' STABLE STRICT PARALLEL SAFE; | |
----------------------------------------------------------------------- | |
-- FIND_EXTENT( <table name>, <column name> ) | |
----------------------------------------------------------------------- | |
-- Availability: 2.2.0 | |
CREATE OR REPLACE FUNCTION ST_FindExtent(text,text) RETURNS box2d AS | |
$$ | |
DECLARE | |
tablename alias for $1; | |
columnname alias for $2; | |
myrec RECORD; | |
BEGIN | |
FOR myrec IN EXECUTE 'SELECT @extschema@.ST_Extent("' || columnname || '") As extent FROM "' || tablename || '"' LOOP | |
return myrec.extent; | |
END LOOP; | |
END; | |
$$ | |
LANGUAGE 'plpgsql' STABLE STRICT PARALLEL SAFE; | |
------------------------------------------- | |
-- other lwgeom functions | |
------------------------------------------- | |
-- Availability: 1.5.0 | |
CREATE OR REPLACE FUNCTION postgis_addbbox(geometry) | |
RETURNS geometry | |
AS 'MODULE_PATHNAME','LWGEOM_addBBOX' | |
LANGUAGE 'c' IMMUTABLE STRICT PARALLEL SAFE | |
_COST_LOW; | |
-- Availability: 1.5.0 | |
CREATE OR REPLACE FUNCTION postgis_dropbbox(geometry) | |
RETURNS geometry | |
AS 'MODULE_PATHNAME','LWGEOM_dropBBOX' | |
LANGUAGE 'c' IMMUTABLE STRICT PARALLEL SAFE | |
_COST_LOW; | |
-- Availability: 1.5.0 | |
CREATE OR REPLACE FUNCTION postgis_hasbbox(geometry) | |
RETURNS bool | |
AS 'MODULE_PATHNAME', 'LWGEOM_hasBBOX' | |
LANGUAGE 'c' IMMUTABLE STRICT PARALLEL SAFE | |
_COST_DEFAULT; | |
-- Availability: 2.5.0 | |
CREATE 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) | |
RETURNS geometry | |
AS 'MODULE_PATHNAME', 'ST_QuantizeCoordinates' | |
LANGUAGE 'c' IMMUTABLE PARALLEL SAFE | |
_COST_MEDIUM; | |
------------------------------------------------------------------------ | |
-- DEBUG | |
------------------------------------------------------------------------ | |
-- Availability: 2.2.0 | |
CREATE OR REPLACE FUNCTION ST_MemSize(geometry) | |
RETURNS int4 | |
AS 'MODULE_PATHNAME', 'LWGEOM_mem_size' | |
LANGUAGE 'c' IMMUTABLE STRICT PARALLEL SAFE | |
_COST_DEFAULT; | |
-- Availability: 1.2.2 | |
CREATE OR REPLACE FUNCTION ST_Summary(geometry) | |
RETURNS text | |
AS 'MODULE_PATHNAME', 'LWGEOM_summary' | |
LANGUAGE 'c' IMMUTABLE STRICT PARALLEL SAFE | |
_COST_LOW; | |
-- Availability: 1.2.2 | |
CREATE OR REPLACE FUNCTION ST_NPoints(geometry) | |
RETURNS int4 | |
AS 'MODULE_PATHNAME', 'LWGEOM_npoints' | |
LANGUAGE 'c' IMMUTABLE STRICT PARALLEL SAFE | |
_COST_LOW; | |
-- Availability: 1.2.2 | |
CREATE OR REPLACE FUNCTION ST_NRings(geometry) | |
RETURNS int4 | |
AS 'MODULE_PATHNAME', 'LWGEOM_nrings' | |
LANGUAGE 'c' IMMUTABLE STRICT PARALLEL SAFE | |
_COST_LOW; | |
------------------------------------------------------------------------ | |
-- Measures | |
------------------------------------------------------------------------ | |
-- Availability: 2.0.0 | |
CREATE OR REPLACE FUNCTION ST_3DLength(geometry) | |
RETURNS FLOAT8 | |
AS 'MODULE_PATHNAME', 'LWGEOM_length_linestring' | |
LANGUAGE 'c' IMMUTABLE STRICT PARALLEL SAFE | |
_COST_LOW; | |
-- Availability: 1.2.2 | |
CREATE OR REPLACE FUNCTION ST_Length2d(geometry) | |
RETURNS FLOAT8 | |
AS 'MODULE_PATHNAME', 'LWGEOM_length2d_linestring' | |
LANGUAGE 'c' IMMUTABLE STRICT PARALLEL SAFE | |
_COST_LOW; | |
-- PostGIS equivalent function: length2d(geometry) | |
CREATE OR REPLACE FUNCTION ST_Length(geometry) | |
RETURNS FLOAT8 | |
AS 'MODULE_PATHNAME', 'LWGEOM_length2d_linestring' | |
LANGUAGE 'c' IMMUTABLE STRICT PARALLEL SAFE | |
_COST_LOW; | |
-- Availability in 2.2.0 | |
CREATE OR REPLACE FUNCTION ST_LengthSpheroid(geometry, spheroid) | |
RETURNS FLOAT8 | |
AS 'MODULE_PATHNAME','LWGEOM_length_ellipsoid_linestring' | |
LANGUAGE 'c' IMMUTABLE STRICT PARALLEL SAFE | |
_COST_MEDIUM; | |
-- Availability: 2.2.0 | |
CREATE OR REPLACE FUNCTION ST_Length2DSpheroid(geometry, spheroid) | |
RETURNS FLOAT8 | |
AS 'MODULE_PATHNAME','LWGEOM_length2d_ellipsoid' | |
LANGUAGE 'c' IMMUTABLE STRICT PARALLEL SAFE | |
_COST_MEDIUM; | |
-- Availability: 2.0.0 | |
CREATE OR REPLACE FUNCTION ST_3DPerimeter(geometry) | |
RETURNS FLOAT8 | |
AS 'MODULE_PATHNAME', 'LWGEOM_perimeter_poly' | |
LANGUAGE 'c' IMMUTABLE STRICT PARALLEL SAFE | |
_COST_LOW; | |
-- Availability: 1.2.2 | |
CREATE OR REPLACE FUNCTION ST_perimeter2d(geometry) | |
RETURNS FLOAT8 | |
AS 'MODULE_PATHNAME', 'LWGEOM_perimeter2d_poly' | |
LANGUAGE 'c' IMMUTABLE STRICT PARALLEL SAFE | |
_COST_LOW; | |
-- PostGIS equivalent function: perimeter2d(geometry) | |
CREATE OR REPLACE FUNCTION ST_Perimeter(geometry) | |
RETURNS FLOAT8 | |
AS 'MODULE_PATHNAME', 'LWGEOM_perimeter2d_poly' | |
LANGUAGE 'c' IMMUTABLE STRICT PARALLEL SAFE | |
_COST_LOW; | |
-- Availability: 1.2.2 | |
-- Deprecation in 1.3.4 | |
CREATE OR REPLACE FUNCTION ST_Area2D(geometry) | |
RETURNS FLOAT8 | |
AS 'MODULE_PATHNAME', 'ST_Area' | |
LANGUAGE 'c' IMMUTABLE STRICT PARALLEL SAFE | |
_COST_LOW; | |
-- PostGIS equivalent function: area(geometry) | |
CREATE OR REPLACE FUNCTION ST_Area(geometry) | |
RETURNS FLOAT8 | |
AS 'MODULE_PATHNAME','ST_Area' | |
LANGUAGE 'c' IMMUTABLE STRICT PARALLEL SAFE | |
_COST_LOW; | |
-- Availability: 2.4.0 | |
CREATE OR REPLACE FUNCTION ST_IsPolygonCW(geometry) | |
RETURNS boolean | |
AS 'MODULE_PATHNAME','ST_IsPolygonCW' | |
LANGUAGE 'c' IMMUTABLE STRICT PARALLEL SAFE | |
_COST_LOW; | |
-- Availability: 2.4.0 | |
CREATE OR REPLACE FUNCTION ST_IsPolygonCCW(geometry) | |
RETURNS boolean | |
AS 'MODULE_PATHNAME','ST_IsPolygonCCW' | |
LANGUAGE 'c' IMMUTABLE STRICT PARALLEL SAFE | |
_COST_LOW; | |
-- Availability: 2.0.0 | |
CREATE OR REPLACE FUNCTION ST_DistanceSpheroid(geom1 geometry, geom2 geometry,spheroid) | |
RETURNS FLOAT8 | |
AS 'MODULE_PATHNAME','LWGEOM_distance_ellipsoid' | |
LANGUAGE 'c' IMMUTABLE STRICT PARALLEL SAFE | |
_COST_MEDIUM; --upped this | |
-- Minimum distance. 2D only. | |
CREATE OR REPLACE FUNCTION ST_Distance(geom1 geometry, geom2 geometry) | |
RETURNS float8 | |
AS 'MODULE_PATHNAME', 'ST_Distance' | |
LANGUAGE 'c' IMMUTABLE STRICT PARALLEL SAFE | |
_COST_HIGH; | |
-- Availability: 2.2.0 | |
CREATE OR REPLACE FUNCTION ST_PointInsideCircle(geometry,float8,float8,float8) | |
RETURNS bool | |
AS 'MODULE_PATHNAME', 'LWGEOM_inside_circle_point' | |
LANGUAGE 'c' IMMUTABLE STRICT PARALLEL SAFE | |
_COST_MEDIUM; | |
-- Availability: 1.2.2 | |
CREATE OR REPLACE FUNCTION ST_azimuth(geom1 geometry, geom2 geometry) | |
RETURNS float8 | |
AS 'MODULE_PATHNAME', 'LWGEOM_azimuth' | |
LANGUAGE 'c' IMMUTABLE STRICT PARALLEL SAFE | |
_COST_LOW; | |
-- Availability: 2.5.0 | |
CREATE OR REPLACE FUNCTION ST_Angle(pt1 geometry, pt2 geometry, pt3 geometry, pt4 geometry default 'POINT EMPTY'::geometry) | |
RETURNS float8 | |
AS 'MODULE_PATHNAME', 'LWGEOM_angle' | |
LANGUAGE 'c' IMMUTABLE STRICT PARALLEL SAFE | |
_COST_LOW; | |
-- Availability: Future | |
-- CREATE OR REPLACE FUNCTION _ST_DistanceRectTree(g1 geometry, g2 geometry) | |
-- RETURNS float8 | |
-- AS 'MODULE_PATHNAME', 'ST_DistanceRectTree' | |
-- LANGUAGE 'c' IMMUTABLE STRICT PARALLEL SAFE | |
-- _COST_MEDIUM; | |
-- Availability: Future | |
-- CREATE OR REPLACE FUNCTION _ST_DistanceRectTreeCached(g1 geometry, g2 geometry) | |
-- RETURNS float8 | |
-- AS 'MODULE_PATHNAME', 'ST_DistanceRectTreeCached' | |
-- LANGUAGE 'c' IMMUTABLE STRICT PARALLEL SAFE | |
-- _COST_MEDIUM; | |
------------------------------------------------------------------------ | |
-- MISC | |
------------------------------------------------------------------------ | |
-- Availability: 2.1.0 | |
CREATE OR REPLACE FUNCTION ST_Force2D(geometry) | |
RETURNS geometry | |
AS 'MODULE_PATHNAME', 'LWGEOM_force_2d' | |
LANGUAGE 'c' IMMUTABLE STRICT PARALLEL SAFE | |
_COST_LOW; | |
-- Availability: 2.1.0 | |
-- Changed: 3.1.0 - add zvalue=0.0 parameter | |
CREATE OR REPLACE FUNCTION ST_Force3DZ(geom geometry, zvalue float8 default 0.0) | |
RETURNS geometry | |
AS 'MODULE_PATHNAME', 'LWGEOM_force_3dz' | |
LANGUAGE 'c' IMMUTABLE STRICT PARALLEL SAFE | |
_COST_LOW; | |
-- Availability: 2.1.0 | |
-- Changed: 3.1.0 - add zvalue=0.0 parameter | |
CREATE OR REPLACE FUNCTION ST_Force3D(geom geometry, zvalue float8 default 0.0) | |
RETURNS geometry | |
AS 'SELECT @extschema@.ST_Force3DZ($1, $2)' | |
LANGUAGE 'sql' IMMUTABLE STRICT PARALLEL SAFE | |
_COST_LOW; | |
-- Availability: 2.1.0 | |
-- Changed: 3.1.0 - add mvalue=0.0 parameter | |
CREATE OR REPLACE FUNCTION ST_Force3DM(geom geometry, mvalue float8 default 0.0) | |
RETURNS geometry | |
AS 'MODULE_PATHNAME', 'LWGEOM_force_3dm' | |
LANGUAGE 'c' IMMUTABLE STRICT PARALLEL SAFE | |
_COST_LOW; | |
-- Availability: 2.1.0 | |
-- Changed: 3.1.0 - add zvalue=0.0 and mvalue=0.0 parameters | |
CREATE OR REPLACE FUNCTION ST_Force4D(geom geometry, zvalue float8 default 0.0, mvalue float8 default 0.0) | |
RETURNS geometry | |
AS 'MODULE_PATHNAME', 'LWGEOM_force_4d' | |
LANGUAGE 'c' IMMUTABLE STRICT PARALLEL SAFE | |
_COST_LOW; | |
-- Availability: 2.1.0 | |
CREATE OR REPLACE FUNCTION ST_ForceCollection(geometry) | |
RETURNS geometry | |
AS 'MODULE_PATHNAME', 'LWGEOM_force_collection' | |
LANGUAGE 'c' IMMUTABLE STRICT PARALLEL SAFE | |
_COST_LOW; | |
-- Availability: 1.5.0 | |
CREATE OR REPLACE FUNCTION ST_CollectionExtract(geometry, integer) | |
RETURNS geometry | |
AS 'MODULE_PATHNAME', 'ST_CollectionExtract' | |
LANGUAGE 'c' IMMUTABLE STRICT PARALLEL SAFE | |
_COST_LOW; | |
-- Availability: 3.1.0 | |
CREATE OR REPLACE FUNCTION ST_CollectionExtract(geometry) | |
RETURNS geometry | |
AS 'MODULE_PATHNAME', 'ST_CollectionExtract' | |
LANGUAGE 'c' IMMUTABLE STRICT PARALLEL SAFE | |
_COST_LOW; | |
-- Availability: 2.0.0 | |
CREATE OR REPLACE FUNCTION ST_CollectionHomogenize(geometry) | |
RETURNS geometry | |
AS 'MODULE_PATHNAME', 'ST_CollectionHomogenize' | |
LANGUAGE 'c' IMMUTABLE STRICT PARALLEL SAFE | |
_COST_LOW; | |
-- Availability: 1.2.2 | |
CREATE OR REPLACE FUNCTION ST_Multi(geometry) | |
RETURNS geometry | |
AS 'MODULE_PATHNAME', 'LWGEOM_force_multi' | |
LANGUAGE 'c' IMMUTABLE STRICT PARALLEL SAFE | |
_COST_LOW; | |
-- Availability: 2.2.0 | |
CREATE OR REPLACE FUNCTION ST_ForceCurve(geometry) | |
RETURNS geometry | |
AS 'MODULE_PATHNAME', 'LWGEOM_force_curve' | |
LANGUAGE 'c' IMMUTABLE STRICT PARALLEL SAFE | |
_COST_MEDIUM; | |
-- Availability: 2.1.0 | |
CREATE OR REPLACE FUNCTION ST_ForceSFS(geometry) | |
RETURNS geometry | |
AS 'MODULE_PATHNAME', 'LWGEOM_force_sfs' | |
LANGUAGE 'c' IMMUTABLE STRICT PARALLEL SAFE | |
_COST_MEDIUM; | |
-- Availability: 2.1.0 | |
CREATE OR REPLACE FUNCTION ST_ForceSFS(geometry, version text) | |
RETURNS geometry | |
AS 'MODULE_PATHNAME', 'LWGEOM_force_sfs' | |
LANGUAGE 'c' IMMUTABLE STRICT PARALLEL SAFE | |
_COST_MEDIUM; | |
-- Availability: 1.2.2 | |
CREATE OR REPLACE FUNCTION ST_Expand(box3d,float8) | |
RETURNS box3d | |
AS 'MODULE_PATHNAME', 'BOX3D_expand' | |
LANGUAGE 'c' IMMUTABLE STRICT PARALLEL SAFE | |
_COST_LOW; | |
-- Availability: 2.3.0 | |
CREATE OR REPLACE FUNCTION ST_Expand(box box3d, dx float8, dy float8, dz float8 DEFAULT 0) | |
RETURNS box3d | |
AS 'MODULE_PATHNAME', 'BOX3D_expand' | |
LANGUAGE 'c' IMMUTABLE STRICT PARALLEL SAFE | |
_COST_LOW; | |
-- Availability: 1.2.2 | |
CREATE OR REPLACE FUNCTION ST_Expand(geometry,float8) | |
RETURNS geometry | |
AS 'MODULE_PATHNAME', 'LWGEOM_expand' | |
LANGUAGE 'c' IMMUTABLE STRICT PARALLEL SAFE | |
_COST_LOW; | |
-- Availability: 2.3.0 | |
CREATE OR REPLACE FUNCTION ST_Expand(geom geometry, dx float8, dy float8, dz float8 DEFAULT 0, dm float8 DEFAULT 0) | |
RETURNS geometry | |
AS 'MODULE_PATHNAME', 'LWGEOM_expand' | |
LANGUAGE 'c' IMMUTABLE STRICT PARALLEL SAFE | |
_COST_LOW; | |
-- PostGIS equivalent function: envelope(geometry) | |
CREATE OR REPLACE FUNCTION ST_Envelope(geometry) | |
RETURNS geometry | |
AS 'MODULE_PATHNAME', 'LWGEOM_envelope' | |
LANGUAGE 'c' IMMUTABLE STRICT PARALLEL SAFE | |
_COST_LOW; | |
-- Availability: 2.2.0 | |
CREATE OR REPLACE FUNCTION ST_BoundingDiagonal(geom geometry, fits boolean DEFAULT false) | |
RETURNS geometry | |
AS 'MODULE_PATHNAME', 'ST_BoundingDiagonal' | |
LANGUAGE 'c' IMMUTABLE STRICT PARALLEL SAFE | |
_COST_DEFAULT; | |
-- Availability: 1.2.2 | |
CREATE OR REPLACE FUNCTION ST_Reverse(geometry) | |
RETURNS geometry | |
AS 'MODULE_PATHNAME', 'LWGEOM_reverse' | |
LANGUAGE 'c' IMMUTABLE STRICT PARALLEL SAFE | |
_COST_LOW; | |
-- Availability: 2.4.0 | |
CREATE OR REPLACE FUNCTION ST_ForcePolygonCW(geometry) | |
RETURNS geometry | |
AS 'MODULE_PATHNAME', 'LWGEOM_force_clockwise_poly' | |
LANGUAGE 'c' IMMUTABLE STRICT PARALLEL SAFE | |
_COST_LOW; | |
-- Availability: 2.4.0 | |
CREATE OR REPLACE FUNCTION ST_ForcePolygonCCW(geometry) | |
RETURNS geometry | |
AS $$ SELECT @extschema@.ST_Reverse(@extschema@.ST_ForcePolygonCW($1)) $$ | |
LANGUAGE 'sql' IMMUTABLE STRICT PARALLEL SAFE | |
_COST_LOW; | |
-- Availability: 1.2.2 | |
CREATE OR REPLACE FUNCTION ST_ForceRHR(geometry) | |
RETURNS geometry | |
AS 'MODULE_PATHNAME', 'LWGEOM_force_clockwise_poly' | |
LANGUAGE 'c' IMMUTABLE STRICT PARALLEL SAFE | |
_COST_LOW; | |
-- Availability: 1.5.0 | |
CREATE OR REPLACE FUNCTION postgis_noop(geometry) | |
RETURNS geometry | |
AS 'MODULE_PATHNAME', 'LWGEOM_noop' | |
LANGUAGE 'c' IMMUTABLE STRICT PARALLEL SAFE | |
_COST_DEFAULT; | |
-- Availability: 3.0.0 | |
CREATE OR REPLACE FUNCTION postgis_geos_noop(geometry) | |
RETURNS geometry | |
AS 'MODULE_PATHNAME', 'GEOSnoop' | |
LANGUAGE 'c' IMMUTABLE STRICT PARALLEL SAFE | |
_COST_DEFAULT; | |
-- Availability: 2.3.0 | |
CREATE OR REPLACE FUNCTION ST_Normalize(geom geometry) | |
RETURNS geometry | |
AS 'MODULE_PATHNAME', 'ST_Normalize' | |
LANGUAGE 'c' IMMUTABLE STRICT PARALLEL SAFE | |
_COST_MEDIUM; | |
-- Deprecation in 1.5.0 | |
CREATE OR REPLACE FUNCTION ST_zmflag(geometry) | |
RETURNS smallint | |
AS 'MODULE_PATHNAME', 'LWGEOM_zmflag' | |
LANGUAGE 'c' IMMUTABLE STRICT PARALLEL SAFE | |
_COST_DEFAULT; | |
-- Availability: 1.2.2 | |
CREATE OR REPLACE FUNCTION ST_NDims(geometry) | |
RETURNS smallint | |
AS 'MODULE_PATHNAME', 'LWGEOM_ndims' | |
LANGUAGE 'c' IMMUTABLE STRICT PARALLEL SAFE | |
_COST_DEFAULT; | |
-- Availability: 1.2.2 | |
CREATE OR REPLACE FUNCTION ST_AsEWKT(geometry) | |
RETURNS TEXT | |
AS 'MODULE_PATHNAME','LWGEOM_asEWKT' | |
LANGUAGE 'c' IMMUTABLE STRICT PARALLEL SAFE | |
_COST_MEDIUM; | |
-- Availability: 3.1.0 | |
CREATE OR REPLACE FUNCTION ST_AsEWKT(geometry, int4) | |
RETURNS TEXT | |
AS 'MODULE_PATHNAME','LWGEOM_asEWKT' | |
LANGUAGE 'c' IMMUTABLE STRICT PARALLEL SAFE | |
_COST_MEDIUM; | |
-- Availability: 2.2.0 | |
CREATE 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) | |
RETURNS bytea | |
AS 'MODULE_PATHNAME','TWKBFromLWGEOM' | |
LANGUAGE 'c' IMMUTABLE PARALLEL SAFE | |
_COST_LOW; | |
-- Availability: 2.2.0 | |
CREATE 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) | |
RETURNS bytea | |
AS 'MODULE_PATHNAME','TWKBFromLWGEOMArray' | |
LANGUAGE 'c' IMMUTABLE PARALLEL SAFE | |
_COST_LOW; | |
-- Availability: 1.2.2 | |
CREATE OR REPLACE FUNCTION ST_AsEWKB(geometry) | |
RETURNS BYTEA | |
AS 'MODULE_PATHNAME','WKBFromLWGEOM' | |
LANGUAGE 'c' IMMUTABLE STRICT PARALLEL SAFE | |
_COST_LOW; | |
-- Availability: 1.2.2 | |
CREATE OR REPLACE FUNCTION ST_AsHEXEWKB(geometry) | |
RETURNS TEXT | |
AS 'MODULE_PATHNAME','LWGEOM_asHEXEWKB' | |
LANGUAGE 'c' IMMUTABLE STRICT PARALLEL SAFE | |
_COST_LOW; | |
-- Availability: 1.2.2 | |
CREATE OR REPLACE FUNCTION ST_AsHEXEWKB(geometry, text) | |
RETURNS TEXT | |
AS 'MODULE_PATHNAME','LWGEOM_asHEXEWKB' | |
LANGUAGE 'c' IMMUTABLE STRICT PARALLEL SAFE | |
_COST_LOW; | |
-- Availability: 1.2.2 | |
CREATE OR REPLACE FUNCTION ST_AsEWKB(geometry,text) | |
RETURNS bytea | |
AS 'MODULE_PATHNAME','WKBFromLWGEOM' | |
LANGUAGE 'c' IMMUTABLE STRICT PARALLEL SAFE | |
_COST_LOW; | |
-- Availability: 2.0.0 | |
CREATE OR REPLACE FUNCTION ST_AsLatLonText(geom geometry, tmpl text DEFAULT '') | |
RETURNS text | |
AS 'MODULE_PATHNAME','LWGEOM_to_latlon' | |
LANGUAGE 'c' IMMUTABLE STRICT PARALLEL SAFE | |
_COST_LOW; | |
-- Deprecation in 1.2.3 | |
CREATE OR REPLACE FUNCTION GeomFromEWKB(bytea) | |
RETURNS geometry | |
AS 'MODULE_PATHNAME','LWGEOMFromEWKB' | |
LANGUAGE 'c' IMMUTABLE STRICT PARALLEL SAFE | |
_COST_LOW; | |
-- Availability: 1.2.2 | |
CREATE OR REPLACE FUNCTION ST_GeomFromEWKB(bytea) | |
RETURNS geometry | |
AS 'MODULE_PATHNAME','LWGEOMFromEWKB' | |
LANGUAGE 'c' IMMUTABLE STRICT PARALLEL SAFE | |
_COST_LOW; | |
-- Availability: 2.2 | |
CREATE OR REPLACE FUNCTION ST_GeomFromTWKB(bytea) | |
RETURNS geometry | |
AS 'MODULE_PATHNAME','LWGEOMFromTWKB' | |
LANGUAGE 'c' IMMUTABLE STRICT PARALLEL SAFE | |
_COST_LOW; | |
-- Deprecation in 1.2.3 | |
CREATE OR REPLACE FUNCTION GeomFromEWKT(text) | |
RETURNS geometry | |
AS 'MODULE_PATHNAME','parse_WKT_lwgeom' | |
LANGUAGE 'c' IMMUTABLE STRICT PARALLEL SAFE | |
_COST_LOW; | |
-- Availability: 1.2.2 | |
CREATE OR REPLACE FUNCTION ST_GeomFromEWKT(text) | |
RETURNS geometry | |
AS 'MODULE_PATHNAME','parse_WKT_lwgeom' | |
LANGUAGE 'c' IMMUTABLE STRICT PARALLEL SAFE | |
_COST_LOW; | |
-- Availability: 1.5.0 | |
CREATE OR REPLACE FUNCTION postgis_cache_bbox() | |
RETURNS trigger | |
AS 'MODULE_PATHNAME', 'cache_bbox' | |
LANGUAGE 'c'; | |
------------------------------------------------------------------------ | |
-- CONSTRUCTORS | |
------------------------------------------------------------------------ | |
-- Availability: 1.2.2 | |
CREATE OR REPLACE FUNCTION ST_MakePoint(float8, float8) | |
RETURNS geometry | |
AS 'MODULE_PATHNAME', 'LWGEOM_makepoint' | |
LANGUAGE 'c' IMMUTABLE STRICT PARALLEL SAFE | |
_COST_LOW; | |
-- Availability: 1.2.2 | |
CREATE OR REPLACE FUNCTION ST_MakePoint(float8, float8, float8) | |
RETURNS geometry | |
AS 'MODULE_PATHNAME', 'LWGEOM_makepoint' | |
LANGUAGE 'c' IMMUTABLE STRICT PARALLEL SAFE | |
_COST_LOW; | |
-- Availability: 1.2.2 | |
CREATE OR REPLACE FUNCTION ST_MakePoint(float8, float8, float8, float8) | |
RETURNS geometry | |
AS 'MODULE_PATHNAME', 'LWGEOM_makepoint' | |
LANGUAGE 'c' IMMUTABLE STRICT PARALLEL SAFE | |
_COST_LOW; | |
-- Availability: 1.3.4 | |
CREATE OR REPLACE FUNCTION ST_MakePointM(float8, float8, float8) | |
RETURNS geometry | |
AS 'MODULE_PATHNAME', 'LWGEOM_makepoint3dm' | |
LANGUAGE 'c' IMMUTABLE STRICT PARALLEL SAFE | |
_COST_LOW; | |
-- Availability: 2.0.0 | |
CREATE OR REPLACE FUNCTION ST_3DMakeBox(geom1 geometry, geom2 geometry) | |
RETURNS box3d | |
AS 'MODULE_PATHNAME', 'BOX3D_construct' | |
LANGUAGE 'c' IMMUTABLE STRICT PARALLEL SAFE | |
_COST_LOW; | |
-- Availability: 1.4.0 | |
CREATE OR REPLACE FUNCTION ST_MakeLine (geometry[]) | |
RETURNS geometry | |
AS 'MODULE_PATHNAME', 'LWGEOM_makeline_garray' | |
LANGUAGE 'c' IMMUTABLE STRICT PARALLEL SAFE | |
_COST_LOW; | |
-- Availability: 1.2.2 | |
CREATE OR REPLACE FUNCTION ST_LineFromMultiPoint(geometry) | |
RETURNS geometry | |
AS 'MODULE_PATHNAME', 'LWGEOM_line_from_mpoint' | |
LANGUAGE 'c' IMMUTABLE STRICT PARALLEL SAFE | |
_COST_LOW; | |
-- Availability: 1.2.2 | |
CREATE OR REPLACE FUNCTION ST_MakeLine(geom1 geometry, geom2 geometry) | |
RETURNS geometry | |
AS 'MODULE_PATHNAME', 'LWGEOM_makeline' | |
LANGUAGE 'c' IMMUTABLE STRICT PARALLEL SAFE | |
_COST_LOW; | |
-- Availability: 1.2.2 | |
CREATE OR REPLACE FUNCTION ST_AddPoint(geom1 geometry, geom2 geometry) | |
RETURNS geometry | |
AS 'MODULE_PATHNAME', 'LWGEOM_addpoint' | |
LANGUAGE 'c' IMMUTABLE STRICT PARALLEL SAFE | |
_COST_LOW; | |
-- Availability: 1.2.2 | |
CREATE OR REPLACE FUNCTION ST_AddPoint(geom1 geometry, geom2 geometry, integer) | |
RETURNS geometry | |
AS 'MODULE_PATHNAME', 'LWGEOM_addpoint' | |
LANGUAGE 'c' IMMUTABLE STRICT PARALLEL SAFE | |
_COST_LOW; | |
-- Availability: 1.2.2 | |
CREATE OR REPLACE FUNCTION ST_RemovePoint(geometry, integer) | |
RETURNS geometry | |
AS 'MODULE_PATHNAME', 'LWGEOM_removepoint' | |
LANGUAGE 'c' IMMUTABLE STRICT PARALLEL SAFE | |
_COST_LOW; | |
-- Availability: 1.2.2 | |
CREATE OR REPLACE FUNCTION ST_SetPoint(geometry, integer, geometry) | |
RETURNS geometry | |
AS 'MODULE_PATHNAME', 'LWGEOM_setpoint_linestring' | |
LANGUAGE 'c' IMMUTABLE STRICT PARALLEL SAFE | |
_COST_LOW; | |
-- Availability: 1.5.0 | |
-- Availability: 2.0.0 - made srid optional | |
CREATE OR REPLACE FUNCTION ST_MakeEnvelope(float8, float8, float8, float8, integer DEFAULT 0) | |
RETURNS geometry | |
AS 'MODULE_PATHNAME', 'ST_MakeEnvelope' | |
LANGUAGE 'c' IMMUTABLE STRICT PARALLEL SAFE | |
_COST_LOW; | |
-- Availability: 3.0.0 | |
-- Changed: 3.1.0 - add margin=0.0 parameter | |
CREATE OR REPLACE FUNCTION ST_TileEnvelope(zoom integer, x integer, y integer, bounds geometry DEFAULT 'SRID=3857;LINESTRING(-20037508.342789244 -20037508.342789244, 20037508.342789244 20037508.342789244)'::geometry, margin float8 DEFAULT 0.0) | |
RETURNS geometry | |
AS 'MODULE_PATHNAME', 'ST_TileEnvelope' | |
LANGUAGE 'c' IMMUTABLE STRICT PARALLEL SAFE | |
_COST_LOW; | |
-- Availability: 1.2.2 | |
CREATE OR REPLACE FUNCTION ST_MakePolygon(geometry, geometry[]) | |
RETURNS geometry | |
AS 'MODULE_PATHNAME', 'LWGEOM_makepoly' | |
LANGUAGE 'c' IMMUTABLE STRICT PARALLEL SAFE | |
_COST_LOW; | |
-- Availability: 1.2.2 | |
CREATE OR REPLACE FUNCTION ST_MakePolygon(geometry) | |
RETURNS geometry | |
AS 'MODULE_PATHNAME', 'LWGEOM_makepoly' | |
LANGUAGE 'c' IMMUTABLE STRICT PARALLEL SAFE | |
_COST_LOW; | |
-- Availability: 1.2.2 | |
CREATE OR REPLACE FUNCTION ST_BuildArea(geometry) | |
RETURNS geometry | |
AS 'MODULE_PATHNAME', 'ST_BuildArea' | |
LANGUAGE 'c' IMMUTABLE STRICT PARALLEL SAFE | |
_COST_HIGH; | |
-- Availability: 1.4.0 | |
CREATE OR REPLACE FUNCTION ST_Polygonize (geometry[]) | |
RETURNS geometry | |
AS 'MODULE_PATHNAME', 'polygonize_garray' | |
LANGUAGE 'c' IMMUTABLE STRICT PARALLEL SAFE | |
_COST_HIGH; | |
-- Availability: 2.2 | |
CREATE OR REPLACE FUNCTION ST_ClusterIntersecting(geometry[]) | |
RETURNS geometry[] | |
AS 'MODULE_PATHNAME', 'clusterintersecting_garray' | |
LANGUAGE 'c' IMMUTABLE STRICT PARALLEL SAFE | |
_COST_HIGH; | |
-- Availability: 2.2 | |
CREATE OR REPLACE FUNCTION ST_ClusterWithin(geometry[], float8) | |
RETURNS geometry[] | |
AS 'MODULE_PATHNAME', 'cluster_within_distance_garray' | |
LANGUAGE 'c' IMMUTABLE STRICT PARALLEL SAFE | |
_COST_HIGH; | |
-- Availability: 2.3 | |
CREATE OR REPLACE FUNCTION ST_ClusterDBSCAN (geometry, eps float8, minpoints int) | |
RETURNS int | |
AS 'MODULE_PATHNAME', 'ST_ClusterDBSCAN' | |
LANGUAGE 'c' IMMUTABLE STRICT WINDOW PARALLEL SAFE | |
_COST_HIGH; | |
-- Availability: 1.2.2 | |
CREATE OR REPLACE FUNCTION ST_LineMerge(geometry) | |
RETURNS geometry | |
AS 'MODULE_PATHNAME', 'linemerge' | |
LANGUAGE 'c' IMMUTABLE STRICT PARALLEL SAFE | |
_COST_HIGH; | |
----------------------------------------------------------------------------- | |
-- Affine transforms | |
----------------------------------------------------------------------------- | |
-- Availability: 1.2.2 | |
CREATE OR REPLACE FUNCTION ST_Affine(geometry,float8,float8,float8,float8,float8,float8,float8,float8,float8,float8,float8,float8) | |
RETURNS geometry | |
AS 'MODULE_PATHNAME', 'LWGEOM_affine' | |
LANGUAGE 'c' IMMUTABLE STRICT PARALLEL SAFE | |
_COST_LOW; | |
-- Availability: 1.2.2 | |
CREATE OR REPLACE FUNCTION ST_Affine(geometry,float8,float8,float8,float8,float8,float8) | |
RETURNS geometry | |
AS 'SELECT @extschema@.ST_Affine($1, $2, $3, 0, $4, $5, 0, 0, 0, 1, $6, $7, 0)' | |
LANGUAGE 'sql' IMMUTABLE STRICT PARALLEL SAFE | |
_COST_LOW; | |
-- Availability: 1.2.2 | |
CREATE OR REPLACE FUNCTION ST_Rotate(geometry,float8) | |
RETURNS geometry | |
AS 'SELECT @extschema@.ST_Affine($1, cos($2), -sin($2), 0, sin($2), cos($2), 0, 0, 0, 1, 0, 0, 0)' | |
LANGUAGE 'sql' IMMUTABLE STRICT PARALLEL SAFE | |
_COST_LOW; | |
-- Availability: 2.0.0 | |
CREATE OR REPLACE FUNCTION ST_Rotate(geometry,float8,float8,float8) | |
RETURNS geometry | |
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)' | |
LANGUAGE 'sql' IMMUTABLE STRICT PARALLEL SAFE | |
_COST_LOW; | |
-- Availability: 2.0.0 | |
CREATE OR REPLACE FUNCTION ST_Rotate(geometry,float8,geometry) | |
RETURNS geometry | |
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)' | |
LANGUAGE 'sql' IMMUTABLE STRICT PARALLEL SAFE | |
_COST_LOW; | |
-- Availability: 1.2.2 | |
CREATE OR REPLACE FUNCTION ST_RotateZ(geometry,float8) | |
RETURNS geometry | |
AS 'SELECT @extschema@.ST_Rotate($1, $2)' | |
LANGUAGE 'sql' IMMUTABLE STRICT PARALLEL SAFE | |
_COST_LOW; | |
-- Availability: 1.2.2 | |
CREATE OR REPLACE FUNCTION ST_RotateX(geometry,float8) | |
RETURNS geometry | |
AS 'SELECT @extschema@.ST_Affine($1, 1, 0, 0, 0, cos($2), -sin($2), 0, sin($2), cos($2), 0, 0, 0)' | |
LANGUAGE 'sql' IMMUTABLE STRICT PARALLEL SAFE | |
_COST_LOW; | |
-- Availability: 1.2.2 | |
CREATE OR REPLACE FUNCTION ST_RotateY(geometry,float8) | |
RETURNS geometry | |
AS 'SELECT @extschema@.ST_Affine($1, cos($2), 0, sin($2), 0, 1, 0, -sin($2), 0, cos($2), 0, 0, 0)' | |
LANGUAGE 'sql' IMMUTABLE STRICT PARALLEL SAFE | |
_COST_LOW; | |
-- Availability: 1.2.2 | |
CREATE OR REPLACE FUNCTION ST_Translate(geometry,float8,float8,float8) | |
RETURNS geometry | |
AS 'SELECT @extschema@.ST_Affine($1, 1, 0, 0, 0, 1, 0, 0, 0, 1, $2, $3, $4)' | |
LANGUAGE 'sql' IMMUTABLE STRICT PARALLEL SAFE | |
_COST_LOW; | |
-- Availability: 1.2.2 | |
CREATE OR REPLACE FUNCTION ST_Translate(geometry,float8,float8) | |
RETURNS geometry | |
AS 'SELECT @extschema@.ST_Translate($1, $2, $3, 0)' | |
LANGUAGE 'sql' IMMUTABLE STRICT PARALLEL SAFE | |
_COST_LOW; | |
-- Availability: 2.2.0 | |
CREATE OR REPLACE FUNCTION ST_Scale(geometry,geometry) | |
RETURNS geometry | |
AS 'MODULE_PATHNAME', 'ST_Scale' | |
LANGUAGE 'c' IMMUTABLE STRICT PARALLEL SAFE | |
_COST_LOW; | |
-- Availability: 2.5.0 | |
CREATE OR REPLACE FUNCTION ST_Scale(geometry,geometry,origin geometry) | |
RETURNS geometry | |
AS 'MODULE_PATHNAME', 'ST_Scale' | |
LANGUAGE 'c' IMMUTABLE STRICT PARALLEL SAFE | |
_COST_LOW; | |
-- Availability: 1.2.2 | |
CREATE OR REPLACE FUNCTION ST_Scale(geometry,float8,float8,float8) | |
RETURNS geometry | |
--AS 'SELECT ST_Affine($1, $2, 0, 0, 0, $3, 0, 0, 0, $4, 0, 0, 0)' | |
AS 'SELECT @extschema@.ST_Scale($1, @extschema@.ST_MakePoint($2, $3, $4))' | |
LANGUAGE 'sql' IMMUTABLE STRICT PARALLEL SAFE | |
_COST_LOW; | |
-- Availability: 1.2.2 | |
CREATE OR REPLACE FUNCTION ST_Scale(geometry,float8,float8) | |
RETURNS geometry | |
AS 'SELECT @extschema@.ST_Scale($1, $2, $3, 1)' | |
LANGUAGE 'sql' IMMUTABLE STRICT PARALLEL SAFE | |
_COST_LOW; | |
-- Availability: 1.2.2 | |
CREATE OR REPLACE FUNCTION ST_Transscale(geometry,float8,float8,float8,float8) | |
RETURNS geometry | |
AS 'SELECT @extschema@.ST_Affine($1, $4, 0, 0, 0, $5, 0, | |
0, 0, 1, $2 * $4, $3 * $5, 0)' | |
LANGUAGE 'sql' IMMUTABLE STRICT PARALLEL SAFE | |
_COST_LOW; | |
----------------------------------------------------------------------- | |
-- Dumping | |
----------------------------------------------------------------------- | |
-- Availability: 1.0.0 | |
CREATE TYPE geometry_dump AS ( | |
path integer[], | |
geom geometry | |
); | |
-- Availability: 1.2.2 | |
CREATE OR REPLACE FUNCTION ST_Dump(geometry) | |
RETURNS SETOF geometry_dump | |
AS 'MODULE_PATHNAME', 'LWGEOM_dump' | |
LANGUAGE 'c' IMMUTABLE STRICT PARALLEL SAFE | |
_COST_MEDIUM; | |
-- Availability: 1.2.2 | |
CREATE OR REPLACE FUNCTION ST_DumpRings(geometry) | |
RETURNS SETOF geometry_dump | |
AS 'MODULE_PATHNAME', 'LWGEOM_dump_rings' | |
LANGUAGE 'c' IMMUTABLE STRICT PARALLEL SAFE | |
_COST_MEDIUM; | |
----------------------------------------------------------------------- | |
-- ST_DumpPoints() | |
----------------------------------------------------------------------- | |
-- This function mimicks that of ST_Dump for collections, but this function | |
-- that returns a path and all the points that make up a particular geometry. | |
-- Availability: 1.5.0 | |
CREATE OR REPLACE FUNCTION ST_DumpPoints(geometry) | |
RETURNS SETOF geometry_dump | |
AS 'MODULE_PATHNAME', 'LWGEOM_dumppoints' | |
LANGUAGE 'c' IMMUTABLE STRICT PARALLEL SAFE | |
_COST_HIGH; | |
------------------------------------------------------------------- | |
-- SPATIAL_REF_SYS | |
------------------------------------------------------------------- | |
CREATE TABLE spatial_ref_sys ( | |
srid integer not null primary key | |
check (srid > 0 and srid <= SRID_USR_MAX), | |
auth_name varchar(256), | |
auth_srid integer, | |
srtext varchar(2048), | |
proj4text varchar(2048) | |
); | |
----------------------------------------------------------------------- | |
-- POPULATE_GEOMETRY_COLUMNS() | |
----------------------------------------------------------------------- | |
-- Truncates and refills the geometry_columns table from all tables and | |
-- views in the database that contain geometry columns. This function | |
-- is a simple wrapper for populate_geometry_columns(oid). In essence, | |
-- this function ensures every geometry column in the database has the | |
-- appropriate spatial contraints (for tables) and exists in the | |
-- geometry_columns table. | |
-- Availability: 1.4.0 | |
-- Revised: 2.0.0 -- no longer deletes from geometry_columns | |
-- Has new use_typmod option that defaults to true. | |
-- If use typmod is set to false will use old constraint behavior. | |
-- Will only touch table missing typmod or geometry constraints | |
----------------------------------------------------------------------- | |
CREATE OR REPLACE FUNCTION populate_geometry_columns(use_typmod boolean DEFAULT true) | |
RETURNS text AS | |
$$ | |
DECLARE | |
inserted integer; | |
oldcount integer; | |
probed integer; | |
stale integer; | |
gcs RECORD; | |
gc RECORD; | |
gsrid integer; | |
gndims integer; | |
gtype text; | |
query text; | |
gc_is_valid boolean; | |
BEGIN | |
SELECT count(*) INTO oldcount FROM @extschema@.geometry_columns; | |
inserted := 0; | |
-- Count the number of geometry columns in all tables and views | |
SELECT count(DISTINCT c.oid) INTO probed | |
FROM pg_class c, | |
pg_attribute a, | |
pg_type t, | |
pg_namespace n | |
WHERE c.relkind IN('r','v','f', 'p') | |
AND t.typname = 'geometry' | |
AND a.attisdropped = false | |
AND a.atttypid = t.oid | |
AND a.attrelid = c.oid | |
AND c.relnamespace = n.oid | |
AND n.nspname NOT ILIKE 'pg_temp%' AND c.relname != 'raster_columns' ; | |
-- Iterate through all non-dropped geometry columns | |
RAISE DEBUG 'Processing Tables.....'; | |
FOR gcs IN | |
SELECT DISTINCT ON (c.oid) c.oid, n.nspname, c.relname | |
FROM pg_class c, | |
pg_attribute a, | |
pg_type t, | |
pg_namespace n | |
WHERE c.relkind IN( 'r', 'f', 'p') | |
AND t.typname = 'geometry' | |
AND a.attisdropped = false | |
AND a.atttypid = t.oid | |
AND a.attrelid = c.oid | |
AND c.relnamespace = n.oid | |
AND n.nspname NOT ILIKE 'pg_temp%' AND c.relname != 'raster_columns' | |
LOOP | |
inserted := inserted + @extschema@.populate_geometry_columns(gcs.oid, use_typmod); | |
END LOOP; | |
IF oldcount > inserted THEN | |
stale = oldcount-inserted; | |
ELSE | |
stale = 0; | |
END IF; | |
RETURN 'probed:' ||probed|| ' inserted:'||inserted; | |
END | |
$$ | |
LANGUAGE 'plpgsql' VOLATILE; | |
----------------------------------------------------------------------- | |
-- POPULATE_GEOMETRY_COLUMNS(tbl_oid oid) | |
----------------------------------------------------------------------- | |
-- DELETEs from and reINSERTs into the geometry_columns table all entries | |
-- associated with the oid of a particular table or view. | |
-- | |
-- If the provided oid is for a table, this function tries to determine | |
-- the srid, dimension, and geometry type of the all geometries | |
-- in the table, adding contraints as necessary to the table. If | |
-- successful, an appropriate row is inserted into the geometry_columns | |
-- table, otherwise, the exception is caught and an error notice is | |
-- raised describing the problem. (This is so the wrapper function | |
-- populate_geometry_columns() can apply spatial constraints to all | |
-- geometry columns across an entire database at once without erroring | |
-- out) | |
-- | |
-- If the provided oid is for a view, as with a table oid, this function | |
-- tries to determine the srid, dimension, and type of all the geometries | |
-- in the view, inserting appropriate entries into the geometry_columns | |
-- table. | |
-- Availability: 1.4.0 | |
----------------------------------------------------------------------- | |
CREATE OR REPLACE FUNCTION populate_geometry_columns(tbl_oid oid, use_typmod boolean DEFAULT true) | |
RETURNS integer AS | |
$$ | |
DECLARE | |
gcs RECORD; | |
gc RECORD; | |
gc_old RECORD; | |
gsrid integer; | |
gndims integer; | |
gtype text; | |
query text; | |
gc_is_valid boolean; | |
inserted integer; | |
constraint_successful boolean := false; | |
BEGIN | |
inserted := 0; | |
-- Iterate through all geometry columns in this table | |
FOR gcs IN | |
SELECT n.nspname, c.relname, a.attname, c.relkind | |
FROM pg_class c, | |
pg_attribute a, | |
pg_type t, | |
pg_namespace n | |
WHERE c.relkind IN('r', 'f', 'p') | |
AND t.typname = 'geometry' | |
AND a.attisdropped = false | |
AND a.atttypid = t.oid | |
AND a.attrelid = c.oid | |
AND c.relnamespace = n.oid | |
AND n.nspname NOT ILIKE 'pg_temp%' | |
AND c.oid = tbl_oid | |
LOOP | |
RAISE DEBUG 'Processing column %.%.%', gcs.nspname, gcs.relname, gcs.attname; | |
gc_is_valid := true; | |
-- Find the srid, coord_dimension, and type of current geometry | |
-- in geometry_columns -- which is now a view | |
SELECT type, srid, coord_dimension, gcs.relkind INTO gc_old | |
FROM geometry_columns | |
WHERE f_table_schema = gcs.nspname AND f_table_name = gcs.relname AND f_geometry_column = gcs.attname; | |
IF upper(gc_old.type) = 'GEOMETRY' THEN | |
-- This is an unconstrained geometry we need to do something | |
-- We need to figure out what to set the type by inspecting the data | |
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 ' || | |
' FROM ONLY ' || quote_ident(gcs.nspname) || '.' || quote_ident(gcs.relname) || | |
' WHERE ' || quote_ident(gcs.attname) || ' IS NOT NULL LIMIT 1;' | |
INTO gc; | |
IF gc IS NULL THEN -- there is no data so we can not determine geometry type | |
RAISE WARNING 'No data in table %.%, so no information to determine geometry type and srid', gcs.nspname, gcs.relname; | |
RETURN 0; | |
END IF; | |
gsrid := gc.srid; gtype := gc.type; gndims := gc.dims; | |
IF use_typmod THEN | |
BEGIN | |
EXECUTE 'ALTER TABLE ' || quote_ident(gcs.nspname) || '.' || quote_ident(gcs.relname) || ' ALTER COLUMN ' || quote_ident(gcs.attname) || | |
' TYPE geometry(' || postgis_type_name(gtype, gndims, true) || ', ' || gsrid::text || ') '; | |
inserted := inserted + 1; | |
EXCEPTION | |
WHEN invalid_parameter_value OR feature_not_supported THEN | |
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; | |
gc_is_valid := false; | |
END; | |
ELSE | |
-- Try to apply srid check to column | |
constraint_successful = false; | |
IF (gsrid > 0 AND postgis_constraint_srid(gcs.nspname, gcs.relname,gcs.attname) IS NULL ) THEN | |
BEGIN | |
EXECUTE 'ALTER TABLE ONLY ' || quote_ident(gcs.nspname) || '.' || quote_ident(gcs.relname) || | |
' ADD CONSTRAINT ' || quote_ident('enforce_srid_' || gcs.attname) || | |
' CHECK (ST_srid(' || quote_ident(gcs.attname) || ') = ' || gsrid || ')'; | |
constraint_successful := true; | |
EXCEPTION | |
WHEN check_violation THEN | |
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; | |
gc_is_valid := false; | |
END; | |
END IF; | |
-- Try to apply ndims check to column | |
IF (gndims IS NOT NULL AND postgis_constraint_dims(gcs.nspname, gcs.relname,gcs.attname) IS NULL ) THEN | |
BEGIN | |
EXECUTE 'ALTER TABLE ONLY ' || quote_ident(gcs.nspname) || '.' || quote_ident(gcs.relname) || ' | |
ADD CONSTRAINT ' || quote_ident('enforce_dims_' || gcs.attname) || ' | |
CHECK (st_ndims(' || quote_ident(gcs.attname) || ') = '||gndims||')'; | |
constraint_successful := true; | |
EXCEPTION | |
WHEN check_violation THEN | |
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; | |
gc_is_valid := false; | |
END; | |
END IF; | |
-- Try to apply geometrytype check to column | |
IF (gtype IS NOT NULL AND postgis_constraint_type(gcs.nspname, gcs.relname,gcs.attname) IS NULL ) THEN | |
BEGIN | |
EXECUTE 'ALTER TABLE ONLY ' || quote_ident(gcs.nspname) || '.' || quote_ident(gcs.relname) || ' | |
ADD CONSTRAINT ' || quote_ident('enforce_geotype_' || gcs.attname) || ' | |
CHECK (geometrytype(' || quote_ident(gcs.attname) || ') = ' || quote_literal(gtype) || ')'; | |
constraint_successful := true; | |
EXCEPTION | |
WHEN check_violation THEN | |
-- No geometry check can be applied. This column contains a number of geometry types. | |
RAISE WARNING 'Could not add geometry type check (%) to table column: %.%.%', gtype, quote_ident(gcs.nspname),quote_ident(gcs.relname),quote_ident(gcs.attname); | |
END; | |
END IF; | |
--only count if we were successful in applying at least one constraint | |
IF constraint_successful THEN | |
inserted := inserted + 1; | |
END IF; | |
END IF; | |
END IF; | |
END LOOP; | |
RETURN inserted; | |
END | |
$$ | |
LANGUAGE 'plpgsql' VOLATILE; | |
----------------------------------------------------------------------- | |
-- ADDGEOMETRYCOLUMN | |
-- <catalogue>, <schema>, <table>, <column>, <srid>, <type>, <dim> | |
----------------------------------------------------------------------- | |
-- | |
-- Type can be one of GEOMETRY, GEOMETRYCOLLECTION, POINT, MULTIPOINT, POLYGON, | |
-- MULTIPOLYGON, LINESTRING, or MULTILINESTRING. | |
-- | |
-- Geometry types (except GEOMETRY) are checked for consistency using a CHECK constraint. | |
-- Uses an ALTER TABLE command to add the geometry column to the table. | |
-- Addes a row to geometry_columns. | |
-- Addes a constraint on the table that all the geometries MUST have the same | |
-- SRID. Checks the coord_dimension to make sure its between 0 and 3. | |
-- Should also check the precision grid (future expansion). | |
-- | |
----------------------------------------------------------------------- | |
CREATE 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) | |
RETURNS text | |
AS | |
$$ | |
DECLARE | |
rec RECORD; | |
sr varchar; | |
real_schema name; | |
sql text; | |
new_srid integer; | |
BEGIN | |
-- Verify geometry type | |
IF (postgis_type_name(new_type,new_dim) IS NULL ) | |
THEN | |
RAISE EXCEPTION 'Invalid type name "%(%)" - valid ones are: | |
POINT, MULTIPOINT, | |
LINESTRING, MULTILINESTRING, | |
POLYGON, MULTIPOLYGON, | |
CIRCULARSTRING, COMPOUNDCURVE, MULTICURVE, | |
CURVEPOLYGON, MULTISURFACE, | |
GEOMETRY, GEOMETRYCOLLECTION, | |
POINTM, MULTIPOINTM, | |
LINESTRINGM, MULTILINESTRINGM, | |
POLYGONM, MULTIPOLYGONM, | |
CIRCULARSTRINGM, COMPOUNDCURVEM, MULTICURVEM | |
CURVEPOLYGONM, MULTISURFACEM, TRIANGLE, TRIANGLEM, | |
POLYHEDRALSURFACE, POLYHEDRALSURFACEM, TIN, TINM | |
or GEOMETRYCOLLECTIONM', new_type, new_dim; | |
RETURN 'fail'; | |
END IF; | |
-- Verify dimension | |
IF ( (new_dim >4) OR (new_dim <2) ) THEN | |
RAISE EXCEPTION 'invalid dimension'; | |
RETURN 'fail'; | |
END IF; | |
IF ( (new_type LIKE '%M') AND (new_dim!=3) ) THEN | |
RAISE EXCEPTION 'TypeM needs 3 dimensions'; | |
RETURN 'fail'; | |
END IF; | |
-- Verify SRID | |
IF ( new_srid_in > 0 ) THEN | |
IF new_srid_in > SRID_USR_MAX THEN | |
RAISE EXCEPTION 'AddGeometryColumn() - SRID must be <= %', SRID_USR_MAX; | |
END IF; | |
new_srid := new_srid_in; | |
SELECT SRID INTO sr FROM spatial_ref_sys WHERE SRID = new_srid; | |
IF NOT FOUND THEN | |
RAISE EXCEPTION 'AddGeometryColumn() - invalid SRID'; | |
RETURN 'fail'; | |
END IF; | |
ELSE | |
new_srid := @extschema@.ST_SRID('POINT EMPTY'::@extschema@.geometry); | |
IF ( new_srid_in != new_srid ) THEN | |
RAISE NOTICE 'SRID value % converted to the officially unknown SRID value %', new_srid_in, new_srid; | |
END IF; | |
END IF; | |
-- Verify schema | |
IF ( schema_name IS NOT NULL AND schema_name != '' ) THEN | |
sql := 'SELECT nspname FROM pg_namespace ' || | |
'WHERE text(nspname) = ' || quote_literal(schema_name) || | |
'LIMIT 1'; | |
RAISE DEBUG '%', sql; | |
EXECUTE sql INTO real_schema; | |
IF ( real_schema IS NULL ) THEN | |
RAISE EXCEPTION 'Schema % is not a valid schemaname', quote_literal(schema_name); | |
RETURN 'fail'; | |
END IF; | |
END IF; | |
IF ( real_schema IS NULL ) THEN | |
RAISE DEBUG 'Detecting schema'; | |
sql := 'SELECT n.nspname AS schemaname ' || | |
'FROM pg_catalog.pg_class c ' || | |
'JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace ' || | |
'WHERE c.relkind = ' || quote_literal('r') || | |
' AND n.nspname NOT IN (' || quote_literal('pg_catalog') || ', ' || quote_literal('pg_toast') || ')' || | |
' AND pg_catalog.pg_table_is_visible(c.oid)' || | |
' AND c.relname = ' || quote_literal(table_name); | |
RAISE DEBUG '%', sql; | |
EXECUTE sql INTO real_schema; | |
IF ( real_schema IS NULL ) THEN | |
RAISE EXCEPTION 'Table % does not occur in the search_path', quote_literal(table_name); | |
RETURN 'fail'; | |
END IF; | |
END IF; | |
-- Add geometry column to table | |
IF use_typmod THEN | |
sql := 'ALTER TABLE ' || | |
quote_ident(real_schema) || '.' || quote_ident(table_name) | |
|| ' ADD COLUMN ' || quote_ident(column_name) || | |
' geometry(' || @extschema@.postgis_type_name(new_type, new_dim) || ', ' || new_srid::text || ')'; | |
RAISE DEBUG '%', sql; | |
ELSE | |
sql := 'ALTER TABLE ' || | |
quote_ident(real_schema) || '.' || quote_ident(table_name) | |
|| ' ADD COLUMN ' || quote_ident(column_name) || | |
' geometry '; | |
RAISE DEBUG '%', sql; | |
END IF; | |
EXECUTE sql; | |
IF NOT use_typmod THEN | |
-- Add table CHECKs | |
sql := 'ALTER TABLE ' || | |
quote_ident(real_schema) || '.' || quote_ident(table_name) | |
|| ' ADD CONSTRAINT ' | |
|| quote_ident('enforce_srid_' || column_name) | |
|| ' CHECK (st_srid(' || quote_ident(column_name) || | |
') = ' || new_srid::text || ')' ; | |
RAISE DEBUG '%', sql; | |
EXECUTE sql; | |
sql := 'ALTER TABLE ' || | |
quote_ident(real_schema) || '.' || quote_ident(table_name) | |
|| ' ADD CONSTRAINT ' | |
|| quote_ident('enforce_dims_' || column_name) | |
|| ' CHECK (st_ndims(' || quote_ident(column_name) || | |
') = ' || new_dim::text || ')' ; | |
RAISE DEBUG '%', sql; | |
EXECUTE sql; | |
IF ( NOT (new_type = 'GEOMETRY')) THEN | |
sql := 'ALTER TABLE ' || | |
quote_ident(real_schema) || '.' || quote_ident(table_name) || ' ADD CONSTRAINT ' || | |
quote_ident('enforce_geotype_' || column_name) || | |
' CHECK (GeometryType(' || | |
quote_ident(column_name) || ')=' || | |
quote_literal(new_type) || ' OR (' || | |
quote_ident(column_name) || ') is null)'; | |
RAISE DEBUG '%', sql; | |
EXECUTE sql; | |
END IF; | |
END IF; | |
RETURN | |
real_schema || '.' || | |
table_name || '.' || column_name || | |
' SRID:' || new_srid::text || | |
' TYPE:' || new_type || | |
' DIMS:' || new_dim::text || ' '; | |
END; | |
$$ | |
LANGUAGE 'plpgsql' VOLATILE STRICT; | |
---------------------------------------------------------------------------- | |
-- ADDGEOMETRYCOLUMN ( <schema>, <table>, <column>, <srid>, <type>, <dim> ) | |
---------------------------------------------------------------------------- | |
-- | |
-- This is a wrapper to the real AddGeometryColumn, for use | |
-- when catalogue is undefined | |
-- | |
---------------------------------------------------------------------------- | |
CREATE 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 $$ | |
DECLARE | |
ret text; | |
BEGIN | |
SELECT @extschema@.AddGeometryColumn('',$1,$2,$3,$4,$5,$6,$7) into ret; | |
RETURN ret; | |
END; | |
$$ | |
LANGUAGE 'plpgsql' STABLE STRICT; | |
---------------------------------------------------------------------------- | |
-- ADDGEOMETRYCOLUMN ( <table>, <column>, <srid>, <type>, <dim> ) | |
---------------------------------------------------------------------------- | |
-- | |
-- This is a wrapper to the real AddGeometryColumn, for use | |
-- when catalogue and schema are undefined | |
-- | |
---------------------------------------------------------------------------- | |
CREATE 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 $$ | |
DECLARE | |
ret text; | |
BEGIN | |
SELECT @extschema@.AddGeometryColumn('','',$1,$2,$3,$4,$5, $6) into ret; | |
RETURN ret; | |
END; | |
$$ | |
LANGUAGE 'plpgsql' VOLATILE STRICT; | |
----------------------------------------------------------------------- | |
-- DROPGEOMETRYCOLUMN | |
-- <catalogue>, <schema>, <table>, <column> | |
----------------------------------------------------------------------- | |
-- | |
-- Removes geometry column reference from geometry_columns table. | |
-- Drops the column with pgsql >= 73. | |
-- Make some silly enforcements on it for pgsql < 73 | |
-- | |
----------------------------------------------------------------------- | |
CREATE OR REPLACE FUNCTION DropGeometryColumn(catalog_name varchar, schema_name varchar,table_name varchar,column_name varchar) | |
RETURNS text | |
AS | |
$$ | |
DECLARE | |
myrec RECORD; | |
okay boolean; | |
real_schema name; | |
BEGIN | |
-- Find, check or fix schema_name | |
IF ( schema_name != '' ) THEN | |
okay = false; | |
FOR myrec IN SELECT nspname FROM pg_namespace WHERE text(nspname) = schema_name LOOP | |
okay := true; | |
END LOOP; | |
IF ( okay <> true ) THEN | |
RAISE NOTICE 'Invalid schema name - using current_schema()'; | |
SELECT current_schema() into real_schema; | |
ELSE | |
real_schema = schema_name; | |
END IF; | |
ELSE | |
SELECT current_schema() into real_schema; | |
END IF; | |
-- Find out if the column is in the geometry_columns table | |
okay = false; | |
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 | |
okay := true; | |
END LOOP; | |
IF (okay <> true) THEN | |
RAISE EXCEPTION 'column not found in geometry_columns table'; | |
RETURN false; | |
END IF; | |
-- Remove table column | |
EXECUTE 'ALTER TABLE ' || quote_ident(real_schema) || '.' || | |
quote_ident(table_name) || ' DROP COLUMN ' || | |
quote_ident(column_name); | |
RETURN real_schema || '.' || table_name || '.' || column_name ||' effectively removed.'; | |
END; | |
$$ | |
LANGUAGE 'plpgsql' VOLATILE STRICT; | |
----------------------------------------------------------------------- | |
-- DROPGEOMETRYCOLUMN | |
-- <schema>, <table>, <column> | |
----------------------------------------------------------------------- | |
-- | |
-- This is a wrapper to the real DropGeometryColumn, for use | |
-- when catalogue is undefined | |
-- | |
----------------------------------------------------------------------- | |
CREATE OR REPLACE FUNCTION DropGeometryColumn(schema_name varchar, table_name varchar,column_name varchar) | |
RETURNS text | |
AS | |
$$ | |
DECLARE | |
ret text; | |
BEGIN | |
SELECT @extschema@.DropGeometryColumn('',$1,$2,$3) into ret; | |
RETURN ret; | |
END; | |
$$ | |
LANGUAGE 'plpgsql' VOLATILE STRICT; | |
----------------------------------------------------------------------- | |
-- DROPGEOMETRYCOLUMN | |
-- <table>, <column> | |
----------------------------------------------------------------------- | |
-- | |
-- This is a wrapper to the real DropGeometryColumn, for use | |
-- when catalogue and schema is undefined. | |
-- | |
----------------------------------------------------------------------- | |
CREATE OR REPLACE FUNCTION DropGeometryColumn(table_name varchar, column_name varchar) | |
RETURNS text | |
AS | |
$$ | |
DECLARE | |
ret text; | |
BEGIN | |
SELECT @extschema@.DropGeometryColumn('','',$1,$2) into ret; | |
RETURN ret; | |
END; | |
$$ | |
LANGUAGE 'plpgsql' VOLATILE STRICT; | |
----------------------------------------------------------------------- | |
-- DROPGEOMETRYTABLE | |
-- <catalogue>, <schema>, <table> | |
----------------------------------------------------------------------- | |
-- | |
-- Drop a table and all its references in geometry_columns | |
-- | |
----------------------------------------------------------------------- | |
CREATE OR REPLACE FUNCTION DropGeometryTable(catalog_name varchar, schema_name varchar, table_name varchar) | |
RETURNS text | |
AS | |
$$ | |
DECLARE | |
real_schema name; | |
BEGIN | |
IF ( schema_name = '' ) THEN | |
SELECT current_schema() into real_schema; | |
ELSE | |
real_schema = schema_name; | |
END IF; | |
-- TODO: Should we warn if table doesn't exist probably instead just saying dropped | |
-- Remove table | |
EXECUTE 'DROP TABLE IF EXISTS ' | |
|| quote_ident(real_schema) || '.' || | |
quote_ident(table_name) || ' RESTRICT'; | |
RETURN | |
real_schema || '.' || | |
table_name ||' dropped.'; | |
END; | |
$$ | |
LANGUAGE 'plpgsql' VOLATILE STRICT; | |
----------------------------------------------------------------------- | |
-- DROPGEOMETRYTABLE | |
-- <schema>, <table> | |
----------------------------------------------------------------------- | |
-- | |
-- Drop a table and all its references in geometry_columns | |
-- | |
----------------------------------------------------------------------- | |
CREATE OR REPLACE FUNCTION DropGeometryTable(schema_name varchar, table_name varchar) RETURNS text AS | |
$$ SELECT @extschema@.DropGeometryTable('',$1,$2) $$ | |
LANGUAGE 'sql' VOLATILE STRICT; | |
----------------------------------------------------------------------- | |
-- DROPGEOMETRYTABLE | |
-- <table> | |
----------------------------------------------------------------------- | |
-- | |
-- Drop a table and all its references in geometry_columns | |
-- For PG>=73 use current_schema() | |
-- | |
----------------------------------------------------------------------- | |
CREATE OR REPLACE FUNCTION DropGeometryTable(table_name varchar) RETURNS text AS | |
$$ SELECT @extschema@.DropGeometryTable('','',$1) $$ | |
LANGUAGE 'sql' VOLATILE STRICT; | |
----------------------------------------------------------------------- | |
-- UPDATEGEOMETRYSRID | |
-- <catalogue>, <schema>, <table>, <column>, <srid> | |
----------------------------------------------------------------------- | |
-- | |
-- Change SRID of all features in a spatially-enabled table | |
-- | |
----------------------------------------------------------------------- | |
-- Changed: 2.1.4 check against real_schema | |
CREATE OR REPLACE FUNCTION UpdateGeometrySRID(catalogn_name varchar,schema_name varchar,table_name varchar,column_name varchar,new_srid_in integer) | |
RETURNS text | |
AS | |
$$ | |
DECLARE | |
myrec RECORD; | |
okay boolean; | |
cname varchar; | |
real_schema name; | |
unknown_srid integer; | |
new_srid integer := new_srid_in; | |
BEGIN | |
-- Find, check or fix schema_name | |
IF ( schema_name != '' ) THEN | |
okay = false; | |
FOR myrec IN SELECT nspname FROM pg_namespace WHERE text(nspname) = schema_name LOOP | |
okay := true; | |
END LOOP; | |
IF ( okay <> true ) THEN | |
RAISE EXCEPTION 'Invalid schema name'; | |
ELSE | |
real_schema = schema_name; | |
END IF; | |
ELSE | |
SELECT INTO real_schema current_schema()::text; | |
END IF; | |
-- Ensure that column_name is in geometry_columns | |
okay = false; | |
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 | |
okay := true; | |
END LOOP; | |
IF (NOT okay) THEN | |
RAISE EXCEPTION 'column not found in geometry_columns table'; | |
RETURN false; | |
END IF; | |
-- Ensure that new_srid is valid | |
IF ( new_srid > 0 ) THEN | |
IF ( SELECT count(*) = 0 from spatial_ref_sys where srid = new_srid ) THEN | |
RAISE EXCEPTION 'invalid SRID: % not found in spatial_ref_sys', new_srid; | |
RETURN false; | |
END IF; | |
ELSE | |
unknown_srid := @extschema@.ST_SRID('POINT EMPTY'::@extschema@.geometry); | |
IF ( new_srid != unknown_srid ) THEN | |
new_srid := unknown_srid; | |
RAISE NOTICE 'SRID value % converted to the officially unknown SRID value %', new_srid_in, new_srid; | |
END IF; | |
END IF; | |
IF postgis_constraint_srid(real_schema, table_name, column_name) IS NOT NULL THEN | |
-- srid was enforced with constraints before, keep it that way. | |
-- Make up constraint name | |
cname = 'enforce_srid_' || column_name; | |
-- Drop enforce_srid constraint | |
EXECUTE 'ALTER TABLE ' || quote_ident(real_schema) || | |
'.' || quote_ident(table_name) || | |
' DROP constraint ' || quote_ident(cname); | |
-- Update geometries SRID | |
EXECUTE 'UPDATE ' || quote_ident(real_schema) || | |
'.' || quote_ident(table_name) || | |
' SET ' || quote_ident(column_name) || | |
' = @extschema@.ST_SetSRID(' || quote_ident(column_name) || | |
', ' || new_srid::text || ')'; | |
-- Reset enforce_srid constraint | |
EXECUTE 'ALTER TABLE ' || quote_ident(real_schema) || | |
'.' || quote_ident(table_name) || | |
' ADD constraint ' || quote_ident(cname) || | |
' CHECK (st_srid(' || quote_ident(column_name) || | |
') = ' || new_srid::text || ')'; | |
ELSE | |
-- We will use typmod to enforce if no srid constraints | |
-- We are using postgis_type_name to lookup the new name | |
-- (in case Paul changes his mind and flips geometry_columns to return old upper case name) | |
EXECUTE 'ALTER TABLE ' || quote_ident(real_schema) || '.' || quote_ident(table_name) || | |
' 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 || ');' ; | |
END IF; | |
RETURN real_schema || '.' || table_name || '.' || column_name ||' SRID changed to ' || new_srid::text; | |
END; | |
$$ | |
LANGUAGE 'plpgsql' VOLATILE STRICT; | |
----------------------------------------------------------------------- | |
-- UPDATEGEOMETRYSRID | |
-- <schema>, <table>, <column>, <srid> | |
----------------------------------------------------------------------- | |
CREATE OR REPLACE FUNCTION UpdateGeometrySRID(varchar,varchar,varchar,integer) | |
RETURNS text | |
AS $$ | |
DECLARE | |
ret text; | |
BEGIN | |
SELECT @extschema@.UpdateGeometrySRID('',$1,$2,$3,$4) into ret; | |
RETURN ret; | |
END; | |
$$ | |
LANGUAGE 'plpgsql' VOLATILE STRICT; | |
----------------------------------------------------------------------- | |
-- UPDATEGEOMETRYSRID | |
-- <table>, <column>, <srid> | |
----------------------------------------------------------------------- | |
CREATE OR REPLACE FUNCTION UpdateGeometrySRID(varchar,varchar,integer) | |
RETURNS text | |
AS $$ | |
DECLARE | |
ret text; | |
BEGIN | |
SELECT @extschema@.UpdateGeometrySRID('','',$1,$2,$3) into ret; | |
RETURN ret; | |
END; | |
$$ | |
LANGUAGE 'plpgsql' VOLATILE STRICT; | |
----------------------------------------------------------------------- | |
-- FIND_SRID( <schema>, <table>, <geom col> ) | |
----------------------------------------------------------------------- | |
-- Changed: 2.1.8 improve performance | |
CREATE OR REPLACE FUNCTION find_srid(varchar,varchar,varchar) RETURNS int4 AS | |
$$ | |
DECLARE | |
schem varchar = $1; | |
tabl varchar = $2; | |
sr int4; | |
BEGIN | |
-- if the table contains a . and the schema is empty | |
-- split the table into a schema and a table | |
-- otherwise drop through to default behavior | |
IF ( schem = '' and strpos(tabl,'.') > 0 ) THEN | |
schem = substr(tabl,1,strpos(tabl,'.')-1); | |
tabl = substr(tabl,length(schem)+2); | |
END IF; | |
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; | |
IF NOT FOUND THEN | |
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?'; | |
END IF; | |
return sr; | |
END; | |
$$ | |
LANGUAGE 'plpgsql' STABLE STRICT PARALLEL SAFE; | |
--------------------------------------------------------------- | |
-- PROJ support | |
--------------------------------------------------------------- | |
CREATE OR REPLACE FUNCTION get_proj4_from_srid(integer) RETURNS text AS | |
$$ | |
BEGIN | |
RETURN proj4text::text FROM @extschema@.spatial_ref_sys WHERE srid= $1; | |
END; | |
$$ | |
LANGUAGE 'plpgsql' IMMUTABLE STRICT PARALLEL SAFE; | |
-- Availability: 1.2.2 | |
CREATE OR REPLACE FUNCTION ST_SetSRID(geom geometry, srid int4) | |
RETURNS geometry | |
AS 'MODULE_PATHNAME','LWGEOM_set_srid' | |
LANGUAGE 'c' IMMUTABLE STRICT PARALLEL SAFE | |
_COST_DEFAULT; | |
CREATE OR REPLACE FUNCTION ST_SRID(geom geometry) | |
RETURNS int4 | |
AS 'MODULE_PATHNAME','LWGEOM_get_srid' | |
LANGUAGE 'c' IMMUTABLE STRICT PARALLEL SAFE | |
_COST_DEFAULT; | |
CREATE OR REPLACE FUNCTION postgis_transform_geometry(geom geometry, text, text, int) | |
RETURNS geometry | |
AS 'MODULE_PATHNAME','transform_geom' | |
LANGUAGE 'c' IMMUTABLE STRICT PARALLEL SAFE | |
_COST_MEDIUM; | |
-- PostGIS equivalent of old function: transform(geometry,integer) | |
CREATE OR REPLACE FUNCTION ST_Transform(geometry,integer) | |
RETURNS geometry | |
AS 'MODULE_PATHNAME','transform' | |
LANGUAGE 'c' IMMUTABLE STRICT PARALLEL SAFE | |
_COST_HIGH; | |
-- Availability: 2.3.0 | |
CREATE OR REPLACE FUNCTION ST_Transform(geom geometry, to_proj text) | |
RETURNS geometry AS | |
'SELECT @extschema@.postgis_transform_geometry($1, proj4text, $2, 0) | |
FROM spatial_ref_sys WHERE srid=@extschema@.ST_SRID($1);' | |
LANGUAGE 'sql' IMMUTABLE STRICT PARALLEL SAFE | |
_COST_HIGH; | |
-- Availability: 2.3.0 | |
CREATE OR REPLACE FUNCTION ST_Transform(geom geometry, from_proj text, to_proj text) | |
RETURNS geometry AS | |
'SELECT @extschema@.postgis_transform_geometry($1, $2, $3, 0)' | |
LANGUAGE 'sql' IMMUTABLE STRICT PARALLEL SAFE | |
_COST_HIGH; | |
-- Availability: 2.3.0 | |
CREATE OR REPLACE FUNCTION ST_Transform(geom geometry, from_proj text, to_srid integer) | |
RETURNS geometry AS | |
'SELECT @extschema@.postgis_transform_geometry($1, $2, proj4text, $3) | |
FROM spatial_ref_sys WHERE srid=$3;' | |
LANGUAGE 'sql' IMMUTABLE STRICT PARALLEL SAFE | |
_COST_HIGH; | |
----------------------------------------------------------------------- | |
-- POSTGIS_VERSION() | |
----------------------------------------------------------------------- | |
CREATE OR REPLACE FUNCTION postgis_version() RETURNS text | |
AS 'MODULE_PATHNAME' | |
LANGUAGE 'c' IMMUTABLE | |
_COST_DEFAULT; | |
CREATE OR REPLACE FUNCTION postgis_liblwgeom_version() RETURNS text | |
AS 'MODULE_PATHNAME' | |
LANGUAGE 'c' IMMUTABLE | |
_COST_DEFAULT; | |
CREATE OR REPLACE FUNCTION postgis_proj_version() RETURNS text | |
AS 'MODULE_PATHNAME' | |
LANGUAGE 'c' IMMUTABLE | |
_COST_DEFAULT; | |
CREATE OR REPLACE FUNCTION postgis_wagyu_version() RETURNS text | |
AS 'MODULE_PATHNAME' | |
LANGUAGE 'c' IMMUTABLE | |
_COST_DEFAULT; | |
-- | |
-- IMPORTANT: | |
-- Starting at 1.1.0 this function is used by postgis_proc_upgrade.pl | |
-- to extract version of postgis being installed. | |
-- Do not modify this w/out also changing postgis_proc_upgrade.pl | |
-- | |
CREATE OR REPLACE FUNCTION postgis_scripts_installed() RETURNS text | |
AS _POSTGIS_SQL_SELECT_POSTGIS_SCRIPTS_VERSION | |
LANGUAGE 'sql' IMMUTABLE; | |
CREATE OR REPLACE FUNCTION postgis_lib_version() RETURNS text | |
AS 'MODULE_PATHNAME' | |
LANGUAGE 'c' IMMUTABLE; -- a new lib will require a new session | |
-- NOTE: from 1.1.0 to 1.5.x this was the same of postgis_lib_version() | |
-- NOTE: from 2.0.0 up it includes postgis revision | |
CREATE OR REPLACE FUNCTION postgis_scripts_released() RETURNS text | |
AS 'MODULE_PATHNAME' | |
LANGUAGE 'c' IMMUTABLE; | |
CREATE OR REPLACE FUNCTION postgis_geos_version() RETURNS text | |
AS 'MODULE_PATHNAME' | |
LANGUAGE 'c' IMMUTABLE; | |
--- Availability: 3.1.0 | |
CREATE OR REPLACE FUNCTION postgis_lib_revision() RETURNS text | |
AS 'MODULE_PATHNAME' | |
LANGUAGE 'c' IMMUTABLE; | |
--- Availability: 2.0.0 | |
--- Deprecation in 3.1.0 | |
CREATE OR REPLACE FUNCTION postgis_svn_version() | |
RETURNS text AS $$ | |
SELECT @extschema@._postgis_deprecate( | |
'postgis_svn_version', 'postgis_lib_revision', '3.1.0'); | |
SELECT @extschema@.postgis_lib_revision(); | |
$$ | |
LANGUAGE 'sql' IMMUTABLE SECURITY INVOKER; | |
CREATE OR REPLACE FUNCTION postgis_libxml_version() RETURNS text | |
AS 'MODULE_PATHNAME' | |
LANGUAGE 'c' IMMUTABLE; | |
CREATE OR REPLACE FUNCTION postgis_scripts_build_date() RETURNS text | |
AS _POSTGIS_SQL_SELECT_POSTGIS_BUILD_DATE | |
LANGUAGE 'sql' IMMUTABLE; | |
CREATE OR REPLACE FUNCTION postgis_lib_build_date() RETURNS text | |
AS 'MODULE_PATHNAME' | |
LANGUAGE 'c' IMMUTABLE; | |
CREATE OR REPLACE FUNCTION _postgis_scripts_pgsql_version() RETURNS text | |
AS _POSTGIS_SQL_SELECT_POSTGIS_PGSQL_VERSION | |
LANGUAGE 'sql' IMMUTABLE; | |
CREATE OR REPLACE FUNCTION _postgis_pgsql_version() RETURNS text | |
AS $$ | |
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 | |
FROM substring(version(), 'PostgreSQL ([0-9\.]+)') AS s; | |
$$ LANGUAGE 'sql' STABLE; | |
-- Availability: 2.5.0 | |
-- Changed: 3.0.1 install from unpackaged should include postgis schema #4581 | |
-- Changed: 3.0.0 also upgrade postgis_raster if it exists | |
CREATE OR REPLACE FUNCTION postgis_extensions_upgrade() RETURNS text | |
AS $$ | |
DECLARE rec record; sql text; var_schema text; | |
BEGIN | |
FOR rec IN | |
SELECT name, default_version, installed_version | |
FROM pg_catalog.pg_available_extensions | |
WHERE name IN ( | |
'postgis', | |
'postgis_raster', | |
'postgis_sfcgal', | |
'postgis_topology', | |
'postgis_tiger_geocoder' | |
) | |
ORDER BY length(name) -- this is to make sure 'postgis' is first ! | |
LOOP | |
IF rec.installed_version IS NULL THEN | |
-- If the support installed by available extension | |
-- is found unpackaged, we package it | |
IF | |
-- PostGIS is always available (this function is part of it) | |
rec.name = 'postgis' | |
-- PostGIS raster is available if type 'raster' exists | |
OR ( rec.name = 'postgis_raster' AND EXISTS ( | |
SELECT 1 FROM pg_catalog.pg_type | |
WHERE typname = 'raster' ) ) | |
-- PostGIS SFCGAL is availble if | |
-- 'postgis_sfcgal_version' function exists | |
OR ( rec.name = 'postgis_sfcgal' AND EXISTS ( | |
SELECT 1 FROM pg_catalog.pg_proc | |
WHERE proname = 'postgis_sfcgal_version' ) ) | |
-- PostGIS Topology is available if | |
-- 'topology.topology' table exists | |
-- NOTE: watch out for https://trac.osgeo.org/postgis/ticket/2503 | |
OR ( rec.name = 'postgis_topology' AND EXISTS ( | |
SELECT 1 FROM pg_catalog.pg_class c | |
JOIN pg_catalog.pg_namespace n ON (c.relnamespace = n.oid ) | |
WHERE n.nspname = 'topology' AND c.relname = 'topology') ) | |
OR ( rec.name = 'postgis_tiger_geocoder' AND EXISTS ( | |
SELECT 1 FROM pg_catalog.pg_class c | |
JOIN pg_catalog.pg_namespace n ON (c.relnamespace = n.oid ) | |
WHERE n.nspname = 'tiger' AND c.relname = 'geocode_settings') ) | |
THEN | |
-- Force install in same schema as postgis | |
SELECT INTO var_schema n.nspname | |
FROM pg_namespace n, pg_proc p | |
WHERE p.proname = 'postgis_full_version' | |
AND n.oid = p.pronamespace | |
LIMIT 1; | |
IF rec.name NOT IN('postgis_topology', 'postgis_tiger_geocoder') | |
THEN | |
sql := format( | |
'CREATE EXTENSION %1$I SCHEMA %2$I VERSION unpackaged;' | |
'ALTER EXTENSION %1$I UPDATE TO %3$I', | |
rec.name, var_schema, rec.default_version); | |
ELSE | |
sql := format( | |
'CREATE EXTENSION %1$I VERSION unpackaged;' | |
'ALTER EXTENSION %1$I UPDATE TO %2$I', | |
rec.name, rec.default_version); | |
END IF; | |
RAISE NOTICE 'Packaging extension %', rec.name; | |
RAISE DEBUG '%', sql; | |
EXECUTE sql; | |
ELSE | |
RAISE NOTICE 'Extension % is not available or not packagable for some reason', rec.name; | |
END IF; | |
ELSIF rec.default_version != rec.installed_version | |
THEN | |
sql = 'ALTER EXTENSION ' || rec.name || ' UPDATE TO ' || | |
quote_ident(rec.default_version) || ';'; | |
RAISE NOTICE 'Updating extension % from % to %', | |
rec.name, rec.installed_version, rec.default_version; | |
RAISE DEBUG '%', sql; | |
EXECUTE sql; | |
ELSIF (rec.default_version = rec.installed_version AND rec.installed_version ILIKE '%dev') OR | |
(@extschema@._postgis_pgsql_version() != @extschema@._postgis_scripts_pgsql_version()) | |
THEN | |
-- we need to upgrade to next and back | |
RAISE NOTICE 'Updating extension % %', | |
rec.name, rec.installed_version; | |
sql = 'ALTER EXTENSION ' || rec.name || ' UPDATE TO ' || | |
quote_ident(rec.default_version || 'next') || ';'; | |
RAISE DEBUG '%', sql; | |
EXECUTE sql; | |
sql = 'ALTER EXTENSION ' || rec.name || ' UPDATE TO ' || | |
quote_ident(rec.default_version) || ';'; | |
RAISE DEBUG '%', sql; | |
EXECUTE sql; | |
END IF; | |
END LOOP; | |
RETURN 'Upgrade completed, run SELECT postgis_full_version(); for details'; | |
END | |
$$ LANGUAGE plpgsql VOLATILE; | |
-- Changed: 3.0.0 | |
CREATE OR REPLACE FUNCTION postgis_full_version() RETURNS text | |
AS $$ | |
DECLARE | |
libver text; | |
librev text; | |
projver text; | |
geosver text; | |
sfcgalver text; | |
gdalver text := NULL; | |
libxmlver text; | |
liblwgeomver text; | |
dbproc text; | |
relproc text; | |
fullver text; | |
rast_lib_ver text := NULL; | |
rast_scr_ver text := NULL; | |
topo_scr_ver text := NULL; | |
json_lib_ver text; | |
protobuf_lib_ver text; | |
wagyu_lib_ver text; | |
sfcgal_lib_ver text; | |
sfcgal_scr_ver text; | |
pgsql_scr_ver text; | |
pgsql_ver text; | |
core_is_extension bool; | |
BEGIN | |
SELECT @extschema@.postgis_lib_version() INTO libver; | |
SELECT @extschema@.postgis_proj_version() INTO projver; | |
SELECT @extschema@.postgis_geos_version() INTO geosver; | |
SELECT @extschema@.postgis_libjson_version() INTO json_lib_ver; | |
SELECT @extschema@.postgis_libprotobuf_version() INTO protobuf_lib_ver; | |
SELECT @extschema@.postgis_wagyu_version() INTO wagyu_lib_ver; | |
SELECT @extschema@._postgis_scripts_pgsql_version() INTO pgsql_scr_ver; | |
SELECT @extschema@._postgis_pgsql_version() INTO pgsql_ver; | |
BEGIN | |
SELECT @extschema@.postgis_gdal_version() INTO gdalver; | |
EXCEPTION | |
WHEN undefined_function THEN | |
RAISE DEBUG 'Function postgis_gdal_version() not found. Is raster support enabled and rtpostgis.sql installed?'; | |
END; | |
BEGIN | |
SELECT @extschema@.postgis_sfcgal_version() INTO sfcgalver; | |
BEGIN | |
SELECT @extschema@.postgis_sfcgal_scripts_installed() INTO sfcgal_scr_ver; | |
EXCEPTION | |
WHEN undefined_function THEN | |
sfcgal_scr_ver := 'missing'; | |
END; | |
EXCEPTION | |
WHEN undefined_function THEN | |
RAISE DEBUG 'Function postgis_sfcgal_scripts_installed() not found. Is sfcgal support enabled and sfcgal.sql installed?'; | |
END; | |
SELECT @extschema@.postgis_liblwgeom_version() INTO liblwgeomver; | |
SELECT @extschema@.postgis_libxml_version() INTO libxmlver; | |
SELECT @extschema@.postgis_scripts_installed() INTO dbproc; | |
SELECT @extschema@.postgis_scripts_released() INTO relproc; | |
SELECT @extschema@.postgis_lib_revision() INTO librev; | |
BEGIN | |
SELECT topology.postgis_topology_scripts_installed() INTO topo_scr_ver; | |
EXCEPTION | |
WHEN undefined_function OR invalid_schema_name THEN | |
RAISE DEBUG 'Function postgis_topology_scripts_installed() not found. Is topology support enabled and topology.sql installed?'; | |
WHEN insufficient_privilege THEN | |
RAISE NOTICE 'Topology support cannot be inspected. Is current user granted USAGE on schema "topology" ?'; | |
WHEN OTHERS THEN | |
RAISE NOTICE 'Function postgis_topology_scripts_installed() could not be called: % (%)', SQLERRM, SQLSTATE; | |
END; | |
BEGIN | |
SELECT postgis_raster_scripts_installed() INTO rast_scr_ver; | |
EXCEPTION | |
WHEN undefined_function THEN | |
RAISE DEBUG 'Function postgis_raster_scripts_installed() not found. Is raster support enabled and rtpostgis.sql installed?'; | |
WHEN OTHERS THEN | |
RAISE NOTICE 'Function postgis_raster_scripts_installed() could not be called: % (%)', SQLERRM, SQLSTATE; | |
END; | |
BEGIN | |
SELECT @extschema@.postgis_raster_lib_version() INTO rast_lib_ver; | |
EXCEPTION | |
WHEN undefined_function THEN | |
RAISE DEBUG 'Function postgis_raster_lib_version() not found. Is raster support enabled and rtpostgis.sql installed?'; | |
WHEN OTHERS THEN | |
RAISE NOTICE 'Function postgis_raster_lib_version() could not be called: % (%)', SQLERRM, SQLSTATE; | |
END; | |
fullver = 'POSTGIS="' || libver; | |
IF librev IS NOT NULL THEN | |
fullver = fullver || ' ' || librev; | |
END IF; | |
fullver = fullver || '"'; | |
IF EXISTS ( | |
SELECT * FROM pg_catalog.pg_extension | |
WHERE extname = 'postgis') | |
THEN | |
fullver = fullver || ' [EXTENSION]'; | |
core_is_extension := true; | |
ELSE | |
core_is_extension := false; | |
END IF; | |
IF liblwgeomver != relproc THEN | |
fullver = fullver || ' (liblwgeom version mismatch: "' || liblwgeomver || '")'; | |
END IF; | |
fullver = fullver || ' PGSQL="' || pgsql_scr_ver || '"'; | |
IF pgsql_scr_ver != pgsql_ver THEN | |
fullver = fullver || ' (procs need upgrade for use with PostgreSQL "' || pgsql_ver || '")'; | |
END IF; | |
IF geosver IS NOT NULL THEN | |
fullver = fullver || ' GEOS="' || geosver || '"'; | |
END IF; | |
IF sfcgalver IS NOT NULL THEN | |
fullver = fullver || ' SFCGAL="' || sfcgalver || '"'; | |
END IF; | |
IF projver IS NOT NULL THEN | |
fullver = fullver || ' PROJ="' || projver || '"'; | |
END IF; | |
IF gdalver IS NOT NULL THEN | |
fullver = fullver || ' GDAL="' || gdalver || '"'; | |
END IF; | |
IF libxmlver IS NOT NULL THEN | |
fullver = fullver || ' LIBXML="' || libxmlver || '"'; | |
END IF; | |
IF json_lib_ver IS NOT NULL THEN | |
fullver = fullver || ' LIBJSON="' || json_lib_ver || '"'; | |
END IF; | |
IF protobuf_lib_ver IS NOT NULL THEN | |
fullver = fullver || ' LIBPROTOBUF="' || protobuf_lib_ver || '"'; | |
END IF; | |
IF wagyu_lib_ver IS NOT NULL THEN | |
fullver = fullver || ' WAGYU="' || wagyu_lib_ver || '"'; | |
END IF; | |
IF dbproc != relproc THEN | |
fullver = fullver || ' (core procs from "' || dbproc || '" need upgrade)'; | |
END IF; | |
IF topo_scr_ver IS NOT NULL THEN | |
fullver = fullver || ' TOPOLOGY'; | |
IF topo_scr_ver != relproc THEN | |
fullver = fullver || ' (topology procs from "' || topo_scr_ver || '" need upgrade)'; | |
END IF; | |
IF core_is_extension AND NOT EXISTS ( | |
SELECT * FROM pg_catalog.pg_extension | |
WHERE extname = 'postgis_topology') | |
THEN | |
fullver = fullver || ' [UNPACKAGED!]'; | |
END IF; | |
END IF; | |
IF rast_lib_ver IS NOT NULL THEN | |
fullver = fullver || ' RASTER'; | |
IF rast_lib_ver != relproc THEN | |
fullver = fullver || ' (raster lib from "' || rast_lib_ver || '" need upgrade)'; | |
END IF; | |
IF core_is_extension AND NOT EXISTS ( | |
SELECT * FROM pg_catalog.pg_extension | |
WHERE extname = 'postgis_raster') | |
THEN | |
fullver = fullver || ' [UNPACKAGED!]'; | |
END IF; | |
END IF; | |
IF rast_scr_ver IS NOT NULL AND rast_scr_ver != relproc THEN | |
fullver = fullver || ' (raster procs from "' || rast_scr_ver || '" need upgrade)'; | |
END IF; | |
IF sfcgal_scr_ver IS NOT NULL AND sfcgal_scr_ver != relproc THEN | |
fullver = fullver || ' (sfcgal procs from "' || sfcgal_scr_ver || '" need upgrade)'; | |
END IF; | |
RETURN fullver; | |
END | |
$$ | |
LANGUAGE 'plpgsql' IMMUTABLE; | |
--------------------------------------------------------------- | |
-- CASTS | |
--------------------------------------------------------------- | |
CREATE OR REPLACE FUNCTION box2d(geometry) | |
RETURNS box2d | |
AS 'MODULE_PATHNAME','LWGEOM_to_BOX2D' | |
LANGUAGE 'c' IMMUTABLE STRICT PARALLEL SAFE | |
_COST_LOW; | |
CREATE OR REPLACE FUNCTION box3d(geometry) | |
RETURNS box3d | |
AS 'MODULE_PATHNAME','LWGEOM_to_BOX3D' | |
LANGUAGE 'c' IMMUTABLE STRICT PARALLEL SAFE | |
_COST_LOW; | |
CREATE OR REPLACE FUNCTION box(geometry) | |
RETURNS box | |
AS 'MODULE_PATHNAME','LWGEOM_to_BOX' | |
LANGUAGE 'c' IMMUTABLE STRICT PARALLEL SAFE | |
_COST_LOW; | |
CREATE OR REPLACE FUNCTION box2d(box3d) | |
RETURNS box2d | |
AS 'MODULE_PATHNAME','BOX3D_to_BOX2D' | |
LANGUAGE 'c' IMMUTABLE STRICT PARALLEL SAFE | |
_COST_LOW; | |
CREATE OR REPLACE FUNCTION box3d(box2d) | |
RETURNS box3d | |
AS 'MODULE_PATHNAME','BOX2D_to_BOX3D' | |
LANGUAGE 'c' IMMUTABLE STRICT PARALLEL SAFE | |
_COST_LOW; | |
CREATE OR REPLACE FUNCTION box(box3d) | |
RETURNS box | |
AS 'MODULE_PATHNAME','BOX3D_to_BOX' | |
LANGUAGE 'c' IMMUTABLE STRICT PARALLEL SAFE | |
_COST_LOW; | |
CREATE OR REPLACE FUNCTION text(geometry) | |
RETURNS text | |
AS 'MODULE_PATHNAME','LWGEOM_to_text' | |
LANGUAGE 'c' IMMUTABLE STRICT PARALLEL SAFE | |
_COST_LOW; | |
-- this is kept for backward-compatibility | |
-- Deprecation in 1.2.3 | |
CREATE OR REPLACE FUNCTION box3dtobox(box3d) | |
RETURNS box | |
AS 'MODULE_PATHNAME','BOX3D_to_BOX' | |
LANGUAGE 'c' IMMUTABLE STRICT PARALLEL SAFE | |
_COST_LOW; | |
CREATE OR REPLACE FUNCTION geometry(box2d) | |
RETURNS geometry | |
AS 'MODULE_PATHNAME','BOX2D_to_LWGEOM' | |
LANGUAGE 'c' IMMUTABLE STRICT PARALLEL SAFE | |
_COST_LOW; | |
CREATE OR REPLACE FUNCTION geometry(box3d) | |
RETURNS geometry | |
AS 'MODULE_PATHNAME','BOX3D_to_LWGEOM' | |
LANGUAGE 'c' IMMUTABLE STRICT PARALLEL SAFE | |
_COST_LOW; | |
CREATE OR REPLACE FUNCTION geometry(text) | |
RETURNS geometry | |
AS 'MODULE_PATHNAME','parse_WKT_lwgeom' | |
LANGUAGE 'c' IMMUTABLE STRICT PARALLEL SAFE | |
_COST_LOW; | |
CREATE OR REPLACE FUNCTION geometry(bytea) | |
RETURNS geometry | |
AS 'MODULE_PATHNAME','LWGEOM_from_bytea' | |
LANGUAGE 'c' IMMUTABLE STRICT PARALLEL SAFE | |
_COST_LOW; | |
CREATE OR REPLACE FUNCTION bytea(geometry) | |
RETURNS bytea | |
AS 'MODULE_PATHNAME','LWGEOM_to_bytea' | |
LANGUAGE 'c' IMMUTABLE STRICT PARALLEL SAFE | |
_COST_LOW; | |
-- 7.3+ explicit casting definitions | |
CREATE CAST (geometry AS box2d) WITH FUNCTION box2d(geometry) AS IMPLICIT; | |
CREATE CAST (geometry AS box3d) WITH FUNCTION box3d(geometry) AS IMPLICIT; | |
-- ticket: 2262 changed 2.1.0 to assignment to prevent PostGIS | |
-- from misusing PostgreSQL geometric functions | |
CREATE CAST (geometry AS box) WITH FUNCTION box(geometry) AS ASSIGNMENT; | |
CREATE CAST (box3d AS box2d) WITH FUNCTION box2d(box3d) AS IMPLICIT; | |
CREATE CAST (box2d AS box3d) WITH FUNCTION box3d(box2d) AS IMPLICIT; | |
CREATE CAST (box2d AS geometry) WITH FUNCTION geometry(box2d) AS IMPLICIT; | |
CREATE CAST (box3d AS box) WITH FUNCTION box(box3d) AS IMPLICIT; | |
CREATE CAST (box3d AS geometry) WITH FUNCTION geometry(box3d) AS IMPLICIT; | |
CREATE CAST (text AS geometry) WITH FUNCTION geometry(text) AS IMPLICIT; | |
CREATE CAST (geometry AS text) WITH FUNCTION text(geometry) AS IMPLICIT; | |
CREATE CAST (bytea AS geometry) WITH FUNCTION geometry(bytea) AS IMPLICIT; | |
CREATE CAST (geometry AS bytea) WITH FUNCTION bytea(geometry) AS IMPLICIT; | |
--------------------------------------------------------------- | |
-- Algorithms | |
--------------------------------------------------------------- | |
-- Availability: 1.2.2 | |
CREATE OR REPLACE FUNCTION ST_Simplify(geometry, float8) | |
RETURNS geometry | |
AS 'MODULE_PATHNAME', 'LWGEOM_simplify2d' | |
LANGUAGE 'c' IMMUTABLE STRICT PARALLEL SAFE | |
_COST_LOW; | |
-- Availability: 2.2.0 | |
CREATE OR REPLACE FUNCTION ST_Simplify(geometry, float8, boolean) | |
RETURNS geometry | |
AS 'MODULE_PATHNAME', 'LWGEOM_simplify2d' | |
LANGUAGE 'c' IMMUTABLE STRICT PARALLEL SAFE | |
_COST_LOW; | |
-- Availability: 2.2.0 | |
CREATE OR REPLACE FUNCTION ST_SimplifyVW(geometry, float8) | |
RETURNS geometry | |
AS 'MODULE_PATHNAME', 'LWGEOM_SetEffectiveArea' | |
LANGUAGE 'c' IMMUTABLE STRICT PARALLEL SAFE | |
_COST_HIGH; | |
-- Availability: 2.2.0 | |
CREATE OR REPLACE FUNCTION ST_SetEffectiveArea(geometry, float8 default -1, integer default 1) | |
RETURNS geometry | |
AS 'MODULE_PATHNAME', 'LWGEOM_SetEffectiveArea' | |
LANGUAGE 'c' IMMUTABLE STRICT PARALLEL SAFE | |
_COST_HIGH; | |
-- Availability: 2.5.0 | |
CREATE OR REPLACE FUNCTION ST_FilterByM(geometry, double precision, double precision default null, boolean default false) | |
RETURNS geometry | |
AS 'MODULE_PATHNAME', 'LWGEOM_FilterByM' | |
LANGUAGE 'c' IMMUTABLE PARALLEL SAFE | |
_COST_LOW; | |
-- Availability: 2.5.0 | |
CREATE OR REPLACE FUNCTION ST_ChaikinSmoothing(geometry, integer default 1, boolean default false) | |
RETURNS geometry | |
AS 'MODULE_PATHNAME', 'LWGEOM_ChaikinSmoothing' | |
LANGUAGE 'c' IMMUTABLE STRICT PARALLEL SAFE | |
_COST_MEDIUM; | |
-- ST_SnapToGrid(input, xoff, yoff, xsize, ysize) | |
-- Availability: 1.2.2 | |
CREATE OR REPLACE FUNCTION ST_SnapToGrid(geometry, float8, float8, float8, float8) | |
RETURNS geometry | |
AS 'MODULE_PATHNAME', 'LWGEOM_snaptogrid' | |
LANGUAGE 'c' IMMUTABLE STRICT PARALLEL SAFE | |
_COST_LOW; | |
-- ST_SnapToGrid(input, xsize, ysize) # offsets=0 | |
-- Availability: 1.2.2 | |
CREATE OR REPLACE FUNCTION ST_SnapToGrid(geometry, float8, float8) | |
RETURNS geometry | |
AS 'SELECT @extschema@.ST_SnapToGrid($1, 0, 0, $2, $3)' | |
LANGUAGE 'sql' IMMUTABLE STRICT PARALLEL SAFE | |
_COST_LOW; | |
-- ST_SnapToGrid(input, size) # xsize=ysize=size, offsets=0 | |
-- Availability: 1.2.2 | |
CREATE OR REPLACE FUNCTION ST_SnapToGrid(geometry, float8) | |
RETURNS geometry | |
AS 'SELECT @extschema@.ST_SnapToGrid($1, 0, 0, $2, $2)' | |
LANGUAGE 'sql' IMMUTABLE STRICT PARALLEL SAFE | |
_COST_LOW; | |
-- ST_SnapToGrid(input, point_offsets, xsize, ysize, zsize, msize) | |
-- Availability: 1.2.2 | |
CREATE OR REPLACE FUNCTION ST_SnapToGrid(geom1 geometry, geom2 geometry, float8, float8, float8, float8) | |
RETURNS geometry | |
AS 'MODULE_PATHNAME', 'LWGEOM_snaptogrid_pointoff' | |
LANGUAGE 'c' IMMUTABLE STRICT PARALLEL SAFE | |
_COST_LOW; | |
-- Availability: 1.2.2 | |
CREATE OR REPLACE FUNCTION ST_Segmentize(geometry, float8) | |
RETURNS geometry | |
AS 'MODULE_PATHNAME', 'LWGEOM_segmentize2d' | |
LANGUAGE 'c' IMMUTABLE STRICT PARALLEL SAFE | |
_COST_MEDIUM; | |
--------------------------------------------------------------- | |
-- LRS | |
--------------------------------------------------------------- | |
-- Availability: 2.1.0 | |
CREATE OR REPLACE FUNCTION ST_LineInterpolatePoint(geometry, float8) | |
RETURNS geometry | |
AS 'MODULE_PATHNAME', 'LWGEOM_line_interpolate_point' | |
LANGUAGE 'c' IMMUTABLE STRICT PARALLEL SAFE | |
_COST_MEDIUM; | |
-- Availability: 2.5.0 | |
CREATE OR REPLACE FUNCTION ST_LineInterpolatePoints(geometry, float8, repeat boolean DEFAULT true) | |
RETURNS geometry | |
AS 'MODULE_PATHNAME', 'LWGEOM_line_interpolate_point' | |
LANGUAGE 'c' IMMUTABLE STRICT PARALLEL SAFE | |
_COST_MEDIUM; | |
-- Availability: 2.1.0 | |
CREATE OR REPLACE FUNCTION ST_LineSubstring(geometry, float8, float8) | |
RETURNS geometry | |
AS 'MODULE_PATHNAME', 'LWGEOM_line_substring' | |
LANGUAGE 'c' IMMUTABLE STRICT PARALLEL SAFE | |
_COST_MEDIUM; | |
-- Availability: 2.1.0 | |
CREATE OR REPLACE FUNCTION ST_LineLocatePoint(geom1 geometry, geom2 geometry) | |
RETURNS float8 | |
AS 'MODULE_PATHNAME', 'LWGEOM_line_locate_point' | |
LANGUAGE 'c' IMMUTABLE STRICT PARALLEL SAFE | |
_COST_MEDIUM; | |
-- Availability: 1.5.0 | |
CREATE OR REPLACE FUNCTION ST_AddMeasure(geometry, float8, float8) | |
RETURNS geometry | |
AS 'MODULE_PATHNAME', 'ST_AddMeasure' | |
LANGUAGE 'c' IMMUTABLE STRICT PARALLEL SAFE | |
_COST_MEDIUM; | |
--------------------------------------------------------------- | |
-- TEMPORAL | |
--------------------------------------------------------------- | |
-- Availability: 2.2.0 | |
CREATE OR REPLACE FUNCTION ST_ClosestPointOfApproach(geometry, geometry) | |
RETURNS float8 | |
AS 'MODULE_PATHNAME', 'ST_ClosestPointOfApproach' | |
LANGUAGE 'c' IMMUTABLE STRICT PARALLEL SAFE | |
_COST_HIGH; | |
-- Availability: 2.2.0 | |
CREATE OR REPLACE FUNCTION ST_DistanceCPA(geometry, geometry) | |
RETURNS float8 | |
AS 'MODULE_PATHNAME', 'ST_DistanceCPA' | |
LANGUAGE 'c' IMMUTABLE STRICT PARALLEL SAFE | |
_COST_HIGH; | |
-- Availability: 2.2.0 | |
CREATE OR REPLACE FUNCTION ST_CPAWithin(geometry, geometry, float8) | |
RETURNS bool | |
AS 'MODULE_PATHNAME', 'ST_CPAWithin' | |
LANGUAGE 'c' IMMUTABLE STRICT PARALLEL SAFE | |
_COST_HIGH; | |
-- Availability: 2.2.0 | |
CREATE OR REPLACE FUNCTION ST_IsValidTrajectory(geometry) | |
RETURNS bool | |
AS 'MODULE_PATHNAME', 'ST_IsValidTrajectory' | |
LANGUAGE 'c' IMMUTABLE STRICT PARALLEL SAFE | |
_COST_HIGH; | |
--------------------------------------------------------------- | |
-- GEOS | |
--------------------------------------------------------------- | |
-- Changed: 3.1.0 to add gridSize default argument | |
CREATE OR REPLACE FUNCTION ST_Intersection(geom1 geometry, geom2 geometry, gridSize float8 DEFAULT -1) | |
RETURNS geometry | |
AS 'MODULE_PATHNAME','ST_Intersection' | |
LANGUAGE 'c' IMMUTABLE STRICT PARALLEL SAFE | |
_COST_HIGH; | |
CREATE OR REPLACE FUNCTION ST_Buffer(geom geometry, radius float8, options text DEFAULT '') | |
RETURNS geometry | |
AS 'MODULE_PATHNAME','buffer' | |
LANGUAGE 'c' IMMUTABLE STRICT PARALLEL SAFE | |
_COST_HIGH; | |
-- Availability: 1.2.2 | |
CREATE OR REPLACE FUNCTION ST_Buffer(geom geometry, radius float8, quadsegs integer) | |
RETURNS geometry | |
AS $$ SELECT @extschema@.ST_Buffer($1, $2, CAST('quad_segs='||CAST($3 AS text) as text)) $$ | |
LANGUAGE 'sql' IMMUTABLE STRICT PARALLEL SAFE | |
_COST_HIGH; | |
-- Availability: 2.3.0 | |
CREATE OR REPLACE FUNCTION ST_MinimumBoundingRadius(geometry, OUT center geometry, OUT radius double precision) | |
AS 'MODULE_PATHNAME', 'ST_MinimumBoundingRadius' | |
LANGUAGE 'c' IMMUTABLE STRICT PARALLEL SAFE | |
_COST_HIGH; | |
-- Availability: 1.4.0 | |
CREATE OR REPLACE FUNCTION ST_MinimumBoundingCircle(inputgeom geometry, segs_per_quarter integer DEFAULT 48) | |
RETURNS geometry | |
AS 'MODULE_PATHNAME', 'ST_MinimumBoundingCircle' | |
LANGUAGE 'c' IMMUTABLE STRICT PARALLEL SAFE | |
_COST_HIGH; | |
-- Availability: 2.5.0 |