0
# Database Administration
1

2
Administrative functions and tools for managing spatial databases, including automatic DDL event handling, spatial index creation, and database-specific initialization procedures.
3

4
## Capabilities
5

6
### DDL Event Management
7

8
Automatic management of spatial database schema events with support for spatial indexes and metadata.
9

10
```python { .api }
11
def setup_ddl_event_listeners():
12
    """
13
    Setup DDL event listeners to automatically process spatial columns.
14
    
15
    Automatically called when importing geoalchemy2. Registers event handlers for:
16
    - before_create: Handle spatial indexes before table creation
17
    - after_create: Restore column lists and create spatial metadata  
18
    - before_drop: Remove spatial metadata before table drop
19
    - after_drop: Clean up spatial references after table drop
20
    - column_reflect: Handle spatial column reflection during introspection
21
    - after_parent_attach: Automatically add spatial indexes to columns
22
    """
23
```
24

25
The DDL event system automatically handles:
26

27
- **Spatial Index Creation**: Automatically creates spatial indexes for geometry/geography columns
28
- **Metadata Management**: Manages spatial_ref_sys and geometry_columns tables
29
- **Column Reflection**: Properly reflects spatial column types during database introspection
30
- **Cross-Database Compatibility**: Handles dialect-specific spatial schema requirements
31

32
### Database Engine Configuration
33

34
Spatial database engine initialization and configuration management.
35

36
```python { .api }
37
class GeoEngine:
38
    """
39
    SQLAlchemy plugin for spatial database engine initialization.
40
    
41
    Automatically registered as a SQLAlchemy plugin via entry points.
42
    Provides database-specific spatial initialization when engines are created.
43
    """
44
```
45

46
Usage examples:
47

48
```python
49
from sqlalchemy import create_engine
50
from geoalchemy2.admin.plugin import GeoEngine
51

52
# Engine automatically configured for spatial operations
53
engine = create_engine(
54
    'postgresql://user:pass@localhost/spatial_db',
55
    plugins=[GeoEngine()]  # Explicitly add if needed
56
)
57

58
# Or use standard engine creation (plugin auto-registered)
59
engine = create_engine('postgresql://user:pass@localhost/spatial_db')
60
```
61

62
### SpatiaLite Initialization
63

64
Initialize SpatiaLite extension for SQLite databases with spatial capabilities.
65

66
```python { .api }
67
def load_spatialite(dbapi_conn, *args):
68
    """
69
    Load SpatiaLite extension for SQLite database connections.
70
    
71
    Parameters:
72
    - dbapi_conn: SQLite database connection
73
    - *args: Additional arguments (unused)
74
    
75
    Automatically loads the SpatiaLite extension library and initializes
76
    spatial metadata tables. Use as a SQLAlchemy engine event listener.
77
    """
78
```
79

80
Usage examples:
81

82
```python
83
from sqlalchemy import create_engine, event
84
from geoalchemy2.admin.dialects.sqlite import load_spatialite
85

86
# Method 1: Using engine events
87
engine = create_engine('sqlite:///spatial.db')
88
event.listen(engine, 'connect', load_spatialite)
89

90
# Method 2: Direct import triggers automatic setup
91
from geoalchemy2 import load_spatialite
92
engine = create_engine('sqlite:///spatial.db')
93
# load_spatialite automatically registered as connect event
94

95
# Create tables with spatial columns
96
Base.metadata.create_all(engine)
97
```
98

99
### GeoPackage Initialization
100

101
Initialize GeoPackage databases with OGC standard compliance and spatial capabilities.
102

103
```python { .api }
104
def load_spatialite_gpkg(dbapi_conn, *args, **kwargs):
105
    """
106
    Load SpatiaLite extension for GeoPackage database connections.
107
    
108
    Parameters:
109
    - dbapi_conn: SQLite database connection for GeoPackage
110
    - *args: Additional arguments
111
    - **kwargs: Keyword arguments including connection parameters
112
    
113
    Initializes GeoPackage-specific spatial capabilities including:
114
    - OGC GeoPackage standard compliance
115
    - Spatial reference system metadata
116
    - Built-in R-tree spatial indexing
117
    """
118
```
119

