Proposal: Support for Spatial Data Type

76 views

Skip to first unread message

Artyom Ivanov

unread,

Apr 4, 2025, 8:17:07 AM4/4/25

to firebird-devel

Functional Architecture

Tags will be used to indicate which standard the function or table is taken from:
[OGC] - OGC SFA (or OpenGIS)
[SQL/MM] - SQL/MM Part 3: Spatial

If the function is not contained in the standard and was taken from another DBMS (PostGIS, Oracle, MySQL, etc.), the tag [Other DBMS] will be used.

Dividing into GEOMETRY and GEOGRAPHY

According to the standard there is no explicit division into GEOMETRY and GEOGRAPHY, the type is defined depending on the used spatial coordinate system. If the spatial object was created with WGS 84 coordinate system (geodetic coordinate system using lat/lon), then this object is GEOGRAPHY type, or if the spatial object was created with JGD2011 / Japan Plane Rectangular CS IX coordinate system (projection or cartesian coordinate system), then this object is GEOMETRY type. In the current implementation, the type (GEOMETRY or GEOGRAPHY) is defined through the spatial object creation functions, but this does not mean that it will be possible to create a GEOMETRY object with a geodetic coordinate system, in this case there is a check and protection against incorrect object creation.

In different DBMS this moment is made differently, for example, PostGIS and MS SQL Server made an explicit separation of types, but for example, in MySQL and Oracle the type is determined implicitly by specifying the coordinate system.

The question of which approach to choose is open for discussion.

Field creation

Syntax

GEOMETRY([<geometry type>] [<additional dimension>] [, <SRID>])

<geometry type> : POINT

LINESTRING

POLYGON

MULTIPOINT

MULTILINESTRING

MULTIPOLYGON

GEOMETRYCOLLECTION

<additional dimension> : Z, M, ZM

CREATE TABLE F_TABLE (geom GEOMETRY); -- Any type of geometry with XY dimension where SRID = 0

CREATE TABLE F_TABLE (geom GEOMETRY(POINT)); -- Only geometry with POINT type with XY dimension where SRID = 0

CREATE TABLE F_TABLE (geom GEOMETRY(5636)); -- Any type of geometry with XY dimension where SRID = 5636

CREATE TABLE F_TABLE (geom GEOMETRY(ZM, 5636)); -- Any type of geometry with XYZM dimension where SRID = 5636

CREATE TABLE F_TABLE (geom GEOMETRY(POINT, 5636)); -- Only geometry with POINT type with XY dimension where SRID = 5636

CREATE TABLE F_TABLE (geom GEOMETRY(LINESTRINGZ, 5636)); -- Only geometry with LINESTRING type with XYZ dimension where SRID = 5636

—------------------------------------------

GEOGRAPHY([<geometry type>] [<additional dimension>] [, <SRID>])

<geometry type> : POINT

LINESTRING

POLYGON

MULTIPOINT

MULTILINESTRING

MULTIPOLYGON

GEOMETRYCOLLECTION

<additional dimension> : Z, M, ZM

CREATE TABLE F_TABLE (geom GEOGRAPHY); -- Any type of geography with XY dimension where SRID = 4326

CREATE TABLE F_TABLE (geom GEOGRAPHY(POINT)); -- Only geography with POINT type with XY dimension where SRID = 4326

CREATE TABLE F_TABLE (geom GEOGRAPHY(4326)); -- Any type of geography with XY dimension where SRID = 4326

CREATE TABLE F_TABLE (geom GEOGRAPHY(ZM, 4326)); -- Any type of geography with XYZM dimension where SRID = 4326

CREATE TABLE F_TABLE (geom GEOGRAPHY(POINT, 4326)); -- Only geography with POINT type with XY dimension where SRID = 4326

CREATE TABLE F_TABLE (geom GEOGRAPHY(LINESTRINGZ, 4326)); -- Only geography with LINESTRING type with XYZ dimension where SRID = 4326

Spatial Reference System (SRS)

A spatial coordinate system is a system used to accurately measure locations on the Earth's surface in the form of coordinates.

A geodetic system uses angular coordinates (longitude and latitude) that correspond to the Earth's surface.
A projection system uses a mathematical projection transformation to reflect the spheroidal surface of the Earth onto a plane.
Local is a Cartesian coordinate system that is not referenced to the Earth's surface. It is set to SRID = 0.

