sqlite-tg Documentation

As a reminder, sqlite-tg is still young, so breaking changes should be expected while sqlite-tg is in a pre-v1 stage.

Supported Formats

tg and sqlite-tg can accept geometries in Well-known Text (WKT), Well-known Binary(WKB), and GeoJSON formats.

sqlite-tg functions will infer which format to use based on the following rules:

1. If a provided argument is a BLOB, then it is assumed the blob is valid WKB.
2. If the provided argument is TEXT and is the return value of a JSON SQL function, or if it starts with "{", then it is assumed the string is valid GeoJSON.
3. If the provided argument is still TEXT, then it is assumed the text is valid WKT.
4. If the provided argument is the return value of a sqlite-tg function that returns a geometry pointer,

Pointer functions

Some functions in sqlite-tg use SQLite's Pointer Passsing Interface to return special objects. This is mainly done for performance benefits in specific queries, to avoid the overhead serializing/de-serializing the same geometric object multiple times.

When using one of these functions it may appear to return NULL. Technically it is not null, but user-facing SQL queries can't directly access the real value. Instead, other sqlite-tg functions can read the underlying data in their own functions. For example:

select tg_point(1, 2); -- appears to be NULL

select tg_to_wkt(tg_point(1, 2)); -- returns 'POINT(1 2)'

tg_point is a pointer function, which appears to return NULL when directly accessing in a query. However, it can be passed into other sqlite-tg functions, such as tg_to_wkt(), which access the underlying geometric object and serializes it to WKT.

Keep in mind, SQLite pointer values don't exist past CTE boundaries.

with step1 as (
  select tg_point(1,1) as point1
),
step2 as (
  select tg_to_wkt(point1) from step1
)
select * from step2;

-- Runtime error: invalid geometry input. Must be WKT (as text), WKB (as blob), or GeoJSON (as text).

The above query returns an error because the "pointer" returned from tg_point() inside step1 doesn't exist outside the step1 CTE boundary. When accessed in step2, the point1 return is NULL, so tg_to_wkt() throws the error.

The solution is to "serialize" the point with tg_to_wkt inside of step1. This ensure that point1 will be a normal SQL TEXT value, and can be queries in other table expressions like normal.

with step1 as (
  select tg_to_wkt(tg_point(1,1)) as point1
),
step2 as (
  select point1 from step1
)
select * from step2;
/*
┌────────────┐
│   point1   │
├────────────┤
│ POINT(1 1) │
└────────────┘
*/

API Reference

All functions offered by sqlite-tg.

Meta Functions

tg_version()

Returns the version string of sqlite-tg.

select tg_version(); -- "v0...."

tg_debug()

Returns fuller debug information of sqlite-tg.

select tg_debug(); -- "Version...Date...Commit..."

Constructors

tg_point(x, y)

A pointer function that returns a point geometry with the given x and y values. This value will appear to be NULL on direct access, and is meant for performance critical SQL queries where you want to avoid serializing/de-serializing.

select
  -- appears to be NULL,
  tg_point(1, 2) as p1,
  -- convert a point to a real value
  tg_to_wkt(tg_point(1, 2)) as p2;

tg_multipoint(p1, p2, ...)

A pointer function that returns a MultiPoint geometry with the given points. This value will appear to be NULL on direct access, so consider wr

Input arguments must be Point geometries, which can be WKT, WKB, or GeoJSON.

select tg_multipoint(tg_point());

tg_group_multipoint()

TODO

select tg_group_multipoint();

Conversions

tg_to_geojson(geometry)

Converts the given geometry into a GeoJSON string. Inputs can be in any supported formats, including WKT, WKB, and GeoJSON. Based on tg_geom_geojson().

select
  tg_to_geojson('POINT(0 1)') as src_wkt,
  tg_to_geojson(X'01010000000000000000000000000000000000f03f') as src_wkb,
  tg_to_geojson('{"type":"Point","coordinates":[0,1]}') as src_geojson,
  tg_to_geojson(tg_point(0, 1)) as src_pointer;

tg_to_wkb(geometry)

Converts the given geometry into a WKB blob. Inputs can be in any supported formats, including WKT, WKB, and GeoJSON. Based on tg_geom_wkb().

select tg_to_wkb('POINT(0 1)');
-- X'01010000000000000000000000000000000000f03f'

select tg_to_wkb(X'01010000000000000000000000000000000000f03f');
-- X'01010000000000000000000000000000000000f03f'

select tg_to_wkb('{"type":"Point","coordinates":[0,1]}');
-- X'01010000000000000000000000000000000000f03f'

select tg_to_wkb(tg_point(0, 1));
-- X'01010000000000000000000000000000000000f03f'

tg_to_wkt(geometry)

