AW: [postgis-users] joining geometry and data tables

Klingseisen Bernhard Bernhard.Klingseisen at
Mon Mar 20 03:33:09 PST 2006

Hi Brent,

Thanks for your help. With your suggested procedure I can generate a valid mapserver layer.


-----Ursprüngliche Nachricht-----
Von: postgis-users-bounces at [mailto:postgis-users-bounces at] Im Auftrag von Brent Wood
Gesendet: Montag, 20. März 2006 10:21
An: PostGIS Users Discussion
Betreff: Re: [postgis-users] joining geometry and data tables

--- Klingseisen Bernhard <Bernhard.Klingseisen at> wrote:

> 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.

I have one suggestion,

If you create the gepmetry attribute like this, then Postgis won't generate the record in the geometry_columns table which stores information about the geometries in the database (which is read by mapserver).

Create your table first as described in the Postgis docs:

CREATE TABLE jointest (plz3 integer,
                       count_ integer);
SELECT ADDGEOMETRYCOLUMN (see the postgis docs for the proper use of this
command) ;


INSERT INTO jointest 
FROM plz_austria p 
LEFT JOIN wassercheck2005_ges d ON d.gid = p.plz;

This will result in a table with a geometry attribute which can be used by mapserver (it will be recorded in the geometry_columns table)


  Brent Wood

> CREATE TABLE jointest AS (
>  SELECT 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
> )
> 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
>   (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
> > _______________________________________________
> postgis-users mailing list postgis-users at

postgis-users mailing list postgis-users at

More information about the postgis-users mailing list