There are many different spatial coordinate systems. Common SRSs are standardized in the European Petroleum Survey Group (EPSG) database. The coordinate system is defined through an integer SRS identifier called SRID.

Below is the syntax for creating/deleting SRS, it does not conform to any standard, because the standard has only an example of direct insertion into the system table, which is generally forbidden in our codebase.

Creation of a spatial system

{CREATE | RECREATE} SPATIAL REFERENCE SYSTEM (<SRID>, {<name of standard> | NULL}, {<SRS identifier in the standard> | NULL}, {<SRS WKT-representation> | NULL}, {<SRS PROJ- representation> | NULL})

<SRS WKT-representation> and <SRS PROJ-representation> cannot be NULL at the same time, at least one representation must be present.

Changing the spatial system

ALTER SPATIAL REFERENCE SYSTEM <SRID> <field to change> [, <field to change> ... ]

<field to change> ::= AUTH_NAME = '<name of standard>'

| AUTH_SRID = '<SRS identifier in the standard>'

| SRTEXT = '<SRS WKT-representation>'

| PROJ4TEXT = '<SRS PROJ-representation>'

Deletion of the spatial system

DROP SPATIAL REFERENCE SYSTEM [IF EXISTS] <SRID>;

Here we have a divergence with the rest of metadata objects that are identified by name, while SRS is identified by numeric SRID in the standard. This does not give us any troubles as long as we don’t introduce permissions and/or dependencies for SRS. But alternative suggestions are surely appreciated.

Ways to represent a spatial object

[OGC, SQL/MM] Well-known Text (WKT)

| <linestring tagged text>

| <polygon tagged text>

| <multipoint tagged text>

| <multilinestring tagged text>

| <multipolygon tagged text>

| <geometrycollection tagged text>

<point tagged text> ::= point <dimension> <point text>

<linestring tagged text> ::= linestring <dimension> <linestring text>

<polygon tagged text> ::= polygon <dimension> <polygon text>

<multipoint tagged text> ::= multipoint <dimension> <multipoint text>

<multilinestring tagged text> ::= multilinestring <dimension> <multilinestring text>

<multipolygon tagged text> ::= multipolygon <dimension> <multipolygon text>

<geometrycollection tagged text> ::= geometrycollection <dimension> <geometrycollection text>

| <left paren> <point> <right paren>

| <left paren> <point> {<comma> <point>}* <right paren>

| <left paren> <linestring text> {<comma> <linestring text>}* <right paren>

| <left paren> <point text> {<comma> <point text>}* <right paren>

| <left paren> <linestring text> {<comma> <linestring text>}* <right paren>

| <left paren> <polygon text> {<comma> <polygon text>}* <right paren>

| <left paren> <geometry tagged text> {<comma> <geometry tagged text>}* <right paren>

<dimension> ::= z | m | zm

<comma> ::= ,

<left paren> ::= (

<right paren> ::= )

<empty set> ::= EMPTY

Examples:

POINT(0 0)

POINT Z (0 0 0)

POINT ZM (0 0 0 0)

POINT EMPTY

LINESTRING(0 0,1 1,1 2)

LINESTRING EMPTY

POLYGON((0 0,4 0,4 4,0 4,0 0),(1 1, 2 1, 2 2, 1 2,1 1))

MULTIPOINT((0 0),(1 2))

MULTIPOINT Z ((0 0 0),(1 2 3))

MULTIPOINT EMPTY

MULTILINESTRING((0 0,1 1,1 2),(2 3,3 2,5 4))

MULTIPOLYGON(((0 0,4 0,4 4,0 4,0 0),(1 1,2 1,2 2,1 2,1 1)), ((-1 -1,-1 -2,-2 -2, -2 -1,-1 -1)))

GEOMETRYCOLLECTION(POINT(2 3), LINESTRING(2 3,3 4))

GEOMETRYCOLLECTION EMPTY

[Other DBMS] Extended Well-Known Text (EWKT)

The syntax is the same as WKT, except that the ability to specify an SRID is added:

SRID = 6677; POINT(0 0)

SRID = 5636; LINESTRINGZ(0 0 0, 1 1 1, 2 2 2)

MULTIPOINT(0 0, 1 0, 2 0) -- Same as normal WKT

The following functions are used to transform a WKT/EWKT representation into a spatial object:

