ALTIBASE Technical Center(http://atc.altibase.com)의 자료실에 등록된 [Spatial SQL User’s Manual Release 5.5.1] 문서에서 발췌했습니다.
============================================
◎ Geometry Reference Table
============================================
1. How to Install
You can add $ALTIBASE_HOME/thirdparty/ArcGIS/geometry_colums.sql to ALTIBASE package by running iSQL.
$ isql -u sys -p manager -f
$ALTIBASE_HOME/thirdparty/ArcGIS/geometry_columns.sql
2. How to Use
SPATIAL_REF_SYS and GEOMETRY_COLUMNS are PUBLIC SYNONYM tables. You can only look up them.
ADDGEOMETRYCOLUMNS and DROPGEOMETRYCOLUMNS are PUBLIC SYNONYM procedures. You can insert and delete information in GEOMETRY_COLUMNS table by executing them.
You can add extra information to geometry reference table besides information of geometry reference system by modifying $ALTIBASE_HOME/thirdparty/ArcGIS/geometry_colums.sql.
3. GEOMETRY_COLUMNS
You can use this to define and mange SRID (Spatial Reference ID) in GEOMETRY column.
Column name Type Description
F_TABLE_SCHEMA VARCHAR(256) User Name
F_TABLE_NAME VARCHAR(256) Name of Feature
F_GEOMETRY_COLUMN VARCHAR(256) COLUMN Name
COORD_DIMENSION INTEGER Dimension of Geometry Object
SRID INTEGER Spatial Reference Indentifier
4. SPATIAL_REF_SYS
You can manage information of SRID (Spatial Reference System) and SRS (Spatial Reference System) with this.
Column name Type Description
SRID INTEGER Spatial reference identifier
AUTH_NAME VARCHAR(80) Certified name
AUTH_SRID INTEGER Certified spatial reference identifier
SRTEXT VARCHAR (2048) Description of WKT geometries
PROJ4TEXT VARCHAR (2048) PROJ4 infromation
5. Stored Procedure
▷ ADDGEOMETRYCOLUMNS
ADDGEOMETRYCOLUMNS(f_schema VARCHAR(40), f_table_name VARCHAR(40), f_column_name VARCHAR(40), srid INTEGER);
ISQL> exec AddGeometryColumns( 'SYS', 'T2', 'I1', 100 );
Execute success.
ISQL> exec AddGeometryColumns( 'SYS', 'T2', 'I1', -1 );
[ERR-F1F14 : This column is already added.
▷ DROPGEOMETRYCOLUMNS
DROPGEOMETRYCOLUMNS( varchar(40), varchar(40), varchar(40) );
iSQL> exec DropGeometryColumns( 'SYS', 'T2', 'I1' );
Execute success.
ISQL> exec DropGeometryColumns( 'SYS', 'T1', 'I1' );
[ERR-F1F13 : This column is not geometry column.
============================================
◎ Spatial SQL
============================================
1. GEOMETRY Data Types
▷ POINT
▷ MULTIPOINT
▷ LINESTRING
▷ MULTILINESTRING
▷ POLYGON
▷ MULTIPOLYGON
▷ GEOMETRYCOLLECTION
ex)
CREATE TABLE road ( id INTEGER, obj GEOMETRY ) ;
INSERT INTO road VALUES (1, GEOMETRY'LINESTRING( 0 18, 10 21, 16 23, 28 26, 44 31 )') ;
INSERT INTO road VALUES (2, GEOMFROMTEXT('LINESTRING( 30 31, 56 34, 70 24)')) ;
INSERT INTO road VALUES (3, LINEFROMTEXT('LINESTRING( 70 38, 32 12, 36 36)')) ;
2. GEOMETRY Data Type Format
In Altibase, GEOMETRY data type can be expressed in one of the three ways described below :
▷ WKT (Well-Known Text)
▷ WKB (Well-Known Binary)
▷ A binary form that is used to store data in Altibase.
3. DDL For Geometry
▷ CREATE TABLE
CREATE TABLE table_name (column_name GEOMETRY [(precision)] );
ex)
CREATE TABLE t1 ( id INTEGER, obj GEOMETRY ) ;
CREATE TABLE t2 ( id INTEGER, obj GEOMETRY (128) ) ;
▷ CREATE INDEX
CREATE INDEX index_name ON table_name ( column_name ) [INDEXTYPE IS RTREE] ;
ex)
CREATE TABLE t1 ( id INTEGER, obj GEOMETRY ) ;
CREATE INDEX idx_t1 ON t1 ( obj ) ;
CREATE INDEX idx_t2 ON t2 ( obj ) INDEXTYPE IS RTREE ;
4. Spatial Functions for Altibase
Depending on their features, spatial functions that are available in Altibase can be classified as follows:
• Basic Functions: Used to find characteristics and basic attributes.
• Spatial Analysis Functions: Used to perform various analyses using GEOMETRY.
• Spatial Object Creation Functions: Used to create a spatial object in WKT or WKB format rather than the interior storage format of Altibase.
Functions support ST_* : ASTEXT or ST_ASTEXT
4.1 Basic Spatial Functions
▷ DIMENSION( GEOMETRY )
▷ GEOMETRYTYPE( GEOMETRY )
▷ ENVELOPE( GEOMETRY )
▷ ASTEXT( GEOMETRY[,precision] )
▷ ASBINARY( GEOMETRY )
▷ ISEMPTY( GEOMETRY )
▷ ISSIMPLE( GEOMETRY )
▷ ISVALID( GEOMETRY )
▷ BOUNDARY( GEOMETRY )
▷ X( GEOMETRY ) or COORDX( GEOMETRY )
▷ Y( GEOMETRY ) or COORDY( GEOMETRY )
▷ MINX( GEOMETRY )
▷ MINY( GEOMETRY )
▷ MAXX( GEOMETRY )
▷ MAXY( GEOMETRY )
▷ GEOMETRYLENGTH( GEOMETRY )
▷ STARTPOINT( GEOMETRY )
▷ ENDPOINT( GEOMETRY )
▷ ISCLOSED( GEOMETRY )
▷ ISRING( GEOMETRY )
▷ NUMPOINTS( GEOMETRY )
▷ POINTN( GEOMETRY, N )
▷ AREA( GEOMETRY )
▷ CENTROID( GEOMETRY )
▷ POINTONSURFACE( GEOMETRY )
▷ EXTERIORRING( GEOMETRY )
▷ NUMINTERIORRING( GEOMETRY )
▷ INTERIORRINGN( GEOMETRY, N )
▷ NUMGEOMETRIES( GEOMETRY )
▷ GEOMETRYN( GEOMETRY, N )
ex) SELECT MIN(MINX(F2)), MIN(MINY(F2)), MAX(MAXX(F2)), MAX(MAXY(F2) FROM TB1;
4.2. Spatial Analysis Functions
▷ DISTANCE( GEOMETRY1, GEOMETRY2 )
▷ BUFFER( GEOMETRY, NUMBER )
▷ CONVEXHULL( GEOMETRY )
▷ INTERSECTION( GEOMETRY1, GEOMETRY2 )
▷ UNION( GEOMETRY1, GEOMETRY2 )
▷ DIFFERENCE( GEOMETRY1, GEOMETRY2 )
▷ SYMDIFFERENCE( GEOMETRY1, GEOMETRY2 )
7. Spatial Object Creation Functions
▷ GEOMFROMTEXT( WKT )
▷ POINTFROMTEXT( WKT )
▷ LINEFROMTEXT( WKT )
▷ POLYFROMTEXT( WKT )
▷ MPOINTFROMTEXT( WKT )
▷ MLINEFROMTEXT( WKT )
▷ MPOLYFROMTEXT( WKT )
▷ GEOMCOLLFROMTEXT( WKT )
▷ GEOMFROMWKB( WKB )
▷ POINTFROMWKB( WKB )
▷ LINEFROMWKB( WKB )
▷ POLYFROMWKB( WKB )
▷ MPOINTFROMWKB( WKB )
▷ MLINEFROMWKB( WKB )
▷ MPOLYFROMWKB( WKB )
▷ GEOMCOLLFROMWKB( WKB )
4.3. Spatial Relational Operators
▷ EQUALS( GEOMETRY1, GEOMETRY2 )
▷ DISJOINT( GEOMETRY1, GEOMETRY2 )
▷ INTERSECTS( GEOMETRY1, GEOMETRY2 )
▷ TOUCHES( GEOMETRY1, GEOMETRY2 )
▷ CROSSES( GEOMETRY1, GEOMETRY2 )
▷ WITHIN( GEOMETRY1, GEOMETRY2 )
▷ CONTAINS( GEOMETRY1, GEOMETRY2 )
▷ OVERLAPS( GEOMETRY1, GEOMETRY2 )
▷ RELATE( GEOMETRY1, GEOMETRY2 , patterns )
▷ ISMBRINTERSECTS( GEOMETRY1, GEOMETRY2 )
▷ ISMBRWITHIN( GEOMETRY1, GEOMETRY2 )
▷ ISMBRCONTAINS( GEOMETRY1, GEOMETRY2 )
* PostGIS Reference도 한번 보세요~
http://postgis.refractions.net/documentation/manual-1.5/reference.html
