From 41b6af4b886b5544f11cc7e30590020fb09bf598 Mon Sep 17 00:00:00 2001 From: Sandro Santilli Date: Tue, 11 Nov 2003 10:38:23 +0000 Subject: [PATCH] Postgresql 7.4 enabler scripts. git-svn-id: http://svn.osgeo.org/postgis/trunk@354 b70326c6-7e19-0410-871a-916f4a2858ee --- Attic/postgis_sql_74_end.sql.in | 210 ++++++++++++++++++++++++++++++ Attic/postgis_sql_74_start.sql.in | 161 +++++++++++++++++++++++ 2 files changed, 371 insertions(+) create mode 100644 Attic/postgis_sql_74_end.sql.in create mode 100644 Attic/postgis_sql_74_start.sql.in diff --git a/Attic/postgis_sql_74_end.sql.in b/Attic/postgis_sql_74_end.sql.in new file mode 100644 index 000000000..2770eea7a --- /dev/null +++ b/Attic/postgis_sql_74_end.sql.in @@ -0,0 +1,210 @@ + +-- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - +-- +-- $Id$ +-- +-- PostGIS - Spatial Types for PostgreSQL +-- http://postgis.refractions.net +-- Copyright 2001-2003 Refractions Research Inc. +-- +-- This is free software; you can redistribute and/or modify it under +-- the terms of hte GNU General Public Licence. See the COPYING file. +-- +-- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - +-- $Log$ +-- Revision 1.1 2003/11/11 10:38:23 strk +-- Postgresql 7.4 enabler scripts. +-- +-- Revision 1.4 2003/07/01 18:30:55 pramsey +-- Added CVS revision headers. +-- +-- +-- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - + +-- - - - - - - - - - - - - - - - - - - - - - - - - - - - +-- WKB + +-- this has been moved here at _end from _start +-- because we need the definition of function bytea +-- found in _common +CREATE TYPE wkb ( + internallength = variable, + input = wkb_in, + output = wkb_out, + storage = extended, + send = bytea +); + +-- +-- 7.3 explicit casting definitions +-- + +CREATE CAST ( chip AS geometry ) WITH FUNCTION geometry(chip) AS IMPLICIT; +CREATE CAST ( geometry AS box3d ) WITH FUNCTION box3d(geometry) AS IMPLICIT; +CREATE CAST ( geometry AS box ) WITH FUNCTION box(geometry) 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 ( wkb AS bytea ) WITH FUNCTION bytea(wkb) AS IMPLICIT; +CREATE CAST ( box3d AS box ) WITH FUNCTION box3dtobox(box3d); + +-- - - - - - - - - - - - - - - - - - - - - - - - - - - - +-- UPDATE_GEOMETRY_STATS() + +CREATE FUNCTION update_geometry_stats() +RETURNS text +AS +' +BEGIN + EXECUTE ''update geometry_columns set attrelid = (select pg_class.oid AS attrelid from pg_class,pg_attribute where relname =geometry_columns.f_table_name::name and pg_attribute.attrelid = pg_class.oid and pg_attribute.attname = geometry_columns.f_geometry_column::name), varattnum = (select pg_attribute.attnum from pg_class,pg_attribute where relname =geometry_columns.f_table_name::name and pg_attribute.attrelid = pg_class.oid and pg_attribute.attname = geometry_columns.f_geometry_column::name)''; + execute ''update geometry_columns set stats = (build_histogram2d( create_histogram2d(find_extent(f_table_name,f_geometry_column),40 ),f_table_name::text, f_geometry_column::text)) ''; + return ''done''; +END; +' +LANGUAGE 'plpgsql' ; + +-- - - - - - - - - - - - - - - - - - - - - - - - - - - - +-- UPDATE_GEOMETRY_STATS( , ) + +CREATE FUNCTION update_geometry_stats(varchar,varchar) +RETURNS text +AS +' +DECLARE + tablename aliAS for $1; + columnname aliAS for $2; + +BEGIN + EXECUTE ''update geometry_columns set attrelid = (select pg_class.oid AS attrelid from pg_class,pg_attribute where relname =geometry_columns.f_table_name::name and pg_attribute.attrelid = pg_class.oid and pg_attribute.attname = geometry_columns.f_geometry_column::name), varattnum = (select pg_attribute.attnum from pg_class,pg_attribute where relname =geometry_columns.f_table_name::name and pg_attribute.attrelid = pg_class.oid and pg_attribute.attname = geometry_columns.f_geometry_column::name)''; + execute ''update geometry_columns set stats = (build_histogram2d( create_histogram2d(find_extent(''|| quote_literal(tablename) || '',''||quote_literal(columnname) ||''),40 ),''|| quote_literal(tablename) || ''::text,''||quote_literal(columnname) ||''::text )) WHERE f_table_name=''|| quote_literal(tablename) || ''and f_geometry_column=''||quote_literal(columnname) ; + return ''done''; +END; +' +LANGUAGE 'plpgsql' ; + +-- - - - - - - - - - - - - - - - - - - - - - - - - - - - +-- CREATE_HISTOGRAM2D( , ) +-- +-- Returns a histgram with 0s in all the boxes. + +CREATE FUNCTION create_histogram2d(box3d,int) + RETURNS histogram2d + AS '@MODULE_FILENAME@','create_histogram2d' + LANGUAGE 'C' with (isstrict); + +-- - - - - - - - - - - - - - - - - - - - - - - - - - - - - +-- BUILD_HISTOGRAM2D( , , ) +-- + +CREATE FUNCTION build_histogram2d (histogram2d, text, text) + RETURNS histogram2d + AS '@MODULE_FILENAME@','build_histogram2d' + LANGUAGE 'C' with (isstrict); + +-- - - - - - - - - - - - - - - - - - - - - - - - - - - - +-- EXPLODE_HISTOGRAM2D( , ) +-- + +CREATE FUNCTION explode_histogram2d (histogram2d, text) + RETURNS histogram2d + AS '@MODULE_FILENAME@','explode_histogram2d' + LANGUAGE 'C' with (isstrict); + +-- - - - - - - - - - - - - - - - - - - - - - - - - - - - +-- ESTIMATE_HISTOGRAM2D( , ) +-- + +CREATE FUNCTION estimate_histogram2d(histogram2d,box) + RETURNS float8 + AS '@MODULE_FILENAME@','estimate_histogram2d' + LANGUAGE 'C' with (isstrict); + +CREATE FUNCTION postgisgistcostestimate(internal,internal,internal,internal,internal,internal,internal,internal) + RETURNS opaque + AS '@MODULE_FILENAME@','postgisgistcostestimate' + LANGUAGE 'C' with (isstrict); + +-- +-- 7.2 GiST support functions +-- + +CREATE FUNCTION ggeometry_consistent(internal,geometry,int4) + RETURNS bool + AS '@MODULE_FILENAME@' + LANGUAGE 'C'; + +CREATE FUNCTION ggeometry_compress(internal) + RETURNS internal + AS '@MODULE_FILENAME@' + LANGUAGE 'C'; + +CREATE FUNCTION gbox_penalty(internal,internal,internal) + RETURNS internal + AS '@MODULE_FILENAME@' + LANGUAGE 'C'; + +CREATE FUNCTION gbox_picksplit(internal, internal) + RETURNS internal + AS '@MODULE_FILENAME@' + LANGUAGE 'C'; + +CREATE FUNCTION gbox_union(bytea, internal) + RETURNS internal + AS '@MODULE_FILENAME@' + LANGUAGE 'C'; + +CREATE FUNCTION gbox_same(box, box, internal) + RETURNS internal + AS '@MODULE_FILENAME@' + LANGUAGE 'C'; + +CREATE FUNCTION rtree_decompress(internal) + RETURNS internal + AS '@MODULE_FILENAME@' + LANGUAGE 'C'; + +-- +-- 7.2 RTREE support functions +-- + +CREATE FUNCTION geometry_union(geometry,geometry) + RETURNS geometry + AS '@MODULE_FILENAME@' + LANGUAGE 'C'; + +CREATE FUNCTION geometry_inter(geometry,geometry) + RETURNS geometry + AS '@MODULE_FILENAME@' + LANGUAGE 'C'; + +CREATE FUNCTION geometry_size(geometry,internal) + RETURNS float4 + AS '@MODULE_FILENAME@' + LANGUAGE 'C'; + +-- +-- Create opclass index bindings +-- + +CREATE OPERATOR CLASS gist_geometry_ops + DEFAULT FOR TYPE geometry USING gist AS + OPERATOR 1 << , + OPERATOR 2 &< , + OPERATOR 3 && , + OPERATOR 4 &> , + OPERATOR 5 >> , + OPERATOR 6 ~= , + OPERATOR 7 ~ , + OPERATOR 8 @ , + FUNCTION 1 ggeometry_consistent (internal, geometry, int4), + FUNCTION 2 gbox_union (bytea, internal), + FUNCTION 3 ggeometry_compress (internal), + FUNCTION 4 rtree_decompress (internal), + FUNCTION 5 gbox_penalty (internal, internal, internal), + FUNCTION 6 gbox_picksplit (internal, internal), + FUNCTION 7 gbox_same (box, box, internal); + +UPDATE pg_opclass + SET opckeytype = (select oid from pg_type where typname = 'box') + WHERE opcname = 'gist_geometry_ops'; + +END TRANSACTION; diff --git a/Attic/postgis_sql_74_start.sql.in b/Attic/postgis_sql_74_start.sql.in new file mode 100644 index 000000000..a72811d3a --- /dev/null +++ b/Attic/postgis_sql_74_start.sql.in @@ -0,0 +1,161 @@ + +-- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - +-- +-- $Id$ +-- +-- PostGIS - Spatial Types for PostgreSQL +-- http://postgis.refractions.net +-- Copyright 2001-2003 Refractions Research Inc. +-- +-- This is free software; you can redistribute and/or modify it under +-- the terms of hte GNU General Public Licence. See the COPYING file. +-- +-- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - +-- $Log$ +-- Revision 1.1 2003/11/11 10:38:23 strk +-- Postgresql 7.4 enabler scripts. +-- +-- Revision 1.2 2003/07/01 18:30:55 pramsey +-- Added CVS revision headers. +-- +-- +-- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - + +BEGIN TRANSACTION; + +-- You might have to define the PL/PgSQL language usually done with the +-- changelang script. + +-- Here's some hokey code to test to see if PL/PgSQL is installed +-- if it is, you get a message "PL/PgSQL is installed" +-- otherwise it will give a big error message. + +(select 'PL/PgSQL is installed.' as message from pg_language where lanname='plpgsql') union (select 'You must install PL/PgSQL before running this SQL file,\nor you will get an error. To install PL/PgSQL run:\n\tcreatelang plpgsql '::text as message) order by message limit 1; + +-- - - - - - - - - - - - - - - - - - - - - - - - - - - - +-- HISTOGRAM2D + +CREATE FUNCTION histogram2d_in(cstring) + RETURNS histogram2d + AS '@MODULE_FILENAME@' + LANGUAGE 'C' WITH (isstrict); + +CREATE FUNCTION histogram2d_out(histogram2d) + RETURNS cstring + AS '@MODULE_FILENAME@' + LANGUAGE 'C' WITH (isstrict); + +CREATE TYPE histogram2d ( + alignment = double, + internallength = variable, + input = histogram2d_in, + output = histogram2d_out, + storage = main +); + +-- - - - - - - - - - - - - - - - - - - - - - - - - - - - +-- BOX3D + +CREATE FUNCTION box3d_in(cstring) + RETURNS box3d + AS '@MODULE_FILENAME@' + LANGUAGE 'C' WITH (isstrict); + +CREATE FUNCTION box3d_out(box3d) + RETURNS cstring + AS '@MODULE_FILENAME@' + LANGUAGE 'C' WITH (isstrict); + +CREATE TYPE box3d ( + alignment = double, + internallength = 48, + input = box3d_in, + output = box3d_out +); + +-- - - - - - - - - - - - - - - - - - - - - - - - - - - - +-- SPHEROID + +CREATE FUNCTION spheroid_in(cstring) + RETURNS spheroid + AS '@MODULE_FILENAME@','ellipsoid_in' + LANGUAGE 'C' WITH (isstrict,iscachable); + +CREATE FUNCTION spheroid_out(spheroid) + RETURNS cstring + AS '@MODULE_FILENAME@','ellipsoid_out' + LANGUAGE 'C' WITH (isstrict); + +CREATE TYPE spheroid ( + alignment = double, + internallength = 65, + input = spheroid_in, + output = spheroid_out +); + +-- - - - - - - - - - - - - - - - - - - - - - - - - - - - +-- WKB + +CREATE FUNCTION wkb_in(cstring) + RETURNS wkb + AS '@MODULE_FILENAME@','WKB_in' + LANGUAGE 'C' WITH (isstrict); + +CREATE FUNCTION wkb_out(wkb) + RETURNS cstring + AS '@MODULE_FILENAME@','WKB_out' + LANGUAGE 'C' WITH (isstrict); + + +-- - - - - - - - - - - - - - - - - - - - - - - - - - - - +-- CHIP + +CREATE FUNCTION chip_in(cstring) + RETURNS chip + AS '@MODULE_FILENAME@','CHIP_in' + LANGUAGE 'C' WITH (isstrict); + +CREATE FUNCTION chip_out(chip) + RETURNS cstring + AS '@MODULE_FILENAME@','CHIP_out' + LANGUAGE 'C' WITH (isstrict); + +CREATE TYPE chip ( + alignment = double, + internallength = variable, + input = chip_in, + output = chip_out, + storage = extended +); + +-- - - - - - - - - - - - - - - - - - - - - - - - - - - - +-- GEOMETRY + +CREATE FUNCTION geometry_in(cstring) + RETURNS geometry + AS '@MODULE_FILENAME@' + LANGUAGE 'C' WITH (isstrict); + +CREATE FUNCTION geometry_out(geometry) + RETURNS cstring + AS '@MODULE_FILENAME@' + LANGUAGE 'C' WITH (isstrict); + +CREATE TYPE geometry ( + alignment = double, + internallength = variable, + input = geometry_in, + output = geometry_out, + storage = main +); + +-- +-- GiST selectivity function +-- + +CREATE FUNCTION postgis_gist_sel (internal, oid, internal, int4) + RETURNS float8 + AS '@MODULE_FILENAME@' + LANGUAGE 'C'; + + -- 2.40.0