SIST ISO 19125-2:2005

Geographic information -- Simple feature access -- Part 2: SQL option

This part of ISO 19125:2004 specifies an SQL schema that supports storage, retrieval, query and update of simple geospatial feature collections via the SQL Call Level Interface (SQL/CLI) and establishes an architecture for the implementation of feature tables.
This part of ISO 19125:2004 defines terms to use within the architecture. of geographic information and defines a simple feature profile of ISO 19107. In addition, this part of ISO 19125:2004 describes a set of SQL Geometry Types together with SQL functions on those types. The Geometry Types and Functions described represent a profile of ISO 13249-3.
This part of ISO 19125:2004 standardizes the names and geometric definitions of the SQL Types for Geometry and the names, signatures and geometric definitions of the SQL Functions for Geometry.
This part of ISO 19125:2004 does not attempt to standardize and does not depend upon any part of the mechanism by which Types are added and maintained in the SQL environment, including the following:
the syntax and functionality provided for defining types;
the syntax and functionality provided for defining SQL functions;
the physical storage of type instances in the database;
specific terminology used to refer to User Defined Types, for example, UDT.

Information géographique -- Accès aux entités simples -- Partie 2: Option SQL

Geografske informacije – Dostop do enostavnih pojavov – 2. del: Možnost SQL

General Information

• Status: Withdrawn
• Publication Date: 31-Jan-2005
• Withdrawal Date: 31-May-2006
• ICS: 35.240.70 - IT applications in science
• Technical Committee: GIG - Geographic information
• Current Stage: 9900 - Withdrawal (Adopted Project)
• Start Date: 01-Jun-2006
• Due Date: 01-Jun-2006
• Completion Date: 01-Jun-2006
• Ref Project: ISO 19125-2:2004 - Geographic information — Simple feature access — Part 2: SQL option

Standards Content (Sample)

INTERNATIONAL ISO
STANDARD 19125-2
First edition
2004-08-01

Geographic information — Simple feature
access —
Part 2:
SQL option
Information géographique — Accès aux entités simples —
Partie 2: Option SQL




Reference number
ISO 19125-2:2004(E)
©
ISO 2004

---------------------- Page: 1 ----------------------
ISO 19125-2:2004(E)
PDF disclaimer
This PDF file may contain embedded typefaces. In accordance with Adobe's licensing policy, this file may be printed or viewed but
shall not be edited unless the typefaces which are embedded are licensed to and installed on the computer performing the editing. In
downloading this file, parties accept therein the responsibility of not infringing Adobe's licensing policy. The ISO Central Secretariat
accepts no liability in this area.
Adobe is a trademark of Adobe Systems Incorporated.
Details of the software products used to create this PDF file can be found in the General Info relative to the file; the PDF-creation
parameters were optimized for printing. Every care has been taken to ensure that the file is suitable for use by ISO member bodies. In
the unlikely event that a problem relating to it is found, please inform the Central Secretariat at the address given below.


©  ISO 2004
All rights reserved. Unless otherwise specified, no part of this publication may be reproduced or utilized in any form or by any means,
electronic or mechanical, including photocopying and microfilm, without permission in writing from either ISO at the address below or
ISO's member body in the country of the requester.
ISO copyright office
Case postale 56 • CH-1211 Geneva 20
Tel. + 41 22 749 01 11
Fax + 41 22 749 09 47
E-mail [e-mail]
Web www.iso.org
Published in Switzerland

ii © ISO 2004 – All rights reserved

---------------------- Page: 2 ----------------------
ISO 19125-2:2004(E)
Contents Page
Foreword. iv
Introduction . v
1 Scope. 1
2 Conformance . 2
3 Normative references . 2
4 Terms and definitions. 2
5 Symbols and abbreviated terms. 3
6 Architecture . 4
6.1 Architecture — SQL implementation of feature tables based on predefined data types. 4
6.2 Architecture — SQL with Geometry Types implementation of feature tables. 7
7 Clause component specifications. 12
7.1 Components — Implementation of feature tables based on predefined data types . 12
7.2 Components — SQL with Geometry Types implementation of feature tables. 17
Annex A (informative) Comparison of Simple feature access/SQL and SQL/MM – Spatial. 31
Annex B (normative) Conformance tests. 32

© ISO 2004 – All rights reserved iii

---------------------- Page: 3 ----------------------
ISO 19125-2:2004(E)
Foreword
ISO (the International Organization for Standardization) is a worldwide federation of national standards bodies
(ISO member bodies). The work of preparing International Standards is normally carried out through ISO
technical committees. Each member body interested in a subject for which a technical committee has been
established has the right to be represented on that committee. International organizations, governmental and
non-governmental, in liaison with ISO, also take part in the work. ISO collaborates closely with the
International Electrotechnical Commission (IEC) on all matters of electrotechnical standardization.
International Standards are drafted in accordance with the rules given in the ISO/IEC Directives, Part 2.
The main task of technical committees is to prepare International Standards. Draft International Standards
adopted by the technical committees are circulated to the member bodies for voting. Publication as an
International Standard requires approval by at least 75 % of the member bodies casting a vote.
Attention is drawn to the possibility that some of the elements of this document may be the subject of patent
rights. ISO shall not be held responsible for identifying any or all such patent rights.
ISO 19125-2 was prepared by Technical Committee ISO/TC 211, Geographic information/Geomatics from a
base document supplied by the Open GIS Consortium, Inc.
ISO 19125 consists of the following parts, under the general title Geographic information — Simple feature
access:
— Part 1: Common architecture
— Part 2: SQL option
Part 3: COM/OLE option is under preparation.

iv © ISO 2004 – All rights reserved

---------------------- Page: 4 ----------------------
ISO 19125-2:2004(E)
Introduction
The purpose of this part of ISO 19125 is to define a standard Structured Query Language (SQL) schema that
supports storage, retrieval, query and update of feature collections via the SQL Call-Level Interface (SQL/CLI)
(ISO/IEC 9075-3:2003). A feature has both spatial and non-spatial attributes. Spatial attributes are geometry
valued, and simple features are based on 2D geometry with linear interpolation between vertices. This part of
ISO 19125 is dependent on the common architectural components defined in ISO 19125-1.
Feature collections are stored as tables with geometry valued columns in a SQL-implementation; each feature
is a row in the table. The non-spatial attributes of features are mapped onto columns whose types are drawn
from the set of standard SQL data types. The spatial attributes of features are mapped onto columns whose
SQL data types are based on the underlying concept of additional geometric data types for SQL. A table
whose rows represent these features is referred to as a feature table. Such a table contains one or more
geometry valued columns. Feature-table schemas are described for two SQL-implementations:
implementations based on predefined data types and SQL with Geometry Types.
In an implementation based on predefined data types, a geometry-valued column is implemented as a Foreign
Key reference into a geometry table. A geometry value is stored using one or more rows in the geometry table.
The geometry table may be implemented using either standard SQL numeric types or SQL binary types;
schemas for both are described.
The term SQL with Geometry Types is used to refer to a SQL-implementation that has been extended with a
set of Geometry Types. In this environment, a geometry-valued column is implemented as a column whose
SQL type is drawn from this set of Geometry Types. The mechanism for extending the type system of
an SQL-implementation is through the definition of user defined User Defined Types. Commercial
SQL-implementations with user defined type support have been available since mid-1997.

© ISO 2004 – All rights reserved v

---------------------- Page: 5 ----------------------
INTERNATIONAL STANDARD ISO 19125-2:2004(E)

Geographic information — Simple feature access —
Part 2:
SQL option
1 Scope
This part of ISO 19125 specifies an SQL schema that supports storage, retrieval, query and update of simple
geospatial feature collections via the SQL Call Level Interface (SQL/CLI) (ISO/IEC 9075-3:2003).
This part of ISO 19125 establishes an architecture for the implementation of feature tables.
This part of ISO 19125 defines terms to use within the architecture.
This part of ISO 19125 defines a simple feature profile of ISO 19107.
This part of ISO 19125 describes a set of SQL Geometry Types together with SQL functions on those types.
The Geometry Types and Functions described in this part of ISO 19125 represent a profile of ISO 13249-3.
This part of ISO 19125 does not attempt to standardize and does not depend upon any part of the mechanism
by which Types are added and maintained in the SQL environment including the following:
a) the syntax and functionality provided for defining types;
b) the syntax and functionality provided for defining SQL functions;
c) the physical storage of type instances in the database;
d) specific terminology used to refer to User Defined Types, for example, UDT.
This part of ISO 19125 does standardize:
 names and geometric definitions of the SQL Types for Geometry;
 names, signatures and geometric definitions of the SQL Functions for Geometry.
This part of ISO 19125 describes a feature access implementation in SQL based on a profile of ISO 19107.
ISO 19107 does not place any requirements on how to define the Geometry Types in the internal schema.
ISO 19107 does not place any requirements on when or how or who defines the Geometry Types. In
particular, a compliant system may be shipped to the database user with the set of Geometry Types and
Functions already built into the SQL-implementation, or with the set of Geometry Types and Functions
supplied to the database user as a dynamically loaded extension to the SQL-implementation or in any other
manner not mentioned in this part of ISO 19125.
© ISO 2004 – All rights reserved 1

