| | 37 | * well-defined subset of the geometry_columns table (as per SF spec, section 7.1.3.2, 7.1.3.3); see below for specific column requirements, |
| | 38 | * spatial_ref_sys table (as per SF spec, section 7.1.2.2, 7.1.2.3) |
| | 39 | * geometry_format column extending the geometry_columns table, set to one of WKB, WKT, WKB12, WKT12 or FGF. If not present, WKB will be assumed. |
| | 40 | * one or more tables to store the geometry and associated features. Although in general [http://www.sqlite.org/datatype3.html type does not matter in SQLite], BLOB type is recommended at least for WKB and FGF formats as it does not perform character translation on the data. |
| | 41 | |
| | 42 | Note that this implementation does ''not'' currently include support for the enhanced multi-dimensional WKB and WKT formats that are defined in the OGC Simple Feature Access specification [http://www.opengeospatial.org/standards/sfa 06-103r3]. The tags WKB12 and WKT12 are reserved for future use in support of this enhanced format. FGF refers to the [http://fdo.osgeo.org/files/fdo/docs/FDG_FDODevGuide/files/WSfacf1429558a55de8821c21057fbebc2b-789.htm FDO Binary Geometry Format]. |
| | 43 | |
| | 44 | == geometry_columns Subset == |
| | 45 | |
| | 46 | SQLite databases require the following fields in their geometry_columns table. Other fields are permitted, but will not affect data access. For detailed explanations of all of the standard fields (everything but geometry_format), see the SFSQL [http://www.opengeospatial.org/standards/sfs 06-104r3] document, section 7.1.3.3. |
| | 47 | |
| | 48 | || Column Name || Type || Notes || |
| | 49 | || f_table_name || VARCHAR || name of a feature table with a geometry column. || |
| | 50 | || f_geometry_column || VARCHAR || name of a geometry column in the feature table || |
| | 51 | || geometry_type || INTEGER || integer value representing the form of geometry (1=POINT, 2=LINESTRING, etc). See Table 4 on Page 29-31 of the SFSQL [http://www.opengeospatial.org/standards/sfs 06-104r3] spec for a full list. || |
| | 52 | || coord_dimension || INTEGER || dimensionality of the geometric features: 2, 3 or 4 || |
| | 53 | || srid || INTEGER || foreign key into the spatial_ref_sys table || |
| | 54 | || geometry_format || VARCHAR || One of "WKB", "WKT", "WKB12", "WKT12", "FGF" || |
| | 55 | |
| | 56 | The suggested definition is: |
| | 57 | |
| | 58 | {{{ |
| | 59 | CREATE TABLE geometry_columns ( |
| | 60 | f_table_name VARCHAR, |
| | 61 | f_geometry_column VARCHAR, |
| | 62 | geometry_type INTEGER, |
| | 63 | coord_dimension INTEGER, |
| | 64 | srid INTEGER, |
| | 65 | geometry_format VARCHAR ) |
| | 66 | }}} |
| 46 | | * Geometry will be serialized as BLOBs in FDO geometry format (FGF), WKB , or WKT 1.2 format. The type of geometry encoding will be specified in the geometry_columns table (see below) in a column named g_geometry_format. In addition, the provider may support point geometry with coordinates stored in non-geometric columns, via a connection string setting. |
| 47 | | * A non-persistent, spatial index usable for BBOX queries built on the fly and valid for the duration of the FDO connection; |
| | 73 | * Geometry will be serialized in FDO geometry format (FGF), WKB, or WKT and stored in a BLOB column. The type of geometry encoding will be specified in the geometry_columns table in a column named geometry_format. In addition, the provider may support point geometry with coordinates stored in non-geometric columns, via a connection string setting. |
| | 74 | * A non-persistent spatial index usable for BBOX queries, built on the fly and valid for the duration of the FDO connection; |
| 61 | | |
| 62 | | == geometry_columns == |
| 63 | | |
| 64 | | || Column Name || Type || Notes || |
| 65 | | || f_table_catalog || VARCHAR || blank || |
| 66 | | || f_table_schema || VARCHAR || blank || |
| 67 | | || f_table_name || VARCHAR || name of a feature table with a geometry column || |
| 68 | | || f_geometry_column || VARCHAR || name of a geometry column in the feature table || |
| 69 | | || f_geometry_format || VARCHAR || One of "WKT", "WKB", "FGF", ... (should we differentiate between 1.0 and 1.2 geometries?) || |
| 70 | | || coord_dimension || INTEGER || 2, 3 or 4 || |
| 71 | | || srid || INTEGER || shared key with spatial_ref_sys table || |
| 72 | | || type || VARCHAR || geometry class name, one of GEOMETRY, GEOMETRYCOLLECTION, POINT, MULTIPOINT, POLYGON, MULTIPOLYGON, LINESTRING, or MULTILINESTRING || |
| 73 | | |
| 74 | | The above definition is related to the SFSQL specification from OGC and closely matches the PostGIS definition (with the addition of the f_geometry_format field). The suggested definition is: |
| 75 | | |
| 76 | | {{{ |
| 77 | | CREATE TABLE geometry_columns ( |
| 78 | | f_table_catalog VARCHAR, |
| 79 | | f_table_schema VARCHAR, |
| 80 | | f_table_name VARCHAR, |
| 81 | | f_geometry_column VARCHAR, |
| 82 | | f_geometry_format VARCHAR, |
| 83 | | type VARCHAR, |
| 84 | | coord_dimension INTEGER, |
| 85 | | srid INTEGER ) |
| 86 | | }}} |
| 87 | | |
