1. Spatial data
H2GIS geometry types used the EWKB (extended well-known binary) format provided by H2 since the 2.0.X version.
The geometry model which implements the OGC Simple Feature Access - Part 1: Common Architecture.
Figure 1. OGC geometry model
All types are used to represent geometric features in the 2D (XY), Z (XYZ), M (XYM), and ZM (XYZM) coordinate dimension space.
H2GIS supports the EWKB, EWKT, WKB and WKT encodings.
The EWKB formats a PostGIS specific format that includes the spatial reference system (SRID) identifier.
Please consult https://postgis.net/docs/using_postgis_dbmanagement.html#EWKB_EWKT for more detailed informations.
The Well-known Binary Representation for Geometry provides a portable representation of a geometric object as a contiguous stream of bytes. The two encodings (NDR or XDR) are used to specificy the byte order, the XDR encoding is Big Endian, and the NDR encoding is Little Endian.
You can use the ST_AsBinary function to convert a geometry to its WKB representation.
SELECT ST_AsBinary(
ST_GeomFromText('POLYGON((0 0, 0 1, 1 1, 1 0, 0 0))', 4326));
-- Answer: 0020000003000010e600000001000000050000000000000000
-- 000000000000000000000000000000003ff00000000000003ff0000
-- 0000000003ff00000000000003ff000000000000000000000000000
-- 0000000000000000000000000000000000The Well-known Text Representation provides a standard textual representation for Geometry. You can use the ST_AsWKT function to convert a geometry to its WKT representation.
SELECT ST_AsWKT('POLYGON((0 0, 0 1, 1 1, 1 0, 0 0))');
-- Answer: POLYGON((0 0, 0 1, 1 1, 1 0, 0 0))A table may be created with one or more columns that have a declared type of Geometry or one of its subtypes. If a specific spatial reference system (SRS) is associated with a column, then all geometry values in that column shall be in that specific SRS. A constraint can be used to model this restriction.
--Geometry table without SRS
CREATE TABLE myGeometryTable (the_geom GEOMETRY);
INSERT INTO myGeometryTable VALUES(ST_GeomFromText('POLYGON((0 0, 0 1, 1 1, 1 0, 0 0))'));--Geometry table with a SRS constraint
CREATE TABLE myGeometryTable (the_geom GEOMETRY(POLYGON, 4326));
INSERT INTO myGeometryTable VALUES(ST_GeomFromText('POLYGON((0 0, 0 1, 1 1, 1 0, 0 0))', 4326));A spatial information schema organized around two tables is implemented in H2GIS in order to collect informations on geometry tables or manage a list of SRS definitions. This schema is specified in the OGC Simple feature access - Part 2: SQL option.
The geometry columns view is used to identify each Geometry column in the database. The view contains 9 columns : F_TABLE_CATALOG, F_TABLE_SCHEMA, F_TABLE_NAME, F_GEOMETRY_COLUMN, STORAGE_TYPE, GEOMETRY_TYPE, COORD_DIMENSION, SRID, TYPE.
The values of F_TABLE_CATALOG, F_TABLE_SCHEMA, and F_TABLE_NAME are the catalog name, the unqualified schema name, and the qualified identifier, respectively, of the table containing the column being described.
F_GEOMETRY_COLUMN stores the name of the Geometry Column. The column shall have a declared type of Geometry or one of its subtypes.
The STORAGE_TYPE value corresponds to the type of storage being used for this geometry column.
- 0 = normalized geometry implementation,
- 1 = binary geometry implementation (Well-known Binary Representation for Geometry).
- NULL = geometry types implementation.
GEOMETRY_TYPE stores the type of geometry values for the Geometry column. Supported geometry type codes are listed in table 1.
| Code | Geometry type | Coordinates |
|---|---|---|
| 0 | GEOMETRY | IN X Y Z M |
| 1 | POINT | IN X Y Z M |
| 2 | LINESTRING | IN X Y Z M |
| 3 | POLYGON | IN X Y Z M |
| 4 | MULTIPOINT | IN X Y Z M |
| 5 | MULTILINESTRING | IN X Y Z M |
| 6 | MULTIPOLYGON | IN X Y Z M |
| 7 | GEOMCOLLECTION | IN X Y Z M |
| Table 1. Geometry type codes |
The value of COORD_DIMENSION is the coordinate dimension for the Geometry column. COORD_DIMENSION can be 2 (for x and y), 3 (with z or m added), or 4 (with both z and m added). The ordinates x, y and z are spatial, and the ordinate m is a measure.
The value of SRID is the id of the spatial reference system associated with the geometry column (SRID column in SPATIAL_REF_SYS table). If no spatial reference system is associated with the column, SRID represents the zero value.
The value TYPE is the name of geometry type : POINT, LINESTRING, POLYGON, MULTIPOINT, MULTILINESTRING, MULTIPOLYGON, GEOMETRYCOLLECTION. This column is not part of the OpenGIS specification, but is required for ensuring POSTGIS compatibility.
A geometry column and each geometry entities could be associated with a Spatial Reference System.
The SPATIAL_REF_SYS table stores information on each Spatial Reference System in the database that could be used by the Coordinate Transform Library (CTS). 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), the Wellknown Text description of the Spatial Reference System (SRTEXT) and PROJ4TEXT definitions for all EPSG projections. The Spatial Reference System Identifier (SRID) constitutes a unique integer key for a Spatial Reference System within a database. The SPATIAL_REF_SYS table has been populated from the EPSG database definitions, plus the french IGN definitions, including the nadgrid references.
H2GIS uses the RTree index implemented in the H2 database Engine. The H2 sql syntax to build a RTree is aligned on PostGreSQL and PostGIS.
CREATE SPATIAL INDEX ON myGeometryTable (the_geom);H2GIS implements the SQL routines describe in [OpenGIS ® Implementation Standard for Geographic information - Simple feature access - Part 2: SQL option] (http://www.opengeospatial.org/standards/sfs) ( 06-104r4 ). Thanks to The JTS Topology Suite Java library that offers the fuel for creating and manipulating geometries.
H2GIS offers also custom functions needed to build for more advanced spatial analysis chains. All functions are documented and available at http://www.h2gis.org/docs/home/
To use a spatial with H2GIS, the operator && must be used as in PostGIS. It performs a geometry envelope intersection on the left and right operand.
Here is an example SQL script to create a table with a spatial column and index.
CREATE TABLE GEO_TABLE(GID SERIAL, THE_GEOM GEOMETRY);
INSERT INTO GEO_TABLE(THE_GEOM) VALUES
('POINT(500 505)'),
('LINESTRING(550 551, 525 512, 565 566)'),
('POLYGON ((550 521, 580 540, 570 564, 512 566, 550 521))');
CREATE SPATIAL INDEX GEO_TABLE_SPATIAL_INDEX ON GEO_TABLE(THE_GEOM);and to query the table using the envelope index.
SELECT * FROM GEO_TABLE
WHERE THE_GEOM && 'POLYGON ((490 490, 536 490, 536 515, 490 515, 490 490))';You can check if the spatial index is used using the "explain plan" feature.
EXPLAIN SELECT * FROM GEO_TABLE
WHERE THE_GEOM && 'POLYGON ((490 490, 536 490, 536 515, 490 515, 490 490))';
-- Result
SELECT
GEO_TABLE.GID,
GEO_TABLE.THE_GEOM
FROM PUBLIC.GEO_TABLE
/* PUBLIC.GEO_TABLE_SPATIAL_INDEX:
THE_GEOM && 'POLYGON ((490 490, 536 490, 536 515, 490 515, 490 490))' */
WHERE INTERSECTS(THE_GEOM,
'POLYGON ((490 490, 536 490, 536 515, 490 515, 490 490))')Note : For persistent databases, the spatial index is stored on disk; for in-memory databases, the index is kept in memory.