---------------------- Page: 6 ----------------------
ISO 19125-2:2004(E)
2 Conformance
In order to conform to this part of ISO 19125, an implementation shall satisfy the requirements of one of the
following three conformance classes, as well as the appropriate components of ISO 19125-1:
a) SQL implementation of feature tables based on predefined data types:
1) using numeric SQL types for geometry storage and SQL/CLI access,
2) using binary SQL types for geometry storage and SQL/CLI access;
b) SQL with Geometry Types implementation of feature tables supporting both textual and binary SQL/CLI
access to geometry.
Annex B provides conformance tests for each implementation of this part of ISO 19125.
3 Normative references
The following referenced documents are indispensable for the application of this document. For dated
references, only the edition cited applies. For undated references, the latest edition of the referenced
document (including any amendments) applies.
ISO/IEC 9075-1:2003, Information technology — Database languages — SQL — Part 1: Framework
(SQL/Framework)
ISO/IEC 9075-2:2003, Information technology — Database languages — SQL — Part 2: Foundation
(SQL/Foundation)
ISO/IEC 9075-3:2003, Information technology — Database languages — SQL — Part 3: Call-Level Interface
(SQL/CLI)
ISO/IEC 9075-4:2003, Information technology — Database languages — SQL — Part 4: Persistent Stored
Modules (SQL/PSM)
ISO/IEC 9075-5:1999, Information technology — Database languages — SQL — Part 5: Host Language
Bindings (SQL/Bindings)
ISO/IEC 13249-3:2003, Information technology — Database languages — SQL multimedia and application
packages — Part 3: Spatial
ISO 19107:2003, Geographic information ― Spatial schema
1)
ISO 19109:― , Geographic information ― Rules for application schema
ISO 19119:2004, Geographic information ― Services
ISO 19125-1:2004, Geographic information — Simple feature access — Part 1: Common architecture
4 Terms and definitions
For the purposes of this part of ISO 19125, the following terms and definitions apply.
4.1
feature table
table where the columns represent feature attributes, and the rows represent features

1) To be published.
2 © ISO 2004 – All rights reserved

---------------------- Page: 7 ----------------------
ISO 19125-2:2004(E)
4.2
geographic feature
representation of real world phenomenon associated with a location relative to the Earth
5 Symbols and abbreviated terms
FID Feature ID column in the implementation of feature tables based on predefined data types
GID Geometry ID column in the implementation of feature tables based on predefined data types
MM Multimedia
SQL Structured Query Language
SRID Spatial Reference System Identifier
SRTEXT Spatial Reference System Well Known Text
WKB Well-Known Binary (representation for example, geometry)
WKTR Well-Known Text Representation
2D 2-Dimensional
1
ℜ 1-Dimensional space
2
ℜ 2-Dimensional space
∅ empty set
∩ intersection
∪ union
 difference
∈ is a member of
∉ is not a member of
⊂ is a proper subset of
⊆ is a subset of
⇔ if and only if
⇒ implies
∀ for all
{ X | … } set of X such that…
∧ and
∨ or
¬ not
= equal
≠ not equal
< less than
> greater than
© ISO 2004 – All rights reserved 3

---------------------- Page: 8 ----------------------
ISO 19125-2:2004(E)
6 Architecture
6.1 Architecture — SQL implementation of feature tables based on predefined data types
6.1.1 Overview
This part of ISO 19125 defines a schema for the management of feature table, Geometry, and Spatial
Reference System information in an SQL-implementation based on predefined data types. This part
of ISO 19125 does not define SQL functions for access, maintenance, or indexing of Geometry in an
SQL-implementation based on predefined data types.
Figure 1 illustrates the schema to support feature tables, Geometry, and Spatial Reference Information in an
SQL-implementation based on predefined data types.
a) The GEOMETRY_COLUMNS table describes the available feature tables and their Geometry properties.
b) The SPATIAL_REF_SYS table describes the coordinate system and transformations for Geometry.
c) The feature table stores a collection of features. A feature table’s columns represent feature attributes,
while rows represent individual features. The Geometry of a feature is one of its feature attributes; while
logically a geometric data type, a Geometry Column is implemented as a foreign key to a geometry table.
d) The geometry table stores geometric objects, and may be implemented using either standard SQL
numeric types or SQL binary types.

Figure 1 — Schema for feature tables using predefined data types
Depending upon the storage type specified by the GEOMETRY_COLUMNS table, a geometric object is
stored either as an array of coordinate values or as a single binary value. In the former case, predefined SQL
numeric types are used for the coordinates and these numeric values are obtained from the geometry table
until the geometric object has been fully reconstructed. In the latter case, the complete geometric object is
obtained in the Well-known Binary Representation as a single value.
4 © ISO 2004 – All rights reserved

---------------------- Page: 9 ----------------------
ISO 19125-2:2004(E)
6.1.2 Identification of feature tables and geometry columns
Feature tables and Geometry columns are identified through the GEOMETRY_COLUMNS table. Each
Geometry Column in the database has an entry in the GEOMETRY_COLUMNS table. The data stored for
each geometry column consists of the following:
a) the identity of the feature table of which this Geometry Column is a member;
b) the name of the Geometry Column;
c) the spatial reference system ID (SRID) for the Geometry Column;
d) the type of Geometry for the Geometry column;
e) the coordinate dimension for the Geometry Column;
f) the identity of the geometry table that stores geometric objects for this Geometry Column;
g) the information necessary to navigate the geometry table in the case of normalized geometry storage.
6.1.3 Identification of Spatial Reference Systems
Every Geometry Column is associated with a Spatial Reference System. The Spatial Reference System
identifies the coordinate system for all geometric objects stored in the column, and gives meaning to the
numeric coordinate values for any geometric object stored in the column. Examples of commonly used Spatial
Reference Systems include “Latitude Longitude” and “UTM Zone 10”.
The SPATIAL_REF_SYS table stores information on each Spatial Reference System in the database. The
columns of this table are the Spatial Reference System Identifier (SRID), the Spatial Reference System
Authority Name (AUTH_NAME), the Authority Specific Spatial Reference System Identifier (AUTH_SRID) and
the Well-known Text description of the Spatial Reference System (SRTEXT). The Spatial Reference System
Identifier (SRID) constitutes a unique integer key for a Spatial Reference System within a database.
Interoperability between clients is achieved via the SRTEXT column which stores the Well-known Text
representation for a Spatial Reference System.
6.1.4 Feature tables
A feature is an abstraction of a real-world object. Feature attributes are columns in a feature table. Features
are rows in a feature table. The Geometry of a feature is one of its feature attributes; while logically a
geometric data type, a geometry column is implemented as a foreign key to a geometry table.
Relationships between features may be defined as foreign key references between feature tables.
6.1.5 Geometry tables
6.1.5.1 Normalized geometry schema
The normalized geometry schema stores the coordinates of geometric objects as predefined SQL numeric
types. One or more coordinates (X and Y ordinate values) will be represented by pairs of numeric types in the
geometry table, as shown in Figure 2. Each geometric object is identified by a key (GID) and consists of one
or more primitive elements ordered by an element sequence (ESEQ). Each primitive element in the geometric
object is distributed over one or more rows in the geometry table, identified by a primitive type (ETYPE), and
ordered by a sequence number (SEQ).
© ISO 2004 – All rights reserved 5

---------------------- Page: 10 ----------------------
ISO 19125-2:2004(E)
The rules for geometric object representation in the normalized schema are defined as follows.
a) ETYPE designates the Geometry Type.
b) Geometric objects may have multiple elements. The ESEQ value identifies the individual elements.
c) An element may be built up from multiple parts (rows). The rows and their proper sequence are identified
by the SEQ value.
d) Polygons may contain holes, as described in the Geometry object model.
e) PolygonRings shall close when assembled from an ordered list of parts. The SEQ value designates the
part order.
f) Coordinate pairs that are not used shall be set to Nil in complete sets (both X and Y). This is the only way
to identify the end of the list of coordinates.
g) For geometric objects that continue onto an additional row (as defined by a constant element sequence
number or ESEQ), the last Point of one row is equal to the first Point of the next.
h) There is no limit on the number of elements in the geometric object, or the number of rows in an element.

Figure 2 — Example of geometry table for Polygon Geometry using SQL
6.1.5.2 Binary geometry schema
The binary Geometry schema is illustrated in Table 1, uses GID as a key and stores the geometric object
using the Well-known Binary Representation for Geometry (WKBGeometry). The geometry table includes the
minimum bounding rectangle for the geometric object as well as the WKBGeometry for the geometric object.
This permits construction of spatial indexes without accessing the actual geometric object structure, if desired.
6 © ISO 2004 – All rights reserved

---------------------- Page: 11 ----------------------
ISO 19125-2:2004(E)
Table 1 — Example of geometry table for the above Polygon Geometry using the Well-known Binary
Representation for Geometry
GID XMIN YMIN XMAX YMAX Geometry
1 0 0 30 30 < WKBGeometry >
2 30 0 60 30 < WKBGeometry >
3 0 30 30 60 < WKBGeometry >
4 30 30 60 60 < WKBGeometry >

