[postgis-users] joining geometry and data tables

[Brent Wood]

--- Klingseisen Bernhard <Bernhard.Klingseisen at arcs.ac.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) ;

then-

INSERT INTO jointest 
SELECT p.zip, 
       d.count_1, 
       p.the_geom
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)


Cheers,

  Brent Wood


             
>  
> 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
>  
> > _______________________________________________
> postgis-users mailing list
> postgis-users at postgis.refractions.net
> http://postgis.refractions.net/mailman/listinfo/postgis-users
>