[postgis-users] joining geometry and data tables

[Klingseisen Bernhard]

I'm a newby on postgis and have some troubles to join an attribute table
to a geometry table. If I use the the SQL example from the wiki, it
seems to me that I dont get the full postgis support. 
 
CREATE TABLE jointest AS ( 
 SELECT p.zip as plz3, d.count_1 as count, p.the_geom as the_geom
 FROM plz_austria p
 LEFT JOIN wassercheck2005_ges d ON d.gid = p.plz
) 
 
 
The resulting table looks quite different in pg admin to a table that I
natively import to postgis using the shape loader:
 
Table generated with the above sql statement:
 
-- Table: jointest
 
-- DROP TABLE jointest;
 
CREATE TABLE jointest
(
  plz3 int2,
  count numeric,
  the_geom geometry
) 
WITHOUT OIDS;
ALTER TABLE jointest OWNER TO wasserinfo06;
 
 
Original geometry table containg zip code area of Austria:
 
-- Table: plz_austria
 
-- DROP TABLE plz_austria;
 
CREATE TABLE plz_austria
(
  gid int4 NOT NULL DEFAULT nextval('plz_austria_gid_seq'::regclass),
  name varchar(50),
  plz int4,
  zip int2,
  the_geom geometry,
  CONSTRAINT plz_austria_pkey PRIMARY KEY (gid),
  CONSTRAINT enforce_dims_the_geom CHECK (ndims(the_geom) = 2),
  CONSTRAINT enforce_geotype_the_geom CHECK (geometrytype(the_geom) =
'MULTIPOLYGON'::text OR the_geom IS NULL),
  CONSTRAINT enforce_srid_the_geom CHECK (srid(the_geom) = 31297)
) 
WITHOUT OIDS;
ALTER TABLE plz_austria OWNER TO wasserinfo06;
 

-- Index: plz_austria_the_geom_gist
 
-- DROP INDEX plz_austria_the_geom_gist;
 
CREATE INDEX plz_austria_the_geom_gist
  ON plz_austria
  USING gist
  (the_geom);

Apparently this result is not right, because I cannot display it in
mapserver or udig.
Which additions do I need in the SQL statement to generate all the
additional constraints and indexes? Any help is much appreciated.
 
Kind Regards
 
Bernhard
 
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20060320/6b955e8b/attachment.html>