Take the 2-minute tour ×
Geographic Information Systems Stack Exchange is a question and answer site for cartographers, geographers and GIS professionals. It's 100% free, no registration required.

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?

Thanks.

share|improve this question
    
I hope you are not grabbing every single pixel of the raster and storing it as a point :) –  Ragi Yaser Burhum Apr 18 '11 at 23:31
    
no, not at all. :) But I am going to use ST_DumpPoints on some contour linestrings to fill this table. –  BenjaminGolder Apr 18 '11 at 23:34

2 Answers 2

up vote 5 down vote accepted

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.

share|improve this answer
    
Does this mean the two methods will produce the same result? –  BenjaminGolder Apr 18 '11 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 '11 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? –  BenjaminGolder Apr 18 '11 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 '11 at 23:54
    
I edited your answer to make it clearer. Thank you. –  BenjaminGolder Apr 19 '11 at 0:49

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

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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