Change PostGIS Column from Geometry to Geography

Question

I have a PostGIS table with a column of type Geometry(POINT) and SRID 0 that must be changed to the type Geography(POINT) as it will be used to store lat/lon values.

How can we make this alteration to the table without dropping it?

Table was originally created using SQLAlchemy, Geoalchemy2 and Python, with the following model defination class:

class User(Base):
    __tablename__ = 'users'
    id                      = Column(Integer, primary_key=True)
    name                    = Column(String())
    coordinates             = Column(Geometry('POINT'))

which should now be changed to

class User(Base):
    __tablename__ = 'users'
    id                      = Column(Integer, primary_key=True)
    name                    = Column(String())
    coordinates             = Column(Geography('POINT'))

swiss_knight · Accepted Answer · 2021-01-23T05:03:18.847

Pure SQL

This statement should do the job:

ALTER TABLE your_table
ALTER COLUMN your_geometry_column
 TYPE Geography(POINT, <yourFinalSRID_integer>)
USING ST_Transform(
      ST_SetSRID(
          ST_Force2D(your_geometry_column), <yourInitialSRID_integer>),
          yourFinalSRID_integer
      )::Geography(POINT);

You probably don't need the ST_Force2D, but I did as my point originally was a POINTZ.

See here for an explanation:
https://postgis.net/workshops/postgis-intro/geography.html#using-geography

Documentation of the functions used:

But I'm not 100% sure you need to do that, the Geometry field type can perfectly handle WGS84 values with a SRID=4326 unless you have to do some PostGIS computations which actually requires it. Even GeoPandas can handle it.

Python

If you don't want to alter your original database, you can do the transformation of your geometries in Python as well, e.g. using PyProj:

import pyproj
from pyproj import Transformer
import shapely.ops as sp_ops
def  transform_geom_to_srid(geom, scrs, tcrs)
    """ Transform a geometry to a new target CRS.
        Works with pyproj version >= 2.x.x
    """
    project = Transformer.from_crs(
        'EPSG:'+str(scrs),
        'EPSG:'+str(tcrs),
         always_xy=True
    )
    return sp_ops.transform(project.transform, geom)

Notice: if you use an older version of PyProj, you will have to set your CRS manually including an 'init=' string, e.g. pyproj.Proj('init=EPSG:'+str(scrs)) instead of just writing the 'EPSG:' strings in the .from_crs() method.

See here for PROJ documentation: https://proj.org/

And here for PyProj: https://pyproj4.github.io/pyproj/stable/

Change PostGIS Column from Geometry to Geography

1 Answers1

Pure SQL

Python