6.1.6 Use of numeric data types
SQL-implementations usually provide several numeric data types. In this part of ISO 19125, the use of a
numeric data type in examples is not meant to be binding. The data type of any particular column can be
determined, and casting operators between similar data types are available. Any particular implementation
may use alternative data types as long as casting operations shall not lead to difficulties.
6.1.7 Notes on SQL/CLI access to Geometry values stored in binary form
SQL/CLI provides standard mechanisms to bind character, numeric and binary data values.
This subclause describes the process of retrieving geometric object values for the case where the binary
storage alternative is chosen.
The WKB_GEOMETRY column in the geometry table is accessed in SQL/CLI as one of the binary SQL data
types (SQL_BINARY, SQL_VARBINARY, or SQL_LONGVARBINARY).
EXAMPLE The application would use the SQL_C_BINARY value for the fCType parameter of SQLBindCol (or
SQLGetData) in order to describe the application data buffer that shall receive the fetched Geometry data value. Similarly,
a dynamic parameter whose value is a Geometry would be described using the SQL_C_BINARY value for the fCType
parameter of SQLBindParameter.
This allows binary values to be both retrieved from and inserted into the geometry tables.
6.2 Architecture — SQL with Geometry Types implementation of feature tables
6.2.1 Overview
This part of ISO 19125 defines a schema for the management of feature table, Geometry, and Spatial
Reference System information in an SQL-implementation with a Geometry Type extension.
Figure 3 illustrates the schema to support feature tables, Geometry, and Spatial Reference Information in an
SQL-implementation with a Geometry Type extension.
a) The GEOMETRY_COLUMNS table describes the available feature tables and their Geometry properties.
b) The SPATIAL_REF_SYS table describes the coordinate system and transformations for Geometry.
c) The feature table stores a collection of features. A feature table’s columns represent feature attributes,
while rows represent individual features. The Geometry of a feature is one of the feature attributes, and is
an SQL Geometry Type.

© ISO 2004 – All rights reserved 7

---------------------- Page: 12 ----------------------
ISO 19125-2:2004(E)

Figure 3 — Schema for feature tables using SQL with Geometry Types
6.2.2 Identification of feature tables and geometry columns
Feature tables and Geometry columns are identified through the GEOMETRY_COLUMNS table. Each
Geometry Column in the database has an entry in the GEOMETRY_COLUMNS table. The data stored for
each geometry column consists of the following:
a) the identity of the feature table of which this Geometry Column is a member;
b) the name of the Geometry Column;
c) the spatial reference system ID for the Geometry Column;
d) the coordinate dimension for the Geometry column;
The columns in the GEOMETRY_COLUMNS table for the SQL with Geometry Types environment are a
subset of the columns in the GEOMETRY_COLUMNS table defined for the SQL-implementation based on
predefined data types.
An alternative method for identification of feature tables and Geometry Columns may be available for
SQL-implementations with Geometry Types. In the SQL-implementation with Geometry Types, the Geometry
Column may be represented as a row in the COLUMNS metadata view of the SQL INFORMATION_SCHEMA.
Spatial Reference System Identity and coordinate dimension is, however, not a standard part of the
SQL INFORMATION_SCHEMA. To access this information, the GEOMETRY_COLUMNS table would still
need to be referenced.
6.2.3 Identification of Spatial Reference Systems
Every Geometry Column is associated with a Spatial Reference System. The Spatial Reference System
identifies the coordinate system for all geometric objects stored in the column, and gives meaning to the
numeric coordinate values for any geometric object stored in the column. Examples of commonly used Spatial
Reference Systems include “Latitude Longitude” and “UTM Zone 10”.
The SPATIAL_REF_SYS table stores information on each Spatial Reference System in the database. The
columns of this table are the Spatial Reference System Identifier (SRID), the Spatial Reference System
Authority Name (AUTH_NAME), the Authority Specific Spatial Reference System Identifier (AUTH_SRID) and
the Well-known Text description of the Spatial Reference System (SRTEXT). The Spatial Reference System
Identifier (SRID) constitutes a unique integer key for a Spatial Reference System within a database.
Interoperability between clients is achieved via the SRTEXT column which stores the Well-known Text
representation for a Spatial Reference System.
8 © ISO 2004 – All rights reserved

---------------------- Page: 13 ----------------------
ISO 19125-2:2004(E)
6.2.4 Feature tables
A feature is an abstraction of a real-world object. Feature attributes are columns in a feature table. Features
are rows in a feature table. The Geometry of a feature is stored in a Geometry Column whose type is drawn
from a set of SQL Geometry Types.
Relationships between features may be defined as foreign key references between feature tables.
6.2.5 Background information on SQL User Defined Types
The term User Defined Type (UDT) refers to a data type that extends the SQL type system.
UDT types can be used to define the column types for tables, this allows values stored in the columns of a
table to be instances of UDT.
SQL functions may be declared to take UDT values as arguments, and return UDT values as results.
An UDT may be defined as a subtype of another UDT, referred to as its supertype. This allows an instance of
the subtype to be stored in any column where an instance of the supertype is expected and allows an instance
of the subtype to be used as an argument or return value in any SQL function that is declared to use the
supertype as an argument or return value.
The above definition of UDT is value based.
SQL implementations that support User Defined Types may also support the concept of References to User
Defined Types instances that are stored as rows in a table whose type corresponds to the type of the User
Defined Type. The terms RowType and Reference to RowType are also used to describe such types.
This specification allows Geometry Types to be implemented as either pure value based Types or as Types
that support persistent References.
The Types for Geometry are defined in black-box terms, i.e. all access to information about a Geometry Type
instance is through SQL functions. No attempt is made to distinguish functions that may access Type instance
attributes (such as the dimension of a geometric object) from functions that may compute values given a Type
instance (such as the centroid of a Polygon). In particular, an implementation of this part of ISO 19125 would
be free to nominate any set of functions as observer methods on attributes of a User Defined Type, as long as
the signatures of the SQL functions described in this part of ISO 19125 are preserved.
6.2.6 SQL Geometry Type hierarchy
The SQL Geometry Types are organized into a type hierarchy shown in Figure 4.
© ISO 2004 – All rights reserved 9

---------------------- Page: 14 ----------------------
ISO 19125-2:2004(E)

Figure 4 — SQL Geometry Type hierarchy
The root type, named Geometry, has subtypes for Point, Curve, Surface and GeometryCollection. A
GeometryCollection is a Geometry that is a collection of possibly heterogeneous geometric objects. MultiPoint,
MultiCurve and MultiSurface are specific subtypes of GeometryCollection used to manage homogenous
collections of Points, Curves and Surfaces. The 0 dimensional Geometry Types are Point and MultiPoint.
The one-dimensional Geometry Types are Curve and MultiCurve together with their subclasses. The
two-dimensional Geometry Types are Surface and MultiSurface together with their subclasses.
SQL functions are defined to construct instances of the above Types given Well-known Text or Binary
representations of the types. SQL functions defined on the types implement the methods described in the
Geometry Object Model.
6.2.7 Geometry values and spatial reference systems
In order to model Spatial Reference System information, each geometric object in the SQL with Geometry
Types implementation is associated with a Spatial Reference System. Capturing this association at the level
of the individual geometric object allows literal Geometry values that are not yet part of a column in the
database to be associated with a Spatial Reference System. Examples of such a geometric object, Geometry
values is a geometric object that is used as a parameter to a spatial query or a geometric object that is part of
an insert statement. Capturing this association at the level of the individual geometric object also allows
functions that take two geometric objects to check for compatible Spatial Reference Systems.
A Geometry value is associated with a Spatial Reference System by storing the Spatial Reference System
Identity (SRID) for the Spatial Reference System as a part of the geometric object. Each Spatial Reference
System in the database is identified by a unique value of SRID.
The SRID for a geometric object is assigned to it at construction t
...

SLOVENSKI STANDARD
SIST ISO 19125-2:2005
01-februar-2005
Geografske informacije – Dostop do enostavnih pojavov – 2. del: Možnost SQL
Geographic information -- Simple feature access -- Part 2: SQL option
Information géographique -- Accès aux entités simples -- Partie 2: Option SQL
Ta slovenski standard je istoveten z: ISO 19125-2:2004
ICS:
35.240.70 Uporabniške rešitve IT v IT applications in science
znanosti
SIST ISO 19125-2:2005 en
2003-01.Slovenski inštitut za standardizacijo. Razmnoževanje celote ali delov tega standarda ni dovoljeno.

---------------------- Page: 1 ----------------------

SIST ISO 19125-2:2005

---------------------- Page: 2 ----------------------

SIST ISO 19125-2:2005


INTERNATIONAL ISO
STANDARD 19125-2
First edition
2004-08-01

Geographic information — Simple feature
access —
Part 2:
SQL option
Information géographique — Accès aux entités simples —
Partie 2: Option SQL




Reference number
ISO 19125-2:2004(E)
©
ISO 2004

---------------------- Page: 3 ----------------------

SIST ISO 19125-2:2005
ISO 19125-2:2004(E)
PDF disclaimer
This PDF file may contain embedded typefaces. In accordance with Adobe's licensing policy, this file may be printed or viewed but
shall not be edited unless the typefaces which are embedded are licensed to and installed on the computer performing the editing. In
downloading this file, parties accept therein the responsibility of not infringing Adobe's licensing policy. The ISO Central Secretariat
accepts no liability in this area.
Adobe is a trademark of Adobe Systems Incorporated.
Details of the software products used to create this PDF file can be found in the General Info relative to the file; the PDF-creation
parameters were optimized for printing. Every care has been taken to ensure that the file is suitable for use by ISO member bodies. In
the unlikely event that a problem relating to it is found, please inform the Central Secretariat at the address given below.


