Database Scripts

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

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);

CREATE INDEX public_finditsearch_geometry_sidx
ON finditsearch
USING gist
(geometry);

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

Two tables are created:

1. finditsearch
2. finditconstraints

--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

--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

--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

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

--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)

--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) );

--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;

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

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

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

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