120
Usage examples:
121

122
```python
123
from sqlalchemy import create_engine, event  
124
from geoalchemy2.admin.dialects.geopackage import load_spatialite_gpkg
125

126
# Initialize GeoPackage database
127
engine = create_engine('sqlite+pysqlite:///data.gpkg')
128
event.listen(engine, 'connect', load_spatialite_gpkg)
129

130
# Create spatial tables following GeoPackage standard
131
Base.metadata.create_all(engine)
132
```
133

134
### Dialect Selection
135

136
Select appropriate database dialect implementations for spatial operations.
137

138
```python { .api }
139
def select_dialect(dialect_name: str):
140
    """
141
    Select database dialect implementation for spatial operations.
142
    
143
    Parameters:
144
    - dialect_name: str, database dialect name
145
        ("postgresql", "mysql", "mariadb", "sqlite", "geopackage")
146
    
147
    Returns:
148
    Dialect-specific implementation module with functions:
149
    - before_create, after_create, before_drop, after_drop
150
    - reflect_geometry_column
151
    - bind_processor_process
152
    """
153
```
154

155
Supported dialects:
156

157
- **postgresql**: PostGIS-specific implementations
158
- **mysql**: MySQL spatial extension support  
159
- **mariadb**: MariaDB spatial optimizations
160
- **sqlite**: SpatiaLite extension integration
161
- **geopackage**: OGC GeoPackage standard support
162

163
### Spatial Index Management
164

165
Automatic and manual spatial index creation and management.
166

167
```python { .api }
168
# Automatic index creation via column attachment events
169
def after_parent_attach(column, table):
170
    """
171
    Automatically add spatial indexes when spatial columns are attached to tables.
172
    
173
    Creates appropriate spatial indexes based on:
174
    - Column type (Geometry, Geography, Raster)
175
    - Database dialect capabilities
176
    - Index configuration options (N-D indexes, etc.)
177
    """
178
```
179

180
Manual index management:
181

182
```python
183
from sqlalchemy import Index
184
from geoalchemy2.admin.dialects.common import _spatial_idx_name
185

186
# Create spatial index manually
187
spatial_index = Index(
188
    _spatial_idx_name('table_name', 'geom_column'),
189
    MyTable.geom,
190
    postgresql_using='gist'
191
)
192

193
# For N-D indexes (PostgreSQL)
194
nd_index = Index(
195
    _spatial_idx_name('table_name', 'geom_column') + '_nd',
196
    MyTable.geom,
197
    postgresql_using='gist',
198
    postgresql_ops={'geom': 'gist_geometry_ops_nd'}
199
)
200
```
201

202
## Database-Specific Administration
203

204
### PostgreSQL/PostGIS Administration
205

206
```python
207
# PostGIS-specific initialization
208
from geoalchemy2.admin.dialects import postgresql
209

210
# Automatic handling of:
211
# - PostGIS extension loading
212
# - spatial_ref_sys table management  
213
# - geometry_columns metadata
214
# - Advanced indexing options (GiST, SP-GiST, BRIN)
215
```
216

217
### MySQL/MariaDB Administration
218

219
```python
220
# MySQL spatial administration
221
from geoalchemy2.admin.dialects import mysql, mariadb
222

223
# Handles:
224
# - Geometry type validation
225
# - SRID constraint enforcement
226
# - Spatial index creation with R-tree
227
# - NOT NULL constraints for indexed columns
228
```
229

230
### SQLite/SpatiaLite Administration
231

232
```python
233
# SpatiaLite administration
234
from geoalchemy2.admin.dialects import sqlite
235

236
# Manages:
237
# - SpatiaLite extension loading
238
# - Spatial metadata initialization
239
# - Virtual spatial index tables
240
# - Geometry column registration
241
```
242

243
### GeoPackage Administration
244

245
```python
246
# GeoPackage administration  
247
from geoalchemy2.admin.dialects import geopackage
248

249
# Provides:
250
# - OGC GeoPackage standard compliance
251
# - Built-in spatial reference systems
252
# - R-tree spatial indexing
253
# - Geometry type enforcement
254
```
255