©  ISO 2004
All rights reserved. Unless otherwise specified, no part of this publication may be reproduced or utilized in any form or by any means,
electronic or mechanical, including photocopying and microfilm, without permission in writing from either ISO at the address below or
ISO's member body in the country of the requester.
ISO copyright office
Case postale 56 • CH-1211 Geneva 20
Tel. + 41 22 749 01 11
Fax + 41 22 749 09 47
E-mail [e-mail]
Web www.iso.org
Published in Switzerland

ii © ISO 2004 – All rights reserved

---------------------- Page: 4 ----------------------

SIST ISO 19125-2:2005
ISO 19125-2:2004(E)
Contents Page
Foreword. iv
Introduction . v
1 Scope. 1
2 Conformance . 2
3 Normative references . 2
4 Terms and definitions. 2
5 Symbols and abbreviated terms. 3
6 Architecture . 4
6.1 Architecture — SQL implementation of feature tables based on predefined data types. 4
6.2 Architecture — SQL with Geometry Types implementation of feature tables. 7
7 Clause component specifications. 12
7.1 Components — Implementation of feature tables based on predefined data types . 12
7.2 Components — SQL with Geometry Types implementation of feature tables. 17
Annex A (informative) Comparison of Simple feature access/SQL and SQL/MM – Spatial. 31
Annex B (normative) Conformance tests. 32

© ISO 2004 – All rights reserved iii

---------------------- Page: 5 ----------------------

SIST ISO 19125-2:2005
ISO 19125-2:2004(E)
Foreword
ISO (the International Organization for Standardization) is a worldwide federation of national standards bodies
(ISO member bodies). The work of preparing International Standards is normally carried out through ISO
technical committees. Each member body interested in a subject for which a technical committee has been
established has the right to be represented on that committee. International organizations, governmental and
non-governmental, in liaison with ISO, also take part in the work. ISO collaborates closely with the
International Electrotechnical Commission (IEC) on all matters of electrotechnical standardization.
International Standards are drafted in accordance with the rules given in the ISO/IEC Directives, Part 2.
The main task of technical committees is to prepare International Standards. Draft International Standards
adopted by the technical committees are circulated to the member bodies for voting. Publication as an
International Standard requires approval by at least 75 % of the member bodies casting a vote.
Attention is drawn to the possibility that some of the elements of this document may be the subject of patent
rights. ISO shall not be held responsible for identifying any or all such patent rights.
ISO 19125-2 was prepared by Technical Committee ISO/TC 211, Geographic information/Geomatics from a
base document supplied by the Open GIS Consortium, Inc.
ISO 19125 consists of the following parts, under the general title Geographic information — Simple feature
access:
— Part 1: Common architecture
— Part 2: SQL option
Part 3: COM/OLE option is under preparation.

iv © ISO 2004 – All rights reserved

---------------------- Page: 6 ----------------------

SIST ISO 19125-2:2005
ISO 19125-2:2004(E)
Introduction
The purpose of this part of ISO 19125 is to define a standard Structured Query Language (SQL) schema that
supports storage, retrieval, query and update of feature collections via the SQL Call-Level Interface (SQL/CLI)
(ISO/IEC 9075-3:2003). A feature has both spatial and non-spatial attributes. Spatial attributes are geometry
valued, and simple features are based on 2D geometry with linear interpolation between vertices. This part of
ISO 19125 is dependent on the common architectural components defined in ISO 19125-1.
Feature collections are stored as tables with geometry valued columns in a SQL-implementation; each feature
is a row in the table. The non-spatial attributes of features are mapped onto columns whose types are drawn
from the set of standard SQL data types. The spatial attributes of features are mapped onto columns whose
SQL data types are based on the underlying concept of additional geometric data types for SQL. A table
whose rows represent these features is referred to as a feature table. Such a table contains one or more
geometry valued columns. Feature-table schemas are described for two SQL-implementations:
implementations based on predefined data types and SQL with Geometry Types.
In an implementation based on predefined data types, a geometry-valued column is implemented as a Foreign
Key reference into a geometry table. A geometry value is stored using one or more rows in the geometry table.
The geometry table may be implemented using either standard SQL numeric types or SQL binary types;
schemas for both are described.
The term SQL with Geometry Types is used to refer to a SQL-implementation that has been extended with a
set of Geometry Types. In this environment, a geometry-valued column is implemented as a column whose
SQL type is drawn from this set of Geometry Types. The mechanism for extending the type system of
an SQL-implementation is through the definition of user defined User Defined Types. Commercial
SQL-implementations with user defined type support have been available since mid-1997.

© ISO 2004 – All rights reserved v

---------------------- Page: 7 ----------------------

SIST ISO 19125-2:2005

---------------------- Page: 8 ----------------------

SIST ISO 19125-2:2005
INTERNATIONAL STANDARD ISO 19125-2:2004(E)

Geographic information — Simple feature access —
Part 2:
SQL option
1 Scope
This part of ISO 19125 specifies an SQL schema that supports storage, retrieval, query and update of simple
geospatial feature collections via the SQL Call Level Interface (SQL/CLI) (ISO/IEC 9075-3:2003).
This part of ISO 19125 establishes an architecture for the implementation of feature tables.
This part of ISO 19125 defines terms to use within the architecture.
This part of ISO 19125 defines a simple feature profile of ISO 19107.
This part of ISO 19125 describes a set of SQL Geometry Types together with SQL functions on those types.
The Geometry Types and Functions described in this part of ISO 19125 represent a profile of ISO 13249-3.
This part of ISO 19125 does not attempt to standardize and does not depend upon any part of the mechanism
by which Types are added and maintained in the SQL environment including the following:
a) the syntax and functionality provided for defining types;
b) the syntax and functionality provided for defining SQL functions;
c) the physical storage of type instances in the database;
d) specific terminology used to refer to User Defined Types, for example, UDT.
This part of ISO 19125 does standardize:
 names and geometric definitions of the SQL Types for Geometry;
 names, signatures and geometric definitions of the SQL Functions for Geometry.
This part of ISO 19125 describes a feature access implementation in SQL based on a profile of ISO 19107.
ISO 19107 does not place any requirements on how to define the Geometry Types in the internal schema.
ISO 19107 does not place any requirements on when or how or who defines the Geometry Types. In
particular, a compliant system may be shipped to the database user with the set of Geometry Types and
Functions already built into the SQL-implementation, or with the set of Geometry Types and Functions
supplied to the database user as a dynamically loaded extension to the SQL-implementation or in any other
manner not mentioned in this part of ISO 19125.
© ISO 2004 – All rights reserved 1

---------------------- Page: 9 ----------------------

SIST ISO 19125-2:2005
ISO 19125-2:2004(E)
2 Conformance
In order to conform to this part of ISO 19125, an implementation shall satisfy the requirements of one of the
following three conformance classes, as well as the appropriate components of ISO 19125-1:
a) SQL implementation of feature tables based on predefined data types:
1) using numeric SQL types for geometry storage and SQL/CLI access,
2) using binary SQL types for geometry storage and SQL/CLI access;
b) SQL with Geometry Types implementation of feature tables supporting both textual and binary SQL/CLI
access to geometry.
Annex B provides conformance tests for each implementation of this part of ISO 19125.
3 Normative references
The following referenced documents are indispensable for the application of this document. For dated
references, only the edition cited applies. For undated references, the latest edition of the referenced
document (including any amendments) applies.
ISO/IEC 9075-1:2003, Information technology — Database languages — SQL — Part 1: Framework
(SQL/Framework)
ISO/IEC 9075-2:2003, Information technology — Database languages — SQL — Part 2: Foundation
(SQL/Foundation)
ISO/IEC 9075-3:2003, Information technology — Database languages — SQL — Part 3: Call-Level Interface
(SQL/CLI)
ISO/IEC 9075-4:2003, Information technology — Database languages — SQL — Part 4: Persistent Stored
Modules (SQL/PSM)
ISO/IEC 9075-5:1999, Information technology — Database languages — SQL — Part 5: Host Language
Bindings (SQL/Bindings)
ISO/IEC 13249-3:2003, Information technology — Database languages — SQL multimedia and application
packages — Part 3: Spatial
ISO 19107:2003, Geographic information ― Spatial schema
1)
ISO 19109:― , Geographic information ― Rules for application schema
ISO 19119:2004, Geographic information ― Services
ISO 19125-1:2004, Geographic information — Simple feature access — Part 1: Common architecture
4 Terms and definitions
For the purposes of this part of ISO 19125, the following terms and definitions apply.
4.1
feature table
table where the columns represent feature attributes, and the rows represent features

1) To be published.
2 © ISO 2004 – All rights reserved

---------------------- Page: 10 ----------------------

SIST ISO 19125-2:2005
ISO 19125-2:2004(E)
4.2
geographic feature
representation of real world phenomenon associated with a location relative to the Earth
5 Symbols and abbreviated terms
FID Feature ID column in the implementation of feature tables based on predefined data types
GID Geometry ID column in the implementation of feature tables based on predefined data types
MM Multimedia
SQL Structured Query Language
SRID Spatial Reference System Identifier
SRTEXT Spatial Reference System Well Known Text
WKB Well-Known Binary (representation for example, geometry)
WKTR Well-Known Text Representation
2D 2-Dimensional
1
ℜ 1-Dimensional space
2
ℜ 2-Dimensional space
∅ empty set
∩ intersection
∪ union
 difference
∈ is a member of
∉ is not a member of
⊂ is a proper subset of
⊆ is a subset of
⇔ if and only if
⇒ implies
∀ for all
{ X | … } set of X such that…
∧ and
∨ or
¬ not
= equal
≠ not equal
< less than
> greater than
© ISO 2004 – All rights reserved 3

