From cd341742f783ea84e4927af0e7ba91d0263423dc Mon Sep 17 00:00:00 2001 From: Paul Ramsey Date: Tue, 10 Dec 2002 04:45:20 +0000 Subject: [PATCH] Removed old sql.in files. git-svn-id: http://svn.osgeo.org/postgis/trunk@228 b70326c6-7e19-0410-871a-916f4a2858ee --- postgis.sql.in | 831 ----------------------------------------- postgis_gist_71.sql.in | 228 ----------- postgis_gist_72.sql.in | 330 ---------------- postgis_gist_73.sql.in | 204 ---------- 4 files changed, 1593 deletions(-) delete mode 100644 postgis.sql.in delete mode 100644 postgis_gist_71.sql.in delete mode 100644 postgis_gist_72.sql.in delete mode 100644 postgis_gist_73.sql.in diff --git a/postgis.sql.in b/postgis.sql.in deleted file mode 100644 index b9ec1caa5..000000000 --- a/postgis.sql.in +++ /dev/null @@ -1,831 +0,0 @@ -BEGIN TRANSACTION; - - ---- you might have to define the plpgsql language usually done with the; ---- changelang script. - --- here's some hockey code to test to see if plpgsql is installed --- if it is, you get a message "plpgsql is installed" --- otherwise it will give a big error message - -select lanname || ' is installed' as message from pg_language where lanname='plpgsql' union select 'you must install plpgsql before running this sql file, or you will get an error.\nTo install plpgsql:\n1. Install the handler.\n CREATE FUNCTION plpgsql_call_handler () RETURNS OPAQUE AS \n ''/usr/local/pgsql/lib/plpgsql.so'' LANGUAGE ''C'';\n\nYou might have to change the location of the .so file. Common places are:\n/usr/local/lib\n/usr/local/pgsql/lib\n/usr/lib/pgsql\nOr where your postgresql lib directory is\n\n2. Install the language\nCREATE TRUSTED PROCEDURAL LANGUAGE ''plpgsql''\nHANDLER plpgsql_call_handler\nLANCOMPILER ''PL/pgSQL''; '::text order by message limit 1; - - - - -CREATE FUNCTION POSTGIS_VERSION() returns text -as 'select \'@POSTGIS_VERSION@\'::text as version' -LANGUAGE 'sql'; - - --- need this to define geometry_columns table -create function histogram2d_in(OPAQUE) - RETURNS OPAQUE - AS '@MODULE_FILENAME@' - LANGUAGE 'c' with (isstrict); - -create function histogram2d_out(OPAQUE) - RETURNS OPAQUE - AS '@MODULE_FILENAME@' - LANGUAGE 'c' with (isstrict); - -CREATE TYPE HISTOGRAM2D ( - alignment = double, - internallength = VARIABLE, - input = histogram2d_in, - output = histogram2d_out, - storage = main -); - - - - ---drop function FIX_GEOMETRY_COLUMNS() ; ---utility function to fixup the geometry_columns table with system table information -CREATE FUNCTION FIX_GEOMETRY_COLUMNS() 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)''; - RETURN ''GEOMETRY_COLUMNS table is now linked to the system tables''; -END; -' - LANGUAGE 'plpgsql' ; - --- create the table with spatial referencing information in it. spec, section 3.2.1.2 - -create table spatial_ref_sys ( - srid integer not null primary key, - auth_name varchar(256), - auth_srid integer, - srtext varchar(2048), - proj4text varchar(2048) - ); - --- create the metadata table. spec, section 3.2.2.1 - -create table geometry_columns ( -f_table_catalog varchar(256) not null, -f_table_schema varchar(256) not null, -f_table_name varchar(256) not null, -f_geometry_column varchar(256) not null, -coord_dimension integer NOT NULL, -srid integer NOT NULL, -type varchar(30) NOT NULL, -attrelid oid, -varattnum int, -stats HISTOGRAM2D, -CONSTRAINT GC_PK primary key ( f_table_catalog,f_table_schema, f_table_name,f_geometry_column) -) ; - - --- drop function find_srid(varchar,varchar,varchar); - --- given a schema (or ''), table name, geometry column, find its SRID ---- find_SRID(,,) - -CREATE FUNCTION find_SRID(varchar,varchar,varchar) returns int4 as -'select SRID from geometry_columns where f_table_schema like $1 || ''%'' and f_table_name = $2 and f_geometry_column = $3' LANGUAGE 'sql' with (iscachable,isstrict); - --- select find_srid('','geometry_test','mygeom'); - - --- given an SRID, find the proj4 definition string -CREATE FUNCTION get_proj4_from_srid(integer) returns text as -'SELECT proj4text::text FROM spatial_ref_sys WHERE srid= $1' -LANGUAGE 'sql' with (iscachable,isstrict); - --- select get_proj4_from_srid(1); - - - - - ---- DropGeometryColumn(,
,) ---- There is no ALTER TABLE DROP COLUMN command in postgresql ---- There is no ALTER TABLE DROP CONSTRAINT command in postgresql ---- So, we ; ---- 1. remove the unwanted geom column reference from the geometry_columns table ---- 2. update the table so that the geometry column is all NULLS ----- This is okay since the CHECK srid(geometry) = is not ----- checked if geometry is NULL (the isstrict attribute on srid()) ---- 3. add another constraint that the geometry column must be NULL ---- This, effectively kills the geometry column ----- (a) its not in the geometry_column table ----- (b) it only has nulls in it ----- (c) you cannot add anything to the geom column because it must be NULL ----- ----- This will screw up if you put a NOT NULL constraint on the geometry column, so the ----- first thing we must do is remove this constraint (its a modification of the ----- pg_attribute system table) ---- ----- We also check to see if the table/column exists in the geometry_columns table - - ---- drop function DropGeometryColumn(varchar,varchar,varchar); - -CREATE FUNCTION DropGeometryColumn(varchar,varchar,varchar) - RETURNS text - AS -' -DECLARE - database_name alias for $1; - table_name alias for $2; - column_name alias for $3; - myrec RECORD; - okay boolean; -BEGIN - -- first we find out if the column is in the geometry_columns table - okay = ''f''; - FOR myrec IN SELECT * from geometry_columns where f_table_schema = database_name and f_table_name = table_name and f_geometry_column = column_name LOOP - okay := ''t''; - END LOOP; - IF (okay <> ''t'') THEN - RAISE EXCEPTION ''column not found in geometry_columns table''; - return ''f''; - END IF; - - -- ensure the geometry column does not have a NOT NULL attribute - EXECUTE ''update pg_attribute set attnotnull = false from pg_class where pg_attribute.attrelid = pg_class.oid and pg_class.relname = '' || quote_literal(table_name) ||'' and pg_attribute.attname = '' || quote_literal(column_name); - - -- remove ref from geometry_columns table - EXECUTE ''delete from geometry_columns where f_table_schema = '' || quote_literal(database_name) || - '' and f_table_name = '' || quote_literal(table_name) || - '' and f_geometry_column = '' || quote_literal(column_name ); - - -- update the given table/column so that it it all NULLS - - EXECUTE ''update "''||table_name||''" set "''||column_name||''"= NULL''; - - -- add = NULL constraint to given table/column - - EXECUTE ''ALTER TABLE "''||table_name||''" ADD CHECK ("''||column_name||''" IS NULL)''; - - RETURN table_name || ''.'' || column_name ||'' effectively removed.''; - -END; -' - LANGUAGE 'plpgsql' with (isstrict); - --- select DropGeometryColumn('new_test','test_table','mygeom'); - - --- drop function AddGeometryColumn(varchar,varchar,varchar,integer,varchar,integer); - ---- AddGeometryColumn(,
,, , ,) ---- type can be one of GEOMETRY, GEOMETRYCOLLECTION,POINT,MULTIPOINT,POLYGON, ---- MULTIPOLYGON,LINESTRING, or MULTILINESTRING ---- types (except GEOMETRY) are checked for consistency using a CHECK constraint ---- uses SQL ALTER TABLE command to add the geometry column to the table ---- added a row to geometry_columns with info (catalog = '', schema = ) ---- 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) ---- also checks to see if the database_name is in the pg_database table - -CREATE FUNCTION AddGeometryColumn(varchar,varchar,varchar,integer,varchar,integer) - RETURNS text - AS -' -DECLARE - database_name alias for $1; - table_name alias for $2; - column_name alias for $3; - new_srid alias for $4; - new_type alias for $5; - new_dim alias for $6; - - real_db_name varchar; - db_query RECORD; - db_name_ok boolean; - -BEGIN - - IF (not( (new_type =''GEOMETRY'') or (new_type =''GEOMETRYCOLLECTION'') or (new_type =''POINT'') - or (new_type =''MULTIPOINT'') or (new_type =''POLYGON'') or (new_type =''MULTIPOLYGON'') - or (new_type =''LINESTRING'') or (new_type =''MULTILINESTRING'') ) ) THEN - RAISE EXCEPTION ''invalid type name - valid ones are: GEOMETRY, GEOMETRYCOLLECTION,POINT,MULTIPOINT,POLYGON,MULTIPOLYGON,LINESTRING, or MULTILINESTRING ''; - return ''fail''; - END IF; - - IF ( (new_dim >3) or (new_dim <0) ) THEN - RAISE EXCEPTION ''invalid dimension''; - return ''fail''; - END IF; - - db_name_ok := ''f''; - - FOR db_query IN SELECT datname from pg_database where text(datname) = database_name LOOP - db_name_ok := ''t''; - END LOOP; - - if (db_name_ok <> ''t'') THEN - RAISE EXCEPTION ''invalid database name''; - return ''fail''; - end if; - - EXECUTE ''ALTER TABLE "'' || table_name || ''" ADD COLUMN "'' || column_name || ''" GEOMETRY ''; - EXECUTE ''INSERT INTO geometry_columns VALUES ('' || quote_literal('''') || '','' || - quote_literal(database_name) || '','' || quote_literal(table_name) || '','' || - quote_literal(column_name) || '','' || - new_dim ||'',''||new_srid||'',''||quote_literal(new_type)||'')''; - - 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 ''ALTER TABLE "'' ||table_name||''" ADD CHECK (SRID("'' || column_name || - ''") = '' || new_srid || '')'' ; - - IF (not(new_type = ''GEOMETRY'')) THEN - EXECUTE ''ALTER TABLE "'' ||table_name||''" ADD CHECK ( geometrytype("''||column_name||''")=''|| quote_literal(new_type)||'' OR ('' ||column_name ||'') is null)''; - END IF; - - return ''Geometry column '' || column_name || '' added to table '' - ||table_name ||'' with a SRID of ''||new_srid || '' and type ''||new_type; -END; -' - LANGUAGE 'plpgsql' with (isstrict); - ----select AddGeometryColumn('new_test','tt','new_geom3',2,'GEOMETRY',3); - - - -CREATE FUNCTION BOX3D_in(OPAQUE) - RETURNS OPAQUE - AS '@MODULE_FILENAME@' - LANGUAGE 'c' with (isstrict); - -CREATE FUNCTION BOX3D_out(OPAQUE) - RETURNS OPAQUE - AS '@MODULE_FILENAME@' - LANGUAGE 'c' with (isstrict); - -CREATE FUNCTION SPHEROID_in(OPAQUE) - RETURNS OPAQUE - AS '@MODULE_FILENAME@','ellipsoid_in' - LANGUAGE 'c' with (isstrict,iscachable); - -CREATE FUNCTION SPHEROID_out(OPAQUE) - RETURNS OPAQUE - AS '@MODULE_FILENAME@','ellipsoid_out' - LANGUAGE 'c' with (isstrict); - -CREATE TYPE SPHEROID ( - alignment = double, - internallength = 65, - input = SPHEROID_in, - output = SPHEROID_out -); - -CREATE TYPE BOX3D ( - alignment = double, - internallength = 48, - input = BOX3D_in, - output = BOX3D_out -); - -create function WKB_in(OPAQUE) - RETURNS OPAQUE - AS '@MODULE_FILENAME@','WKB_in' - LANGUAGE 'c' with (isstrict); - - - - -create function WKB_out(OPAQUE) - RETURNS OPAQUE - AS '@MODULE_FILENAME@','WKB_out' - LANGUAGE 'c' with (isstrict); - - -CREATE TYPE WKB ( - internallength = VARIABLE, - input = WKB_in, - output = WKB_out, - storage= extended -); - -create function CHIP_in(OPAQUE) - RETURNS OPAQUE - AS '@MODULE_FILENAME@','CHIP_in' - LANGUAGE 'c' with (isstrict); - -create function CHIP_out(OPAQUE) - RETURNS OPAQUE - AS '@MODULE_FILENAME@','CHIP_out' - LANGUAGE 'c' with (isstrict); - - -CREATE TYPE CHIP ( - alignment = double, - internallength = VARIABLE, - input = CHIP_in, - output = CHIP_out, - storage= extended -); - ---drop function find_extent(text,text); --- FIND_EXTENT(table name,column name) -CREATE FUNCTION FIND_EXTENT(text,text) returns box3d as -' -DECLARE - tablename alias for $1; - columnname alias for $2; - okay boolean; - myrec RECORD; - -BEGIN - FOR myrec IN EXECUTE ''SELECT extent("''||columnname||''") FROM "''||tablename||''"'' LOOP - return myrec.extent; - END LOOP; -END; -' -LANGUAGE 'plpgsql' with (isstrict); ---select find_extent('test_data','the_geom'); - - ---CREATE FUNCTION get_proj4_from_srid(integer) returns text as ---'SELECT proj4text::text FROM spatial_ref_sys WHERE srid= $1' ---LANGUAGE 'sql' with (iscachable,isstrict); - - - - -CREATE FUNCTION srid(chip) - RETURNS INT4 - AS '@MODULE_FILENAME@','srid_chip' - LANGUAGE 'c' with (isstrict); - -CREATE FUNCTION height(chip) - RETURNS INT4 - AS '@MODULE_FILENAME@','height_chip' - LANGUAGE 'c' with (isstrict); - -CREATE FUNCTION factor(chip) - RETURNS FLOAT4 - AS '@MODULE_FILENAME@','factor_chip' - LANGUAGE 'c' with (isstrict); - -CREATE FUNCTION width(chip) - RETURNS INT4 - AS '@MODULE_FILENAME@','width_chip' - LANGUAGE 'c' with (isstrict); - -CREATE FUNCTION datatype(chip) - RETURNS INT4 - AS '@MODULE_FILENAME@','datatype_chip' - LANGUAGE 'c' with (isstrict); - -CREATE FUNCTION compression(chip) - RETURNS INT4 - AS '@MODULE_FILENAME@','compression_chip' - LANGUAGE 'c' with (isstrict); - - -CREATE FUNCTION setSRID(chip,int4) - RETURNS chip - AS '@MODULE_FILENAME@','setsrid_chip' - LANGUAGE 'c' with (isstrict,iscachable); - -CREATE FUNCTION setfactor(chip,float4) - RETURNS chip - AS '@MODULE_FILENAME@','setfactor_chip' - LANGUAGE 'c' with (isstrict,iscachable); - -create function geometry_in(OPAQUE) - RETURNS OPAQUE - AS '@MODULE_FILENAME@' - LANGUAGE 'c' with (isstrict); - -create function geometry_out(OPAQUE) - RETURNS OPAQUE - AS '@MODULE_FILENAME@' - LANGUAGE 'c' with (isstrict); - -CREATE TYPE GEOMETRY ( - alignment = double, - internallength = VARIABLE, - input = geometry_in, - output = geometry_out, - storage = main -); - -create function transform_geometry(geometry,text,text,int) - RETURNS geometry - AS '@MODULE_FILENAME@','transform_geom' - LANGUAGE 'c' with (isstrict,iscachable); - ---- drop function transform(geometry,integer); --- given a geometry and a SRID, convert the geometry to the new SRID --- transform(geometry,new_srid) -CREATE FUNCTION transform(geometry,integer) returns geometry as -'BEGIN - RETURN transform_geometry( $1 , get_proj4_from_srid(SRID( $1 ) ), get_proj4_from_srid( $2 ), $2 ); - END; -' -LANGUAGE 'plpgsql' with (iscachable,isstrict); - --- test: ---- trans=# select * from spatial_ref_sys ; ---- srid | auth_name | auth_srid | srtext | proj4text ---- ------+---------------+-----------+--------+-------------------------------------------------------------------------- ---- 1 | latlong WGS84 | 1 | | +proj=longlat +datum=WGS84 ---- 2 | BC albers | 2 | | proj=aea ellps=GRS80 lon_0=-126 lat_0=45 lat_1=50 lat_2=58.5 x_0=1000000 - --- select transform( 'SRID=1;POINT(-120.8 50.3)', 2); ---- -> 'SRID=2;POINT(1370033.37046971 600755.810968684)' - -create function geometry(CHIP) - RETURNS GEOMETRY - AS '@MODULE_FILENAME@','CHIP_to_geom' - LANGUAGE 'c' with (isstrict,iscachable); - -CREATE FUNCTION box3d(GEOMETRY) - RETURNS BOX3D - AS '@MODULE_FILENAME@','get_bbox_of_geometry' - LANGUAGE 'c' WITH (iscachable,isstrict); - - -CREATE FUNCTION box(GEOMETRY) - RETURNS BOX - AS '@MODULE_FILENAME@','geometry2box' - LANGUAGE 'c' WITH (iscachable,isstrict); - -CREATE FUNCTION geometry(BOX3D) - RETURNS GEOMETRY - AS '@MODULE_FILENAME@','get_geometry_of_bbox' - LANGUAGE 'c' WITH (iscachable,isstrict); - -CREATE FUNCTION geometry(text) - RETURNS GEOMETRY - AS '@MODULE_FILENAME@','geometry_text' - LANGUAGE 'c' WITH (iscachable,isstrict); - - -CREATE FUNCTION expand(BOX3D,float8) - RETURNS BOX3D - AS '@MODULE_FILENAME@','expand_bbox' - LANGUAGE 'c' WITH (iscachable,isstrict); - - ---------- functions for converting to wkb - -CREATE FUNCTION asbinary(GEOMETRY) - RETURNS WKB - AS '@MODULE_FILENAME@','asbinary_simple' - LANGUAGE 'c' WITH (iscachable,isstrict); - -CREATE FUNCTION asbinary(GEOMETRY,TEXT) - RETURNS WKB - AS '@MODULE_FILENAME@','asbinary_specify' - LANGUAGE 'c' WITH (iscachable,isstrict); - -CREATE FUNCTION bytea(WKB) - RETURNS bytea - AS '@MODULE_FILENAME@','WKBtoBYTEA' - LANGUAGE 'c' WITH (iscachable,isstrict); ----- Debug (info) functions - - ---CREATE FUNCTION index_thing(GEOMETRY) --- RETURNS BOOL --- AS '@MODULE_FILENAME@' --- LANGUAGE 'c' with (isstrict); - -CREATE FUNCTION npoints(GEOMETRY) - RETURNS INT4 - AS '@MODULE_FILENAME@' - LANGUAGE 'c' with (isstrict); - -CREATE FUNCTION nrings(GEOMETRY) - RETURNS INT4 - AS '@MODULE_FILENAME@' - LANGUAGE 'c' with (isstrict) ; - -CREATE FUNCTION mem_size(GEOMETRY) - RETURNS INT4 - AS '@MODULE_FILENAME@' - LANGUAGE 'c' with (isstrict); - -CREATE FUNCTION numb_sub_objs(GEOMETRY) - RETURNS INT4 - AS '@MODULE_FILENAME@' - LANGUAGE 'c' with (isstrict); - -CREATE FUNCTION summary(GEOMETRY) - RETURNS text - AS '@MODULE_FILENAME@' - LANGUAGE 'c' with (isstrict); - -CREATE FUNCTION translate(GEOMETRY,float8,float8,float8) - RETURNS GEOMETRY - AS '@MODULE_FILENAME@' - LANGUAGE 'c' with (isstrict) ; - -CREATE FUNCTION dimension(GEOMETRY) - RETURNS INT4 - AS '@MODULE_FILENAME@' - LANGUAGE 'c' with (isstrict) ; - -CREATE FUNCTION geometrytype(GEOMETRY) - RETURNS text - AS '@MODULE_FILENAME@' - LANGUAGE 'c' with (isstrict); - -CREATE FUNCTION envelope(GEOMETRY) - RETURNS geometry - AS '@MODULE_FILENAME@' - LANGUAGE 'c' with (isstrict); - -CREATE FUNCTION x(GEOMETRY) - RETURNS float8 - AS '@MODULE_FILENAME@','x_point' - LANGUAGE 'c' with (isstrict); - -CREATE FUNCTION y(GEOMETRY) - RETURNS float8 - AS '@MODULE_FILENAME@','y_point' - LANGUAGE 'c' with (isstrict); - -CREATE FUNCTION z(GEOMETRY) - RETURNS float8 - AS '@MODULE_FILENAME@','z_point' - LANGUAGE 'c' with (isstrict); - -CREATE FUNCTION numpoints(GEOMETRY) - RETURNS integer - AS '@MODULE_FILENAME@','numpoints_linestring' - LANGUAGE 'c' with (isstrict); - -CREATE FUNCTION pointn(GEOMETRY,INTEGER) - RETURNS GEOMETRY - AS '@MODULE_FILENAME@','pointn_linestring' - LANGUAGE 'c' with (isstrict); - -CREATE FUNCTION exteriorring(GEOMETRY) - RETURNS GEOMETRY - AS '@MODULE_FILENAME@','exteriorring_polygon' - LANGUAGE 'c' with (isstrict); - -CREATE FUNCTION numinteriorrings(GEOMETRY) - RETURNS INTEGER - AS '@MODULE_FILENAME@','numinteriorrings_polygon' - LANGUAGE 'c' with (isstrict); - -CREATE FUNCTION interiorringn(GEOMETRY,INTEGER) - RETURNS GEOMETRY - AS '@MODULE_FILENAME@','interiorringn_polygon' - LANGUAGE 'c' with (isstrict); - - -CREATE FUNCTION numgeometries(GEOMETRY) - RETURNS INTEGER - AS '@MODULE_FILENAME@','numgeometries_collection' - LANGUAGE 'c' with (isstrict); - -CREATE FUNCTION geometryn(GEOMETRY,INTEGER) - RETURNS GEOMETRY - AS '@MODULE_FILENAME@','geometryn_collection' - LANGUAGE 'c' with (isstrict); - -CREATE FUNCTION max_distance(GEOMETRY,GEOMETRY) - RETURNS float8 - AS '@MODULE_FILENAME@' - LANGUAGE 'c' with (isstrict,iscachable); - -CREATE FUNCTION optimistic_overlap(GEOMETRY,GEOMETRY,FLOAT8) - RETURNS BOOL - AS '@MODULE_FILENAME@' - LANGUAGE 'c' with (isstrict,iscachable); - -CREATE FUNCTION segmentize(GEOMETRY,FLOAT8) - RETURNS GEOMETRY - AS '@MODULE_FILENAME@' - LANGUAGE 'c' with (isstrict,iscachable); - - -CREATE FUNCTION distance(GEOMETRY,GEOMETRY) - RETURNS float8 - AS '@MODULE_FILENAME@' - LANGUAGE 'c' with (isstrict,iscachable); - -CREATE FUNCTION astext(geometry) - RETURNS TEXT - AS '@MODULE_FILENAME@','astext_geometry' - LANGUAGE 'c' with (isstrict); - -CREATE FUNCTION srid(geometry) - RETURNS INT4 - AS '@MODULE_FILENAME@','srid_geom' - LANGUAGE 'c' with (isstrict); - -CREATE FUNCTION geometryfromtext(geometry,int4) - RETURNS geometry - AS '@MODULE_FILENAME@','geometry_from_text' - LANGUAGE 'c' with (isstrict,iscachable); - -CREATE FUNCTION geomfromtext(geometry,int4) - RETURNS geometry - AS '@MODULE_FILENAME@','geometry_from_text' - LANGUAGE 'c' with (isstrict,iscachable); - -CREATE FUNCTION setSRID(geometry,int4) - RETURNS geometry - AS '@MODULE_FILENAME@','geometry_from_text' - LANGUAGE 'c' with (isstrict,iscachable); - -------- spheroid calcs - -CREATE FUNCTION length_spheroid(GEOMETRY,SPHEROID) - RETURNS FLOAT8 - AS '@MODULE_FILENAME@','length_ellipsoid' - LANGUAGE 'c' with (isstrict); - -CREATE FUNCTION length3d_spheroid(GEOMETRY,SPHEROID) - RETURNS FLOAT8 - AS '@MODULE_FILENAME@','length3d_ellipsoid' - LANGUAGE 'c' with (isstrict); - -CREATE FUNCTION distance_spheroid(GEOMETRY,GEOMETRY,SPHEROID) - RETURNS FLOAT8 - AS '@MODULE_FILENAME@','distance_ellipsoid' - LANGUAGE 'c' with (isstrict); - -------- generic operations - -CREATE FUNCTION length3d(GEOMETRY) - RETURNS FLOAT8 - AS '@MODULE_FILENAME@' - LANGUAGE 'c' with (isstrict); - -CREATE FUNCTION length(GEOMETRY) - RETURNS FLOAT8 - AS '@MODULE_FILENAME@','length2d' - LANGUAGE 'c' with (isstrict); - -CREATE FUNCTION area2d(GEOMETRY) - RETURNS FLOAT8 - AS '@MODULE_FILENAME@' - LANGUAGE 'c' with (isstrict); - -CREATE FUNCTION perimeter3d(GEOMETRY) - RETURNS FLOAT8 - AS '@MODULE_FILENAME@' - LANGUAGE 'c' with (isstrict); - -CREATE FUNCTION perimeter(GEOMETRY) - RETURNS FLOAT8 - AS '@MODULE_FILENAME@','perimeter2d' - LANGUAGE 'c' with (isstrict); - -CREATE FUNCTION truly_inside(GEOMETRY,GEOMETRY) - RETURNS bool - AS '@MODULE_FILENAME@' - LANGUAGE 'c' with (isstrict); - -CREATE FUNCTION point_inside_circle(GEOMETRY,float8,float8,float8) - RETURNS bool - AS '@MODULE_FILENAME@' - LANGUAGE 'c' with (isstrict); - -CREATE FUNCTION startpoint(GEOMETRY) - RETURNS GEOMETRY - AS '@MODULE_FILENAME@' - LANGUAGE 'c' with (isstrict); - -CREATE FUNCTION endpoint(GEOMETRY) - RETURNS GEOMETRY - AS '@MODULE_FILENAME@' - LANGUAGE 'c' with (isstrict); - -CREATE FUNCTION isclosed(GEOMETRY) - RETURNS boolean - AS '@MODULE_FILENAME@' - LANGUAGE 'c' with (isstrict); - -CREATE FUNCTION centroid(GEOMETRY) - RETURNS GEOMETRY - AS '@MODULE_FILENAME@' - LANGUAGE 'c' with (isstrict); - - -------- bbox ops - -CREATE FUNCTION xmin(BOX3D) - RETURNS FLOAT8 - AS '@MODULE_FILENAME@','box3d_xmin' - LANGUAGE 'c' with (isstrict,iscachable); - -CREATE FUNCTION ymin(BOX3D) - RETURNS FLOAT8 - AS '@MODULE_FILENAME@','box3d_ymin' - LANGUAGE 'c' with (isstrict,iscachable); - -CREATE FUNCTION zmin(BOX3D) - RETURNS FLOAT8 - AS '@MODULE_FILENAME@','box3d_zmin' - LANGUAGE 'c' with (isstrict,iscachable); - -CREATE FUNCTION xmax(BOX3D) - RETURNS FLOAT8 - AS '@MODULE_FILENAME@','box3d_xmax' - LANGUAGE 'c' with (isstrict,iscachable); - -CREATE FUNCTION ymax(BOX3D) - RETURNS FLOAT8 - AS '@MODULE_FILENAME@','box3d_ymax' - LANGUAGE 'c' with (isstrict,iscachable); - -CREATE FUNCTION zmax(BOX3D) - RETURNS FLOAT8 - AS '@MODULE_FILENAME@','box3d_zmax' - LANGUAGE 'c' with (isstrict,iscachable); - -CREATE FUNCTION box3dtobox(BOX3D) - RETURNS BOX - AS '@MODULE_FILENAME@','box3dtobox' - LANGUAGE 'c' with (isstrict,iscachable); - -------- Aggregate - -CREATE FUNCTION combine_bbox(BOX3D,GEOMETRY) - RETURNS BOX3D - AS '@MODULE_FILENAME@' - LANGUAGE 'c'; - -CREATE AGGREGATE extent( - sfunc = combine_bbox, - basetype = GEOMETRY, - stype = BOX3D -); - -CREATE FUNCTION collector(GEOMETRY,GEOMETRY) - RETURNS GEOMETRY - AS '@MODULE_FILENAME@' - LANGUAGE 'c'; - -CREATE AGGREGATE collect( - sfunc = collector, - basetype = GEOMETRY, - stype = GEOMETRY -); - - - -------- OPERATOR functions - -CREATE FUNCTION geometry_overleft(GEOMETRY, GEOMETRY) RETURNS bool - AS '@MODULE_FILENAME@' LANGUAGE 'c' with (isstrict); - -CREATE FUNCTION geometry_overright(GEOMETRY, GEOMETRY) RETURNS bool - AS '@MODULE_FILENAME@' LANGUAGE 'c' with (isstrict); - -CREATE FUNCTION geometry_left(GEOMETRY, GEOMETRY) RETURNS bool - AS '@MODULE_FILENAME@' LANGUAGE 'c' with (isstrict); - -CREATE FUNCTION geometry_right(GEOMETRY, GEOMETRY) RETURNS bool - AS '@MODULE_FILENAME@' LANGUAGE 'c' with (isstrict); - -CREATE FUNCTION geometry_contain(GEOMETRY, GEOMETRY) RETURNS bool - AS '@MODULE_FILENAME@' LANGUAGE 'c' with (isstrict); - -CREATE FUNCTION geometry_contained(GEOMETRY, GEOMETRY) RETURNS bool - AS '@MODULE_FILENAME@' LANGUAGE 'c' with (isstrict); - -CREATE FUNCTION geometry_overlap(GEOMETRY, GEOMETRY) RETURNS bool - AS '@MODULE_FILENAME@' LANGUAGE 'c' with (isstrict); - -CREATE FUNCTION geometry_same(GEOMETRY, GEOMETRY) RETURNS bool - AS '@MODULE_FILENAME@' LANGUAGE 'c' with (isstrict); - ---------- functions for doing sorting-like things (not very usefull) - -CREATE FUNCTION geometry_lt(GEOMETRY, GEOMETRY) RETURNS bool - AS '@MODULE_FILENAME@' LANGUAGE 'c' with (isstrict); - -CREATE FUNCTION geometry_gt(GEOMETRY, GEOMETRY) RETURNS bool - AS '@MODULE_FILENAME@' LANGUAGE 'c' with (isstrict); - -CREATE FUNCTION geometry_eq(GEOMETRY, GEOMETRY) RETURNS bool - AS '@MODULE_FILENAME@' LANGUAGE 'c' with (isstrict); - ---------- functions for forcing geometry to be 2d or 3d - -CREATE FUNCTION force_2d(GEOMETRY) RETURNS GEOMETRY - AS '@MODULE_FILENAME@' LANGUAGE 'c' with (isstrict); - -CREATE FUNCTION force_3d(GEOMETRY) RETURNS GEOMETRY - AS '@MODULE_FILENAME@' LANGUAGE 'c' with (isstrict); - --------- cause geometry to be represented as a geometry collection - -CREATE FUNCTION force_collection(GEOMETRY) RETURNS GEOMETRY - AS '@MODULE_FILENAME@' LANGUAGE 'c' with (isstrict); - - ---- workaround for user defined VARIABLE length datatype default value bug -update pg_type set typdefault = NULL where typname = 'wkb'; -update pg_type set typdefault = NULL where typname = 'geometry'; -update pg_type set typdefault = NULL where typname = 'histogram2d'; - -end TRANSACTION; diff --git a/postgis_gist_71.sql.in b/postgis_gist_71.sql.in deleted file mode 100644 index 064ba13c3..000000000 --- a/postgis_gist_71.sql.in +++ /dev/null @@ -1,228 +0,0 @@ -BEGIN TRANSACTION; - --------- GiST support functions -create function ggeometry_consistent(opaque,GEOMETRY,int4) returns bool -as '@MODULE_FILENAME@' language 'C'; - -create function ggeometry_compress(opaque) returns opaque -as '@MODULE_FILENAME@' language 'C'; - -create function ggeometry_penalty(opaque,opaque,opaque) returns opaque -as '@MODULE_FILENAME@' language 'C'; - -create function ggeometry_picksplit(opaque, opaque) returns opaque -as '@MODULE_FILENAME@' language 'C'; - -create function ggeometry_union(bytea, opaque) returns opaque -as '@MODULE_FILENAME@' language 'C'; - -create function ggeometry_same(opaque, opaque, opaque) returns opaque -as '@MODULE_FILENAME@' language 'C'; - -create function rtree_decompress(opaque) returns opaque -as '@MODULE_FILENAME@' language 'C'; --------------------------- -create function postgis_gist_sel(oid, oid, int2, opaque, int4) returns float8 -as '@MODULE_FILENAME@' language 'C'; - ------- 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,opaque) returns float4 -as '@MODULE_FILENAME@' language 'C'; - ----------Create actual operators - -CREATE OPERATOR << ( - LEFTARG = GEOMETRY, RIGHTARG = GEOMETRY, PROCEDURE = geometry_left, - COMMUTATOR = '>>', - RESTRICT = positionsel, JOIN = positionjoinsel -); - -CREATE OPERATOR &< ( - LEFTARG = GEOMETRY, RIGHTARG = GEOMETRY, PROCEDURE = geometry_overleft, - COMMUTATOR = '&>', - RESTRICT = positionsel, JOIN = positionjoinsel -); - -CREATE OPERATOR && ( - LEFTARG = GEOMETRY, RIGHTARG = GEOMETRY, PROCEDURE = geometry_overlap, - COMMUTATOR = '&&', - RESTRICT = postgis_gist_sel, JOIN = positionjoinsel -); - -CREATE OPERATOR &> ( - LEFTARG = GEOMETRY, RIGHTARG = GEOMETRY, PROCEDURE = geometry_overright, - COMMUTATOR = '&<', - RESTRICT = positionsel, JOIN = positionjoinsel -); - -CREATE OPERATOR >> ( - LEFTARG = GEOMETRY, RIGHTARG = GEOMETRY, PROCEDURE = geometry_right, - COMMUTATOR = '<<', - RESTRICT = positionsel, JOIN = positionjoinsel -); - -CREATE OPERATOR ~= ( - LEFTARG = GEOMETRY, RIGHTARG = GEOMETRY, PROCEDURE = geometry_same, - COMMUTATOR = '=', - RESTRICT = eqsel, JOIN = eqjoinsel -); - - - -CREATE OPERATOR @ ( - LEFTARG = GEOMETRY, RIGHTARG = GEOMETRY, PROCEDURE = geometry_contained, - COMMUTATOR = '@', - RESTRICT = contsel, JOIN = contjoinsel -); - - -CREATE OPERATOR ~ ( - LEFTARG = GEOMETRY, RIGHTARG = GEOMETRY, PROCEDURE = geometry_contain, - COMMUTATOR = '@', - RESTRICT = contsel, JOIN = contjoinsel -); - -CREATE OPERATOR = ( - LEFTARG = GEOMETRY, RIGHTARG = GEOMETRY, PROCEDURE = geometry_eq, - COMMUTATOR = '=', - RESTRICT = contsel, JOIN = contjoinsel -); - -CREATE OPERATOR < ( - LEFTARG = GEOMETRY, RIGHTARG = GEOMETRY, PROCEDURE = geometry_lt, - COMMUTATOR = '<', - RESTRICT = contsel, JOIN = contjoinsel -); - -CREATE OPERATOR > ( - LEFTARG = GEOMETRY, RIGHTARG = GEOMETRY, PROCEDURE = geometry_gt, - COMMUTATOR = '>', - RESTRICT = contsel, JOIN = contjoinsel -); - - - - ---- old way = insert into pg_opclass values ('gist_geometry_ops'); - -INSERT INTO pg_opclass (opcname, opcdeftype) - SELECT 'gist_geometry_ops', oid - FROM pg_type - WHERE typname = 'geometry'; - ---- drop table rt_ops_tmp; - -SELECT o.oid AS opoid, o.oprname -INTO TABLE rt_ops_tmp -FROM pg_operator o, pg_type t -WHERE o.oprleft = t.oid - and t.typname = 'geometry'; - --- box_left -INSERT INTO pg_amop (amopid, amopclaid, amopopr, amopstrategy) - SELECT am.oid, opcl.oid, c.opoid, 1 - FROM pg_am am, pg_opclass opcl, rt_ops_tmp c - WHERE amname = 'gist' and opcname = 'gist_geometry_ops' - and c.oprname = '<<'; - --- box_overleft -INSERT INTO pg_amop (amopid, amopclaid, amopopr, amopstrategy) - SELECT am.oid, opcl.oid, c.opoid, 2 - FROM pg_am am, pg_opclass opcl, rt_ops_tmp c - WHERE amname = 'gist' and opcname = 'gist_geometry_ops' - and c.oprname = '&<'; - --- box_overlap -INSERT INTO pg_amop (amopid, amopclaid, amopopr, amopstrategy) - SELECT am.oid, opcl.oid, c.opoid, 3 - FROM pg_am am, pg_opclass opcl, rt_ops_tmp c - WHERE amname = 'gist' and opcname = 'gist_geometry_ops' - and c.oprname = '&&'; - --- box_overright -INSERT INTO pg_amop (amopid, amopclaid, amopopr, amopstrategy) - SELECT am.oid, opcl.oid, c.opoid, 4 - FROM pg_am am, pg_opclass opcl, rt_ops_tmp c - WHERE amname = 'gist' and opcname = 'gist_geometry_ops' - and c.oprname = '&>'; - --- box_right -INSERT INTO pg_amop (amopid, amopclaid, amopopr, amopstrategy) - SELECT am.oid, opcl.oid, c.opoid, 5 - FROM pg_am am, pg_opclass opcl, rt_ops_tmp c - WHERE amname = 'gist' and opcname = 'gist_geometry_ops' - and c.oprname = '>>'; - --- box_same -INSERT INTO pg_amop (amopid, amopclaid, amopopr, amopstrategy) - SELECT am.oid, opcl.oid, c.opoid, 6 - FROM pg_am am, pg_opclass opcl, rt_ops_tmp c - WHERE amname = 'gist' and opcname = 'gist_geometry_ops' - and c.oprname = '~='; - --- box_contains -INSERT INTO pg_amop (amopid, amopclaid, amopopr, amopstrategy) - SELECT am.oid, opcl.oid, c.opoid, 7 - FROM pg_am am, pg_opclass opcl, rt_ops_tmp c - WHERE amname = 'gist' and opcname = 'gist_geometry_ops' - and c.oprname = '~'; - --- box_contained -INSERT INTO pg_amop (amopid, amopclaid, amopopr, amopstrategy) - SELECT am.oid, opcl.oid, c.opoid, 8 - FROM pg_am am, pg_opclass opcl, rt_ops_tmp c - WHERE amname = 'gist' and opcname = 'gist_geometry_ops' - and c.oprname = '@'; - -DROP table rt_ops_tmp; - --- add the entries to amproc for the support methods --- note the amprocnum numbers associated with each are specific! -INSERT INTO pg_amproc (amid, amopclaid, amproc, amprocnum) - SELECT am.oid, opcl.oid, pro.oid, 1 - FROM pg_am am, pg_opclass opcl, pg_proc pro - WHERE amname = 'gist' and opcname = 'gist_geometry_ops' - and proname = 'ggeometry_consistent'; - -INSERT INTO pg_amproc (amid, amopclaid, amproc, amprocnum) - SELECT am.oid, opcl.oid, pro.oid, 2 - FROM pg_am am, pg_opclass opcl, pg_proc pro - WHERE amname = 'gist' and opcname = 'gist_geometry_ops' - and proname = 'ggeometry_union'; - -INSERT INTO pg_amproc (amid, amopclaid, amproc, amprocnum) - SELECT am.oid, opcl.oid, pro.oid, 3 - FROM pg_am am, pg_opclass opcl, pg_proc pro - WHERE amname = 'gist' and opcname = 'gist_geometry_ops' - and proname = 'ggeometry_compress'; - -INSERT INTO pg_amproc (amid, amopclaid, amproc, amprocnum) - SELECT am.oid, opcl.oid, pro.oid, 4 - FROM pg_am am, pg_opclass opcl, pg_proc pro - WHERE amname = 'gist' and opcname = 'gist_geometry_ops' - and proname = 'rtree_decompress'; - -INSERT INTO pg_amproc (amid, amopclaid, amproc, amprocnum) - SELECT am.oid, opcl.oid, pro.oid, 5 - FROM pg_am am, pg_opclass opcl, pg_proc pro - WHERE amname = 'gist' and opcname = 'gist_geometry_ops' - and proname = 'ggeometry_penalty'; - -INSERT INTO pg_amproc (amid, amopclaid, amproc, amprocnum) - SELECT am.oid, opcl.oid, pro.oid, 6 - FROM pg_am am, pg_opclass opcl, pg_proc pro - WHERE amname = 'gist' and opcname = 'gist_geometry_ops' - and proname = 'ggeometry_picksplit'; - -INSERT INTO pg_amproc (amid, amopclaid, amproc, amprocnum) - SELECT am.oid, opcl.oid, pro.oid, 7 - FROM pg_am am, pg_opclass opcl, pg_proc pro - WHERE amname = 'gist' and opcname = 'gist_geometry_ops' - and proname = 'ggeometry_same'; - -END TRANSACTION; diff --git a/postgis_gist_72.sql.in b/postgis_gist_72.sql.in deleted file mode 100644 index 4aec32cdb..000000000 --- a/postgis_gist_72.sql.in +++ /dev/null @@ -1,330 +0,0 @@ -BEGIN TRANSACTION; - ---drop function UPDATE_GEOMETRY_STATS(); --- UPDATE_GEOMETRY_STATS() -- all tables -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' ; ---select UPDATE_GEOMETRY_STATS(); - - ---drop function UPDATE_GEOMETRY_STATS(varchar,varchar); --- UPDATE_GEOMETRY_STATS(table name,column name) -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' ; ---select UPDATE_GEOMETRY_STATS('test_data','the_geom'); - - - - ---- create_histogram2d(BOX3D, boxesPerSide) ---- 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 (HISTOGRAM2D, tablename, columnname) -CREATE FUNCTION build_histogram2d (HISTOGRAM2D,text,text) - RETURNS HISTOGRAM2D - AS '@MODULE_FILENAME@','build_histogram2d' - LANGUAGE 'c' with (isstrict); - ----- explode_histogram2d(HISTOGRAM2D, tablename) -CREATE FUNCTION explode_histogram2d (HISTOGRAM2D,text) - RETURNS HISTOGRAM2D - AS '@MODULE_FILENAME@','explode_histogram2d' - LANGUAGE 'c' with (isstrict); - ----- estimate_histogram2d(HISTOGRAM2D, box) -CREATE FUNCTION estimate_histogram2d(HISTOGRAM2D,box) - RETURNS FLOAT8 - AS '@MODULE_FILENAME@','estimate_histogram2d' - LANGUAGE 'c' with (isstrict); - - - -create function postgisgistcostestimate(opaque,opaque,opaque,opaque,opaque,opaque,opaque,opaque) - RETURNS opaque - AS '@MODULE_FILENAME@','postgisgistcostestimate' - LANGUAGE 'c' with (isstrict); - --------- 7.2 GiST support functions -create function ggeometry_consistent(opaque,GEOMETRY,int4) returns bool -as '@MODULE_FILENAME@' language 'C'; - -create function ggeometry_compress(opaque) returns opaque -as '@MODULE_FILENAME@' language 'C'; - -create function gbox_penalty(opaque,opaque,opaque) returns opaque -as '@MODULE_FILENAME@' language 'C'; - -create function gbox_picksplit(opaque, opaque) returns opaque -as '@MODULE_FILENAME@' language 'C'; - -create function gbox_union(bytea, opaque) returns opaque -as '@MODULE_FILENAME@' language 'C'; - -create function gbox_same(box, box, opaque) returns opaque -as '@MODULE_FILENAME@' language 'C'; - -create function rtree_decompress(opaque) returns opaque -as '@MODULE_FILENAME@' language 'C'; --------------------------- -create function postgis_gist_sel (opaque, oid, opaque, int4) returns float8 -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,opaque) returns float4 -as '@MODULE_FILENAME@' language 'C'; - ----------Create actual operators - -CREATE OPERATOR << ( - LEFTARG = GEOMETRY, RIGHTARG = GEOMETRY, PROCEDURE = geometry_left, - COMMUTATOR = '>>', - RESTRICT = positionsel, JOIN = positionjoinsel -); - -CREATE OPERATOR &< ( - LEFTARG = GEOMETRY, RIGHTARG = GEOMETRY, PROCEDURE = geometry_overleft, - COMMUTATOR = '&>', - RESTRICT = positionsel, JOIN = positionjoinsel -); - -CREATE OPERATOR && ( - LEFTARG = GEOMETRY, RIGHTARG = GEOMETRY, PROCEDURE = geometry_overlap, - COMMUTATOR = '&&', - RESTRICT = postgis_gist_sel, JOIN = positionjoinsel -); - -CREATE OPERATOR &> ( - LEFTARG = GEOMETRY, RIGHTARG = GEOMETRY, PROCEDURE = geometry_overright, - COMMUTATOR = '&<', - RESTRICT = positionsel, JOIN = positionjoinsel -); - -CREATE OPERATOR >> ( - LEFTARG = GEOMETRY, RIGHTARG = GEOMETRY, PROCEDURE = geometry_right, - COMMUTATOR = '<<', - RESTRICT = positionsel, JOIN = positionjoinsel -); - -CREATE OPERATOR ~= ( - LEFTARG = GEOMETRY, RIGHTARG = GEOMETRY, PROCEDURE = geometry_same, - COMMUTATOR = '=', - RESTRICT = eqsel, JOIN = eqjoinsel -); - - - -CREATE OPERATOR @ ( - LEFTARG = GEOMETRY, RIGHTARG = GEOMETRY, PROCEDURE = geometry_contained, - COMMUTATOR = '@', - RESTRICT = contsel, JOIN = contjoinsel -); - - -CREATE OPERATOR ~ ( - LEFTARG = GEOMETRY, RIGHTARG = GEOMETRY, PROCEDURE = geometry_contain, - COMMUTATOR = '@', - RESTRICT = contsel, JOIN = contjoinsel -); - -CREATE OPERATOR = ( - LEFTARG = GEOMETRY, RIGHTARG = GEOMETRY, PROCEDURE = geometry_eq, - COMMUTATOR = '=', - RESTRICT = contsel, JOIN = contjoinsel -); - -CREATE OPERATOR < ( - LEFTARG = GEOMETRY, RIGHTARG = GEOMETRY, PROCEDURE = geometry_lt, - COMMUTATOR = '<', - RESTRICT = contsel, JOIN = contjoinsel -); - -CREATE OPERATOR > ( - LEFTARG = GEOMETRY, RIGHTARG = GEOMETRY, PROCEDURE = geometry_gt, - COMMUTATOR = '>', - RESTRICT = contsel, JOIN = contjoinsel -); - - - - ---- old way = insert into pg_opclass values ('gist_geometry_ops'); - -INSERT INTO pg_opclass (opcamid, opcname, opcintype, opcdefault, opckeytype) - VALUES ( - (SELECT oid FROM pg_am WHERE amname = 'gist'), - 'gist_geometry_ops', - (SELECT oid FROM pg_type WHERE typname = 'geometry'), - true, - (SELECT oid FROM pg_type WHERE typname = 'box')); - ---- drop table rt_ops_tmp; - -SELECT o.oid AS opoid, o.oprname -INTO TABLE rt_ops_tmp -FROM pg_operator o, pg_type t -WHERE o.oprleft = t.oid - and t.typname = 'geometry'; - --- poly_left -INSERT INTO pg_amop (amopclaid, amopstrategy, amopreqcheck, amopopr) - SELECT opcl.oid, 1, true, c.opoid - FROM pg_opclass opcl, rt_ops_tmp c - WHERE - opcamid = (SELECT oid FROM pg_am WHERE amname = 'gist') - and opcname = 'gist_geometry_ops' - and c.oprname = '<<'; - --- poly_overleft -INSERT INTO pg_amop (amopclaid, amopstrategy, amopreqcheck, amopopr) - SELECT opcl.oid, 2, true, c.opoid - FROM pg_opclass opcl, rt_ops_tmp c - WHERE - opcamid = (SELECT oid FROM pg_am WHERE amname = 'gist') - and opcname = 'gist_geometry_ops' - and c.oprname = '&<'; - --- poly_overlap -INSERT INTO pg_amop (amopclaid, amopstrategy, amopreqcheck, amopopr) - SELECT opcl.oid, 3, true, c.opoid - FROM pg_opclass opcl, rt_ops_tmp c - WHERE - opcamid = (SELECT oid FROM pg_am WHERE amname = 'gist') - and opcname = 'gist_geometry_ops' - and c.oprname = '&&'; - --- poly_overright -INSERT INTO pg_amop (amopclaid, amopstrategy, amopreqcheck, amopopr) - SELECT opcl.oid, 4, true, c.opoid - FROM pg_opclass opcl, rt_ops_tmp c - WHERE - opcamid = (SELECT oid FROM pg_am WHERE amname = 'gist') - and opcname = 'gist_geometry_ops' - and c.oprname = '&>'; - --- poly_right -INSERT INTO pg_amop (amopclaid, amopstrategy, amopreqcheck, amopopr) - SELECT opcl.oid, 5, true, c.opoid - FROM pg_opclass opcl, rt_ops_tmp c - WHERE - opcamid = (SELECT oid FROM pg_am WHERE amname = 'gist') - and opcname = 'gist_geometry_ops' - and c.oprname = '>>'; - --- poly_same -INSERT INTO pg_amop (amopclaid, amopstrategy, amopreqcheck, amopopr) - SELECT opcl.oid, 6, true, c.opoid - FROM pg_opclass opcl, rt_ops_tmp c - WHERE - opcamid = (SELECT oid FROM pg_am WHERE amname = 'gist') - and opcname = 'gist_geometry_ops' - and c.oprname = '~='; - --- poly_contains -INSERT INTO pg_amop (amopclaid, amopstrategy, amopreqcheck, amopopr) - SELECT opcl.oid, 7, true, c.opoid - FROM pg_opclass opcl, rt_ops_tmp c - WHERE - opcamid = (SELECT oid FROM pg_am WHERE amname = 'gist') - and opcname = 'gist_geometry_ops' - and c.oprname = '~'; - --- poly_contained -INSERT INTO pg_amop (amopclaid, amopstrategy, amopreqcheck, amopopr) - SELECT opcl.oid, 8, true, c.opoid - FROM pg_opclass opcl, rt_ops_tmp c - WHERE - opcamid = (SELECT oid FROM pg_am WHERE amname = 'gist') - and opcname = 'gist_geometry_ops' - and c.oprname = '@'; - -DROP table rt_ops_tmp; - --- add the entries to amproc for the support methods --- note the amprocnum numbers associated with each are specific! -INSERT INTO pg_amproc (amopclaid, amprocnum, amproc) - SELECT opcl.oid, 1, pro.oid - FROM pg_opclass opcl, pg_proc pro - WHERE - opcamid = (SELECT oid FROM pg_am WHERE amname = 'gist') - and opcname = 'gist_geometry_ops' - and proname = 'ggeometry_consistent'; - -INSERT INTO pg_amproc (amopclaid, amprocnum, amproc) - SELECT opcl.oid, 2, pro.oid - FROM pg_opclass opcl, pg_proc pro - WHERE - opcamid = (SELECT oid FROM pg_am WHERE amname = 'gist') - and opcname = 'gist_geometry_ops' - and proname = 'gbox_union'; - -INSERT INTO pg_amproc (amopclaid, amprocnum, amproc) - SELECT opcl.oid, 3, pro.oid - FROM pg_opclass opcl, pg_proc pro - WHERE - opcamid = (SELECT oid FROM pg_am WHERE amname = 'gist') - and opcname = 'gist_geometry_ops' - and proname = 'ggeometry_compress'; - -INSERT INTO pg_amproc (amopclaid, amprocnum, amproc) - SELECT opcl.oid, 4, pro.oid - FROM pg_opclass opcl, pg_proc pro - WHERE - opcamid = (SELECT oid FROM pg_am WHERE amname = 'gist') - and opcname = 'gist_geometry_ops' - and proname = 'rtree_decompress'; - -INSERT INTO pg_amproc (amopclaid, amprocnum, amproc) - SELECT opcl.oid, 5, pro.oid - FROM pg_opclass opcl, pg_proc pro - WHERE - opcamid = (SELECT oid FROM pg_am WHERE amname = 'gist') - and opcname = 'gist_geometry_ops' - and proname = 'gbox_penalty'; - -INSERT INTO pg_amproc (amopclaid, amprocnum, amproc) - SELECT opcl.oid, 6, pro.oid - FROM pg_opclass opcl, pg_proc pro - WHERE - opcamid = (SELECT oid FROM pg_am WHERE amname = 'gist') - and opcname = 'gist_geometry_ops' - and proname = 'gbox_picksplit'; - -INSERT INTO pg_amproc (amopclaid, amprocnum, amproc) - SELECT opcl.oid, 7, pro.oid - FROM pg_opclass opcl, pg_proc pro - WHERE - opcamid = (SELECT oid FROM pg_am WHERE amname = 'gist') - and opcname = 'gist_geometry_ops' - and proname = 'gbox_same'; - -END TRANSACTION; diff --git a/postgis_gist_73.sql.in b/postgis_gist_73.sql.in deleted file mode 100644 index d3e7fd15a..000000000 --- a/postgis_gist_73.sql.in +++ /dev/null @@ -1,204 +0,0 @@ -BEGIN TRANSACTION; - -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); - ---drop function UPDATE_GEOMETRY_STATS(); --- UPDATE_GEOMETRY_STATS() -- all tables -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' ; ---select UPDATE_GEOMETRY_STATS(); - - ---drop function UPDATE_GEOMETRY_STATS(varchar,varchar); --- UPDATE_GEOMETRY_STATS(table name,column name) -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' ; ---select UPDATE_GEOMETRY_STATS('test_data','the_geom'); - - - - ---- create_histogram2d(BOX3D, boxesPerSide) ---- 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 (HISTOGRAM2D, tablename, columnname) -CREATE FUNCTION build_histogram2d (HISTOGRAM2D,text,text) - RETURNS HISTOGRAM2D - AS '@MODULE_FILENAME@','build_histogram2d' - LANGUAGE 'c' with (isstrict); - ----- explode_histogram2d(HISTOGRAM2D, tablename) -CREATE FUNCTION explode_histogram2d (HISTOGRAM2D,text) - RETURNS HISTOGRAM2D - AS '@MODULE_FILENAME@','explode_histogram2d' - LANGUAGE 'c' with (isstrict); - ----- estimate_histogram2d(HISTOGRAM2D, box) -CREATE FUNCTION estimate_histogram2d(HISTOGRAM2D,box) - RETURNS FLOAT8 - AS '@MODULE_FILENAME@','estimate_histogram2d' - LANGUAGE 'c' with (isstrict); - -create function postgisgistcostestimate(opaque,opaque,opaque,opaque,opaque,opaque,opaque,opaque) - 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'; --------------------------- -create function postgis_gist_sel (internal, oid, internal, int4) returns float8 -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 actual operators - -CREATE OPERATOR << ( - LEFTARG = GEOMETRY, RIGHTARG = GEOMETRY, PROCEDURE = geometry_left, - COMMUTATOR = '>>', - RESTRICT = positionsel, JOIN = positionjoinsel -); - -CREATE OPERATOR &< ( - LEFTARG = GEOMETRY, RIGHTARG = GEOMETRY, PROCEDURE = geometry_overleft, - COMMUTATOR = '&>', - RESTRICT = positionsel, JOIN = positionjoinsel -); - -CREATE OPERATOR && ( - LEFTARG = GEOMETRY, RIGHTARG = GEOMETRY, PROCEDURE = geometry_overlap, - COMMUTATOR = '&&', - RESTRICT = postgis_gist_sel, JOIN = positionjoinsel -); - -CREATE OPERATOR &> ( - LEFTARG = GEOMETRY, RIGHTARG = GEOMETRY, PROCEDURE = geometry_overright, - COMMUTATOR = '&<', - RESTRICT = positionsel, JOIN = positionjoinsel -); - -CREATE OPERATOR >> ( - LEFTARG = GEOMETRY, RIGHTARG = GEOMETRY, PROCEDURE = geometry_right, - COMMUTATOR = '<<', - RESTRICT = positionsel, JOIN = positionjoinsel -); - -CREATE OPERATOR ~= ( - LEFTARG = GEOMETRY, RIGHTARG = GEOMETRY, PROCEDURE = geometry_same, - COMMUTATOR = '=', - RESTRICT = eqsel, JOIN = eqjoinsel -); - -CREATE OPERATOR @ ( - LEFTARG = GEOMETRY, RIGHTARG = GEOMETRY, PROCEDURE = geometry_contained, - COMMUTATOR = '@', - RESTRICT = contsel, JOIN = contjoinsel -); - -CREATE OPERATOR ~ ( - LEFTARG = GEOMETRY, RIGHTARG = GEOMETRY, PROCEDURE = geometry_contain, - COMMUTATOR = '@', - RESTRICT = contsel, JOIN = contjoinsel -); - -CREATE OPERATOR = ( - LEFTARG = GEOMETRY, RIGHTARG = GEOMETRY, PROCEDURE = geometry_eq, - COMMUTATOR = '=', - RESTRICT = contsel, JOIN = contjoinsel -); - -CREATE OPERATOR < ( - LEFTARG = GEOMETRY, RIGHTARG = GEOMETRY, PROCEDURE = geometry_lt, - COMMUTATOR = '<', - RESTRICT = contsel, JOIN = contjoinsel -); - -CREATE OPERATOR > ( - LEFTARG = GEOMETRY, RIGHTARG = GEOMETRY, PROCEDURE = geometry_gt, - COMMUTATOR = '>', - RESTRICT = contsel, JOIN = contjoinsel -); - ----------Create opclass - -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; -- 2.40.0