Creating spatial tables with PostGIS

Question

In the PostGIS documentation it says that there are two steps to creating a spatial table with SQL:

1. Create a normal non-spatial table.
2. Add a spatial column to the table using the OpenGIS "AddGeometryColumn" function.

If I followed the examples, I would create a table called terrain_points like this:

CREATE TABLE terrain_points ( 
  ogc_fid serial NOT NULL, 
  elevation double precision,
);

SELECT AddGeometryColumn('terrain_points', 'wkb_geometry', 3725, 'POINT', 3 );

Alternatively, if I look at existing tables in pgAdmin III, it seems like I could create the same table like this:

CREATE TABLE terrain_points
(
  ogc_fid serial NOT NULL,
  wkb_geometry geometry,
  elevation double precision,
  CONSTRAINT terrain_points_pk PRIMARY KEY (ogc_fid),
  CONSTRAINT enforce_dims_wkb_geometry CHECK (st_ndims(wkb_geometry) = 3),
  CONSTRAINT enforce_geotype_wkb_geometry CHECK (geometrytype(wkb_geometry) = 'POINT'::text OR wkb_geometry IS NULL),
  CONSTRAINT enforce_srid_wkb_geometry CHECK (st_srid(wkb_geometry) = 3725)
)
WITH (
  OIDS=FALSE
);
ALTER TABLE terrain_points OWNER TO postgres;

-- Index: terrain_points_geom_idx

-- DROP INDEX terrain_points_geom_idx;

CREATE INDEX terrain_points_geom_idx
  ON terrain_points
  USING gist
  (wkb_geometry);

Do these two methods produce the same result? Is the version based on pgAdmin III simply more verbose, and doing things that AddGeometryColumn would do by default?

Answer 1

In PostGIS 2.0+ you can create the geometry column directly using common data definition language.

For example:

-- points in geographic wgs84 coordinates (epsg:4326)
create table mypoints (id serial, name varchar, geom geometry(Point, 4326));

-- lines in spherical mercator (epsg:3857)
create table mylines (id serial, name varchar, geom geometry(LineString, 3857));

-- polygons in Dutch national coordinate system (epsg:28992)
create table mypolygons (id serial, name varchar, geom geometry(Polygon, 28992));

-- multipolygons in British National Grid (epsg:27700)
create table 
  mymultipolygons(id serial, name varchar, geom geometry(Multipolygon, 27700));

-- generic geometry (no data type constraints)
create table mygeometries(id serial, name varchar, geom geometry);

Answer 2

No, they are not producing the same results.

With the second method you would still need to add a record in the GEOMETRY_COLUMNS table, and you would need to do it with an INSERT statement, or using the Populate_Geometry_Columns function as suggested in the other answer.

AddGeometryColumn will take care of doing this for you (together with creating the index and the constraints).

Answer 3

The two methods should produce the same results. AddGeometryColumn will not only create the geometry field, it will validate and create necessary indexes too. As long as you do all these things manually, the result will be the same. If you have an existing geometry column, you could use the Populate_Geometry_Columns function to validate it and create the necessary indexes.