---------------------- Page: 11 ----------------------

SIST ISO 19125-2:2005
ISO 19125-2:2004(E)
6 Architecture
6.1 Architecture — SQL implementation of feature tables based on predefined data types
6.1.1 Overview
This part of ISO 19125 defines a schema for the management of feature table, Geometry, and Spatial
Reference System information in an SQL-implementation based on predefined data types. This part
of ISO 19125 does not define SQL functions for access, maintenance, or indexing of Geometry in an
SQL-implementation based on predefined data types.
Figure 1 illustrates the schema to support feature tables, Geometry, and Spatial Reference Information in an
SQL-implementation based on predefined data types.
a) The GEOMETRY_COLUMNS table describes the available feature tables and their Geometry properties.
b) The SPATIAL_REF_SYS table describes the coordinate system and transformations for Geometry.
c) The feature table stores a collection of features. A feature table’s columns represent feature attributes,
while rows represent individual features. The Geometry of a feature is one of its feature attributes; while
logically a geometric data type, a Geometry Column is implemented as a foreign key to a geometry table.
d) The geometry table stores geometric objects, and may be implemented using either standard SQL
numeric types or SQL binary types.

Figure 1 — Schema for feature tables using predefined data types
Depending upon the storage type specified by the GEOMETRY_COLUMNS table, a geometric object is
stored either as an array of coordinate values or as a single binary value. In the former case, predefined SQL
numeric types are used for the coordinates and these numeric values are obtained from the geometry table
until the geometric object has been fully reconstructed. In the latter case, the complete geometric object is
obtained in the Well-known Binary Representation as a single value.
4 © ISO 2004 – All rights reserved

---------------------- Page: 12 ----------------------

SIST ISO 19125-2:2005
ISO 19125-2:2004(E)
6.1.2 Identification of feature tables and geometry columns
Feature tables and Geometry columns are identified through the GEOMETRY_COLUMNS table. Each
Geometry Column in the database has an entry in the GEOMETRY_COLUMNS table. The data stored for
each geometry column consists of the following:
a) the identity of the feature table of which this Geometry Column is a member;
b) the name of the Geometry Column;
c) the spatial reference system ID (SRID) for the Geometry Column;
d) the type of Geometry for the Geometry column;
e) the coordinate dimension for the Geometry Column;
f) the identity of the geometry table that stores geometric objects for this Geometry Column;
g) the information necessary to navigate the geometry table in the case of normalized geometry storage.
6.1.3 Identification of Spatial Reference Systems
Every Geometry Column is associated with a Spatial Reference System. The Spatial Reference System
identifies the coordinate system for all geometric objects stored in the column, and gives meaning to the
numeric coordinate values for any geometric object stored in the column. Examples of commonly used Spatial
Reference Systems include “Latitude Longitude” and “UTM Zone 10”.
The SPATIAL_REF_SYS table stores information on each Spatial Reference System in the database. The
columns of this table are the Spatial Reference System Identifier (SRID), the Spatial Reference System
Authority Name (AUTH_NAME), the Authority Specific Spatial Reference System Identifier (AUTH_SRID) and
the Well-known Text description of the Spatial Reference System (SRTEXT). The Spatial Reference System
Identifier (SRID) constitutes a unique integer key for a Spatial Reference System within a database.
Interoperability between clients is achieved via the SRTEXT column which stores the Well-known Text
representation for a Spatial Reference System.
6.1.4 Feature tables
A feature is an abstraction of a real-world object. Feature attributes are columns in a feature table. Features
are rows in a feature table. The Geometry of a feature is one of its feature attributes; while logically a
geometric data type, a geometry column is implemented as a foreign key to a geometry table.
Relationships between features may be defined as foreign key references between feature tables.
6.1.5 Geometry tables
6.1.5.1 Normalized geometry schema
The normalized geometry schema stores the coordinates of geometric objects as predefined SQL numeric
types. One or more coordinates (X and Y ordinate values) will be represented by pairs of numeric types in the
geometry table, as shown in Figure 2. Each geometric object is identified by a key (GID) and consists of one
or more primitive elements ordered by an element sequence (ESEQ). Each primitive element in the geometric
object is distributed over one or more rows in the geometry table, identified by a primitive type (ETYPE), and
ordered by a sequence number (SEQ).
© ISO 2004 – All rights reserved 5

---------------------- Page: 13 ----------------------

SIST ISO 19125-2:2005
ISO 19125-2:2004(E)
The rules for geometric object representation in the normalized schema are defined as follows.
a) ETYPE designates the Geometry Type.
b) Geometric objects may have multiple elements. The ESEQ value identifies the individual elements.
c) An element may be built up from multiple parts (rows). The rows and their proper sequence are identified
by the SEQ value.
d) Polygons may contain holes, as described in the Geometry object model.
e) PolygonRings shall close when assembled from an ordered list of parts. The SEQ value designates the
part order.
f) Coordinate pairs that are not used shall be set to Nil in complete sets (both X and Y). This is the only way
to identify the end of the list of coordinates.
g) For geometric objects that continue onto an additional row (as defined by a constant element sequence
number or ESEQ), the last Point of one row is equal to the first Point of the next.
h) There is no limit on the number of elements in the geometric object, or the number of rows in an element.

Figure 2 — Example of geometry table for Polygon Geometry using SQL
6.1.5.2 Binary geometry schema
The binary Geometry schema is illustrated in Table 1, uses GID as a key and stores the geometric object
using the Well-known Binary Representation for Geometry (WKBGeometry). The geometry table includes the
minimum bounding rectangle for the geometric object as well as the WKBGeometry for the geometric object.
This permits construction of spatial indexes without accessing the actual geometric object structure, if desired.
6 © ISO 2004 – All rights reserved

---------------------- Page: 14 ----------------------

SIST ISO 19125-2:2005
ISO 19125-2:2004(E)
Table 1 — Example of geometry table for the above Polygon Geometry using the Well-known Binary
Representation for Geometry
GID XMIN YMIN XMAX YMAX Geometry
1 0 0 30 30 < WKBGeometry >
2 30 0 60 30 < WKBGeometry >
3 0 30 30 60 < WKBGeometry >
4 30 30 60 60 < WKBGeometry >

6.1.6 Use of numeric data types
SQL-implementations usually provide several numeric data types. In this part of ISO 19125, the use of a
numeric data type in examples is not meant to be binding. The data type of any particular column can be
determined, and casting operators between similar data types are available. Any particular implementation
may use alternative data types as long as casting operations shall not lead to difficulties.
6.1.7 Notes on SQL/CLI access to Geometry values stored in binary form
SQL/CLI provides standard mechanisms to bind character, numeric and binary data values.
This subclause describes the process of retrieving geometric object values for the case where the binary
storage alternative is chosen.
The WKB_GEOMETRY column in the geometry table is accessed in SQL/CLI as one of the binary SQL data
types (SQL_BINARY, SQL_VARBINARY, or SQL_LONGVARBINARY).
EXAMPLE The application would use the SQL_C_BINARY value for the fCType parameter of SQLBindCol (or
SQLGetData) in order to describe the application data buffer that shall receive the fetched Geometry data value. Similarly,
a dynamic parameter whose value is a Geometry would be described using the SQL_C_BINARY value for the fCType
parameter of SQLBindParameter.
This allows binary values to be both retrieved from and inserted into the geometry tables.
6.2 Architecture — SQL with Geometry Types implementation of feature tables
6.2.1 Overview
This part of ISO 19125 defines a schema for the management of feature table, Geometry, and Spatial
Reference System information in an SQL-implementation with a Geometry Type extension.
Figure 3 illustrates the schema to support feature tables, Geometry, and Spatial Reference Information in an
SQL-implementation with a Geometry Type extension.
a) The GEOMETRY_COLUMNS table describes the available feature tables and their Geometry properties.
b) The SPATIAL_REF_SYS table describes the coordinate system and transformations for Geometry.
c) The feature table stores a collection of features. A feature table’s columns represent feature attributes,
while rows represent individual features. The Geometry of a feature is one of the feature attributes, and is
an SQL Geometry Type.

© ISO 2004 – All rights reserved 7

---------------------- Page: 15 ----------------------

SIST ISO 19125-2:2005
ISO 19125-2:2004(E)

Figure 3 — Schema for feature tables using SQL with Geometry Types
6.2.2 Identification of feature tables and geometry columns
Feature tables and Geometry columns are identified through the GEOMETRY_COLUMNS table. Each
Geometry Column in the database has an entry in the GEOMETRY_COLUMNS table. The data stored for
each geometry column consists of the following:
a) the identity of the feature table of which this Geometry Column is a member;
b) the name of the Geometry Column;
c) the spatial reference system ID for the Geometry Column;
d) the coordinate dimension for the Geometry column;
The columns in the GEOMETRY_COLUMNS table for the SQL with Geometry Types environment are a
subset of the columns in the GEOMETRY_COLUMNS table defined for the SQL-implementation based on
predefined data types.
An alternative method for identification of feature tables and Geometry Columns may be available for
SQL-implementations with Geometry Types. In the SQL-implementation with Geometry Types, the Geometry
Column may be represented as a row in the COLUMNS metadata view of the SQL INFORMATION_SCHEMA.
Spatial Reference System Identity and coordinate dimension is, however, not a standard part of the
SQL INFORMATION_SCHEMA. To access this information, the GEOMETRY_COLUMNS table would still
need to be referenced.
6.2.3 Identification of Spatial Reference Systems
Every Geometry Column is associated with a Spatial Reference System. The Spatial Reference System
identifies the coordinate system for all geometric objects stored in the column, and gives meaning to the
numeric coordinate values for any geometric object stored in the column. Examples of commonly used Spatial
Reference Systems include “Latitude Longitude” and “UTM Zone 10”.
The SPATIAL_REF_SYS table stores information on each Spatial Reference System in the database. The
columns of this table are the Spatial Reference System Identifier (SRID), the Spatial Reference System
Authority Name (AUTH_NAME), the Authority Specific Spatial Reference System Identifier (AUTH_SRID) and
the Well-known Text description of the Spatial Reference System (SRTEXT). The Spatial Reference System
Identifier (SRID) constitutes a unique integer key for a Spatial Reference System within a database.
Interoperability between clients is achieved via the SRTEXT column which stores the Well-known Text
representation for a Spatial Reference System.
8 © ISO 2004 – All rights reserved