Converts the given geometry into a WKT blob. Inputs can be in any supported formats, including WKT, WKB, and GeoJSON. Based on tg_geom_wkt().

select tg_to_wkt('POINT(0 1)');
-- 'POINT(0 1)'

select tg_to_wkt(X'01010000000000000000000000000000000000f03f');
-- 'POINT(0 1)'

select tg_to_wkt('{"type":"Point","coordinates":[0,1]}');
-- 'POINT(0 1)'

select tg_to_wkt(tg_point(0, 1));
-- 'POINT(0 1)'

Misc.

tg_type(geometry)

Returns a string describing the type of the provided geometry. Inputs can be in any supported formats, including WKT, WKB, and GeoJSON. Based on tg_geom_type_string().

Possible values:

• "Point"
• "LineString"
• "Polygon"
• "MultiPoint"
• "MultiLineString"
• "MultiPolygon"
• "GeometryCollection"
• "Unknown"

select tg_type('POINT (30 10)');
-- 'Point'
select tg_type('LINESTRING (30 10, 10 30, 40 40)');
-- 'LineString'
select tg_type('POLYGON ((30 10, 40 40, 20 40, 10 20, 30 10))');
-- 'Polygon'
select tg_type('MULTIPOINT (10 40, 40 30, 20 20, 30 10)');
-- 'MultiPoint'
select tg_type('MULTIPOLYGON (((30 20, 45 40, 10 40, 30 20)),((15 5, 40 10, 10 20, 5 10, 15 5)))');
-- 'MultiPolygon'
select tg_type('GEOMETRYCOLLECTION (POINT (40 10),LINESTRING (10 10, 20 20, 10 40),POLYGON ((40 40, 20 45, 45 30, 40 40)))');
-- 'GeometryCollection'

tg_extra_json(geometry)

If the original geometry is a GeoJSON with extra fields such as id or property, those extra fields will be returned in a JSON object.

select
  tg_extra_json('{
    "type": "Point",
    "coordinates": [-118.2097812,34.0437074]
  }') as no_extra,
  tg_extra_json('{
    "id": "ASG0017",
    "type": "Point",
    "coordinates": [-118.2097812,34.0437074],
    "properties": {"color": "red"}
  }') as some_extra;

Operations

tg_intersects(a, b)

Returns 1 if the a geometry intersects the b geometry, otherwise returns 0. Will raise an error if either a or b are not valid geometries. Based on tg_geom_intersects().

The a and b geometries can be in any supported format, including WKT, WKB, and GeoJSON.

select
  tg_intersects(
    'LINESTRING (0 0, 2 2)',
    'LINESTRING (1 0, 1 2)'
  ) as result1,
  tg_intersects(
    'LINESTRING (0 0, 0 2)',
    'LINESTRING (2 0, 2 2)'
  ) as result2;

Consider this rough bounding box for San Francisco:

POLYGON((
  -122.51610563264538 37.81424532146113,
  -122.51610563264538 37.69618409220847,
  -122.35290547288255 37.69618409220847,
  -122.35290547288255 37.81424532146113,
  -122.51610563264538 37.81424532146113
))

The following SQL query, for a point within the city, returns 1:

select tg_intersects(
  '
    POLYGON((
      -122.51610563264538 37.81424532146113,
      -122.51610563264538 37.69618409220847,
      -122.35290547288255 37.69618409220847,
      -122.35290547288255 37.81424532146113,
      -122.51610563264538 37.81424532146113
    ))
  ',
  'POINT(-122.4075 37.787994)'
) as result;

With a point outside the city it returns 0:

select tg_intersects(
  '
    POLYGON((
      -122.51610563264538 37.81424532146113,
      -122.51610563264538 37.69618409220847,
      -122.35290547288255 37.69618409220847,
      -122.35290547288255 37.81424532146113,
      -122.51610563264538 37.81424532146113
    ))
  ',
  'POINT(-73.985130 40.758896)'
) as result;

tg_contains()

TODO

select tg_contains();

tg_coveredby()

TODO

select tg_coveredby();

tg_covers()

TODO

select tg_covers();

tg_disjoint()

TODO

select tg_disjoint();

tg_touches()

TODO

select tg_touches();

tg_within()

TODO

select tg_within();

tg0()

select tg_XXX();

tg_geom()

TODO

select tg_geom();

tg_valid_geojson()

TODO

select tg_valid_geojson();

tg_valid_wkb()

TODO

select tg_valid_wkb();

tg_valid_wkt()

TODO

select tg_valid_wkt();

tg_geometries_each()

TODO

select tg_geometries_each();

tg_lines_each()

TODO

select tg_lines_each();

tg_points_each()

TODO

select tg_points_each();

tg_polygons_each()

TODO

select tg_polygons_each();

tg_bbox()

TODO

select tg_bbox();