[postgis-users] PostgreSQL 7.3 Support

carl anderson candrsn at mindspring.com
Sun Dec 8 22:13:48 PST 2002


PostGIS CVS is working correctly for me at a binary level.

I am obsessed about the postgis.sql / postgis_undef.sql pair.
I want to be sure that it can be completely removed if necessary.

for testing I recompiled Postgresql 7.1.3 and 7.2.3

Postgresql 7.1.3
     does not "configure" properly uner gcc3.2 (multiline version 
string)
        that effectively eliminates any RH8.0 user who won't hand edit 
the
        configure script. Maybe support here should diminish.
     help documents a DROP AGGREGATE (type) but the accepted syntax is
        DROP AGGREGATE type

Postgresql 7.2.3
     postgis-cvs compiles and installs correctly

Postgrsql 7.3
     DROP AGGREGATE (type) is now required and other syntax fails
     CREATE FUNCTION XXX_in (OPAQUE) RETURNS OPAQUE is automatically
	converted to
     CREATE FUNCTION XXX_in (CSTRING) RETURNS XXX.
     This causes the DROP FUNCTION XXX_in(OPAQUE) to fail.

I have hand edited a pair of postgis.sql / postgis_undef.sql to
work correctly for Postgresql 7.3.  They are inline at the end of
this message.
The test cycle was
dropdb test; createdb -Ttemplate0 test; createlang plpgsql test; psql 
-f postgis.sql test; psql -f postgis_undef.sql test;




what is the function postgiscostestimate(OPAQUE,OPAQUE,...) I cannot
find any use of it in the SQl or the C code.

can
     gbox_union (bytea, internal),
     rtree_decompress (internal),
     gbox_penalty (internal, internal, internal),
     gbox_picksplit (internal, internal),
     gbox_same (box, box, internal)
be renamed so as not to conflict with rtree_gist?  The rename needs 
only to be in the SQL file.




----------------------------------
Carl Anderson
candrsn at mindspring.com

New Hampshire tells us "common sense for all"
     where can I get mine?
----------------------------------


postgis.sql
-----------------------------------------------------------
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 createlang plpgsql <thisdatabasename> '::text 
order by message limit 1;



CREATE FUNCTION POSTGIS_VERSION() returns text
as 'select \'0.7\'::text as version'
LANGUAGE 'sql';


-- need this to define geometry_columns table
create function histogram2d_in(CSTRING)
	RETURNS HISTOGRAM2D
    AS '/usr/pgsql/lib/libpostgis.so.0.7'
    LANGUAGE 'c' with (isstrict);

create function histogram2d_out(HISTOGRAM2D)
	RETURNS CSTRING
    AS '/usr/pgsql/lib/libpostgis.so.0.7'
    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(<schema/database>,<table>,<geom col>)

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(<db name>,<table name>,<column name>)
--- 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) = <srid> 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(<db name>,<table name>,<column name>, <srid>, 
<type>,<dim>)
--- 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 = 
<db name>)
--- 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(CSTRING)
    RETURNS BOX3D    AS '/usr/pgsql/lib/libpostgis.so.0.7'
    LANGUAGE 'c' with (isstrict);

CREATE FUNCTION BOX3D_out(BOX3D)
    RETURNS CSTRING
    AS '/usr/pgsql/lib/libpostgis.so.0.7'
    LANGUAGE 'c' with (isstrict);

CREATE FUNCTION SPHEROID_in(CSTRING)
    RETURNS SPHEROID    AS 
'/usr/pgsql/lib/libpostgis.so.0.7','ellipsoid_in'
    LANGUAGE 'c' with (isstrict,iscachable);

CREATE FUNCTION SPHEROID_out(SPHEROID)
    RETURNS CSTRING
    AS '/usr/pgsql/lib/libpostgis.so.0.7','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(CSTRING)
	RETURNS WKB
    AS '/usr/pgsql/lib/libpostgis.so.0.7','WKB_in'
    LANGUAGE 'c' with (isstrict);




create function WKB_out(WKB)
	RETURNS CSTRING
    AS '/usr/pgsql/lib/libpostgis.so.0.7','WKB_out'
    LANGUAGE 'c' with (isstrict);


