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?

• Ragi Yaser Burhum: I hope you are not grabbing every single pixel of the raster and storing it as a point :)
• BenjaminGolder: no, not at all. :) But I am going to use ST_DumpPoints on some contour linestrings to fill this table.

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

• Janosimas: For future reference, this is no longer true: postgis.net/docs/AddGeometryColumn.html

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.

• BenjaminGolder: Does this mean the two methods will produce the same result?
• Senthil: It will do same, if you used existing geometry_columns, validate and created indexes properly. You could check elsasoft.org/samples/postgre_postgis/…
• BenjaminGolder: sorry @Senthil, I don't quite understand your sentence. What do you mean when you say: "if you used existing geometry_columns, validate and created indexes properly"? Is that a command that is missing from the examples?
• Senthil: @BenjaminGolder Have look what AddGeometryColumn doing with this link : elsasoft.org/samples/postgre_postgis/… In your case, as long as wkb_geometry already there in geometry_columns table and you create index manually. So, looks fine. but, easiest option is go with AddGeometryColumn for new fields.
• BenjaminGolder: I edited your answer to make it clearer. Thank you.