[postgis-users] finalize PostgreSQL 7.3 support
carl anderson
candrsn at mindspring.com
Fri Nov 22 16:44:37 PST 2002
Please note that this stuff is finalizing and has not been fully
santized
for format. It is fully working though
There area 5 areas that need to be dealt with. 3 in Postgis 1 in
Mapserver
the use of the datatype opaque is strongly deprecated
use CSTRING or a specific datatype for XXX_in and XXX_out
functions
Casts must be explicitly defined (even the use of the cast in implicit
situations)
before if a conversion function existed it was used (even
implicitly)
the operator class for GIST needs to be declared using CREATE OPERATOR
CLASS
the direct manipulation of the pg_amproc table is dangerous and no
long works (pg_catalog)
Add / Drop Geometry Column needs to be schema aware, as would Mapserver
That's a bigger fish, that I haven't thought clearly about yet.
the Mapserver mappostgis.c
Explain now returns multiple tuples as a recordset, not as before
using the NOTICES channel
and lastly I changed to
DROP TYPE BOX3D cascade;
DROP TYPE GEOMETRY cascade;
These changes span the *.sql.in files. I did not presume to rewrite
the build mechanisms
so I hand edited the output file postgis.sql (turning off the
transaction blocks while at it
for testing).
The regression tests are trivial and broken for indexing. I used a
"real world" mapserver app
to check things out.
Patches against postgis-0.7.3 and mapserver-0.6.3 attached
the postgis-pg0.7.3.patch was created by a diff -u against postgis.sql
(to use the postgis_gist_73.sql file hand edit
'/usr/pgsql/lib/contrib/libpostgis.so.0.7'
and change it to the path of your installed lib.)
I am willing to insert these changes into the regular build path if I
get some direction
on how to mangle the existing *.sql.in files. The necesary changes
span postgis.sql.in and
postgis_gist.sql.in
-------------- next part --------------
A non-text attachment was scrubbed...
Name: postgis_gist_73.sql
Type: text/x-sql
Size: 29214 bytes
Desc: not available
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20021122/e522d089/attachment.bin>
-------------- next part --------------
A non-text attachment was scrubbed...
Name: pg0.7.3-ms3.6.3.patch
Type: text/x-patch
Size: 2252 bytes
Desc: not available
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20021122/e522d089/attachment-0001.bin>
-------------- next part --------------
--- postgis.sql Fri Nov 22 19:12:56 2002
+++ postgis_gist_73.sql Fri Nov 22 19:27:41 2002
@@ -212,23 +212,23 @@
-CREATE FUNCTION BOX3D_in(opaque)
+CREATE FUNCTION BOX3D_in(geometry)
RETURNS BOX3D
AS '/usr/pgsql/lib/contrib/libpostgis.so.0.7'
LANGUAGE 'c' with (isstrict);
-CREATE FUNCTION BOX3D_out(opaque)
- RETURNS opaque
+CREATE FUNCTION BOX3D_out(box3d)
+ RETURNS cstring
AS '/usr/pgsql/lib/contrib/libpostgis.so.0.7'
LANGUAGE 'c' with (isstrict);
-CREATE FUNCTION SPHEROID_in(opaque)
+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(opaque)
- RETURNS opaque
+CREATE FUNCTION SPHEROID_out(spheroid)
+ RETURNS cstring
AS '/usr/pgsql/lib/contrib/libpostgis.so.0.7','ellipsoid_out'
LANGUAGE 'c' with (isstrict);
@@ -246,7 +246,7 @@
output = BOX3D_out
);
-create function WKB_in(opaque)
+create function WKB_in(cstring)
RETURNS WKB
AS '/usr/pgsql/lib/contrib/libpostgis.so.0.7','WKB_in'
LANGUAGE 'c' with (isstrict);
@@ -254,8 +254,8 @@
-create function WKB_out(opaque)
- RETURNS opaque
+create function WKB_out(WKB)
+ RETURNS cstring
AS '/usr/pgsql/lib/contrib/libpostgis.so.0.7','WKB_out'
LANGUAGE 'c' with (isstrict);
@@ -267,13 +267,13 @@
storage= extended
);
-create function CHIP_in(opaque)
+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(opaque)
- RETURNS opaque
+create function CHIP_out(CHIP)
+ RETURNS cstring
AS '/usr/pgsql/lib/contrib/libpostgis.so.0.7','CHIP_out'
LANGUAGE 'c' with (isstrict);
@@ -332,13 +332,13 @@
AS '/usr/pgsql/lib/contrib/libpostgis.so.0.7','setfactor_chip'
LANGUAGE 'c' with (isstrict,iscachable);
-create function geometry_in(opaque)
+create function geometry_in(cstring)
RETURNS GEOMETRY
AS '/usr/pgsql/lib/contrib/libpostgis.so.0.7'
LANGUAGE 'c' with (isstrict);
-create function geometry_out(opaque)
- RETURNS opaque
+create function geometry_out(GEOMETRY)
+ RETURNS cstring
AS '/usr/pgsql/lib/contrib/libpostgis.so.0.7'
LANGUAGE 'c' with (isstrict);
@@ -386,11 +386,13 @@
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
@@ -416,8 +418,8 @@
AS '/usr/pgsql/lib/contrib/libpostgis.so.0.7','asbinary_specify'
LANGUAGE 'c' WITH (iscachable,isstrict);
-CREATE FUNCTION bytea(WKB)
- RETURNS bytea
+CREATE FUNCTION bytea(WKB)
+ RETURNS bytea
AS '/usr/pgsql/lib/contrib/libpostgis.so.0.7','WKBtoBYTEA'
LANGUAGE 'c' WITH (iscachable,isstrict);
---- Debug (info) functions
@@ -765,40 +767,40 @@
end TRANSACTION;
-BEGIN TRANSACTION;
+--BEGIN TRANSACTION;
--------- 7.2 GiST support functions
-create function ggeometry_consistent(opaque,GEOMETRY,int4) returns bool
+-------- 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(opaque) returns opaque
+create function ggeometry_compress(internal) returns internal
as '/usr/pgsql/lib/contrib/libpostgis.so.0.7' language 'C';
-create function gbox_penalty(opaque,opaque,opaque) returns opaque
+create function gbox_penalty(internal,internal,internal) returns internal
as '/usr/pgsql/lib/contrib/libpostgis.so.0.7' language 'C';
-create function gbox_picksplit(opaque, opaque) returns opaque
+create function gbox_picksplit(internal, internal) returns internal
as '/usr/pgsql/lib/contrib/libpostgis.so.0.7' language 'C';
-create function gbox_union(bytea, opaque) returns opaque
+create function gbox_union(bytea, internal) returns internal
as '/usr/pgsql/lib/contrib/libpostgis.so.0.7' language 'C';
-create function gbox_same(box, box, opaque) returns opaque
+create function gbox_same(internal, internal, internal) returns internal
as '/usr/pgsql/lib/contrib/libpostgis.so.0.7' language 'C';
-create function rtree_decompress(opaque) returns opaque
+create function rtree_decompress(internal) returns internal
as '/usr/pgsql/lib/contrib/libpostgis.so.0.7' language 'C';
--------------------------
-create function postgis_gist_sel (opaque, oid, opaque, int4) returns float8
+create function postgis_gist_sel (internal, oid, internal, int4) returns float8
as '/usr/pgsql/lib/contrib/libpostgis.so.0.7' language 'C';
------- 7.2 RTREE support functions
+------ 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,opaque) returns float4
+create function geometry_size(GEOMETRY,internal) returns float4
as '/usr/pgsql/lib/contrib/libpostgis.so.0.7' language 'C';
---------Create actual operators
@@ -873,156 +875,26 @@
);
+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;
---- old way = insert into pg_opclass values ('gist_geometry_ops');
-INSERT INTO pg_opclass (opcamid, opcname, opcintype, opcdefault, opckeytype)
- VALUES (
- (SELECT oid FROM pg_am WHERE amname = 'gist'),
- 'gist_geometry_ops',
- (SELECT oid FROM pg_type WHERE typname = 'geometry'),
- true,
- (SELECT oid FROM pg_type WHERE typname = 'box'));
-
---- drop table rt_ops_tmp;
-
-SELECT o.oid AS opoid, o.oprname
-INTO TABLE rt_ops_tmp
-FROM pg_operator o, pg_type t
-WHERE o.oprleft = t.oid
- and t.typname = 'geometry';
-
--- poly_left
-INSERT INTO pg_amop (amopclaid, amopstrategy, amopreqcheck, amopopr)
- SELECT opcl.oid, 1, true, c.opoid
- FROM pg_opclass opcl, rt_ops_tmp c
- WHERE
- opcamid = (SELECT oid FROM pg_am WHERE amname = 'gist')
- and opcname = 'gist_geometry_ops'
- and c.oprname = '<<';
-
--- poly_overleft
-INSERT INTO pg_amop (amopclaid, amopstrategy, amopreqcheck, amopopr)
- SELECT opcl.oid, 2, true, c.opoid
- FROM pg_opclass opcl, rt_ops_tmp c
- WHERE
- opcamid = (SELECT oid FROM pg_am WHERE amname = 'gist')
- and opcname = 'gist_geometry_ops'
- and c.oprname = '&<';
-
--- poly_overlap
-INSERT INTO pg_amop (amopclaid, amopstrategy, amopreqcheck, amopopr)
- SELECT opcl.oid, 3, true, c.opoid
- FROM pg_opclass opcl, rt_ops_tmp c
- WHERE
- opcamid = (SELECT oid FROM pg_am WHERE amname = 'gist')
- and opcname = 'gist_geometry_ops'
- and c.oprname = '&&';
-
--- poly_overright
-INSERT INTO pg_amop (amopclaid, amopstrategy, amopreqcheck, amopopr)
- SELECT opcl.oid, 4, true, c.opoid
- FROM pg_opclass opcl, rt_ops_tmp c
- WHERE
- opcamid = (SELECT oid FROM pg_am WHERE amname = 'gist')
- and opcname = 'gist_geometry_ops'
- and c.oprname = '&>';
-
--- poly_right
-INSERT INTO pg_amop (amopclaid, amopstrategy, amopreqcheck, amopopr)
- SELECT opcl.oid, 5, true, c.opoid
- FROM pg_opclass opcl, rt_ops_tmp c
- WHERE
- opcamid = (SELECT oid FROM pg_am WHERE amname = 'gist')
- and opcname = 'gist_geometry_ops'
- and c.oprname = '>>';
-
--- poly_same
-INSERT INTO pg_amop (amopclaid, amopstrategy, amopreqcheck, amopopr)
- SELECT opcl.oid, 6, true, c.opoid
- FROM pg_opclass opcl, rt_ops_tmp c
- WHERE
- opcamid = (SELECT oid FROM pg_am WHERE amname = 'gist')
- and opcname = 'gist_geometry_ops'
- and c.oprname = '~=';
-
--- poly_contains
-INSERT INTO pg_amop (amopclaid, amopstrategy, amopreqcheck, amopopr)
- SELECT opcl.oid, 7, true, c.opoid
- FROM pg_opclass opcl, rt_ops_tmp c
- WHERE
- opcamid = (SELECT oid FROM pg_am WHERE amname = 'gist')
- and opcname = 'gist_geometry_ops'
- and c.oprname = '~';
-
--- poly_contained
-INSERT INTO pg_amop (amopclaid, amopstrategy, amopreqcheck, amopopr)
- SELECT opcl.oid, 8, true, c.opoid
- FROM pg_opclass opcl, rt_ops_tmp c
- WHERE
- opcamid = (SELECT oid FROM pg_am WHERE amname = 'gist')
- and opcname = 'gist_geometry_ops'
- and c.oprname = '@';
-
-DROP table rt_ops_tmp;
-
--- add the entries to amproc for the support methods
--- note the amprocnum numbers associated with each are specific!
-INSERT INTO pg_amproc (amopclaid, amprocnum, amproc)
- SELECT opcl.oid, 1, pro.oid
- FROM pg_opclass opcl, pg_proc pro
- WHERE
- opcamid = (SELECT oid FROM pg_am WHERE amname = 'gist')
- and opcname = 'gist_geometry_ops'
- and proname = 'ggeometry_consistent';
-
-INSERT INTO pg_amproc (amopclaid, amprocnum, amproc)
- SELECT opcl.oid, 2, pro.oid
- FROM pg_opclass opcl, pg_proc pro
- WHERE
- opcamid = (SELECT oid FROM pg_am WHERE amname = 'gist')
- and opcname = 'gist_geometry_ops'
- and proname = 'gbox_union';
-
-INSERT INTO pg_amproc (amopclaid, amprocnum, amproc)
- SELECT opcl.oid, 3, pro.oid
- FROM pg_opclass opcl, pg_proc pro
- WHERE
- opcamid = (SELECT oid FROM pg_am WHERE amname = 'gist')
- and opcname = 'gist_geometry_ops'
- and proname = 'ggeometry_compress';
-
-INSERT INTO pg_amproc (amopclaid, amprocnum, amproc)
- SELECT opcl.oid, 4, pro.oid
- FROM pg_opclass opcl, pg_proc pro
- WHERE
- opcamid = (SELECT oid FROM pg_am WHERE amname = 'gist')
- and opcname = 'gist_geometry_ops'
- and proname = 'rtree_decompress';
-
-INSERT INTO pg_amproc (amopclaid, amprocnum, amproc)
- SELECT opcl.oid, 5, pro.oid
- FROM pg_opclass opcl, pg_proc pro
- WHERE
- opcamid = (SELECT oid FROM pg_am WHERE amname = 'gist')
- and opcname = 'gist_geometry_ops'
- and proname = 'gbox_penalty';
-
-INSERT INTO pg_amproc (amopclaid, amprocnum, amproc)
- SELECT opcl.oid, 6, pro.oid
- FROM pg_opclass opcl, pg_proc pro
- WHERE
- opcamid = (SELECT oid FROM pg_am WHERE amname = 'gist')
- and opcname = 'gist_geometry_ops'
- and proname = 'gbox_picksplit';
-
-INSERT INTO pg_amproc (amopclaid, amprocnum, amproc)
- SELECT opcl.oid, 7, pro.oid
- FROM pg_opclass opcl, pg_proc pro
- WHERE
- opcamid = (SELECT oid FROM pg_am WHERE amname = 'gist')
- and opcname = 'gist_geometry_ops'
- and proname = 'gbox_same';
END TRANSACTION;
More information about the postgis-users
mailing list