2

It is very likely that I am misunderstanding the documentation as I'm new to PostGIS, but it says here regarding the "addGeometryColumn" function that since version 2.0.0 of PostGIS:

This function no longer updates geometry_columns [...] So for example building a wgs84 POINT column with this function is now equivalent to: ALTER TABLE some_table ADD COLUMN geom geometry(Point,4326);

However, the command line tool "shp2pgsql" which was shipped with the "postgis" package (I am on Ubuntu 16.04 LTS xenial) generates SQL containing the addGeometryColumn() function, for example:

SELECT AddGeometryColumn('','mytestgeo','geom','26918','POINT',2);

I am definitely using PostGIS 2.5:

SELECT postgis_version();
2.5 USE_GEOS=1 USE_PROJ=1 USE_STATS=1

Since the shp2pgsql tool was shipped with the postgis package, I would have expected it to conform to PostGIS 2.5 specification. Why then does it still use this method? Is it for backwards compatibility? Have I misunderstood the docs?

  • In the PostGIS doc : addGeometryColumn adds a geometry column to an existing table of attributes. I think shp2pgsql create a new table and add a geomtry column. It's a good method. However, for modifying / updating a geometry column, don't use the addGeometryColumn function. – J. Monticolo Feb 13 '19 at 14:10
  • Ah I think I understand. So in older versions of PostGIS (< 2.x), the addGeometryColumn function was used to both add and update geometry columns. Whereas > 2.x it does not update columns any more. "This function no longer updates geometry columns"... That makes sense! Can anyone confirm that this is indeed the case? We still must use addGeometryColumn to create the column, but we can modify it using ALTER TABLE? – JeneralJames Feb 13 '19 at 14:17
  • @J.Monticolo I would not say it's "a good method". Having to do a "SELECT" for altering the table was not the good SQL syntax. According to SQL, it must be 'ALTER' command, 'SELECT' is for make queries. This SQL is working fine CREATE TABLE test (id serial); ALTER TABLE test ADD COLUMN geom geometry(Point,4326); – etrimaille Feb 13 '19 at 14:21
  • I use PostGIS 2.x for years now and I always use addGeometryColumn to add new geom fields. As the doc says, this function is not deprecated for fields creation. This function allows you to specify the dimension. – J. Monticolo Feb 13 '19 at 14:21
2

The AddGeometryColumn is not deprecated. It is a function that validates and transform your input, and wraps them in a valid ALTER TABLE statement.

First, let's note that you can create the geometry column at the same time as the table, or later by altering it:

CREATE TABLE testgeo (id integer, geom geometry(point,3857));
ALTER TABLE testgeo ADD COLUMN geom2 geometry(point,3857);

AddGeometryColumn allows specifying the geometry base name and the desired number of dimension, while a manual creation requires the exact name:

 SELECT addgeometrycolumn('testgeo','geom3','3857','point',3);
 ALTER TABLE testgeo ADD COLUMN geom4 geometry(pointZ,3857);

AddGeometryColumn will also validate the input SRID.

Let's find the smallest valid SRID:

select min(srid) from spatial_ref_sys;
 min
------
 2000
(1 row)


-- create a point column with SRID 1999 (invalid)

--OK 
ALTER TABLE testgeo ADD COLUMN geom5 geometry(point,1999);

--FAIL 
select addgeometrycolumn('testgeo ','geom6','1999','point',2);

ERROR:  AddGeometryColumn() - invalid SRID

And we finally get:

\d testgeo
                     Table "public.testgeo"
 Column |         Type          | Collation | Nullable | Default
--------+-----------------------+-----------+----------+---------
 id     | integer               |           |          |
 geom   | geometry(Point,3857)  |           |          |
 geom2  | geometry(Point,3857)  |           |          |
 geom3  | geometry(PointZ,3857) |           |          |
 geom4  | geometry(PointZ,3857) |           |          |
 geom5  | geometry(Point,1999)  |           |          |

So AddGeometryColumn is doing more than a simple ALTER TABLE, but it is not required either.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.