OGC standard introduction 4

· SQL
Predefined Schema

In a spatial database, tables are required to store and manage geometric fields, spatial references, and other information. Therefore, OGC first specifies the schema object required in the database [1]
:

Figure
7. schema to be predefined in the OGC space database

It can be seen that the database must have a geometry_columns table that records the geometric field information and a spatial_ref_sys table that records the spatial reference information. We can find these two tables in a spatial database that supports the OGC standard. Of course, some spatial databases directly use geometry_columns and spatial_ref_sys as the names of these two tables, such as postgis, while others use other names, for example, Oracle Spatial uses "ogis_geometry_columns" and "ogis_spatial_reference_systems", and ArcSDE for Oracle uses "geometry_columns" and "spatial_references.

· SQL
Geometric Object Storage

Figure 7 shows that the geometric information in the OGC standard is stored in a ry table, which can store geometric objects in two ways: conventional fields or WKB, the geometry table is associated with the geometric fields of the feature table through the GID field. In fact, there is also a definition in the OGC standard. The geometric field of the feature table can also be SQL UDT (custom type), that is, no additional geometry table is required to store geometric information, and directly stored in the feature table's geometric fields. Most databases use this custom type to store geometric information, such as the st_geometry type in ArcSDE, and the geometry and st_geometry types in postgis.

You can use the geometric type defined in the SFS standard or SQL/mm [2]
For example, postgis supports both definitions, which is a ing between the SFS and SQL/mm ry types definitions:

Figure 8 ing between SFS and SQL/mm geometric types

Users can follow the SFS definition, use a name like "Geometry" and "point", or follow the SQL/mm definition and use "ST _" as the prefix for naming, for example, "st_geometry" and "st_point ".

For the inheritance relationship of the ry type of SQL, see Figure 9. We can find that the ry object model implemented in SQL is very similar to the common ry object model shown in Figure 1.

Figure
9. SQL geometric object model

[1]
Compatible with the definition of Schema short name in iso SQL multimedia and application packages (SQL/mm)-Part 3: spatial.

[2]
ISO "SQL multimedia and application packages" standard, where "Part3: spatial" defines content related to spatial data.