[postgis-users] a newbie question

[Obe, Regina]

 You can also just explicitly add the records into geometry_columns table with a statement like

INSERT INTO geometry_columns(f_table_column, f_table_schema, f_table_name, f_geometry_column, coord_dimension, srid, type)
SELECT f_table_column, f_table_schema, 'mynewtable', f_geometry_column, coord_dimension, srid, type
	FROM geometry_columns where f_table_name = 'myoldtable'

As David mentioned for a lot of clients you need a primary key in addition to entry in geometry_columns and copying a table doesn't copy the constraints and keys so you would need to explicitly recreate those you need or do a create with oid to have a dummy one.

Something like
ALTER TABLE mynewtable
  ADD CONSTRAINT pk_mynewtable_gid PRIMARY KEY(gid);

also recreate gist indexes
CREATE INDEX idx_mynewtable_the_geom
  ON mynewtable
  USING gist
  (the_geom);


Where mynewtable and myoldtable are just placeholders for your new and old table names.

Regina








-----Original Message-----
From: postgis-users-bounces at postgis.refractions.net [mailto:postgis-users-bounces at postgis.refractions.net] On Behalf Of TECHER David
Sent: Wednesday, March 21, 2007 8:30 AM
To: PostGIS Users Discussion
Subject: Re: [postgis-users] a newbie question

Gustavo Ces a écrit :
> Sorry about this question,
>  
>     i want to create another table from selecting a previous one ( 
> with the_geom and gid)
>  
> create table foo as select gid, the_geom, other_columns from 
> previous_table
>  
> this query creates the table with the other columns, gid and geom but 
> if try to open it in a client, it fails.
>  
> Which is the problem?
>  
> Thanks,
>  
> Galois
>   

Hi Gustavo,

If your client fail so I suppose ythat your client is perhaps QGIS or 
MapServer, it is because you don't specify a key or a oid

So do something like this with  foo <=> communes_bourgogne and 
other_columns <=> nom_comm, insee_comm

First create your table and populate it with oids and WITHOUT specify 
the_geom

bourgogne=# create table test WITH OIDS as (select gid, nom_comm, 
insee_comm from communes_bourgogne) ;
SELECT

then use the addgeometrycolumn() function properly:

bourgogne=# select addgeometrycolumn('test','the_geom',(select 
srid(the_geom) from communes_bourgogne limit 1),(select 
geometrytype(the_geom) from communes_bourgogne limit 1),2);
                    addgeometrycolumn
----------------------------------------------------------
 public.test.the_geom SRID:27582 TYPE:MULTIPOLYGON DIMS:2

(1 ligne)

times to populate the_geom

bourgogne=# UPDATE test set the_geom=communes_bourgogne.the_geom from 
communes_bourgogne where communes_bourgogne.gid=test.gid; UPDATE 2042
bourgogne=#

if it doesn't work ...Il should be better to try pgsql2shp/shp2pgsql 
(that's the old solution)

1) export as shp

pgsql2shp -f <shapefile.shp> -h localhost -u <user> <database> "select 
the_geom, other_columns from previous_table"

YOU DON'T HAVE TO SPECIFY GID, IT SHOULD BE AUTOMATICALLY CREATE WHEN 
YOU IMPORT IT

2) import into a new table

shp2pgsql -DI <table> <shapefile.shp> | psql <database>

then delete <shapefile.shp> <shapefile.shx> <shapefile.dbf>

> ------------------------------------------------------------------------
>
> _______________________________________________
> postgis-users mailing list
> postgis-users at postgis.refractions.net
> http://postgis.refractions.net/mailman/listinfo/postgis-users
>   


	

	
		
___________________________________________________________________________ 
Yahoo! Mail réinvente le mail ! Découvrez le nouveau Yahoo! Mail et son interface révolutionnaire.
http://fr.mail.yahoo.com
_______________________________________________
postgis-users mailing list
postgis-users at postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users

-----------------------------------------
The substance of this message, including any attachments, may be
confidential, legally privileged and/or exempt from disclosure
pursuant to Massachusetts law. It is intended
solely for the addressee. If you received this in error, please
contact the sender and delete the material from any computer.