Skip to main content

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.

note

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.:

env "example" {
  dev = "postgres://user:pass@hostname:5432/database"
  // .. redacted for brevity
}

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.

schema.sql
CREATE EXTENSION postgis;

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
Other ORMs

If you are using a different ORM, go to our ORMs Guide for relevant syntax.

  1. 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
  1. In your atlas.hcl config file, add a composite_schema that includes both your extension definition and your models:
atlas.hcl
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
}

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.