[SQL/MM] ST_GeomFromText({<WKT> | <EWKT>} [, <SRID>]) -> GEOMETRY - SRID = 0 by default

[Other DBMS] ST_GeogFromText({<WKT> | <EWKT>} [, <SRID>]) -> GEOGRAPHY - SRID = 4326 by default

[OGC, SQL/MM] ST_WKTToSQL({<WKT> | <EWKT>} [, <SRID>]) -> GEOMETRY - Alias for ST_GeomFromText

The following functions are used to transform a spatial object into a WKT/EWKT representation:

[OGC, SQL/MM] ST_AsText(SPATIAL geom) -> VARCHAR

[Other DBMS] ST_ASEWKT(SPATIAL geom) -> VARCHAR

[OGC, SQL/MM] Well-known Binary (WKB)

// Basic Type definitions

// byte : 1 byte

// uint32 : 32 bit unsigned integer (4 bytes)

// double : double precision number (8 bytes)

// Building Blocks : Coordinate, LinearRing

Point {

double x;

double y;

};

PointZ {

double x;

double y;

double z;

};

PointM {

double x;

double y;

double m;

};

PointZM {

double x;

double y;

double z;

double m;

};

LinearRing {

uint32 numPoints;

Point points[numPoints];

};

enum WKBByteOrder {

wkbXDR = 0, // Big Endian

wkbNDR = 1 // Little Endian

};

enum WKBGeometryType {

wkbPoint = 1,

wkbLineString = 2,

wkbPolygon = 3,

wkbMultiPoint = 4,

wkbMultiLineString = 5,

wkbMultiPolygon = 6,

wkbGeometryCollection = 7,

wkbPointZ = 1001,

wkbLineStringZ = 1002,

wkbPolygonZ = 1003,

wkbMultiPointZ = 1004,

wkbMultiLineStringZ = 1005,

wkbMultiPolygonZ = 1006,

wkbGeometryCollectionZ = 1007,

wkbPointM = 2001,

wkbLineStringM = 2002,

wkbPolygonM = 2003,

wkbMultiPointM = 2004,

wkbMultiLineStringM = 2005,

wkbMultiPolygonM = 2006,

wkbGeometryCollectionM = 2007,

wkbPointZM = 3001,

wkbLineStringZM = 3002,

wkbPolygonZM = 3003,

wkbMultiPointZM = 3004,

wkbMultiLineStringZM = 3005,

wkbMultiPolygonZM = 3006,

wkbGeometryCollectionZM = 3007

}

WKBPoint {

byte byteOrder;

uint32 wkbType;

Point point;

};

WKBLineString {

byte byteOrder;

uint32 wkbType;

uint32 numPoints;

Point points[numPoints];

};

WKBPolygon {

byte byteOrder;

uint32 wkbType;

uint32 numRings;

LinearRing rings[numRings]

};

WKBMultiPoint {

byte byteOrder;

uint32 wkbType;

uint32 numWkbPoints;

WKBPoint WKBPoints[numWkbPoints];

};

WKBMultiLineString {

byte byteOrder;

uint32 wkbType;

uint32 numWkbLineStrings;

WKBLineString WKBLineStrings[numWkbLineStrings];

};

WKBMultiPolygon {

byte byteOrder;

uint32 wkbType;

uint32 numWkbPolygons;

WKBPolygon wkbPolygons[numWkbPolygons];

};

WKBGeometry {

union {

WKBPoint point;

WKBLineString linestring;

WKBPolygon polygon;

WKBGeometryCollection collection;

WKBMultiPoint mpoint;

WKBMultiLineString mlinestring;

WKBMultiPolygon mpolygon;

}

};

WKBGeometryCollection {

byte byteOrder;

uint32 wkbType;

uint32 numWkbGeometries;

WKBGeometry wkbGeometries[numWkbGeometries];

};

Example of LINESTRING(0 0, 1 1, 2 1) in WKB representation:

01 - byteOrder(wkbNDR)

02000000 - wkbType(LineString)

03000000 - numPoints(3)

0000000000000000 - x(0.0)

0000000000000000 - y(0.0)

000000000000F03F - x(1.0)

000000000000F03F - y(1.0)

0000000000000040 - x(2.0)

000000000000F03F - y(1.0)

[Other DBMS] Extended Well-known Binary (EWKB)
EWKB is an extension to the standard WKB that allows SRIDs to be specified.