---------------------- Page: 16 ----------------------

SIST ISO 19125-2:2005
ISO 19125-2:2004(E)
6.2.4 Feature tables
A feature is an abstraction of a real-world object. Feature attributes are columns in a feature table. Features
are rows in a feature table. The Geometry of a feature is stored in a Geometry Column whose type is drawn
from a set of SQL Geometry Types.
Relationships between features may be defined as foreign key references between feature tables.
6.2.5 Background information on SQL User Defined Types
The term User Defined Type (UDT) refers to a data type that extends the SQL type system.
UDT types can be used to define the column types for tables, this allows values stored in the columns of a
table to be instances of UDT.
SQL functions may be declared to take UDT values as arguments, and return UDT values as results.
An UDT may be defined as a subtype of another UDT, referred to as its supertype. This allows an instance of
the subtype to be stored in any column where an instance of the supertype is expected and allows an instance
of the subtype to be used as an argument or return value in any SQL function that is declared to use the
supertype as an argument or return value.
The above definition of UDT is value based.
SQL implementations that support User Defined Types may also support the concept of References to User
Defined Types instances that are stored as rows in a table whose type corresponds to the type of the User
Defined Type. The terms RowType and Reference to RowType are also used to describe such types.
This specification allows Geometry Types to be implemented as either pure value based Types or as Types
that support persistent References.
The Types for Geometry are defined in black-box terms, i.e. all access to information about a Geometry Type
instance is through SQL functions. No attempt is made to distinguish functions that may access Type instance
attributes (such as the dimension of a geometric object) from functions that may compute values given a Type
instance (such as the centroid of a Polygon). In particular, an implementation of this part of ISO 19125 would
be free to nominate any set of functions as observer methods on attributes of a User Defined Type, as long as
the signatures of the SQL functions described in this part of ISO 19125 are preserved.
6.2.6 SQL Geometry Type hierarchy
The SQL Geometry Types are organized into a type hierarchy shown in Figure 4.
© ISO 2004 – All rights reserved 9

---------------------- Page: 17 ----------------------

SIST ISO 19125-2:2005
ISO 19125-2:2004(E)

Figure 4 — SQL Geometry Type hierarchy
The root type, named Geometry, has subtypes for Point, Curve, Surface and GeometryCollection. A
GeometryCollection is a Geometry that is a collection of possibly heterogeneous geometric objects. MultiPoint,
MultiCurve and MultiSurface are specific subtypes of GeometryCollection used to manage homogenous
collections of Points, Curves and Surfaces. The 0 dimensional Geometry Types are Point and MultiPoint.
The one-dimensional Geometry Types are Curve and MultiCurve together with their subclasses. The
two-dimensional Geometry Types are Surface and MultiSurface together with their subclasses.
SQL functions are defined to construct instances of the above Types given Well-known Text or Binary
representations of the types. SQL functions defined on the types implement the methods described in the
Geometry Object Model.
6.2.7 Ge
...

INTERNATIONAL ISO
STANDARD 19125-2
First edition
2004-08-01

Geographic information — Simple feature
access —
Part 2:
SQL option
Information géographique — Accès aux entités simples —
Partie 2: Option SQL




Reference number
ISO 19125-2:2004(E)
©
ISO 2004

---------------------- Page: 1 ----------------------

ISO 19125-2:2004(E)
PDF disclaimer
This PDF file may contain embedded typefaces. In accordance with Adobe's licensing policy, this file may be printed or viewed but
shall not be edited unless the typefaces which are embedded are licensed to and installed on the computer performing the editing. In
downloading this file, parties accept therein the responsibility of not infringing Adobe's licensing policy. The ISO Central Secretariat
accepts no liability in this area.
Adobe is a trademark of Adobe Systems Incorporated.
Details of the software products used to create this PDF file can be found in the General Info relative to the file; the PDF-creation
parameters were optimized for printing. Every care has been taken to ensure that the file is suitable for use by ISO member bodies. In
the unlikely event that a problem relating to it is found, please inform the Central Secretariat at the address given below.


©  ISO 2004
All rights reserved. Unless otherwise specified, no part of this publication may be reproduced or utilized in any form or by any means,
electronic or mechanical, including photocopying and microfilm, without permission in writing from either ISO at the address below or
ISO's member body in the country of the requester.
ISO copyright office
Case postale 56 • CH-1211 Geneva 20
Tel. + 41 22 749 01 11
Fax + 41 22 749 09 47
E-mail [e-mail]
Web www.iso.org
Published in Switzerland

ii © ISO 2004 – All rights reserved

---------------------- Page: 2 ----------------------

ISO 19125-2:2004(E)
Contents Page
Foreword. iv
Introduction . v
1 Scope. 1
2 Conformance . 2
3 Normative references . 2
4 Terms and definitions. 2
5 Symbols and abbreviated terms. 3
6 Architecture . 4
6.1 Architecture — SQL implementation of feature tables based on predefined data types. 4
6.2 Architecture — SQL with Geometry Types implementation of feature tables. 7
7 Clause component specifications. 12
7.1 Components — Implementation of feature tables based on predefined data types . 12
7.2 Components — SQL with Geometry Types implementation of feature tables. 17
Annex A (informative) Comparison of Simple feature access/SQL and SQL/MM – Spatial. 31
Annex B (normative) Conformance tests. 32

© ISO 2004 – All rights reserved iii

---------------------- Page: 3 ----------------------

ISO 19125-2:2004(E)
Foreword
ISO (the International Organization for Standardization) is a worldwide federation of national standards bodies
(ISO member bodies). The work of preparing International Standards is normally carried out through ISO
technical committees. Each member body interested in a subject for which a technical committee has been
established has the right to be represented on that committee. International organizations, governmental and
non-governmental, in liaison with ISO, also take part in the work. ISO collaborates closely with the
International Electrotechnical Commission (IEC) on all matters of electrotechnical standardization.
International Standards are drafted in accordance with the rules given in the ISO/IEC Directives, Part 2.
The main task of technical committees is to prepare International Standards. Draft International Standards
adopted by the technical committees are circulated to the member bodies for voting. Publication as an
International Standard requires approval by at least 75 % of the member bodies casting a vote.
Attention is drawn to the possibility that some of the elements of this document may be the subject of patent
rights. ISO shall not be held responsible for identifying any or all such patent rights.
ISO 19125-2 was prepared by Technical Committee ISO/TC 211, Geographic information/Geomatics from a
base document supplied by the Open GIS Consortium, Inc.
ISO 19125 consists of the following parts, under the general title Geographic information — Simple feature
access:
— Part 1: Common architecture
— Part 2: SQL option
Part 3: COM/OLE option is under preparation.

iv © ISO 2004 – All rights reserved

---------------------- Page: 4 ----------------------

ISO 19125-2:2004(E)
Introduction
The purpose of this part of ISO 19125 is to define a standard Structured Query Language (SQL) schema that
supports storage, retrieval, query and update of feature collections via the SQL Call-Level Interface (SQL/CLI)
(ISO/IEC 9075-3:2003). A feature has both spatial and non-spatial attributes. Spatial attributes are geometry
valued, and simple features are based on 2D geometry with linear interpolation between vertices. This part of
ISO 19125 is dependent on the common architectural components defined in ISO 19125-1.
Feature collections are stored as tables with geometry valued columns in a SQL-implementation; each feature
is a row in the table. The non-spatial attributes of features are mapped onto columns whose types are drawn
from the set of standard SQL data types. The spatial attributes of features are mapped onto columns whose
SQL data types are based on the underlying concept of additional geometric data types for SQL. A table
whose rows represent these features is referred to as a feature table. Such a table contains one or more
geometry valued columns. Feature-table schemas are described for two SQL-implementations:
implementations based on predefined data types and SQL with Geometry Types.
In an implementation based on predefined data types, a geometry-valued column is implemented as a Foreign
Key reference into a geometry table. A geometry value is stored using one or more rows in the geometry table.
The geometry table may be implemented using either standard SQL numeric types or SQL binary types;
schemas for both are described.
The term SQL with Geometry Types is used to refer to a SQL-implementation that has been extended with a
set of Geometry Types. In this environment, a geometry-valued column is implemented as a column whose
SQL type is drawn from this set of Geometry Types. The mechanism for extending the type system of
an SQL-implementation is through the definition of user defined User Defined Types. Commercial
SQL-implementations with user defined type support have been available since mid-1997.

