Log4Classicning

SQL implementation using Geometry Types

GEOMETRY_COLUMNS
SPATIAL_REF_SYS
FEATURE TABLE

GEOMETRY_COLUMNS
1. id of feature table
2. name of geometry column
3. SRID of the column
4. coordinate dimension for the column

SPRIAL_REF_SYS
SRID, AUTH_NAME, AUTH_SRID, SRTEXT

Feature tables

Geometry Column
UDT(User Defined Type), the types for geometry are defined in blank-box terms, and all access to information adbout a Geomtry Type instance is through SQL functions. No attempt is made to distinguish functions that may access Type instance attributes from functions that may conpute values given a Type instance.

DDL
Spatial_ref_sys
CREATE TABLE SPATIAL_REF_SYS(
SRID INTEGER NOT NULL PRIMARY KEY,
AUTH_NAME CHARACTER VARYING,
AUTH_SRID INTEGER,
SRTEXT CHARACTER VARYING(2048)
)

geometry_columns
CREATE TABLE GEOMETRY_COLUMNS (
F_TABLE_CATALOG CHARACTER VARYING NOT NULL, -- feature table info
F_TABLE_SCHEMA CHARACTER VARYING NOT NULL, -- feature table info
F_TABLE_NAME CHARACTER VARYING NOT NULL, -- feature table info
F_GEOMETRY_COLUMN CHARACTER VARYING NOT NULL, -- name of geometry column
G_TABLE_CATALOG CHARACTER VARYING NOT NULL, -- geometry table info
G_TABLE_SCHEMA CHARACTER VARYING NOT NULL, -- geometry table info
G_TABLE_NAME CHARACTER VARYING NOT NULL, -- geometry table info
STORAGE_TYPE INTEGER, --0, normalized geometry implementation; 1, binary(WKB); NULL, geometry type implementation
GEOMETRY_TYPE INTEGER, --defined in WKB
COORD_DIMENSION INTEGER, --the dimension of coordinates
MAX_PPR INTEGER, --max points per row
SRID INTEGER NOT NULL REFERENCES SPATIAL_REF_SYS, --foreign key ref to srid
CONSTRAINT GC_PK PRIMARY KEY
(F_TABLE_CATALOG, F_TABLE_SCHEMA, F_TABLE_NAME, F_GEOMETRY_COLUMN)
)

CREATE TABLE <feature table name> (
<primary key column name> <primary key column type>,
… (other attributes for this feature table)
<geometry column name> <geometry column type>,
… (other geometry columns for this feature table)
PRIMARY KEY <primary key column name>,
FOREIGN KEY <geometry column name> REFERENCES <geometry table name>,
… (other geometry column constraints for this feature table)
)

geometry table
normalized SQL numeric types,
CREATE TABLE <table name>(
GID INTEGER NOT NULL,
ESEQ INTEGER NOT NULL,
ETYPE INTEGER NOT NULL,
SEQ INTEGER NOT NULL,
X1 <ordinate type>,
Y1 <ordinate type>,
Z1 <ordinate type>,
!Optional if Z-value is included
M1 <ordinate type>,
!Optional if M-value is included
... <repeated for each ordinate, repeated for each point>
X<MAX_PPR> <ordinate type>,
Y<MAX_PPR> <ordinate type>,
Z1<MAX_PPR> <ordinate type>,
!Optional if Z-value is included
M1<MAX_PPR> <ordinate type>,
!Optional if M-value is included
...,
<attribute> <attribute type>
CONSTRAINT GID_PK PRIMARY KEY (GID, ESEQ, SEQ)
)

Binary mode
CREATE TABLE <table name>
(
GID NUMERIC NOT NULL PRIMARY KEY,
XMIN <ordinate type>,
YMIN <ordinate type>,
ZMIN <ordinate type>,
MMIN <ordinate type>,
XMAX <ordinate type>,
YMAX <ordinate type>,
ZMAX <ordinate type>,
MMAX <ordinate type>,
WKB_GEOMETRY BIT VARYING(implementation size limit),
{<attribute> <attribute type>}*
)

Geometry Type
CREATE TABLE <feature table name> (
<primary key column name> <primary key column type>,
… (other attributes for this feature table)
<geometry column name> <geometry type>,
… (other geometry columns for this feature table)
PRIMARY KEY <primary key column name>,
CONSTRAINT SRS_1 CHECK (SRID(<geometry column name>)
in (
SELECT SRID from GEOMETRY_COLUMNS
where F_TABLE_CATALOG = <catalog> and
F_TABLE_SCHEMA = <schema> and
F_TABLE_NAME = <feature table name> and
F_GEOMETRY_COLUMN = <geometry column>
)
… ( spatial reference constraints for other geometry columns
in this feature table)
)

SQL rountines on generic geometris
Dimension
GeometryType
ASTEST
ASBINARY
SRID
ISEMPTY
ISSIMPLE
BOUNDARY
ENVELOPE
EQUALS
DISJOINT
INTERSECTS
TOUCHES
CROSSES
WITHIN
CONTAINS
OVERLAPS
RELATE
DISTANCE
INTERSECTION
DIFFERENCE
UNION
SYMDIFFERENCE
BUFFER
CONVEXHULL

SQL routines for Point
X
Y
Z
M

SQL routines for Curve
STARTPOINT
ENDPOINT
ISRING
LENGTH

SQL routines for LineString
NUMPOINTS
POINTN

SQL routines for Surface
AREA
CENTROID
POINTONSURFACE

SQL routines for Polygon
EXTERIORRING
INTERIORRINGS
NUMINTERIORRING
InteriorRingN

SQL routines for Polyhedral Surface
......

SQL routines for GeomCollection
NUMGEOMETRIES
GEOMETRYN