0
# Migration Support
1
2
Comprehensive Alembic integration for managing spatial database schema migrations, including specialized operations for spatial columns, tables, and indexes with support for all major spatial database backends.
3
4
## Installation and Setup
5
6
Migration support requires Alembic to be installed:
7
8
```bash
9
pip install alembic
10
```
11
12
Configure Alembic environment for spatial migrations:
13
14
```python
15
# alembic/env.py
16
from geoalchemy2.alembic_helpers import writer
17
from geoalchemy2 import Geometry, Geography, Raster
18
19
# Register GeoAlchemy2 types for autogeneration
20
target_metadata = Base.metadata
21
22
# Configure rewriter for spatial operations
23
context.configure(
24
# ... other configuration
25
render_item=writer.render_item,
26
compare_type=True,
27
compare_server_default=True
28
)
29
```
30
31
## Capabilities
32
33
### Spatial Column Operations
34
35
Specialized operations for adding and removing spatial columns with proper metadata and index management.
36
37
```python { .api }
38
class AddGeospatialColumnOp:
39
"""
40
Operation for adding spatial columns to existing tables.
41
42
Handles:
43
- Spatial column creation with proper constraints
44
- Automatic spatial index generation
45
- Spatial metadata registration
46
- Database-specific implementation details
47
"""
48
49
class DropGeospatialColumnOp:
50
"""
51
Operation for removing spatial columns from tables.
52
53
Handles:
54
- Spatial index removal
55
- Spatial metadata cleanup
56
- Constraint removal
57
- Cross-database compatibility
58
"""
59
```
60
61
Usage examples:
62
63
```python
64
# In Alembic migration file
65
from alembic import op
66
from geoalchemy2 import Geometry
67
from geoalchemy2.alembic_helpers import AddGeospatialColumnOp, DropGeospatialColumnOp
68
69
def upgrade():
70
# Add spatial column
71
op.invoke(AddGeospatialColumnOp(
72
table_name='buildings',
73
column=Column('geom', Geometry('POLYGON', srid=4326))
74
))
75
76
# Standard Alembic syntax also works
77
op.add_column('buildings',
78
Column('location', Geometry('POINT', srid=4326))
79
)
80
81
def downgrade():
82
# Remove spatial column
83
op.invoke(DropGeospatialColumnOp(
84
table_name='buildings',
85
column_name='geom'
86
))
87
88
op.drop_column('buildings', 'location')
89
```
90
91
### Spatial Table Operations
92
93
Operations for creating and dropping entire spatial tables with proper setup.
94
95
```python { .api }
96
class CreateGeospatialTableOp:
97
"""
98
Operation for creating tables with spatial columns.
99
100
Handles:
101
- Table creation with spatial column definitions
102
- Automatic spatial index creation
103
- Spatial metadata initialization
104
- Database-specific optimizations
105
"""
106
107
class DropGeospatialTableOp:
108
"""
109
Operation for dropping tables containing spatial columns.
110
111
Handles:
112
- Spatial metadata cleanup
113
- Spatial index removal
114
- Proper table dropping order
115
- Cross-database cleanup
116
"""
117
```
118
119
Usage examples:
120
121
```python
122
def upgrade():
123
# Create spatial table
124
op.invoke(CreateGeospatialTableOp(
125
table_name='parcels',
126
columns=[
127
Column('id', Integer, primary_key=True),
128
Column('parcel_id', String(50)),
129
Column('geom', Geometry('POLYGON', srid=4326)),
130
Column('centroid', Geometry('POINT', srid=4326))
131
]
132
))
133
134
def downgrade():
135
# Drop spatial table
136
op.invoke(DropGeospatialTableOp(table_name='parcels'))
137
```
138
139
### Spatial Index Operations
140
141
Dedicated operations for managing spatial indexes independently of columns and tables.
142
143
```python { .api }
144
class CreateGeospatialIndexOp:
145
"""
146
Operation for creating spatial indexes.
147
148
Handles:
149
- Database-specific spatial index creation
150
- Index naming conventions
151
- Advanced index options (N-D, partial indexes)
152
- Performance optimization settings
153
"""
154
155
class DropGeospatialIndexOp:
156
"""
157
Operation for dropping spatial indexes.
158
159
Handles:
160
- Safe index removal
161
- Database-specific cleanup
162
- Index dependency management
163
"""
164
```
165
166
Usage examples:
167
168
```python
169
def upgrade():
170
# Create spatial index
171
op.invoke(CreateGeospatialIndexOp(
172
index_name='idx_buildings_geom_spatial',
173
table_name='buildings',
174
column_name='geom',
175
index_type='gist', # PostgreSQL
176
postgresql_ops={'geom': 'gist_geometry_ops_nd'} # N-D index
177
))
178
179
def downgrade():
180
# Drop spatial index
181
op.invoke(DropGeospatialIndexOp(
182
index_name='idx_buildings_geom_spatial'
183
))
184
```
185
186
### Database Implementation Support
187
188
Specialized implementations for different database backends with appropriate spatial handling.
189
190
```python { .api }
191
class GeoPackageImpl:
192
"""
193
Alembic implementation for GeoPackage databases.
194
195
Extends SQLiteImpl with GeoPackage-specific handling:
196
- OGC GeoPackage standard compliance
197
- Spatial reference system management
198
- Built-in R-tree spatial indexing
199
"""
200
```
201
202
## Autogeneration Support
203
204
Automatic detection and generation of spatial schema changes:
205
206
### Type Comparison
207
208
```python
209
# alembic/env.py configuration for spatial type detection
210
def compare_type(context, inspected_column, metadata_column,
211
inspected_type, metadata_type):
212
# Handle spatial type changes
213
if isinstance(metadata_type, (Geometry, Geography, Raster)):
214
return not spatial_types_equal(inspected_type, metadata_type)
215
return None
216
217
def spatial_types_equal(inspected, metadata):
218
"""Compare spatial types for schema changes."""
219
if type(inspected) != type(metadata):
220
return False
221
222
# Compare geometry_type, srid, dimension
223
return (
224
getattr(inspected, 'geometry_type', None) ==
225
getattr(metadata, 'geometry_type', None) and
226
getattr(inspected, 'srid', -1) ==
227
getattr(metadata, 'srid', -1)
228
)
229
```
230
231
### Automatic Migration Generation
232
233
```python
234
# Generate migration with spatial changes
235
$ alembic revision --autogenerate -m "Add spatial columns"
236
237
# Generated migration includes spatial operations
238
def upgrade():
239
# ### commands auto generated by Alembic ###
240
op.add_column('locations',
241
sa.Column('geom', geoalchemy2.types.Geometry(
242
geometry_type='POINT', srid=4326), nullable=True))
243
op.create_geospatial_index('idx_locations_geom_spatial',
244
'locations', ['geom'])
245
# ### end Alembic commands ###
246
```
247
248
## Migration Workflows
249
250
### Adding Spatial Capabilities to Existing Database
251
252
Migrate non-spatial database to support spatial operations:
253
254
```python
255
# Migration: Add spatial extension
256
def upgrade():
257
# PostgreSQL: Enable PostGIS
258
op.execute('CREATE EXTENSION IF NOT EXISTS postgis')
259
260
# Add spatial columns to existing tables
261
op.add_column('addresses',
262
Column('location', Geometry('POINT', srid=4326))
263
)
264
265
# Create spatial indexes
266
op.create_index('idx_addresses_location_spatial',
267
'addresses', ['location'], postgresql_using='gist')
268
269
def downgrade():
270
op.drop_index('idx_addresses_location_spatial')
271
op.drop_column('addresses', 'location')
272
# Note: PostGIS extension cleanup may require manual intervention
273
```
274
275
### Changing Spatial Reference Systems
276
277
Migrate spatial data between coordinate systems:
278
279
```python
280
def upgrade():
281
# Add new column with different SRID
282
op.add_column('parcels',
283
Column('geom_utm', Geometry('POLYGON', srid=3857))
284
)
285
286
# Transform existing data
287
op.execute('''
288
UPDATE parcels
289
SET geom_utm = ST_Transform(geom, 3857)
290
WHERE geom IS NOT NULL
291
''')
292
293
# Drop old column (in separate migration for safety)
294
295
def downgrade():
296
op.drop_column('parcels', 'geom_utm')
297
```
298
299
### Cross-Database Migration
300
301
Migrate spatial data between different database backends:
302
303
```python
304
# From PostgreSQL to SQLite with SpatiaLite
305
def upgrade():
306
# Initialize SpatiaLite (handled automatically)
307
pass
308
309
def downgrade():
310
pass
311
312
# Data migration script (separate from schema migration)
313
def migrate_spatial_data():
314
# Source: PostgreSQL
315
source_engine = create_engine('postgresql://...')
316
317
# Target: SQLite with SpatiaLite
318
target_engine = create_engine('sqlite:///spatial.db')
319
320
# Read from source
321
source_data = source_engine.execute(
322
'SELECT id, name, ST_AsEWKT(geom) as geom_wkt FROM spatial_table'
323
).fetchall()
324
325
# Write to target
326
for row in source_data:
327
target_engine.execute(
328
'INSERT INTO spatial_table (id, name, geom) VALUES (?, ?, GeomFromEWKT(?))',
329
(row.id, row.name, row.geom_wkt)
330
)
331
```
332
333
### Schema Evolution Patterns
334
335
Common patterns for evolving spatial schemas:
336
337
```python
338
# Pattern 1: Add spatial constraint
339
def upgrade():
340
# Add constraint to existing geometry column
341
op.execute('''
342
ALTER TABLE buildings
343
ADD CONSTRAINT enforce_geom_type
344
CHECK (geometrytype(geom) = 'POLYGON' OR geom IS NULL)
345
''')
346
347
# Pattern 2: Partition spatial data
348
def upgrade():
349
# Create partitioned table for large spatial datasets
350
op.execute('''
351
CREATE TABLE spatial_data_2024 (
352
LIKE spatial_data INCLUDING ALL
353
) INHERITS (spatial_data)
354
''')
355
356
# Add partition constraint
357
op.execute('''
358
ALTER TABLE spatial_data_2024
359
ADD CONSTRAINT year_2024
360
CHECK (extract(year from created_at) = 2024)
361
''')
362
363
# Pattern 3: Upgrade geometry dimension
364
def upgrade():
365
# Convert 2D geometries to 3D
366
op.execute('''
367
UPDATE elevation_points
368
SET geom = ST_Force3D(geom)
369
WHERE ST_CoordDim(geom) = 2
370
''')
371
```
372
373
## Error Handling and Rollback
374
375
Robust error handling for spatial migrations:
376
377
```python
378
def upgrade():
379
try:
380
# Spatial operations in transaction
381
with op.get_context().autocommit_block():
382
op.add_column('features',
383
Column('geom', Geometry('POLYGON', srid=4326))
384
)
385
386
# Validate spatial data
387
result = op.get_bind().execute('''
388
SELECT COUNT(*) FROM features
389
WHERE geom IS NOT NULL AND NOT ST_IsValid(geom)
390
''').scalar()
391
392
if result > 0:
393
raise ValueError(f"Found {result} invalid geometries")
394
395
except Exception as e:
396
# Rollback spatial changes
397
op.drop_column('features', 'geom')
398
raise
399
400
def downgrade():
401
# Safe downgrade with data preservation
402
invalid_geoms = op.get_bind().execute('''
403
SELECT id FROM features WHERE NOT ST_IsValid(geom)
404
''').fetchall()
405
406
if invalid_geoms:
407
print(f"Warning: {len(invalid_geoms)} invalid geometries will be lost")
408
409
op.drop_column('features', 'geom')
410
```
411
412
## Performance Considerations
413
414
Optimize spatial migrations for large datasets:
415
416
### Batch Processing
417
```python
418
def upgrade():
419
# Process large spatial updates in batches
420
batch_size = 10000
421
offset = 0
422
423
while True:
424
result = op.get_bind().execute(f'''
425
UPDATE large_spatial_table
426
SET geom_transformed = ST_Transform(geom, 3857)
427
WHERE id BETWEEN {offset} AND {offset + batch_size - 1}
428
AND geom_transformed IS NULL
429
''')
430
431
if result.rowcount == 0:
432
break
433
434
offset += batch_size
435
print(f"Processed {offset} rows")
436
```
437
438
### Index Management
439
```python
440
def upgrade():
441
# Drop spatial indexes before bulk operations
442
op.drop_index('idx_features_geom_spatial')
443
444
# Perform bulk spatial updates
445
op.execute('UPDATE features SET geom = ST_Transform(geom, 3857)')
446
447
# Recreate spatial indexes
448
op.create_index('idx_features_geom_spatial', 'features', ['geom'],
449
postgresql_using='gist')
450
```