laterite_ags4

Downloads

843

GitHub stars

Extension repository on GitHub

Read AGS4 geotechnical data files as typed, UUID-keyed tables directly from SQL — born-typed columns, deterministic content-addressed keys that join across groups by construction, and an embedded AGS dictionary. A read-only SQL surface. Local, http(s):// and s3:// (with httpfs).

Maintainer(s): niko86

Installing and Loading

INSTALL laterite_ags4 FROM community;
LOAD laterite_ags4;

Example

-- Read a group as a typed table (columns typed from the file's own TYPE row):
SELECT loca_id, loca_gl
FROM read_ags('site.ags', 'LOCA')
WHERE loca_gl > 50.0;

-- Join across groups on the deterministic keys — no shared state, joins by
-- construction (every SAMP row's _parent_id equals its LOCA's _id):
SELECT l.loca_id, s.samp_ref, s.samp_top
FROM read_ags('site.ags', 'SAMP') s
JOIN read_ags('site.ags', 'LOCA') l ON s._parent_id = l._id;

-- Inspect structure + the embedded AGS dictionary:
SELECT "group", n_rows, parent FROM ags_groups('site.ags') ORDER BY n_rows DESC;
SELECT child, parent, shared_keys FROM ags_relationships() WHERE parent = 'LOCA';

-- Remote, lazily (with httpfs):
-- LOAD httpfs;
-- SELECT loca_id FROM read_ags('s3://bucket/site.ags', 'LOCA');

About laterite_ags4

laterite_ags4 reads AGS4 geotechnical & geoenvironmental data files as first-class DuckDB tables — no conversion step, no bundled engine.

Written in 🦀 Rust on DuckDB’s C Extension API (zero C++).

What it gives you

Born-typed columns — each heading is typed from the file’s own TYPE row (2DP → DOUBLE, ID → VARCHAR, 0DP → BIGINT, YN → BOOLEAN, …).
Deterministic content-addressed keys — every row carries _id and _parent_id (UUIDv8 of the row’s spec key-chain). child._parent_id == parent._id by construction, so groups join across independent read_ags(...) calls with no shared state.
Self-describing metadata — ags_groups, ags_headings, ags_dictionary, ags_relationships expose the file’s structure and the embedded AGS dictionary.
Persistence — load_ags(path) emits CREATE-TABLE DDL for an indexed, repeat-/remote-query store.
Local or remote — reads go through DuckDB’s virtual filesystem, so local paths, http(s):// and s3:// (with LOAD httpfs) all work on one code path.
Native + wasm — built for every community-extensions platform, DuckDB-WASM included: the same reader for native and browser DuckDB.

Functions

function	returns
`read_ags(path, group)`	one group as a typed table — `_id`, `_parent_id`, then one column per heading (typed from the file’s `TYPE` row). Reads local / `http(s)://` / `s3://`.
`read_ags_text(content, group)`	the same typed table, from an inline AGS4 string (no filesystem).
`ags_groups(path)`	`(group, n_rows, n_headings, parent)` — the file’s group list.
`ags_headings(path)`	`(group, heading, unit, ags_type, sql_type, status, is_key, ordinal)` — the per-heading schema, enriched with the dictionary’s KEY status.
`ags_dictionary()`	the embedded standard AGS dictionary as a table (`group`, `heading`, `status`, `ags_type`, `unit`, `description`, …).
`ags_relationships()`	`(child, parent, shared_keys)` — the spec parent→child graph that `_parent_id` follows.
`ags_rules()`	the AGS4 numbered-rule catalogue (`rule`, `title`, `severity`, `fixable`) — the extension lists the rules; the CLI/library run them.
`load_ags(path)`	`(seq, stmt)` — CREATE-TABLE DDL to materialise every group into an indexed, repeat-/remote-queryable store.

Readers stream lazily (≈2048-row vector chunks); a non-conforming numeric cell becomes NULL, never an error (the born-typed behaviour). Optional arguments are named (edition := '4.2', encoding := 'windows-1252'), the rest positional. The path verbs take an encoding named param (e.g. encoding := 'windows-1252') for non-UTF-8 sources; the _text variant is UTF-8 (its input is already a VARCHAR). This is a read-only SQL surface — validation, certification and repair stay in the lat CLI / the laterite library.

Added Functions

function_name	function_type	description	comment	examples
read_ags	table	Read one AGS4 group as a typed table — born-typed columns plus content-addressed _id/_parent_id keys	Local / http(s):// / s3:// (with LOAD httpfs); consumes a sibling .ags.idx for a fast single-group slice	[SELECT loca_id, loca_gl FROM read_ags(‘site.ags’, ‘LOCA’);]
read_ags_text	table	Read one AGS4 group as a typed table from an inline AGS4 string	No filesystem — the input is a VARCHAR (already UTF-8)	[SELECT * FROM read_ags_text(ags_string, ‘LOCA’);]
ags_groups	table	List the groups in an AGS4 file with row and heading counts	Returns (group, n_rows, n_headings, parent)	[SELECT * FROM ags_groups(‘site.ags’);]
ags_headings	table	The per-heading schema of an AGS4 file, enriched with dictionary KEY status	Returns (group, heading, unit, ags_type, sql_type, status, is_key, ordinal)	[SELECT * FROM ags_headings(‘site.ags’) LIMIT 5;]
ags_dictionary	table	The embedded standard AGS dictionary as a table	Returns (group, heading, status, ags_type, unit, description)	[SELECT * FROM ags_dictionary() LIMIT 5;]
ags_relationships	table	The AGS group parent-child (KEY) graph that _parent_id follows	Returns (child, parent, shared_keys)	[SELECT * FROM ags_relationships();]
ags_rules	table	The AGS4 numbered-rule catalogue	Returns (rule, title, severity, fixable) — the extension lists the rules; the CLI and libraries run them	[SELECT * FROM ags_rules() WHERE fixable;]
load_ags	table	Emit CREATE-TABLE DDL to materialise every AGS4 group into an indexed, queryable store	Returns (seq, stmt) — run the statements to get keyed tables	[SELECT stmt FROM load_ags(‘site.ags’) ORDER BY seq;]
to_duckdb	table	Emit ATTACH/CREATE-TABLE/DETACH DDL to persist every AGS4 group as a standalone .duckdb file	Returns (seq, stmt); the file matches the libraries’ to_duckdb() output — keyed by _id/_parent_id	[SELECT stmt FROM to_duckdb(‘site.ags’, ‘site.duckdb’) ORDER BY seq;]

Overloaded Functions

This extension does not add any function overloads.

Added Types

This extension does not add any types.

Added Settings

This extension does not add any settings.