CREATE TYPE WKB (
	internallength = VARIABLE,
	input =  WKB_in,
	output =  WKB_out,
	storage= extended
);

create function CHIP_in(CSTRING)
	RETURNS CHIP
    AS '/usr/pgsql/lib/libpostgis.so.0.7','CHIP_in'
    LANGUAGE 'c' with (isstrict);

create function CHIP_out(CHIP)
	RETURNS CSTRING
    AS '/usr/pgsql/lib/libpostgis.so.0.7','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 '/usr/pgsql/lib/libpostgis.so.0.7','srid_chip'
	     LANGUAGE 'c'  with (isstrict);

CREATE FUNCTION height(chip)
    RETURNS INT4
    AS '/usr/pgsql/lib/libpostgis.so.0.7','height_chip'
	     LANGUAGE 'c'  with (isstrict);

CREATE FUNCTION factor(chip)
    RETURNS FLOAT4
    AS '/usr/pgsql/lib/libpostgis.so.0.7','factor_chip'
	     LANGUAGE 'c'  with (isstrict);

CREATE FUNCTION width(chip)
    RETURNS INT4
    AS '/usr/pgsql/lib/libpostgis.so.0.7','width_chip'
	     LANGUAGE 'c'  with (isstrict);

CREATE FUNCTION datatype(chip)
    RETURNS INT4
    AS '/usr/pgsql/lib/libpostgis.so.0.7','datatype_chip'
	     LANGUAGE 'c'  with (isstrict);

CREATE FUNCTION compression(chip)
    RETURNS INT4
    AS '/usr/pgsql/lib/libpostgis.so.0.7','compression_chip'
	     LANGUAGE 'c'  with (isstrict);


CREATE FUNCTION setSRID(chip,int4)
    RETURNS chip
    AS '/usr/pgsql/lib/libpostgis.so.0.7','setsrid_chip'
	     LANGUAGE 'c'  with (isstrict,iscachable);

CREATE FUNCTION setfactor(chip,float4)
    RETURNS chip
    AS '/usr/pgsql/lib/libpostgis.so.0.7','setfactor_chip'
	     LANGUAGE 'c'  with (isstrict,iscachable);

create function geometry_in(CSTRING)
	RETURNS GEOMETRY
    AS '/usr/pgsql/lib/libpostgis.so.0.7'
    LANGUAGE 'c' with (isstrict);

create function geometry_out(GEOMETRY)
	RETURNS CSTRING
    AS '/usr/pgsql/lib/libpostgis.so.0.7'
    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 '/usr/pgsql/lib/libpostgis.so.0.7','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 '/usr/pgsql/lib/libpostgis.so.0.7','CHIP_to_geom'
    LANGUAGE 'c' with (isstrict,iscachable);

CREATE FUNCTION box3d(GEOMETRY)
    RETURNS BOX3D
    AS '/usr/pgsql/lib/libpostgis.so.0.7','get_bbox_of_geometry'
	     LANGUAGE 'c' WITH (iscachable,isstrict);


CREATE FUNCTION box(GEOMETRY)
    RETURNS BOX
    AS '/usr/pgsql/lib/libpostgis.so.0.7','geometry2box'
	     LANGUAGE 'c' WITH (iscachable,isstrict);

CREATE FUNCTION geometry(BOX3D)
    RETURNS GEOMETRY
    AS '/usr/pgsql/lib/libpostgis.so.0.7','get_geometry_of_bbox'
	     LANGUAGE 'c' WITH (iscachable,isstrict);

CREATE FUNCTION geometry(text)
    RETURNS GEOMETRY
    AS '/usr/pgsql/lib/libpostgis.so.0.7','geometry_text'
	     LANGUAGE 'c' WITH (iscachable,isstrict);


CREATE FUNCTION expand(BOX3D,float8)
    RETURNS BOX3D
    AS '/usr/pgsql/lib/libpostgis.so.0.7','expand_bbox'
	     LANGUAGE 'c' WITH (iscachable,isstrict);


--------- functions for converting to wkb

CREATE FUNCTION asbinary(GEOMETRY)
    RETURNS WKB
    AS '/usr/pgsql/lib/libpostgis.so.0.7','asbinary_simple'
	     LANGUAGE 'c' WITH (iscachable,isstrict);

