[postgis-users] a newbie question

[TECHER David]

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