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 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?

share|improve this question
add comment

3 Answers

up vote 3 down vote accepted
+50

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;
share|improve this answer
    
Thank you, your instructions did the trick –  Mimo Apr 24 '12 at 6:03
add comment

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.

share|improve this answer
    
Very helpful and a good reason to upgrade to 2.0. –  Mimo Apr 24 '12 at 6:04
add comment

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

share|improve this answer
add comment

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.