[postgis-users] joining geometry and data tables

Klingseisen Bernhard Bernhard.Klingseisen at arcs.ac.at
Mon Mar 20 00:54:34 PST 2006

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;
  plz3 int2,
  count numeric,
  the_geom geometry
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)
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

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
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20060320/6b955e8b/attachment.html>

More information about the postgis-users mailing list