Geometry issues when creating table from existing spatial table

Question

In short, I want to split an existing spatial table into two parts. The split itself is straight forward. I start with this (fully functioning) table:

CREATE TABLE table
(
  my_identifier smallint,
  geom geometry,
  CONSTRAINT enforce_dims_geom CHECK (st_ndims(geom) = 2),
  CONSTRAINT enforce_srid_geom CHECK (st_srid(geom) = 4326)
)
WITH (
  OIDS=TRUE
);

and create the new tables by:

CREATE TABLE public.table_part1 AS
SELECT * FROM public.table WHERE idmy_identifier = 1;

CREATE TABLE public.table_part2 AS
SELECT * FROM public.table WHERE my_identifier = 2;

However, the problem is that although the geom column gets transferred to my new tables, the geometry (?) itself doesn't get transferred correctly:

CREATE TABLE table_part1
(
  my_identifier smallint,
  geom geometry
)
WITH (
  OIDS=FALSE
);

I tried running SELECT Probe_Geometry_Columns(); but it didn't help. Do I need to insert data into the geometry_columns table? If so, exactly what?

Answer 1

It may not matter, but I am assuming postGis version 1.5.
To manually register the geometry, insert the the proper values in the geometry_columns table thusly:

INSERT INTO geometry_columns(f_table_catalog, f_table_schema, f_table_name, f_geometry_column, coord_dimension, srid, "type")
SELECT '', 'public', 'table_part1', 'geom', ST_CoordDim(geom), ST_SRID(geom), GeometryType(geom)
FROM public.table_part1 LIMIT 1;  

More info here: http://postgis.refractions.net/documentation/manual-1.5/ch04.html#Manual_Register_Spatial_Column

Also, creating table with 'SELECT *' is arguably bad form. Instead, create the schema explicitly:

CREATE TABLE public.table_part1 AS
SELECT my_identifier, geom
FROM public.table
WHERE my_identifier = 1;

Answer 2

For PostGIS 1.x, SELECT Probe_Geometry_Columns() fails because you don't have a geometry type check constraint for your geometry column. I'm going to guess the geometry type is POINT for these examples. Normally you would see something in the table DDL:

CONSTRAINT enforce_geotype_geom CHECK (geometrytype(geom) = 'POINT'::text OR geom IS NULL),
...

or you could add add a check constraint to your existing public."table":

ALTER TABLE public."table"
  ADD CONSTRAINT enforce_geotype_geom CHECK (geometrytype(geom) = 'POINT'::text OR geom IS NULL);

For your derived tables, not only would the geometry_columns table need updating (as suggested by @Scro), but you would also need to manually create a few other check constraints for each of these tables. @Nicklas has a good suggestion with Populate_Geometry_Columns().

---

Upgrade to PostGIS 2.0

If you upgrade to PostGIS 2.0, problems like this don't exist since the geometry_columns table is actually a view (so it updates itself), and the geometry tables don't require check constraints. The geometry columns use a typmod (or typemod) to define the geometry type/dimensions and SRID. For example:

• geometry(PointM,4326),
• geometry(MultiLineStringZ,26910),
• geometry(Triangle,27200), etc.

To upgrade, you would first need to dump the data from "table" (i.e., --data-only with pg_dump, or the equivalent check box in PgAdminIII), then restore the dumped data into a PostGIS 2.0 table with a new table definition that uses the new typmod syntax:

CREATE TABLE "table"
(
  my_identifier smallint,
  geom geometry(Point,4326)
);

then load the old data into the new table. Any other derivative data (e.g., views and tables created using select * into mynewtable) will keep the geometry(Point,4326) typmod, and geometry_columns will also be up-to-date. It's pretty slick, and makes the 2.0 upgrade worth it.

Answer 3

What makes you say your geometries not is populated as expected?

What do you get from:

SELECT ST_AsText(geom) FROM table_part1;

If you are using 1.5 and the problem is that the geometry column not is registred in geometry_columns table, then you can use

ST_Populate_geometry_columns()

That function will create all necessary constraints and register the geometry columns(s) in geometry_columns table.

But I am not sure from the question if the geometry_columns table is the problem.

/Nicklas