CREATE FUNCTION asbinary(GEOMETRY,TEXT)
    RETURNS WKB
    AS '/usr/pgsql/lib/libpostgis.so.0.7','asbinary_specify'
	     LANGUAGE 'c' WITH (iscachable,isstrict);

CREATE FUNCTION bytea(WKB)
    RETURNS bytea
    AS '/usr/pgsql/lib/libpostgis.so.0.7','WKBtoBYTEA'
	     LANGUAGE 'c' WITH (iscachable,isstrict);
---- Debug (info) functions


--CREATE FUNCTION index_thing(GEOMETRY)
--   RETURNS BOOL
--   AS '/usr/pgsql/lib/libpostgis.so.0.7'
--	     LANGUAGE 'c'  with (isstrict);

CREATE FUNCTION npoints(GEOMETRY)
    RETURNS INT4
    AS '/usr/pgsql/lib/libpostgis.so.0.7'
	     LANGUAGE 'c'  with (isstrict);

CREATE FUNCTION nrings(GEOMETRY)
    RETURNS INT4
    AS '/usr/pgsql/lib/libpostgis.so.0.7'
	     LANGUAGE 'c'  with (isstrict) ;

CREATE FUNCTION mem_size(GEOMETRY)
    RETURNS INT4
    AS '/usr/pgsql/lib/libpostgis.so.0.7'
	     LANGUAGE 'c'  with (isstrict);

CREATE FUNCTION numb_sub_objs(GEOMETRY)
    RETURNS INT4
    AS '/usr/pgsql/lib/libpostgis.so.0.7'
	     LANGUAGE 'c'  with (isstrict);

CREATE FUNCTION summary(GEOMETRY)
    RETURNS text
    AS '/usr/pgsql/lib/libpostgis.so.0.7'
	     LANGUAGE 'c'  with (isstrict);

CREATE FUNCTION translate(GEOMETRY,float8,float8,float8)
    RETURNS GEOMETRY
    AS '/usr/pgsql/lib/libpostgis.so.0.7'
	     LANGUAGE 'c' with (isstrict) ;

CREATE FUNCTION dimension(GEOMETRY)
    RETURNS INT4
    AS '/usr/pgsql/lib/libpostgis.so.0.7'
	     LANGUAGE 'c' with (isstrict) ;

CREATE FUNCTION geometrytype(GEOMETRY)
    RETURNS text
    AS '/usr/pgsql/lib/libpostgis.so.0.7'
	     LANGUAGE 'c'  with (isstrict);

CREATE FUNCTION envelope(GEOMETRY)
    RETURNS geometry
    AS '/usr/pgsql/lib/libpostgis.so.0.7'
	     LANGUAGE 'c'  with (isstrict);

CREATE FUNCTION x(GEOMETRY)
    RETURNS float8
    AS '/usr/pgsql/lib/libpostgis.so.0.7','x_point'
	     LANGUAGE 'c'  with (isstrict);

CREATE FUNCTION y(GEOMETRY)
    RETURNS float8
    AS '/usr/pgsql/lib/libpostgis.so.0.7','y_point'
	     LANGUAGE 'c'  with (isstrict);

CREATE FUNCTION z(GEOMETRY)
    RETURNS float8
    AS '/usr/pgsql/lib/libpostgis.so.0.7','z_point'
	     LANGUAGE 'c'  with (isstrict);

CREATE FUNCTION numpoints(GEOMETRY)
    RETURNS integer
    AS '/usr/pgsql/lib/libpostgis.so.0.7','numpoints_linestring'
	     LANGUAGE 'c'  with (isstrict);

CREATE FUNCTION pointn(GEOMETRY,INTEGER)
    RETURNS GEOMETRY
    AS '/usr/pgsql/lib/libpostgis.so.0.7','pointn_linestring'
	     LANGUAGE 'c'  with (isstrict);

CREATE FUNCTION exteriorring(GEOMETRY)
    RETURNS GEOMETRY
    AS '/usr/pgsql/lib/libpostgis.so.0.7','exteriorring_polygon'
	     LANGUAGE 'c'  with (isstrict);

CREATE FUNCTION numinteriorrings(GEOMETRY)
    RETURNS INTEGER
    AS '/usr/pgsql/lib/libpostgis.so.0.7','numinteriorrings_polygon'
	     LANGUAGE 'c'  with (isstrict);

