[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