Database Scripts

The results of a FindIT search are written to a database. To build the required tables in the appropriate database, use the scripts below.

Two tables are created:

finditsearch
finditconstraints

1. PostGIS

Copy

CREATE SEQUENCE finditsearch_gid_seq
INCREMENT 1
MINVALUE 1
MAXVALUE 9223372036854775807
START 3
CACHE 1;

Copy

Query to create finditsearch table

CREATE TABLE finditsearch
(
gid integer NOT NULL DEFAULT nextval('finditsearch_gid_seq'::regclass),
geometry geometry(Geometry,27700),
polygonid uuid,
searchtime date,
constraints boolean,
CONSTRAINT finditsearch_pkey PRIMARY KEY (gid))
WITH (OIDS=FALSE);

Copy

CREATE INDEX public_finditsearch_geometry_sidx
ON finditsearch
USING gist
(geometry);

Copy

Query to create finditconstraints table

CREATE TABLE finditconstraints
(polygonid uuid,
constraintlayer text,
constraintidcolumn text
)
WITH (OIDS=FALSE);

2. SQL Server

Copy

Query to create finditsearch table

--create table finditsearch
CREATE TABLE [qa].[finditsearch](
gid [int] IDENTITY(1,1) NOT NULL,
[geometry] [geometry] NULL,
[polygonid] [uniqueidentifier] NULL,
[id] [smallint] NULL,
[searchtime] [datetime] NULL,
[constraints] [bit] NULL,
CONSTRAINT finditsearch_pkey PRIMARY KEY CLUSTERED
(    [gid] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, 
ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

Copy

--Create Spatial Index
CREATE SPATIAL INDEX [finditsearch_geometry_sidx] ON [qa].[finditsearch]
(    [geometry]
) USING  GEOMETRY_GRID
WITH (BOUNDING_BOX =(0, 0, 1000000, 1000000), GRIDS =(LEVEL_1 = MEDIUM,
LEVEL_2 = MEDIUM,LEVEL_3 = MEDIUM,LEVEL_4 = MEDIUM),
CELLS_PER_OBJECT = 16, PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, 
DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
GO

Copy

--Create Index
CREATE NONCLUSTERED INDEX [finditsearch_polygonid_idx] ON [qa].[finditsearch]
([polygonid] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, 
ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO

Copy

Query to create finditconstraints table

--create table finditconstraints
CREATE TABLE [qa].[finditconstraints]
(polygonid [uniqueidentifier] ,
constraintlayer text,
constraintidcolumn varchar(8000));

Copy

--Create Index
CREATE NONCLUSTERED INDEX [finditconstraints_idx] ON [qa].[finditconstraints]
([polygonid] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, 
DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO

Note: Use the column type 'text' in SQL Server prevents an index being built on the column ( use varchar(8000) for long text field which can be indexed)

3. Oracle

Copy

Query to create findit table

--create table
CREATE TABLE finditsearch (
gid NUMBER GENERATED ALWAYS AS IDENTITY,
geometry MDSYS.SDO_GEOMETRY,
polygonid  varchar2(32) NULL,
id  NUMBER(10)  NULL,
searchtime DATE NULL,
constraints CHAR CHECK (constraints IN ('1','0')),
CONSTRAINT finditsearch_pk PRIMARY KEY (gid) );

Copy

--Register spatial table (alter MBT to suit)
INSERT INTO user_sdo_geom_metadata (TABLE_NAME,COLUMN_NAME,DIMINFO,SRID) VALUES (
'FINDITSEARCH','GEOMETRY',
MDSYS.SDO_DIM_ARRAY
(MDSYS.SDO_DIM_ELEMENT('X', 0, 1000000, 0.50),
MDSYS.SDO_DIM_ELEMENT('Y', 0, 1000000, 0.50)),27700);
COMMIT;

Copy

--Create spatial index
CREATE INDEX finditsearch_geometry_sidx ON finditsearch(geometry) INDEXTYPE IS MDSYS.SPATIAL_INDEX;

Copy

--Create index
CREATE INDEX finditsearch_polygonid_idx on finditsearch (POLYGONID);

Copy

Query to create finditconstraints table

--create table
CREATE TABLE finditconstraints
(polygonid varchar2(32) NULL,
constraintlayer varchar2(4000),
constraintidcolumn varchar2(4000));

Copy

--create index
CREATE INDEX finditconstraints_polygonid_idx on finditconstraints(POLYGONID);

Database tables are created and populated as below:

Table name: SEARCH	Data Type	Description
SEARCHTIME	Datetime (SQL) or timestamp (PostGIS)	Date and Time of the search
POLYGONID	Number (auto increment)	Auto-generated ID for the drawn element (circle, polygon etc) used for the search.
GEOMETRY	Geometry	Spatial object of the polygon
CONSTRAINTS	Boolean	TRUE if items were found in the overlays and FALSE if no items found.

Here is an example:

Table name: CONSTRAINTS	Data Type	Description
POLYGONID	Number	Unique auto-generated ID for the drawn element (circle, polygon etc) used for the search.
CONSTRAINTLAYER	String	Column to denote the layer the result has been found in. This is layer from the FindIT SWD.
CONSTRAINTIDCOLUMN	String	Column containing the actual results. This column lists items found in the searched area on any particular layer.

Here is an example:

The tables are linked by the POLYGONID. In the CONSTRAINTS table all of the constraints against each POLYGONID are listed in individual rows. This is to cater for an unknown number of constraints being returned for each search.

After making the changes restart the IIS application pool.

NEXT: PDF templates for FindIT