CREATE FUNCTION interiorringn(GEOMETRY,INTEGER)
    RETURNS GEOMETRY
    AS '/usr/pgsql/lib/libpostgis.so.0.7','interiorringn_polygon'
	     LANGUAGE 'c'  with (isstrict);


CREATE FUNCTION numgeometries(GEOMETRY)
    RETURNS INTEGER
    AS '/usr/pgsql/lib/libpostgis.so.0.7','numgeometries_collection'
	     LANGUAGE 'c'  with (isstrict);

CREATE FUNCTION geometryn(GEOMETRY,INTEGER)
    RETURNS GEOMETRY
    AS '/usr/pgsql/lib/libpostgis.so.0.7','geometryn_collection'
	     LANGUAGE 'c'  with (isstrict);

CREATE FUNCTION max_distance(GEOMETRY,GEOMETRY)
    RETURNS float8
    AS '/usr/pgsql/lib/libpostgis.so.0.7'
	     LANGUAGE 'c'  with (isstrict,iscachable);

CREATE FUNCTION optimistic_overlap(GEOMETRY,GEOMETRY,FLOAT8)
    RETURNS BOOL
    AS '/usr/pgsql/lib/libpostgis.so.0.7'
	     LANGUAGE 'c'  with (isstrict,iscachable);

CREATE FUNCTION segmentize(GEOMETRY,FLOAT8)
    RETURNS GEOMETRY
    AS '/usr/pgsql/lib/libpostgis.so.0.7'
	     LANGUAGE 'c'  with (isstrict,iscachable);


CREATE FUNCTION distance(GEOMETRY,GEOMETRY)
    RETURNS float8
    AS '/usr/pgsql/lib/libpostgis.so.0.7'
	     LANGUAGE 'c'  with (isstrict,iscachable);

CREATE FUNCTION astext(geometry)
    RETURNS TEXT
    AS '/usr/pgsql/lib/libpostgis.so.0.7','astext_geometry'
	     LANGUAGE 'c'  with (isstrict);

CREATE FUNCTION srid(geometry)
    RETURNS INT4
    AS '/usr/pgsql/lib/libpostgis.so.0.7','srid_geom'
	     LANGUAGE 'c'  with (isstrict);

CREATE FUNCTION geometryfromtext(geometry,int4)
    RETURNS geometry
    AS '/usr/pgsql/lib/libpostgis.so.0.7','geometry_from_text'
	     LANGUAGE 'c'  with (isstrict,iscachable);

CREATE FUNCTION geomfromtext(geometry,int4)
    RETURNS geometry
    AS '/usr/pgsql/lib/libpostgis.so.0.7','geometry_from_text'
	     LANGUAGE 'c'  with (isstrict,iscachable);

CREATE FUNCTION setSRID(geometry,int4)
    RETURNS geometry
    AS '/usr/pgsql/lib/libpostgis.so.0.7','geometry_from_text'
	     LANGUAGE 'c'  with (isstrict,iscachable);

------- spheroid calcs

CREATE FUNCTION length_spheroid(GEOMETRY,SPHEROID)
    RETURNS FLOAT8
    AS '/usr/pgsql/lib/libpostgis.so.0.7','length_ellipsoid'
	     LANGUAGE 'c'  with (isstrict);

CREATE FUNCTION length3d_spheroid(GEOMETRY,SPHEROID)
    RETURNS FLOAT8
    AS '/usr/pgsql/lib/libpostgis.so.0.7','length3d_ellipsoid'
	     LANGUAGE 'c'  with (isstrict);

CREATE FUNCTION distance_spheroid(GEOMETRY,GEOMETRY,SPHEROID)
    RETURNS FLOAT8
    AS '/usr/pgsql/lib/libpostgis.so.0.7','distance_ellipsoid'
	     LANGUAGE 'c'  with (isstrict);

-------  generic operations

CREATE FUNCTION length3d(GEOMETRY)
    RETURNS FLOAT8
    AS '/usr/pgsql/lib/libpostgis.so.0.7'
    LANGUAGE 'c' with (isstrict);