© ISO 2004 – All rights reserved v

---------------------- Page: 5 ----------------------

INTERNATIONAL STANDARD ISO 19125-2:2004(E)

Geographic information — Simple feature access —
Part 2:
SQL option
1 Scope
This part of ISO 19125 specifies an SQL schema that supports storage, retrieval, query and update of simple
geospatial feature collections via the SQL Call Level Interface (SQL/CLI) (ISO/IEC 9075-3:2003).
This part of ISO 19125 establishes an architecture for the implementation of feature tables.
This part of ISO 19125 defines terms to use within the architecture.
This part of ISO 19125 defines a simple feature profile of ISO 19107.
This part of ISO 19125 describes a set of SQL Geometry Types together with SQL functions on those types.
The Geometry Types and Functions described in this part of ISO 19125 represent a profile of ISO 13249-3.
This part of ISO 19125 does not attempt to standardize and does not depend upon any part of the mechanism
by which Types are added and maintained in the SQL environment including the following:
a) the syntax and functionality provided for defining types;
b) the syntax and functionality provided for defining SQL functions;
c) the physical storage of type instances in the database;
d) specific terminology used to refer to User Defined Types, for example, UDT.
This part of ISO 19125 does standardize:
 names and geometric definitions of the SQL Types for Geometry;
 names, signatures and geometric definitions of the SQL Functions for Geometry.
This part of ISO 19125 describes a feature access implementation in SQL based on a profile of ISO 19107.
ISO 19107 does not place any requirements on how to define the Geometry Types in the internal schema.
ISO 19107 does not place any requirements on when or how or who defines the Geometry Types. In
particular, a compliant system may be shipped to the database user with the set of Geometry Types and
Functions already built into the SQL-implementation, or with the set of Geometry Types and Functions
supplied to the database user as a dynamically loaded extension to the SQL-implementation or in any other
manner not mentioned in this part of ISO 19125.
© ISO 2004 – All rights reserved 1

---------------------- Page: 6 ----------------------

ISO 19125-2:2004(E)
2 Conformance
In order to conform to this part of ISO 19125, an implementation shall satisfy the requirements of one of the
following three conformance classes, as well as the appropriate components of ISO 19125-1:
a) SQL implementation of feature tables based on predefined data types:
1) using numeric SQL types for geometry storage and SQL/CLI access,
2) using binary SQL types for geometry storage and SQL/CLI access;
b) SQL with Geometry Types implementation of feature tables supporting both textual and binary SQL/CLI
access to geometry.
Annex B provides conformance tests for each implementation of this part of ISO 19125.
3 Normative references
The following referenced documents are indispensable for the application of this document. For dated
references, only the edition cited applies. For undated references, the latest edition of the referenced
document (including any amendments) applies.
ISO/IEC 9075-1:2003, Information technology — Database languages — SQL — Part 1: Framework
(SQL/Framework)
ISO/IEC 9075-2:2003, Information technology — Database languages — SQL — Part 2: Foundation
(SQL/Foundation)
ISO/IEC 9075-3:2003, Information technology — Database languages — SQL — Part 3: Call-Level Interface
(SQL/CLI)
ISO/IEC 9075-4:2003, Information technology — Database languages — SQL — Part 4: Persistent Stored
Modules (SQL/PSM)
ISO/IEC 9075-5:1999, Information technology — Database languages — SQL — Part 5: Host Language
Bindings (SQL/Bindings)
ISO/IEC 13249-3:2003, Information technology — Database languages — SQL multimedia and application
packages — Part 3: Spatial
ISO 19107:2003, Geographic information ― Spatial schema
1)
ISO 19109:― , Geographic information ― Rules for application schema
ISO 19119:2004, Geographic information ― Services
ISO 19125-1:2004, Geographic information — Simple feature access — Part 1: Common architecture
4 Terms and definitions
For the purposes of this part of ISO 19125, the following terms and definitions apply.
4.1
feature table
table where the columns represent feature attributes, and the rows represent features

1) To be published.
2 © ISO 2004 – All rights reserved

---------------------- Page: 7 ----------------------

ISO 19125-2:2004(E)
4.2
geographic feature
representation of real world phenomenon associated with a location relative to the Earth
5 Symbols and abbreviated terms
FID Feature ID column in the implementation of feature tables based on predefined data types
GID Geometry ID column in the implementation of feature tables based on predefined data types
MM Multimedia
SQL Structured Query Language
SRID Spatial Reference System Identifier
SRTEXT Spatial Reference System Well Known Text
WKB Well-Known Binary (representation for example, geometry)
WKTR Well-Known Text Representation
2D 2-Dimensional
1
ℜ 1-Dimensional space
2
ℜ 2-Dimensional space
∅ empty set
∩ intersection
∪ union
 difference
∈ is a member of
∉ is not a member of
⊂ is a proper subset of
⊆ is a subset of
⇔ if and only if
⇒ implies
∀ for all
{ X | … } set of X such that…
∧ and
∨ or
¬ not
= equal
≠ not equal
< less than
> greater than
© ISO 2004 – All rights reserved 3

---------------------- Page: 8 ----------------------

ISO 19125-2:2004(E)
6 Architecture
6.1 Architecture — SQL implementation of feature tables based on predefined data types
6.1.1 Overview
This part of ISO 19125 defines a schema for the management of feature table, Geometry, and Spatial
Reference System information in an SQL-implementation based on predefined data types. This part
of ISO 19125 does not define SQL functions for access, maintenance, or indexing of Geometry in an
SQL-implementation based on predefined data types.
Figure 1 illustrates the schema to support feature tables, Geometry, and Spatial Reference Information in an
SQL-implementation based on predefined data types.
a) The GEOMETRY_COLUMNS table describes the available feature tables and their Geometry properties.
b) The SPATIAL_REF_SYS table describes the coordinate system and transformations for Geometry.
c) The feature table stores a collection of features. A feature table’s columns represent feature attributes,
while rows represent individual features. The Geometry of a feature is one of its feature attributes; while
logically a geometric data type, a Geometry Column is implemented as a foreign key to a geometry table.
d) The geometry table stores geometric objects, and may be implemented using either standard SQL
numeric types or SQL binary types.

Figure 1 — Schema for feature tables using predefined data types
Depending upon the storage type specified by the GEOMETRY_COLUMNS table, a geometric object is
stored either as an array of coordinate values or as a single binary value. In the former case, predefined SQL
numeric types are used for the coordinates and these numeric values are obtained from the geometry table
until the geometric object has been fully reconstructed. In the latter case, the complete geometric object is
obtained in the Well-known Binary Representation as a single value.
4 © ISO 2004 – All rights reserved

---------------------- Page: 9 ----------------------

ISO 19125-2:2004(E)
6.1.2 Identification of feature tables and geometry columns
Feature tables and Geometry columns are identified through the GEOMETRY_COLUMNS table. Each
Geometry Column in the database has an entry in the GEOMETRY_COLUMNS table. The data stored for
each geometry column consists of the following:
a) the identity of the feature table of which this Geometry Column is a member;
b) the name of the Geometry Column;
c) the spatial reference system ID (SRID) for the Geometry Column;
d) the type of Geometry for the Geometry column;
e) the coordinate dimension for the Geometry Column;
f) the identity of the geometry table that stores geometric objects for this Geometry Column;
g) the information necessary to navigate the geometry table in the case of normalized geometry storage.
6.1.3 Identification of Spatial Reference Systems
Every Geometry Column is associated with a Spatial Reference System. The Spatial Reference System
identifies the coordinate system for all geometric objects stored in the column, and gives meaning to the
numeric coordinate values for any geometric object stored in the column. Examples of commonly used Spatial
Reference Systems include “Latitude Longitude” and “UTM Zone 10”.
The SPATIAL_REF_SYS table stores information on each Spatial Reference System in the database. The
columns of this table are the Spatial Reference System Identifier (SRID), the Spatial Reference System
Authority Name (AUTH_NAME), the Authority Specific Spatial Reference System Identifier (AUTH_SRID) and
the Well-known Text description of the Spatial Reference System (SRTEXT). The Spatial Reference System
Identifier (SRID) constitutes a unique integer key for a Spatial Reference System within a database.
Interoperability between clients is achieved via the SRTEXT column which stores the Well-known Text
representation for a Spatial Reference System.
6.1.4 Feature tables
A feature is an abstraction of a real-world object. Feature attributes are columns in a feature table. Features
are rows in a feature table. The Geometry of a feature is one of its feature attributes; while logically a
geometric data type, a geometry column is implemented as a foreign key to a geometry table.
Relationships between features may be defined as foreign key references between feature tables.
6.1.5 Geometry tables
6.1.5.1 Normalized geometry schema
The normalized geometry schema stores the coordinates of geometric objects as predefined SQL numeric
types. One or more coordinates (X and Y ordinate values) will be represented by pairs of numeric types in the
geometry table, as shown in Figure 2. Each geometric object is identified by a key (GID) and consists of one
or more primitive elements ordered by an element sequence (ESEQ). Each primitive element in the geometric
object is distributed over one or more rows in the geometry table, identified by a primitive type (ETYPE), and
ordered by a sequence number (SEQ).
© ISO 2004 – All rights reserved 5

---------------------- Page: 10 ----------------------

