APSG tutorial - Part 7

The apsg.database module provides an SQLAlchemy interface to the PySDB database format, which stores structural geology field data in a sqlite3 file.

Setting up a database

We start by creating a new database. The create=True argument removes any existing file and creates a fresh database with default units and structure types.

from apsg.database import SDBSession

db = SDBSession('demo.sdb', create=True)
print(db.info())

PySDB database version: 3.1.0
PySDB database CRS: EPSG:4326
PySDB database created: 04.06.2026 13:59
PySDB database updated: 04.06.2026 13:59
Number of sites: 0
Number of units: 1
Number of structures: 2
Number of measurements: 0

Units and Sites

A Unit is a geological unit (formation, intrusion, etc.). A Site is a field station linked to a unit. Both are created with the get-or-create pattern: call the method with the name and metadata to create, or with just the name to retrieve.

unit = db.unit('DMU', description='Deamonic Magmatic Unit')
unit

Unit:DMU

site1 = db.site('LX001', unit=unit, x_coord=25934.36, y_coord=564122.5,
                description='diorite dyke')
site2 = db.site('LX002', unit=unit, x_coord=26100.0, y_coord=564200.0,
                description='granite outcrop')
print(site1)
print(site2)

Site:LX001 (DMU)
Site:LX002 (DMU)

Structure Types

Structure types define the kind of measurement (foliation, lineation, fracture, etc.). The planar argument distinguishes planar (1) from linear (0) features.

S2 = db.structype('S2', description='Solid-state foliation', planar=1)
L2 = db.structype('L2', description='Solid-state lineation', planar=0)
S3 = db.structype('S3', description='Crenulation cleavage', planar=1)
print(S2)
print(L2)

Type:S2
Type:L2

Adding measurements

You can add raw measurements by providing azimuth (dip direction / plunge direction) and inclination (dip / plunge) values:

fol1 = db.add_structdata(site1, S2, 150, 36)
fol2 = db.add_structdata(site1, S2, 160, 42)
fol3 = db.add_structdata(site2, S2, 320, 65)
lin1 = db.add_structdata(site2, L2, 83, 16)
lin2 = db.add_structdata(site2, L2, 310, 45)
print(fol1)
print(lin1)

S2:150/36
L2:83/16

Or you can insert Foliation, Lineation and Pair objects directly:

from apsg.feature import Foliation, Lineation, Pair

f = Foliation(200, 30)
l = Lineation(150, 25)
sdata = db.add_fol(site2, S3, f)
ldata = db.add_lin(site2, L2, l)
print(sdata)
print(ldata)

S3:200/30
L2:150/25

Attaching planar to linear features

In structural geology, lineations are often measured on a specific foliation plane. The attach method links them and the add_pair method does it in one step:

# Attach existing measurements
pair = db.attach(fol3, lin1)
pair

S2:320/65 - L2:83/16

# Create and attach a new pair in one step
p = Pair(258, 42, 220, 30)  # (dip direction, dip, trend, plunge)
ap = db.add_pair(site1, S2, L2, p)
ap

S2:260.033/39.9562 - L2:218.808/32.2163

Tags

Tags allow filtering and grouping measurements:

tag = db.tag('plot', description='to be plotted')
fol_tagged = db.add_structdata(site1, S2, 190, 55, tags=[tag])
db.commit()
db.tags()

[Tag:plot]

Retrieving data as APSG feature sets

The getset method returns a FoliationSet or LineationSet, filtered by site, unit, or tag:

g = db.getset('S2')
print(type(g).__name__)
print(g)

FoliationSet
S(5) S2

# Filter by site
g_lx001 = db.getset('S2', site=dict(name='LX001'))
print(f'S2 at LX001: {g_lx001}')

# Filter by tag
g_plot = db.getset('S2', tag=dict(name='plot'))
print(f'S2 tagged "plot": {g_plot}')

S2 at LX001: S(4) S2
S2 tagged "plot": S(1) S2

Retrieving pairs and faults

The getpairs method retrieves attached planar+linear measurements as a PairSet:

pairs = db.getpairs('S2', 'L2')
print(type(pairs).__name__)
pairs

Too big misfit for pair S2:320/65-L2:83/16 on Site:LX002 (DMU)
PairSet

P(1) S2-L2

If sense of movement information is available, use getfaults to get a FaultSet:

from apsg.feature import Fault

# Add fault-type data
fa = Fault(280, 60, 210, 35, 1.0)  # (dd, dip, trend, plunge, sense)
db.add_pair(site2, S2, L2, fa)
db.commit()

# Retrieve as FaultSet
faults = db.getfaults('S2', 'L2', 1.0)
print(type(faults).__name__)
faults

Too big misfit for pair S2:320/65-L2:83/16 on Site:LX002 (DMU)
FaultSet

F(2) S2-L2

Retrieving data as pandas DataFrame

The df method returns a pandas.DataFrame with optional APSG feature columns and metadata.

from apsg.pandas import pd

df = db.df('S2', apsg=True, store=['site', 'unit', 'x_coord', 'y_coord'])
df

	site	x_coord	y_coord	unit	S2	tags
0	LX001	25934.36	25934.36	DMU	S:150/36
1	LX001	25934.36	25934.36	DMU	S:160/42
2	LX002	26100.00	26100.00	DMU	S:320/65
3	LX001	25934.36	25934.36	DMU	S:260/40
4	LX001	25934.36	25934.36	DMU	S:190/55	plot
5	LX002	26100.00	26100.00	DMU	S:279/61

Listing database contents

print('Units:', db.units())
print('Sites:', db.sites())
print('Structure types:', db.structypes())
print('Tags:', db.tags())

Units: [Unit:Default, Unit:DMU]
Sites: [Site:LX001 (DMU), Site:LX002 (DMU)]
Structure types: [Type:S, Type:L, Type:S2, Type:L2, Type:S3]
Tags: [Tag:plot]

Clean up

Always close the database session when done:

db.close()
import os
os.remove('demo.sdb')

Summary

• Use SDBSession('file.sdb', create=True) to create a new database

• unit(), site(), structype(), tag() use a get-or-create pattern

• add_structdata() adds raw azimuth/inclination measurements

• add_fol() / add_lin() / add_pair() accept APSG feature objects

• getset() returns FoliationSet or LineationSet with optional site/unit/tag filters

• getpairs() / getfaults() retrieve attached measurements as PairSet / FaultSet

• df() exports data to a pandas.DataFrame, optionally with APSG feature columns