CREATE FUNCTION length(GEOMETRY)
    RETURNS FLOAT8
    AS '/usr/pgsql/lib/libpostgis.so.0.7','length2d'
    LANGUAGE 'c' with (isstrict);

CREATE FUNCTION area2d(GEOMETRY)
    RETURNS FLOAT8
    AS '/usr/pgsql/lib/libpostgis.so.0.7'
    LANGUAGE 'c' with (isstrict);

CREATE FUNCTION perimeter3d(GEOMETRY)
    RETURNS FLOAT8
    AS '/usr/pgsql/lib/libpostgis.so.0.7'
    LANGUAGE 'c' with (isstrict);

CREATE FUNCTION perimeter(GEOMETRY)
    RETURNS FLOAT8
    AS '/usr/pgsql/lib/libpostgis.so.0.7','perimeter2d'
    LANGUAGE 'c' with (isstrict);

CREATE FUNCTION truly_inside(GEOMETRY,GEOMETRY)
    RETURNS bool
    AS '/usr/pgsql/lib/libpostgis.so.0.7'
    LANGUAGE 'c' with (isstrict);

CREATE FUNCTION point_inside_circle(GEOMETRY,float8,float8,float8)
    RETURNS bool
    AS '/usr/pgsql/lib/libpostgis.so.0.7'
    LANGUAGE 'c' with (isstrict);

CREATE FUNCTION startpoint(GEOMETRY)
    RETURNS GEOMETRY
    AS '/usr/pgsql/lib/libpostgis.so.0.7'
    LANGUAGE 'c' with (isstrict);

CREATE FUNCTION endpoint(GEOMETRY)
    RETURNS GEOMETRY
    AS '/usr/pgsql/lib/libpostgis.so.0.7'
    LANGUAGE 'c' with (isstrict);

CREATE FUNCTION isclosed(GEOMETRY)
    RETURNS boolean
    AS '/usr/pgsql/lib/libpostgis.so.0.7'
    LANGUAGE 'c' with (isstrict);

CREATE FUNCTION centroid(GEOMETRY)
    RETURNS GEOMETRY
    AS '/usr/pgsql/lib/libpostgis.so.0.7'
    LANGUAGE 'c' with (isstrict);


------- bbox ops

CREATE FUNCTION xmin(BOX3D)
    RETURNS FLOAT8
    AS '/usr/pgsql/lib/libpostgis.so.0.7','box3d_xmin'
    LANGUAGE 'c' with (isstrict,iscachable);

CREATE FUNCTION ymin(BOX3D)
    RETURNS FLOAT8
    AS '/usr/pgsql/lib/libpostgis.so.0.7','box3d_ymin'
    LANGUAGE 'c' with (isstrict,iscachable);

CREATE FUNCTION zmin(BOX3D)
    RETURNS FLOAT8
    AS '/usr/pgsql/lib/libpostgis.so.0.7','box3d_zmin'
    LANGUAGE 'c' with (isstrict,iscachable);

CREATE FUNCTION xmax(BOX3D)
    RETURNS FLOAT8
    AS '/usr/pgsql/lib/libpostgis.so.0.7','box3d_xmax'
    LANGUAGE 'c' with (isstrict,iscachable);

CREATE FUNCTION ymax(BOX3D)
    RETURNS FLOAT8
    AS '/usr/pgsql/lib/libpostgis.so.0.7','box3d_ymax'
    LANGUAGE 'c' with (isstrict,iscachable);

CREATE FUNCTION zmax(BOX3D)
    RETURNS FLOAT8
    AS '/usr/pgsql/lib/libpostgis.so.0.7','box3d_zmax'
    LANGUAGE 'c' with (isstrict,iscachable);

CREATE FUNCTION box3dtobox(BOX3D)
    RETURNS BOX
    AS '/usr/pgsql/lib/libpostgis.so.0.7','box3dtobox'
    LANGUAGE 'c' with (isstrict,iscachable);

------- Aggregate

CREATE FUNCTION combine_bbox(BOX3D,GEOMETRY)
    RETURNS BOX3D
    AS '/usr/pgsql/lib/libpostgis.so.0.7'
    LANGUAGE 'c';

CREATE AGGREGATE extent(
	sfunc = combine_bbox,
	basetype = GEOMETRY,
	stype = BOX3D
);

