Spatial index for gml:EnvelopeType slot types

From Omar Wiki

Jump to: navigation, search

Contents

[edit]

OMAR Spatial Index

This section describes how to add SQL triggers to Omar tables to create a spatial index for particular slot types. We use the minx,miny maxx,maxy convention for bounding box as it isn't possible to register the recommended gml:pos from the OGC ebRIM catalog profile spec as it doesn't validate :-)

Whilst this section deals with using PostgreSQL and the excellent PostGIS extension (http://postgis.refractions.net/) it is possible to use any spatial database since the SQL functions are OGC Well Known Text (WKT) and Simple Features for SQL (SFSQL).

[edit]

PostGIS

Full documentation for PostGIS is available at http://postgis.refractions.net but in summary do (if your database is called omar)

psql -d omar -f lwpostgis.sql psql -d omar -f spatial_ref_sys.sql

or load and execute the sql files using a PostgreSQL client such as pgAdmin III.

[edit]

Extents Table

We need to configure a spatial table to register certain extrinsic objects and their geometric properties. We do this as follows 

 CREATE TABLE dataset_extents(
   id serial not null PRIMARY KEY, 
   extrinsic_object_fk varchar not null REFERENCES extrinsicobject(id) ON DELETE CASCADE, 
   extents geometry not null); 


 CREATE index extents_idx on dataset_extents using gist(extents);

Note that we have create a spatial index on this table.

It is very important to alter the owner of this table to the default owner of your database so that it can be accessed by the Java container. 

 ALTER TABLE dataset_extents OWNER TO omar;

[edit]

Create Trigger

We need to create a trigger on the slot table so that every time a slot is registered with the slot type of gml:EnvelopeType we update our extents table. 

   CREATE OR REPLACE FUNCTION update_extents_geom() RETURNS TRIGGER AS $update_extents_geom$
       DECLARE
           minx          varchar;
           miny          varchar;
           maxx          varchar;
           maxy          varchar;
           envelope      geometry;
       BEGIN
           IF (NEW.slottype = 'gml:EnvelopeType') THEN
               -- slot value is of type minx,miny maxx,maxy and is in lat lon
               minx = split_part(new.value,',', 1);
               miny = split_part(split_part(new.value,',', 2), ' ', 1);
               maxx = split_part(split_part(new.value,',', 2), ' ', 2);
               maxy = split_part(new.value,',', 3);
               envelope = GEOMFROMEWKT('SRID=4326;POLYGON((' || minx || ' ' || maxy || ',' || minx || ' ' || miny || ',' || maxx || ' ' || miny || ',' || maxx || ' ' || maxy || ',' || minx || ' ' || maxy || '))');
               IF (TG_OP = 'UPDATE') THEN
                   UPDATE dataset_extents SET extents = envelope WHERE extrinsic_object_fk=NEW.parent;
               ELSEIF (TG_OP = 'INSERT') THEN
                   INSERT INTO dataset_extents (extrinsic_object_fk, extents) VALUES (NEW.parent, envelope);
               END IF;
           END IF;
           RETURN NULL;
       END;
   $update_extents_geom$ LANGUAGE plpgsql;


   CREATE TRIGGER update_extents_geom
   AFTER INSERT OR UPDATE ON slot
       FOR EACH ROW EXECUTE PROCEDURE update_extents_geom();

[edit]

Test Trigger 

 INSERT into extrinsicobject(id,lid,status,isopaque) VALUES ('spatialtest', 'spatialtest','on','1');
 INSERT into slot(sequenceid, name_, slottype, value, parent) VALUES (1, 'spatialtest','gml:EnvelopeType', '0,0 1,1', 'spatialtest');
 SELECT *, AsText(extents) from dataset_extents;

delete the inserted row 

 DELETE  from extrinsicobject where id='spatialtest';

Test the cascading delete 

 SELECT COUNT(*) from dataset_extents where extrinsic_object_fk=’spatialtest’

Clean up 

 Delete from slot where parent='spatialtest'

Retrieved from "http://ebxmlrr.sourceforge.net/wiki/index.php/Spatial_index_for_gml:EnvelopeType_slot_types"