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

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

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

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.

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

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-Dimensional space
ℜ 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
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 t
...

SLOVENSKI STANDARD
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
2003-01.Slovenski inštitut za standardizacijo. Razmnoževanje celote ali delov tega standarda ni dovoljeno.

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

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

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

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.

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

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-Dimensional space
ℜ 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
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
...