27

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?

2
  • I hope you are not grabbing every single pixel of the raster and storing it as a point :) Apr 18, 2011 at 23:31
  • no, not at all. :) But I am going to use ST_DumpPoints on some contour linestrings to fill this table. Apr 18, 2011 at 23:34

3 Answers 3

16

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);
9

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

1
7

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.

6
  • Does this mean the two methods will produce the same result? Apr 18, 2011 at 23:01
  • It will do same, if you used existing geometry_columns, validate and created indexes properly. You could check elsasoft.org/samples/postgre_postgis/…
    – Senthil
    Apr 18, 2011 at 23:10
  • 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? Apr 18, 2011 at 23:42
  • @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.
    – Senthil
    Apr 18, 2011 at 23:54
  • I edited your answer to make it clearer. Thank you. Apr 19, 2011 at 0:49

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

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