256
## Administrative Workflows
257

258
### New Spatial Database Setup
259

260
Complete setup workflow for new spatial databases:
261

262
```python
263
from sqlalchemy import create_engine, MetaData
264
from geoalchemy2 import Geometry, load_spatialite
265
from geoalchemy2.admin import setup_ddl_event_listeners
266

267
# 1. Create engine with appropriate dialect
268
engine = create_engine('postgresql://user:pass@localhost/new_spatial_db')
269
# or for SQLite
270
engine = create_engine('sqlite:///new_spatial.db')
271

272
# 2. DDL events automatically set up via import
273
# setup_ddl_event_listeners() called automatically
274

275
# 3. Define spatial models
276
class SpatialTable(Base):
277
    __tablename__ = 'spatial_features'
278
    
279
    id = Column(Integer, primary_key=True)
280
    name = Column(String)
281
    geom = Column(Geometry('POLYGON', srid=4326))  # Index auto-created
282

283
# 4. Create all tables and indexes
284
Base.metadata.create_all(engine)
285
```
286

287
### Database Migration Support
288

289
Support for evolving spatial schemas:
290

291
```python
292
# Spatial columns automatically handled during reflection
293
from sqlalchemy import MetaData
294

295
metadata = MetaData(bind=engine)
296
metadata.reflect()
297

298
# Spatial columns properly reflected with types and indexes
299
spatial_table = metadata.tables['spatial_features']
300
geom_column = spatial_table.c.geom  # Properly typed as Geometry
301

302
# Existing spatial indexes automatically discovered
303
spatial_indexes = [idx for idx in spatial_table.indexes 
304
                  if 'gist' in str(idx) or 'spatial' in idx.name]
305
```
306

307
### Cross-Database Compatibility
308

309
Ensure spatial operations work across different database backends:
310

311
```python
312
from geoalchemy2.admin import select_dialect
313

314
# Automatically handles dialect differences
315
def create_spatial_table(engine):
316
    dialect = select_dialect(engine.dialect.name)
317
    
318
    # Dialect-specific setup handled automatically
319
    Base.metadata.create_all(engine)
320
    
321
    # Spatial indexes created appropriately for each database
322
    return True
323

324
# Works with any supported database
325
postgres_engine = create_engine('postgresql://...')
326
sqlite_engine = create_engine('sqlite://...')
327
mysql_engine = create_engine('mysql://...')
328

329
for engine in [postgres_engine, sqlite_engine, mysql_engine]:
330
    create_spatial_table(engine)  # Automatically handles differences
331
```
332

333
## Error Handling and Diagnostics
334

335
Administrative functions provide comprehensive error handling:
336

337
```python
338
from geoalchemy2.exc import ArgumentError
339

340
try:
341
    # Spatial column with conflicting options
342
    column = Column(Geometry(use_N_D_index=True, spatial_index=False))
343
except ArgumentError as e:
344
    # "spatial_index must be True when use_N_D_index is True"
345
    print(f"Configuration error: {e}")
346

347
# Diagnostic information available
348
from geoalchemy2.admin.dialects.common import _check_spatial_type
349

350
spatial_column = MyTable.geom
351
is_geometry = _check_spatial_type(spatial_column.type, Geometry)
352
is_raster = _check_spatial_type(spatial_column.type, Raster)
353
```
354

355
## Performance Optimization
356

357
Administrative functions optimize spatial database performance:
358

359
### Automatic Index Selection
360
- **PostGIS**: Uses GiST indexes for most cases, SP-GiST for points
361
- **MySQL/MariaDB**: Uses R-tree indexes for geometry columns  
362
- **SQLite**: Creates virtual spatial index tables via SpatiaLite
363
- **GeoPackage**: Uses built-in R-tree spatial indexing
364

365
### Memory Management
366
- Efficient spatial metadata caching
367
- Lazy loading of dialect-specific implementations
368
- Minimal overhead for non-spatial operations
369

370
### Query Optimization
371
- Spatial index hints for query planner
372
- Dialect-specific query optimizations
373
- Automatic spatial operator mapping