2011년 6월 20일 월요일

Altibase Spatial SQL Quick Reference

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