Additional dimensions are specified by adding bit flags to wkbType, which is present in all WKB representations:

wkbZ = 0x80000000

wkbM = 0x40000000

wkbSRID = 0x20000000

Example of EWKB structure for a point with additional Z dimension:

wkbZ = 0x80000000

wkbM = 0x40000000

wkbSRID = 0x20000000

enum wkbGeometryTypeZ {

wkbPoint = 1,

wkbLineString = 2,

wkbPolygon = 3,

wkbMultiPoint = 4,

wkbMultiLineString = 5,

wkbMultiPolygon = 6,

wkbGeometryCollection = 7

}

WKBPointZ {

byte byteOrder;

uint32 wkbType; // wkbPointZ = (wkbPoint | wkbZ) = 0x80000001

Point {

Double x;

Double y;

Double z;

};

If the wkbSRID flag is set, the SRID number is added after wkbType:

WKBPointS {

byte byteOrder; // wkbXDR or wkbNDR

uint32 wkbType; // wkbPointS = (wkbPoint | wkbZ | wkbSRID) = 0xA0000001

uint32 SRID;

Point {

Double x;

Double y;

Double z;

};

The following functions are used to transform a WKB/EWKB representation into a spatial object:

[SQL/MM] ST_GeomFromWKB({<WKB> | <EWKB>} [, <SRID>]) -> GEOMETRY - SRID = 0 by default

[Other DBMS] ST_GeogFromWKB({<WKB> | <EWKB>} [, <SRID>]) -> GEOGRAPHY - SRID = 4326 by default

[OGC, SQL/MM] ST_WKBToSQL({<WKB> | <EWKB>} [, <SRID>]) -> GEOMETRY - Alias for ST_GeomFromWKB

The WKB representation must be passed through HEX_DECODE:

ST_GEOMFROMWKB(HEX_DECODE('01010000000000000000002E400000000000002E40'))

The following functions are used to transform a spatial object into a WKB/EWKB representation:

[OGC, SQL/MM] ST_AsBinary(SPATIAL geom) -> VARBINARY

[Other DBMS] ST_ASEWKB(SPATIAL geom) -> VARBINARY

Constructor functions

[Other DBMS] ST_MakePoint(DOUBLE X, DOUBLE Y, DOUBLE Z, DOUBLE M) -> GEOMETRY(POINT)

[Other DBMS] ST_MakePointM(DOUBLE X, DOUBLE Y, DOUBLE M) -> GEOMETRY(POINT)

[Other DBMS] ST_MakeLine(SPATIAL(POINT)[] geoms) -> SPATIAL(LINESTRING)

[Other DBMS] ST_MakePolygon(SPATIAL(LINESTRING)[] geoms) -> SPATIAL(POLYGON)

[Other DBMS] ST_Collect(SPATIAL[] geoms) -> SPATIAL(MULTI... | GEOMETRYCOLLECTION) - if the input objects are of different types, the output will be GEOMETRYCOLLECTION

ST_MakeLine(ST_MakePoint(1, 1), ST_MakePoint(10, 10))

ST_Collect(ST_GeomFromText('POINT(12 48)', 5636), ST_GeomFromText('LINESTRING(0 0, 15 0)', 5636)

Functions for working with spatial type

Finding relationships between spatial objects

[OGC, SQL/MM] ST_Intersects(SPATIAL geom1, SPATIAL geom2) -> BOOL

[OGC, SQL/MM] ST_Touches(SPATIAL geom1, SPATIAL geom2) -> BOOL

[OGC, SQL/MM] ST_Disjoint(SPATIAL geom1, SPATIAL geom2) -> BOOL

[OGC, SQL/MM] ST_Crosses(SPATIAL geom1, SPATIAL geom2) -> BOOL

[OGC, SQL/MM] ST_Within(SPATIAL geom1, SPATIAL geom2) -> BOOL

[OGC, SQL/MM] ST_Contains(SPATIAL geom1, SPATIAL geom2) -> BOOL

[OGC, SQL/MM] ST_Overlaps(SPATIAL geom1, SPATIAL geom2) -> BOOL

[OGC, SQL/MM] ST_Equals(SPATIAL geom1, SPATIAL geom2) -> BOOL

[Other DBMS] ST_Covers(SPATIAL geom1, SPATIAL geom2) -> BOOL

[OGC, SQL/MM] ST_Relate(SPATIAL geom1, SPATIAL geom2, CHAR[9] matrix) -> BOOL

Performing operations on a spatial object

[OGC, SQL/MM] ST_Union(SPATIAL[] geoms) -> SPATIAL

[OGC, SQL/MM] ST_Distance(SPATIAL geom1, SPATIAL geom2) -> DOUBLE

[OGC, SQL/MM] ST_Intersection(SPATIAL geom1, SPATIAL geom2) -> SPATIAL

[OGC, SQL/MM] ST_Difference(SPATIAL geom1, SPATIAL geom2) -> SPATIAL

[OGC, SQL/MM] ST_SymDifference(SPATIAL geom1, SPATIAL geom2) -> SPATIAL

[OGC, SQL/MM] ST_ConvexHull(SPATIAL geom) -> SPATIAL

[OGC, SQL/MM] ST_Envelope(SPATIAL geom) -> SPATIAL

[OGC, SQL/MM] ST_Buffer(SPATIAL geom, ST_BufferStrategy(<distance>) [, <strategy>, ... , <strategy>, SQL/MM]) -> SPATIAL -- more about ST_Buffer further

[Other DBMS] ST_BufferStrategy() -> Strategy

[Other DBMS] ST_Simplify(SPATIAL geom, DOUBLE distance) -> SPATIAL

[OGC, SQL/MM] ST_Area(SPATIAL geom) -> DOUBLE

[OGC, SQL/MM] ST_Length(SPATIAL geom) -> DOUBLE

[SQL/MM] ST_Perimeter(SPATIAL geom) -> DOUBLE

[OGC, SQL/MM] ST_Centroid(GEOMETRY geom) -> GEOMETRY

[OGC, SQL/MM] ST_PointOnSurface(GEOMETRY geom) -> GEOMETRY

[OGC, SQL/MM] ST_Boundary(SPATIAL geom) -> SPATIAL

[OGC, SQL/MM] ST_LocateAlong(SPATIAL geom, DOUBLE measure) -> SPATIAL

[OGC, SQL/MM] ST_LocateBetween(SPATIAL geom, DOUBLE measureStart, DOUBLE measureEnd) -> SPATIAL

More details about ST_Buffer and ST_BufferStrategy:

ST_Buffer(<spatial object>, ST_BufferStrategy(<distance>) [, <strategy>, ... , <strategy>])

<distance> ::= 'distance_sym', <distance value>

| 'distance_asym', <left-hand distance value>, <right-hand distance value>

<strategy> ::= ST_BufferStrategy(<strategy type>)

| <End>

| <Join>

<Point> ::= 'point_circle', <number of points>

| 'point_square'

<End> ::= 'end_round', <number of points>

| 'end_flat'

<Join> ::= 'join_round', <number of points>

| 'join_miter', <distance value>

Additional functions for working with spatial type

[OGC, SQL/MM] ST_Dimension(SPATIAL geom) -> INTEGER

[OGC, SQL/MM] ST_GeometryType(SPATIAL geom) -> VARCHAR

[Other DBMS] ST_NPoints(SPATIAL geom) -> INTEGER

[OGC, SQL/MM] ST_Srid(SPATIAL geom) -> INTEGER

[Other DBMS] ST_SetSrid(SPATIAL geom, INTEGER srid) -> SPATIAL

[SQL/MM] ST_Transform(SPATIAL geom, INTEGER targetSrid) -> SPATIAL

[OGC, SQL/MM] ST_IsEmpty(SPATIAL geom) -> BOOL

[OGC, SQL/MM] ST_IsRing(SPATIAL geom) -> BOOL

[OGC, SQL/MM] ST_IsClosed(SPATIAL geom) -> BOOL

[OGC, SQL/MM] ST_IsSimple(SPATIAL geom) -> BOOL

[SQL/MM] ST_IsValid(SPATIAL geom) -> BOOL

[Other DBMS] ST_HasZ(SPATIAL geom) -> BOOL

[SQL/MM] ST_Is3D(SPATIAL geom) -> BOOL

[Other DBMS] ST_HasM(SPATIAL geom) -> BOOL

[SQL/MM] ST_IsMeasured(SPATIAL geom) -> BOOL

Functions on POINT

[OGC, SQL/MM] ST_X(SPATIAL(POINT) geom) -> DOUBLE

[OGC, SQL/MM] ST_Y(SPATIAL(POINT) geom) -> DOUBLE

[OGC, SQL/MM] ST_Z(SPATIAL(POINT) geom) -> DOUBLE

[OGC, SQL/MM] ST_M(SPATIAL(POINT) geom) -> DOUBLE

Functions on LINESTRING

[OGC, SQL/MM] ST_NumPoints(SPATIAL(LINESTRING) geom) -> INTEGER

[OGC, SQL/MM] ST_PointN(SPATIAL(LINESTRING) geom, INTEGER n) -> SPATIAL(POINT)

[OGC, SQL/MM] ST_StartPoint(SPATIAL(LINESTRING) geom) -> SPATIAL(POINT)

[OGC, SQL/MM] ST_EndPoint(SPATIAL(LINESTRING) geom) -> SPATIAL(POINT)

Functions on POLYGON

[OGC, SQL/MM] ST_ExteriorRing(SPATIAL(POLYGON) geom) -> SPATIAL(LINESTRING)

[OGC, SQL/MM] ST_NumInteriorRing(SPATIAL(POLYGON) geom) -> INTEGER

[OGC, SQL/MM] ST_InteriorRingN(SPATIAL(POLYGON) geom, INTEGER n) -> SPATIAL(LINESTRING)

Functions on GEOMETRYCOLLECTION and all MULTI... types

[OGC, SQL/MM] ST_NumGeometries(SPATIAL geom) -> INTEGER

[OGC, SQL/MM] ST_GeometryN(SPATIAL geom, INTEGER n) -> SPATIAL

Examples of queries:

-- Find total length of roads in kilometers

SELECT SUM(ST_LENGTH(ROAD)) / 1000 SUM_OF_ROAD_LENGTHS FROM ROADS;

-- Find area of biggest lake in a particular region

SELECT MAX(ST_AREA(LAKE)) FROM LAKES WHERE ST_WITHIN(LAKE, ST_GEOGFROMTEXT('POLYGON ((7.187119 45.883795, 7.414398 45.883795, 7.414398 46.020807, 7.187119 46.020807, 7.187119 45.883795))'));

-- Find all bus stations in Stockholm around some position within a range of 1km.

SELECT ST_ASTEXT(POS) FROM BUS_STATIONS WHERE ST_INTERSECTS(ST_BUFFER(ST_GEOGFROMTEXT('POINT (18 59.3)'), ST_BUFFERSTRATEGY('distance_sym', 1000), ST_BUFFERSTRATEGY('point_circle', 20)), POS) AND CITY = 'STOCKHOLM';

Geographic type

The geographic data type should provide built-in support for spatial objects represented in “geographic” coordinates (or “geodetic” coordinates, or “lat/lon” or “lon/lat”). Geographic coordinates are spherical coordinates expressed in angular units (degrees).

The basis of the geometric data type is the plane. The shortest path between two points in the plane is a straight line. This means that functions for geometry (areas, distances, lengths, intersections, etc.) are computed using straight line vectors. This makes them easier to implement and faster to execute, but it also makes them inaccurate for data on the spheroidal surface of the Earth.

Geographic data type is based on a spherical model. The shortest path between two points on the sphere is the arc of a great circle. Geography functions (areas, distances, lengths, intersections, etc.) are calculated using arcs on the sphere. Given the spheroidal shape of the world, the functions give more accurate results.

Because the underlying math is more complex, fewer functions are defined for the geography type than for the geometry type. As a workaround, it is possible to convert geometry and geography back and forth using ST_Transform(), but this method will give more or less accurate results only at short distances.

Like the geometry data type, geographic data is linked to a spatial reference system through a SRID value.

For all spatial reference systems the units returned by measurement functions (ST_Distance, ST_Length, ST_Area, etc.) are expressed in meters.

Indices

Indices are currently not implemented and are in the design stage. It is still to be determined what type of index should be implemented, classic RTree (e.g.: PostGIS, Oracle) or BTree with Hilbert space-filling curve or with Z-order curve (e.g.: SQL Server, Open Street Map).

System tables

RDB$SPATIAL_FIELDS:

+-----------------------------+-------------------+

| FIELDS NAME | TYPE |

+-----------------------------+-------------------+

| RDB$SPATIAL_FIELD_NAME (FK) | CHAR (63) |

| RDB$SPATIAL_TYPE | SMALLINT NOT NULL |

| RDB$SPATIAL_DATA_TYPE | SMALLINT NOT NULL |

| RDB$COORD_DIMENSION | SMALLINT NOT NULL |

| RDB$SRID (FK) | INTEGER NOT NULL |

+-----------------------------+-------------------+

Description:

This table is used to store spatial type metadata.

RDB$SPATIAL_FIELD_NAME - The field name is the foreign key to the RDB$FIELD_NAME system table.

RDB$SPATIAL_TYPE - The type of spatial object, geometry or geography.

RDB$SPATIAL_DATA_TYPE - The type of geometry, i.e. POINT, LINESTRING, POLYGON, etc.

RDB$COORD_DIMENSION - Dimension of the spatial object.

RDB$SRID - ID of spatial coordinate system, is a foreign key to the system table RDB$SPATIAL_REF_SYS.

[OGC] RDB$SPATIAL_REF_SYS :

+---------------+----------------+

| FIELDS NAME | TYPE |

+---------------+----------------+

| RDB$SRID (PK) | INTEGER |

| RDB$AUTH_NAME | CHAR (256) |

| RDB$AUTH_SRID | INTEGER |

| RDB$SRTEXT | VARCHAR (2048) |

| RDB$PROJ4TEXT | VARCHAR (2048) |

+---------------+----------------+

Description:

This table is a virtual table and stores all SRSs available for use, including predefined and custom SRSs. To store predefined SRSs it was decided to create a separate database srs.fdb, which contains a mirror of SRS$SPATIAL_REF_SYS table with the same fields.

RDB$SRID - Integer code that uniquely identifies the spatial coordinate system (SRS) in the database.

RDB$AUTH_NAME - The name of the standard or standardization agency to which the system refers, e.g. “EPSG”.

RDB$AUTH_SRID - The identifier of the spatial coordinate system defined by the authority specified in RDB$AUTH_NAME, in the case of EPSG this is the EPSG code.

RDB$SRTEXT - WKT representation of the spatial coordinate system.

RDB$PROJ4TEXT - Contains the PROJ coordinate definition string for a particular SRID.

RDB$PROJ4TEXT is taken from PostGIS as another option for specifying SRS, and since the library for working with SRS supports this representation, why not include it too.

RDB$CUSTOM_SPATIAL_REF_SYS :

+---------------+----------------+

| FIELDS NAME | TYPE |

+---------------+----------------+

| RDB$SRID (PK) | INTEGER |

| RDB$AUTH_NAME | CHAR (256) |

| RDB$AUTH_SRID | INTEGER |

| RDB$SRTEXT | VARCHAR (2048) |

| RDB$PROJ4TEXT | VARCHAR (2048) |

+---------------+----------------+

Description:

Structurally repeats RDB$SPATIAL_REF_SYS, except it is at the database level and stores only user SRS.

[OGC] RDB$GEOMETRY_COLUMNS:

+---------------------+-----------+

| FIELDS NAME | TYPE |

+---------------------+-----------+

| RDB$F_TABLE_CATALOG | CHAR (63) |

| RDB$F_TABLE_SCHEMA | CHAR (63) |

| RDB$F_TABLE_NAME | CHAR (63) |

| RDB$GEOMETRY_COLUMN | CHAR (63) |

| RDB$COORD_DIMENSION | SMALLINT |

| RDB$SRID | INTEGER |

+---------------------+-----------+

Description:

This table is only needed for compliance with the OGC standard. It has been implemented as a view, for this purpose the system view mechanism has been implemented.

OGC SFA (or OpenGIS) and SQL/MM standards

OGC SFA (or OpenGIS)

At the moment all functions specified in the standard are implemented, but not all geometry types, specifically Triangle, PolyhedralSurface and TIN have not been implemented.

There are plans to add these spatial types, but timeframes are not defined yet.

SQL/MM

The 2015 standard is a rather large list (1300 pages) of various features (3D, topology, curves) and as far as I know there is no DBMS that fully supports this list, so I would rather be guided by the 2003 standard:

From geometry types we are currently missing curves: CircularString, CompoundCurve, CurvePolygon, MultiCurve, MultiSurface.
There is no support for GML (Geography Markup Language) representation.
From 3D support there is only the possibility to store additional Z and M coordinates, but they do not participate in calculations.

There are plans to add curve types, GML and support for 3D operations, but timeframes are not defined yet.

Technical architecture

Third-party libraries

Boost.Geometry is the main library for working with spatial objects. It is used to perform various operations on spatial objects.

PROJ allows the conversion of WKT and PROJ representation of SRS into integer characteristics, which can be used in geographic calculations. It also allows us to convert a spatial object from one SRS to another.

The annoying thing is that PROJ requires an additional sqlite dependency, so it was also added to the project.

Boost.Geometry is a header-only library, so there is no need to link it. All Boost has its own license Boost Software License, it does not restrict the use of the library, but it requires mentioning that it is used in the project.

PROJ is linked to us statically, we also had to remove the -fno-rtti flag, because PROJ header files use dynamic_cast. PROJ uses the MIT license, it does not restrict the use of the library, but it requires mentioning that it is used in the project.

A way of storing a spatial object

Currently, a spatial object is a BLOB with the new subtype isc_blob_spatial_data. The serialization in EWKB view is used to store the spatial object.

About storing a spatial object in a descriptor (dsc): the main meta-information about the object is stored in dsc_scale (geometry or geography, geometry type and dimension), new USHORT dsc_srid field has been introduced to store the SRID value (dsc size increased by 8 bytes due to alignment). A new USHORT dsc_srid field has also been added to Ods::Descriptor (structure size has not changed).

Spatial functions

At the moment all functions are implemented as system functions.

I considered the possibility of implementing spatial functions in the form of a package, but encountered the limitation that the descriptor of the function result is formed at the pass stage, the descriptor itself is built on the data from the system tables, and the data in the system tables appear at the stage of creating the database in ini.epp. Also, we have no access to the descriptor during the function execution. That is, since the descriptor for most spatial functions is built at runtime based on input arguments, the package architecture does not fit well with spatial functions.

SRS

The table RDB$SPATIAL_REF_SYS contains pre-defined SRSs which are stored in a separate srs.fdb. This was done in order not to inflate the newly created databases, because srs.fdb contains a large amount of data, the size of the database is about 11MB. SRS that are in this database can not be changed because all databases refer to this database, and one change can suddenly affect the work of others.

When working with spatial data we need to get information about SRS, for this purpose RDB$SPATIAL_REF_SYS is used. Through SRID value we get WKT or PROJ representation of the specified SRS, and then convert the obtained representation into numerical characteristics that are used in calculations. This process takes a lot of time, so it was decided to add a cache where the numerical characteristics will be stored after conversion. This eliminates repeated queries to srs.fdb and further transformations, greatly speeding up work with spatial objects.

Custom SRS
Dependencies in the body of procedures and functions are not tracked for SRS, i.e. if a custom SRS is used in the body, for example ST_GeomFromText('POINT(0 0)', 1), and then the user deleted this SRS, we will get an error only in runtime.

Also at the moment anyone can create and delete any custom SRS (this is how PostGIS does it, for example). I would like to know what will need to be added. So far there are plans to add a new privilege to create/delete SRS, about adding the owner of SRS is an open question, whether it is necessary.

System views

During the implementation of the OGC standard, a system view mechanism was added to create RDB$GEOMETRY_COLUMNS as a view. System views are specified in the same way as system tables, but only in a separate file views.h, an example of creating RDB$GEOMETRY_COLUMNS:

/* views.h */

VIEW(nam_geom_columns, view_geom_columns, ODS_14_0)

SOURCE("SELECT NULL AS RDB$F_TABLE_CATALOG, NULL AS RDB$F_TABLE_SCHEMA, REL.RDB$RELATION_NAME AS RDB$F_TABLE_NAME, "

"REL.RDB$FIELD_NAME AS RDB$F_GEOMETRY_COLUMN, (RDB$SPATIAL_DATA_TYPE + RDB$COORD_DIMENSION * 1000) AS RDB$GEOMETRY_TYPE, RDB$COORD_DIMENSION, RDB$SRID "

"FROM RDB$SPATIAL_FIELDS SF "

"JOIN RDB$RELATION_FIELDS REL ON SF.RDB$SPATIAL_FIELD_NAME = REL.RDB$FIELD_SOURCE")

END_VIEW

System views are created in ini.epp like system tables. System views always come after system tables, i.e. if a new system table appears during a minor upgrade, we will need to move all system views so that there is no overlap between system tables and views.

This feature is poorly tested and experimental, as well as it is not yet integrated into the current branch with spatial data.

Reply all

Reply to author

Forward

0 new messages