Search code examples
sqlpostgresqlpostgis

PostGIS Create Geography Column From Geometry Column

I currently have a table with the following type format:

varchar | numeric | numeric | geometry

To add a geography column, the following query was executed:

ALTER TABLE table ADD COLUMN geography geography(POINT,4326);

And now the type format of the table looks like:

varchar | numeric | numeric | geometry | geography

The issue I've now run into is that I'm trying to populate the geography column with information based off the geometry column. I've tried:

UPDATE table SET geography = geometry::geography;

But I run into the following error:

ERROR: Geometry type (MultiPolygon) does not match column type

Is there a query that I'm missing that would be able to successfully populate the new geography column with a geography conversion from the geometry data found in the geometry column?

Solution

  • You added a point geography, but the source column contains a multipolygon geometry.

    If all of your data is multipolygon, create a geography of the proper type:

    ALTER TABLE table ADD COLUMN geography geography(multipolygon ,4326);

    If you have a mix of input, create a generic geography column:

    ALTER TABLE table ADD COLUMN geography geography(4326);