Using Geometry types (PostGIS) with Atlas
PostGIS is a spatial database extension for PostgreSQL. It adds support for geometric objects, enabling the processing
of spatial data like points, lines, and polygons. After installing PostGIS, the GEOMETRY
data type can be used to define
tables that capture geo-spatial data, for example:
-- Create extension "postgis"
CREATE EXTENSION "postgis" WITH SCHEMA "public" VERSION "3.5.2";
-- Create "users" table
CREATE TABLE "public"."users" ("id" serial NOT NULL, "name" character varying NOT NULL, "email" character varying NOT
NULL, "address" public.geometry(Point,4326) NOT NULL, PRIMARY KEY ("id"));
-- Create index "idx_users_address" to table: "users"
CREATE INDEX "idx_users_address" ON "public"."users" USING gist ("address");
-- Create index "ix_users_id" to table: "users"
CREATE INDEX "ix_users_id" ON "public"."users" ("id");
When using the GEOMETRY
type, there are two common errors you may experience upon inspecting your schema with Atlas:
pq: extension "postgis" is not available
This error suggests that your local dev database cannot enable the PostGIS extension.
This error can also be related to the loading of the extension itself. If you still experience an error after following these instructions, try followng those for the second common error.
When comparing the current state of your schema to the desired state, Atlas requires access to a running empty database to process the migration. To enable a PostgreSQL extension, it must be available in a location the database can access.
This is typically done in one of two ways:
Using a pre-built docker image
In order to use the GEOMETRY
type, we can use a PostGIS Docker image as the dev database.
The dev database URL can be provided via the dev
attribute in the atlas.hcl
configuration, e.g.:
env "example" {
dev = "docker://postgis/latest/dev"
// .. redacted for brevity
}
Alternatively, use the --dev-url
command line flag to set the dev database:
atlas migrate diff --env example --dev-url "docker://postgis/latest/dev"
Providing a URL to an already running server that has PostGIS available
If you already have a running server with PostGIS enabled, simply replace the dev
attribute with your server's URL, e.g.:
- HCL
- Command Line
env "example" {
dev = "postgres://user:pass@hostname:5432/database"
// .. redacted for brevity
}
atlas migrate diff --env example --dev-url "postgres://user:pass@hostname:5432/database&sslmode=disable"
Additionally, extensions are database-level objects, meaning they exist outside the scope of a specific schema. Therefore,
when defining your dev database, there should not be a search_path
attribute in its URL, as this variable defines a schema scope.
pq: type "geometry" does not exist
This error suggests that the PostGIS extension for PostgreSQL has not been loaded to your database. To properly include the extension in your database, add it to the start of your SQL or HCL schema definition.
- SQL
- HCL
CREATE EXTENSION postgis;
schema "public" {}
extension "postgis" {
schema = schema.public
version = "3.4.2"
comment = "PostGIS geometry and geography spatial types and functions"
}
Now that the PostGIS extension is defined, it must be properly loaded into the database. The way to do so depends on the configuration of your system – you may define your schema in SQL or HCL files, or you may use an ORM (e.g. SQLAlchemy) to define your models.
No ORM (only SQL/HCL)
If there is no ORM to integrate into your system and your schema is defined in either a SQL or HCL file, you
can simply create an atlas.hcl
file to connect your schema to your dev database.
env "local" {
src = "file://schema.sql" // schema file containing PostGIS extension creation
dev = "docker://postgis/latest/dev" // dev database
}
Using an ORM
To include both the PostGIS extension and ORM models, we configure Atlas to read the state of the schema from a Composite Schema data source. We will use SQLAlchemy as our ORM in this example.
In order for Atlas to work alongside your ORM, you need to install the corresponding Atlas provider. For SQLAlchemy, run:
pip install atlas-provider-sqlalchemy
If you are using a different ORM, go to our ORMs Guide for relevant syntax.
- In addition to creating the PostGIS extension in your SQL/HCL file, set up a file or directory containing your SQLAlchemy models which contain the Geometry type, e.g.:
project-directory
├── models.py
├── schema.sql
└── atlas.hcl
- In your
atlas.hcl
config file, add acomposite_schema
that includes both your extension definition and your models:
- atlas.hcl
- (Example) schema.sql
- (Example) models.py using SQLAlchemy
data "external_schema" "sqlalchemy" {
program = [
"atlas-provider-sqlalchemy",
"--path", "./models.py", // path to your SQLAlchemy models directory or file
"--dialect", "postgresql"
]
}
data "composite_schema" "app" {
# Load custom types first
schema {
url = "file://schema.sql" // schema file containing PostGIS extension creation
}
# Then, load SQLAlchemy models
schema "public" {
url = data.external_schema.sqlalchemy.url
}
}
env "local" {
src = data.composite_schema.app.url // link to current schema state
dev = "docker://postgis/latest/dev" // dev database
}
CREATE EXTENSION postgis;
// ... other extensions, baseline schema definitions, etc.
from sqlalchemy import Column, Integer, String, DateTime
from sqlalchemy.orm import DeclarativeBase
from geoalchemy2 import Geometry
class Base(DeclarativeBase):
pass
class Users(Base):
__tablename__ = "Users"
id = Column(Integer, primary_key=True, index=True)
name = Column(String, nullable=False)
email = Column(String, nullable=False)
address = Column(Geometry('POINT', srid=4326), nullable=False)
Run atlas schema inspect --env local --url env://src
to check for additional errors. The expected output using the above example files should be:
table "Users" {
schema = schema.public
column "id" {
null = false
type = serial
}
column "name" {
null = false
type = character_varying
}
column "email" {
null = false
type = character_varying
}
column "address" {
null = false
type = sql("public.geometry(Point,4326)")
}
primary_key {
columns = [column.id]
}
index "idx_Users_address" {
columns = [column.address]
type = GIST
}
index "ix_Users_id" {
columns = [column.id]
}
}
extension "postgis" {
schema = schema.public
version = "3.5.2"
comment = "PostGIS geometry and geography spatial types and functions"
}
schema "public" {
comment = "standard public schema"
}
Have additional questions or feedback? Feel free to reach out on our Discord server.