ISO 19125-2:2004(E)
The rules for geometric object representation in the normalized schema are defined as follows.
a) ETYPE designates the Geometry Type.
b) Geometric objects may have multiple elements. The ESEQ value identifies the individual elements.
c) An element may be built up from multiple parts (rows). The rows and their proper sequence are identified
by the SEQ value.
d) Polygons may contain holes, as described in the Geometry object model.
e) PolygonRings shall close when assembled from an ordered list of parts. The SEQ value designates the
part order.
f) Coordinate pairs that are not used shall be set to Nil in complete sets (both X and Y). This is the only way
to identify the end of the list of coordinates.
g) For geometric objects that continue onto an additional row (as defined by a constant element sequence
number or ESEQ), the last Point of one row is equal to the first Point of the next.
h) There is no limit on the number of elements in the geometric object, or the number of rows in an element.

Figure 2 — Example of geometry table for Polygon Geometry using SQL
6.1.5.2 Binary geometry schema
The binary Geometry schema is illustrated in Table 1, uses GID as a key and stores the geometric object
using the Well-known Binary Representation for Geometry (WKBGeometry). The geometry table includes the
minimum bounding rectangle for the geometric object as well as the WKBGeometry for the geometric object.
This permits construction of spatial indexes without accessing the actual geometric object structure, if desired.
6 © ISO 2004 – All rights reserved

---------------------- Page: 11 ----------------------

ISO 19125-2:2004(E)
Table 1 — Example of geometry table for the above Polygon Geometry using the Well-known Binary
Representation for Geometry
GID XMIN YMIN XMAX YMAX Geometry
1 0 0 30 30 < WKBGeometry >
2 30 0 60 30 < WKBGeometry >
3 0 30 30 60 < WKBGeometry >
4 30 30 60 60 < WKBGeometry >

6.1.6 Use of numeric data types
SQL-implementations usually provide several numeric data types. In this part of ISO 19125, the use of a
numeric data type in examples is not meant to be binding. The data type of any particular column can be
determined, and casting operators between similar data types are available. Any particular implementation
may use alternative data types as long as casting operations shall not lead to difficulties.
6.1.7 Notes on SQL/CLI access to Geometry values stored in binary form
SQL/CLI provides standard mechanisms to bind character, numeric and binary data values.
This subclause describes the process of retrieving geometric object values for the case where the binary
storage alternative is chosen.
The WKB_GEOMETRY column in the geometry table is accessed in SQL/CLI as one of the binary SQL data
types (SQL_BINARY, SQL_VARBINARY, or SQL_LONGVARBINARY).
EXAMPLE The application would use the SQL_C_BINARY value for the fCType parameter of SQLBindCol (or
SQLGetData) in order to describe the application data buffer that shall receive the fetched Geometry data value. Similarly,
a dynamic parameter whose value is a Geometry would be described using the SQL_C_BINARY value for the fCType
parameter of SQLBindParameter.
This allows binary values to be both retrieved from and inserted into the geometry tables.
6.2 Architecture — SQL with Geometry Types implementation of feature tables
6.2.1 Overview
This part of ISO 19125 defines a schema for the management of feature table, Geometry, and Spatial
Reference System information in an SQL-implementation with a Geometry Type extension.
Figure 3 illustrates the schema to support feature tables, Geometry, and Spatial Reference Information in an
SQL-implementation with a Geometry Type extension.
a) The GEOMETRY_COLUMNS table describes the available feature tables and their Geometry properties.
b) The SPATIAL_REF_SYS table describes the coordinate system and transformations for Geometry.
c) The feature table stores a collection of features. A feature table’s columns represent feature attributes,
while rows represent individual features. The Geometry of a feature is one of the feature attributes, and is
an SQL Geometry Type.

© ISO 2004 – All rights reserved 7

---------------------- Page: 12 ----------------------

ISO 19125-2:2004(E)

Figure 3 — Schema for feature tables using SQL with Geometry Types
6.2.2 Identification of feature tables and geometry columns
Feature tables and Geometry columns are identified through the GEOMETRY_COLUMNS table. Each
Geometry Column in the database has an entry in the GEOMETRY_COLUMNS table. The data stored for
each geometry column consists of the following:
a) the identity of the feature table of which this Geometry Column is a member;
b) the name of the Geometry Column;
c) the spatial reference system ID for the Geometry Column;
d) the coordinate dimension for the Geometry column;
The columns in the GEOMETRY_COLUMNS table for the SQL with Geometry Types environment are a
subset of the columns in the GEOMETRY_COLUMNS table defined for the SQL-implementation based on
predefined data types.
An alternative method for identification of feature tables and Geometry Columns may be available for
SQL-implementations with Geometry Types. In the SQL-implementation with Geometry Types, the Geometry
Column may be represented as a row in the COLUMNS metadata view of the SQL INFORMATION_SCHEMA.
Spatial Reference System Identity and coordinate dimension is, however, not a standard part of the
SQL INFORMATION_SCHEMA. To access this information, the GEOMETRY_COLUMNS table would still
need to be referenced.
6.2.3 Identification of Spatial Reference Systems
Every Geometry Column is associated with a Spatial Reference System. The Spatial Reference System
identifies the coordinate system for all geometric objects stored in the column, and gives meaning to the
numeric coordinate values for any geometric object stored in the column. Examples of commonly used Spatial
Reference Systems include “Latitude Longitude” and “UTM Zone 10”.
The SPATIAL_REF_SYS table stores information on each Spatial Reference System in the database. The
columns of this table are the Spatial Reference System Identifier (SRID), the Spatial Reference System
Authority Name (AUTH_NAME), the Authority Specific Spatial Reference System Identifier (AUTH_SRID) and
the Well-known Text description of the Spatial Reference System (SRTEXT). The Spatial Reference System
Identifier (SRID) constitutes a unique integer key for a Spatial Reference System within a database.
Interoperability between clients is achieved via the SRTEXT column which stores the Well-known Text
representation for a Spatial Reference System.
8 © ISO 2004 – All rights reserved

---------------------- Page: 13 ----------------------

ISO 19125-2:2004(E)
6.2.4 Feature tables
A feature is an abstraction of a real-world object. Feature attributes are columns in a feature table. Features
are rows in a feature table. The Geometry of a feature is stored in a Geometry Column whose type is drawn
from a set of SQL Geometry Types.
Relationships between features may be defined as foreign key references between feature tables.
6.2.5 Background information on SQL User Defined Types
The term User Defined Type (UDT) refers to a data type that extends the SQL type system.
UDT types can be used to define the column types for tables, this allows values stored in the columns of a
table to be instances of UDT.
SQL functions may be declared to take UDT values as arguments, and return UDT values as results.
An UDT may be defined as a subtype of another UDT, referred to as its supertype. This allows an instance of
the subtype to be stored in any column where an instance of the supertype is expected and allows an instance
of the subtype to be used as an argument or return value in any SQL function that is declared to use the
supertype as an argument or return value.
The above definition of UDT is value based.
SQL implementations that support User Defined Types may also support the concept of References to User
Defined Types instances that are stored as rows in a table whose type corresponds to the type of the User
Defined Type. The terms RowType and Reference to RowType are also used to describe such types.
This specification allows Geometry Types to be implemented as either pure value based Types or as Types
that support persistent References.
The Types for Geometry are defined in black-box terms, i.e. all access to information about a Geometry Type
instance is through SQL functions. No attempt is made to distinguish functions that may access Type instance
attributes (such as the dimension of a geometric object) from functions that may compute values given a Type
instance (such as the centroid of a Polygon). In particular, an implementation of this part of ISO 19125 would
be free to nominate any set of functions as observer methods on attributes of a User Defined Type, as long as
the signatures of the SQL functions described in this part of ISO 19125 are preserved.
6.2.6 SQL Geometry Type hierarchy
The SQL Geometry Types are organized into a type hierarchy shown in Figure 4.
© ISO 2004 – All rights reserved 9

---------------------- Page: 14 ----------------------

ISO 19125-2:2004(E)

Figure 4 — SQL Geometry Type hierarchy
The root type, named Geometry, has subtypes for Point, Curve, Surface and GeometryCollection. A
GeometryCollection is a Geometry that is a collection of possibly heterogeneous geometric objects. MultiPoint,
MultiCurve and MultiSurface are specific subtypes of GeometryCollection used to manage homogenous
collections of Points, Curves and Surfaces. The 0 dimensional Geometry Types are Point and MultiPoint.
The one-dimensional Geometry Types are Curve and MultiCurve together with their subclasses. The
two-dimensional Geometry Types are Surface and MultiSurface together with their subclasses.
SQL functions are defined to construct instances of the above Types given Well-known Text or Binary
representations of the types. SQL functions defined on the types implement the methods described in the
Geometry Object Model.
6.2.7 Geometry values and spatial reference systems
In order to model Spatial Reference System information, each geometric object in the SQL with Geometry
Types implementation is associated with a Spatial Reference System. Capturing this association at the level
of the individual geometric object allows literal Geometry values that are not yet part of a column in the
database to be associated with a Spatial Reference System. Examples of such a geometric object, Geometry
values is a geometric object that is used as a parameter to a spatial query or a geometric object that is part of
an insert statement. Capturing this association at the level of the individual geometric object also allows
functions that take two geometric objects to check for compatible Spatial Reference Systems.
A Geometry value is associated with a Spatial Reference System by storing the Spatial Reference System
Identity (SRID) for the Spatial Reference System as a part of the geometric object. Each Spatial Reference
System in the database is identified by a unique value of SRID.
The SRID for a geometric object is assi
...