<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<HTML><HEAD><TITLE>Nachricht</TITLE>
<META http-equiv=Content-Type content="text/html; charset=us-ascii">
<META content="MSHTML 6.00.2800.1400" name=GENERATOR></HEAD>
<BODY>
<DIV><FONT face=Arial size=2><SPAN class=454394308-20032006>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 <SPAN
class=454394308-20032006>I dont get the full postgis support.
</SPAN></SPAN></FONT></DIV>
<DIV><FONT face=Arial size=2><SPAN
class=454394308-20032006></SPAN></FONT> </DIV>
<DIV><FONT face=Arial size=2><SPAN class=454394308-20032006>CREATE TABLE
jointest AS ( </SPAN></FONT></DIV>
<DIV><FONT face=Arial size=2><SPAN class=454394308-20032006> SELECT p.zip
as plz3, d.count_1 as count, p.the_geom as the_geom<BR> FROM plz_austria
p<BR> LEFT JOIN wassercheck2005_ges d ON d.gid = p.plz</SPAN></FONT></DIV>
<DIV><FONT face=Arial size=2><SPAN class=454394308-20032006>)
</SPAN></FONT></DIV>
<DIV><FONT face=Arial size=2><SPAN
class=454394308-20032006></SPAN></FONT> </DIV>
<DIV><FONT face=Arial size=2><SPAN
class=454394308-20032006></SPAN></FONT> </DIV>
<DIV><FONT face=Arial size=2><SPAN class=454394308-20032006>The resulting
table looks quite different in pg admin to a table that I natively
import to postgis using the shape loader:</SPAN></FONT></DIV>
<DIV><FONT face=Arial size=2><SPAN
class=454394308-20032006></SPAN></FONT> </DIV>
<DIV><FONT face=Arial size=2><SPAN class=454394308-20032006><STRONG>Table
generated with the above sql statement:</STRONG></SPAN></FONT></DIV>
<DIV><FONT face=Arial size=2><SPAN
class=454394308-20032006></SPAN></FONT> </DIV>
<DIV><FONT face=Arial size=2><SPAN class=454394308-20032006>-- Table:
jointest</SPAN></FONT></DIV>
<DIV> </DIV>
<DIV><FONT face=Arial size=2><SPAN class=454394308-20032006>-- DROP TABLE
jointest;</SPAN></FONT></DIV>
<DIV> </DIV>
<DIV><FONT face=Arial size=2><SPAN class=454394308-20032006>CREATE TABLE
jointest<BR>(<BR> plz3 int2,<BR> count numeric,<BR> the_geom
geometry<BR>) <BR>WITHOUT OIDS;<BR>ALTER TABLE jointest OWNER TO
wasserinfo06;</SPAN></FONT></DIV>
<DIV><FONT face=Arial size=2><SPAN
class=454394308-20032006></SPAN></FONT> </DIV>
<DIV><FONT face=Arial size=2><SPAN class=454394308-20032006></SPAN></FONT><FONT
face=Arial size=2><SPAN class=454394308-20032006></SPAN></FONT><FONT face=Arial
size=2><SPAN class=454394308-20032006></SPAN></FONT><FONT face=Arial
size=2><SPAN class=454394308-20032006></SPAN></FONT> </DIV>
<DIV><FONT face=Arial size=2><SPAN class=454394308-20032006><STRONG>Original
geometry table containg zip code area of Austria:</STRONG></SPAN></FONT></DIV>
<DIV><FONT face=Arial size=2><SPAN
class=454394308-20032006></SPAN></FONT> </DIV>
<DIV><FONT face=Arial size=2><SPAN class=454394308-20032006>-- Table:
plz_austria</SPAN></FONT></DIV>
<DIV> </DIV>
<DIV><FONT face=Arial size=2><SPAN class=454394308-20032006>-- DROP TABLE
plz_austria;</SPAN></FONT></DIV>
<DIV> </DIV>
<DIV><FONT face=Arial size=2><SPAN class=454394308-20032006>CREATE TABLE
plz_austria<BR>(<BR> gid int4 NOT NULL DEFAULT
nextval('plz_austria_gid_seq'::regclass),<BR> name varchar(50),<BR>
plz int4,<BR> zip int2,<BR> the_geom geometry,<BR> CONSTRAINT
plz_austria_pkey PRIMARY KEY (gid),<BR> CONSTRAINT enforce_dims_the_geom
CHECK (ndims(the_geom) = 2),<BR> CONSTRAINT enforce_geotype_the_geom CHECK
(geometrytype(the_geom) = 'MULTIPOLYGON'::text OR the_geom IS NULL),<BR>
CONSTRAINT enforce_srid_the_geom CHECK (srid(the_geom) = 31297)<BR>) <BR>WITHOUT
OIDS;<BR>ALTER TABLE plz_austria OWNER TO wasserinfo06;</SPAN></FONT></DIV>
<DIV> </DIV><FONT><SPAN class=454394308-20032006>
<DIV><BR><FONT face=Arial size=2>-- Index:
plz_austria_the_geom_gist</FONT></DIV>
<DIV><FONT face=Arial size=2></FONT> </DIV>
<DIV><FONT face=Arial size=2>-- DROP INDEX
plz_austria_the_geom_gist;</FONT></DIV>
<DIV><FONT face=Arial size=2></FONT> </DIV>
<DIV><FONT face=Arial size=2>CREATE INDEX plz_austria_the_geom_gist<BR> ON
plz_austria<BR> USING gist<BR> (the_geom);<BR></FONT></DIV>
<DIV><FONT face=Arial size=2><SPAN class=454394308-20032006>Apparently this
result is not right, because I cannot display it in mapserver or
udig.</SPAN></FONT></DIV>
<DIV><SPAN class=454394308-20032006><FONT face=Arial size=2>Which additions do I
need in the SQL statement to generate all the additional constraints and
indexes? Any help is much appreciated.</FONT></SPAN></DIV>
<DIV><SPAN class=454394308-20032006><FONT face=Arial
size=2></FONT></SPAN> </DIV>
<DIV><SPAN class=454394308-20032006><FONT face=Arial size=2>Kind
Regards</FONT></SPAN></SPAN></FONT></DIV>
<DIV><FONT face=Arial size=2><SPAN
class=454394308-20032006></SPAN></FONT> </DIV>
<DIV><FONT face=Arial size=2><SPAN
class=454394308-20032006>Bernhard</SPAN></FONT></DIV>
<DIV><FONT face=Arial size=2><SPAN
class=454394308-20032006></SPAN></FONT> </DIV></BODY></HTML>