CREATE FUNCTION collector(GEOMETRY,GEOMETRY)
    RETURNS GEOMETRY
    AS '/usr/pgsql/lib/libpostgis.so.0.7'
	     LANGUAGE 'c';

CREATE AGGREGATE collect(
	sfunc = collector,
	basetype = GEOMETRY,
	stype = GEOMETRY
);



-------  OPERATOR functions

CREATE FUNCTION geometry_overleft(GEOMETRY, GEOMETRY) RETURNS bool
         AS '/usr/pgsql/lib/libpostgis.so.0.7' LANGUAGE 'c' with 
(isstrict);

CREATE FUNCTION geometry_overright(GEOMETRY, GEOMETRY) RETURNS bool
         AS '/usr/pgsql/lib/libpostgis.so.0.7' LANGUAGE 'c' with 
(isstrict);

CREATE FUNCTION geometry_left(GEOMETRY, GEOMETRY) RETURNS bool
         AS '/usr/pgsql/lib/libpostgis.so.0.7'  LANGUAGE 'c' with 
(isstrict);

CREATE FUNCTION geometry_right(GEOMETRY, GEOMETRY) RETURNS bool
         AS '/usr/pgsql/lib/libpostgis.so.0.7' LANGUAGE 'c' with 
(isstrict);

CREATE FUNCTION geometry_contain(GEOMETRY, GEOMETRY) RETURNS bool
         AS '/usr/pgsql/lib/libpostgis.so.0.7'  LANGUAGE 'c' with 
(isstrict);

CREATE FUNCTION geometry_contained(GEOMETRY, GEOMETRY) RETURNS bool
         AS '/usr/pgsql/lib/libpostgis.so.0.7'  LANGUAGE 'c' with 
(isstrict);

CREATE FUNCTION geometry_overlap(GEOMETRY, GEOMETRY) RETURNS bool
         AS '/usr/pgsql/lib/libpostgis.so.0.7'  LANGUAGE 'c' with 
(isstrict);

CREATE FUNCTION geometry_same(GEOMETRY, GEOMETRY) RETURNS bool
         AS '/usr/pgsql/lib/libpostgis.so.0.7'  LANGUAGE 'c' with 
(isstrict);

--------- functions for doing sorting-like things (not very usefull)

CREATE FUNCTION geometry_lt(GEOMETRY, GEOMETRY) RETURNS bool
         AS '/usr/pgsql/lib/libpostgis.so.0.7'  LANGUAGE 'c' with 
(isstrict);

CREATE FUNCTION geometry_gt(GEOMETRY, GEOMETRY) RETURNS bool
         AS '/usr/pgsql/lib/libpostgis.so.0.7'  LANGUAGE 'c' with 
(isstrict);

CREATE FUNCTION geometry_eq(GEOMETRY, GEOMETRY) RETURNS bool
         AS '/usr/pgsql/lib/libpostgis.so.0.7'  LANGUAGE 'c' with 
(isstrict);

--------- functions for forcing geometry to be 2d or 3d

CREATE FUNCTION force_2d(GEOMETRY) RETURNS GEOMETRY
         AS '/usr/pgsql/lib/libpostgis.so.0.7'  LANGUAGE 'c' with 
(isstrict);

CREATE FUNCTION force_3d(GEOMETRY) RETURNS GEOMETRY
         AS '/usr/pgsql/lib/libpostgis.so.0.7'  LANGUAGE 'c' with 
(isstrict);

-------- cause geometry to be represented as a geometry collection

CREATE FUNCTION force_collection(GEOMETRY) RETURNS GEOMETRY
         AS '/usr/pgsql/lib/libpostgis.so.0.7'  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;
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);
CREATE CAST ( geometry as wkb ) WITH FUNCTION asbinary(geometry) AS 
IMPLICIT;

--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 '/usr/pgsql/lib/libpostgis.so.0.7','create_histogram2d'
	     LANGUAGE 'c'  with (isstrict);


---- build_histogram2d (HISTOGRAM2D, tablename, columnname)
CREATE FUNCTION build_histogram2d (HISTOGRAM2D,text,text)
    RETURNS HISTOGRAM2D
    AS '/usr/pgsql/lib/libpostgis.so.0.7','build_histogram2d'
	     LANGUAGE 'c'  with (isstrict);

