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 \'0.7\'::text as version'
LANGUAGE 'sql';


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

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

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

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




create function WKB_out(WKB)
	RETURNS cstring
   AS '/usr/pgsql/lib/contrib/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/contrib/libpostgis.so.0.7','CHIP_in'
   LANGUAGE 'c' with (isstrict);

create function CHIP_out(CHIP)
	RETURNS cstring
   AS '/usr/pgsql/lib/contrib/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
);






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

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

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

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

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

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


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

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

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

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



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

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

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


CREATE FUNCTION expand(BOX3D,float8)
   RETURNS BOX3D
   AS '/usr/pgsql/lib/contrib/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/contrib/libpostgis.so.0.7','asbinary_simple'
	     LANGUAGE 'c' WITH (iscachable,isstrict);

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

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


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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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


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

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

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

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

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


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

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

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

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

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

CREATE FUNCTION setSRID(geometry,int4)
   RETURNS geometry
   AS '/usr/pgsql/lib/contrib/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/contrib/libpostgis.so.0.7','length_ellipsoid'
	     LANGUAGE 'c'  with (isstrict);

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

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

-------  generic operations

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

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

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

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

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

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

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

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

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

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

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


------- bbox ops

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

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

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

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

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

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

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

------- Aggregate

CREATE FUNCTION combine_bbox(BOX3D,GEOMETRY)
   RETURNS BOX3D
   AS '/usr/pgsql/lib/contrib/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/contrib/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/contrib/libpostgis.so.0.7' LANGUAGE 'c' with (isstrict);

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

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

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

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

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

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

CREATE FUNCTION geometry_same(GEOMETRY, GEOMETRY) RETURNS bool
        AS '/usr/pgsql/lib/contrib/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/contrib/libpostgis.so.0.7'  LANGUAGE 'c' with (isstrict);

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

CREATE FUNCTION geometry_eq(GEOMETRY, GEOMETRY) RETURNS bool
        AS '/usr/pgsql/lib/contrib/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/contrib/libpostgis.so.0.7'  LANGUAGE 'c' with (isstrict);

CREATE FUNCTION force_3d(GEOMETRY) RETURNS GEOMETRY
        AS '/usr/pgsql/lib/contrib/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/contrib/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';


end TRANSACTION;
--BEGIN TRANSACTION;

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

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

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

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

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

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

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

------ 7.3 RTREE support functions

create function geometry_union(GEOMETRY,GEOMETRY) returns GEOMETRY 
as '/usr/pgsql/lib/contrib/libpostgis.so.0.7'   language 'C';
create function geometry_inter(GEOMETRY,GEOMETRY) returns GEOMETRY 
as '/usr/pgsql/lib/contrib/libpostgis.so.0.7'   language 'C';
create function geometry_size(GEOMETRY,internal) returns float4 
as '/usr/pgsql/lib/contrib/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 OPERATOR CLASS gist_geometry_ops
    DEFAULT FOR TYPE geometry USING gist AS
	OPERATOR 	1	<< , -- poly left
	OPERATOR 	2	&< , -- poly overleft
	OPERATOR 	3	&& (GEOMETRY,GEOMETRY), -- poly overlap
	OPERATOR 	4	&> , -- poly overright
	OPERATOR 	5	>> , -- poly right
	OPERATOR 	6	~= , -- poly same
	OPERATOR 	7	~ , -- poly contains
	OPERATOR 	8	@ , -- poly contained
	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 (internal, internal, internal),
	STORAGE 		box;




END TRANSACTION;