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

[Klingseisen Bernhard]

Hi Brent,

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

Cheers
Bernie 

-----Ursprüngliche Nachricht-----
Von: postgis-users-bounces at postgis.refractions.net [mailto:postgis-users-bounces at postgis.refractions.net] 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 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
> 

_______________________________________________
postgis-users mailing list postgis-users at postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users