---- explode_histogram2d(HISTOGRAM2D, tablename)
CREATE FUNCTION explode_histogram2d (HISTOGRAM2D,text)
    RETURNS HISTOGRAM2D
    AS '/usr/pgsql/lib/libpostgis.so.0.7','explode_histogram2d'
	     LANGUAGE 'c'  with (isstrict);

---- estimate_histogram2d(HISTOGRAM2D, box)
CREATE FUNCTION estimate_histogram2d(HISTOGRAM2D,box)
    RETURNS FLOAT8
    AS '/usr/pgsql/lib/libpostgis.so.0.7','estimate_histogram2d'
	     LANGUAGE 'c'  with (isstrict);

create function 
postgisgistcostestimate(opaque,opaque,opaque,opaque,opaque,opaque,opaque,opaque)
    RETURNS opaque
    AS '/usr/pgsql/lib/libpostgis.so.0.7','postgisgistcostestimate'
	     LANGUAGE 'c'  with (isstrict);

-------- 7.2 GiST support functions
create function ggeometry_consistent(internal,GEOMETRY,int4) returns 
bool as '/usr/pgsql/lib/libpostgis.so.0.7'   language 'C';

create function ggeometry_compress(internal) returns internal as 
'/usr/pgsql/lib/libpostgis.so.0.7'  language 'C';

create function gbox_penalty(internal,internal,internal) returns 
internal as '/usr/pgsql/lib/libpostgis.so.0.7'   language 'C';

create function gbox_picksplit(internal, internal) returns internal as 
'/usr/pgsql/lib/libpostgis.so.0.7'   language 'C';

create function gbox_union(bytea, internal) returns internal as 
'/usr/pgsql/lib/libpostgis.so.0.7'   language 'C';

create function gbox_same(box, box, internal) returns internal as 
'/usr/pgsql/lib/libpostgis.so.0.7'   language 'C';

create function rtree_decompress(internal) returns internal
as '/usr/pgsql/lib/libpostgis.so.0.7'   language 'C';
--------------------------
create function postgis_gist_sel (internal, oid, internal, int4)  
returns float8 as '/usr/pgsql/lib/libpostgis.so.0.7'   language 'C';

------ 7.2 RTREE support functions

create function geometry_union(GEOMETRY,GEOMETRY) returns GEOMETRY as 
'/usr/pgsql/lib/libpostgis.so.0.7'   language 'C';
create function geometry_inter(GEOMETRY,GEOMETRY) returns GEOMETRY as 
'/usr/pgsql/lib/libpostgis.so.0.7'   language 'C';
create function geometry_size(GEOMETRY,internal) returns float4 as 
'/usr/pgsql/lib/libpostgis.so.0.7'   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;



postgis_undef.sql
----------------------------------------------------------------------
begin;
--- indexing meta table stuff
drop type GEOMETRY cascade;
drop type WKB cascade;
drop type BOX3D cascade;
drop type SPHEROID cascade;
drop type CHIP cascade;
drop type HISTOGRAM2D cascade;
--drop type histogram2d cascade;

--- functions
drop function POSTGIS_VERSION ();
drop function FIX_GEOMETRY_COLUMNS ();
drop function find_SRID (varchar,varchar,varchar);
drop function get_proj4_from_srid (integer);
drop function DropGeometryColumn (varchar,varchar,varchar);
drop function AddGeometryColumn 
(varchar,varchar,varchar,integer,varchar,integer);
drop function UPDATE_GEOMETRY_STATS ();
drop function UPDATE_GEOMETRY_STATS (varchar,varchar);

drop function postgisgistcostestimate 
(opaque,opaque,opaque,opaque,opaque,opaque,opaque,opaque);
drop function ggeometry_compress (internal);
drop function gbox_penalty (internal,internal,internal);
drop function gbox_picksplit (internal, internal);
drop function gbox_union (bytea, internal);
drop function gbox_same (box, box, internal);
drop function rtree_decompress (internal);
drop function postgis_gist_sel  (internal, oid, internal, int4);
----tables
drop table spatial_ref_sys;
drop table geometry_columns;

end;



More information about the postgis-users mailing list