[postgis] Problems with INHERITS

[lczub]

Moin,

I played a little with inheriting from tables with geometryfields.

The first result is, inserting geometries into a geometryfield, which is
inherited from another table, is possible.
	CREATE TABEL aaa (field_a TYP_A);
	SELECT AddGeometryColumn(..., 'aaa','a_geom',a_srid,'POINT',2);
	CREATE TABEL bbb () INHERITS (aaa);
	INSERT INTO bbb FIELDS field_a, a_geom VALUES (1, GeometryFromText(...));

The second result is, geometry-questions on this field did not
function. One reasone could be, that there exits no entry for tabel bbb
in the  metadata-table GEOMETRY_COLUMNS after these steps. (cause
INHERITS is no OPEN GIS standard?)

Question 1
	My first idea for a workaround was
	- first creating table aaa without geometryfields, 
	- then creating table bbb with INHERITS and without geometryfields
	- after these steps I create GeometryColumns for aaa and bbb seperatly

	But this creates a failure during creating geometryfields for
	table bbb. 
	      	ALTER TABLE: column name "a_geom" already exists in table "bbb"
	(yes I want to use the same column-names!)
	
	Well, then I started manipulation of the AddGeometryColumn and
	changed "ALTR TABLE" into "ALTER TABLE ONLY". But then I get
	the failure 
	    parser: parse error at or near "ONLY"
	Tests shows, that generaly "ONLY" did not funtion with ALTER
	TABLE.

	Well, is there a chance to combine inherits and geometryfields?    

Question 2
	 Well , if I build up db the first time, the workaround (if it function) is ok.
But
	 how should I create a new table ccc, which even inherits from
	 table aaa and there are thousands of entries in table aaa?

	 Must I dump the table aaa + bbb, drop them and create aaa,
	 bbb and ccc completly new and load the dump?

	 Is there another way?

Question 3
	 Should I use INHERITS or not?
	 It seems as an usefull (Postgres specific) function.
	 


The attachment inherit_problem.sql cointains my script for the test.


--

Gruß Luiko Czub

______________________________________________________
Luiko Czub
Stürtzelstraße 1         Tel.:  ++49-761/8098-240
D-79106 Freiburg         Email: lczub at t-online.de
______________________________________________________
------------------------ Yahoo! Groups Sponsor ---------------------~-->
Sponsored by VeriSign - The Value of Trust
Secure all your Web servers now - with a proven 5-part
strategy. The FREE Server Security Guide shows you how.
http://us.click.yahoo.com/iWSNbC/VdiDAA/yigFAA/PhFolB/TM
---------------------------------------------------------------------~->

To unsubscribe from this group, send an email to:
postgis-unsubscribe at yahoogroups.com

 

Your use of Yahoo! Groups is subject to http://docs.yahoo.com/info/terms/ 

-------------- next part --------------

-- Tabelle für Textsaetze
BEGIN;
CREATE TABLE dfk_textsatz (
       kennzahl			CHAR(4) NOT NULL, 
       flurkarte		CHAR(8) NOT NULL,
       rechtswert		INTEGER	NOT NULL,
       hochwert			INTEGER	NOT NULL,
       rw_bezug_delta		INTEGER,
       hw_bezug_delta		INTEGER,
       winkel			NUMERIC(6,3),
       inhalt			VARCHAR(256)
       
);
-- Tabelle fuer Text um Geometriefeld ergaenzen
-- es gibt 2 Punktgeometriefelder
-- a) real_pos		Position des Bezugspunktes
-- b) draw_pos		Position, an der Text / Symbol platziert wird
-- DFK Daten beziehen sich auf GK-Bessel 4. Streifen, 
-- es werden POINT mit 2 dimensionalen Koordinaten gespeichert
SELECT AddGeometryColumn('dfktest','dfk_textsatz','real_pos','31494','POINT',2);
SELECT AddGeometryColumn('dfktest','dfk_textsatz','draw_pos','31494','POINT',2);

-- wenn mit mapserver gearbeitet wird, wird UNIQUE Index auf oid benoetig 
-- (siehe POSTGIS Manual Chapter 4. Using PostGis-Retrieving GIS Data
CREATE UNIQUE INDEX dfk_textsatz_oid ON dfk_textsatz ( oid );

-- Zur Suche nach Textinhalten INDEX auf Feld inhalt
CREATE INDEX dfk_textsatz_inhalt ON dfk_textsatz ( inhalt );

COMMIT;

-- Tabelle für Symbolsaetze 
-- Aufbau entspricht 1:1 dem der Textsaetze, deshalb wird von dfk_textsatz geerbt
BEGIN;
CREATE TABLE dfk_symbolsatz () INHERITS ( dfk_textsatz );

-- INDEXE vererben sich nicht
-- wenn mit mapserver gearbeitet wird, wird UNIQUE Index auf oid benoetig 
-- (siehe POSTGIS Manual Chapter 4. Using PostGis-Retrieving GIS Data
CREATE UNIQUE INDEX dfk_symbolsatz_oid ON dfk_symbolsatz ( oid );

-- Zur Suche nach Symbolnummer INDEX auf Feld inhalt
CREATE INDEX dfk_symbolsatz_inhalt ON dfk_symbolsatz ( inhalt );

COMMIT;



-- Einfuegetest für dfk_textsatz
-- + 4350$30940519$ 48058953$ 55118938$+    0$+    0$149.842$Thomas-Mayer-Weg
BEGIN;
	INSERT INTO dfk_textsatz (kennzahl, flurkarte, rechtswert, hochwert,
				  rw_bezug_delta, hw_bezug_delta, winkel, inhalt, real_pos, draw_pos)
		    VALUES (4350, 30940519 , 48058953 , 55118938, 0, 0, 149.842, 'Thomas-Mayer-Weg',   
			    GeometryFromText('POINT (4480589.53 5551189.38)',31494),
			    GeometryFromText('POINT (4480589.53 5551189.38)',31494));

COMMIT;

-- Einfuegetest für dfk_symbolsatz
-- + 4450$30940519$ 48035847$ 55130613$+    0$+    0$364.673$290
BEGIN;
	INSERT INTO dfk_symbolsatz (kennzahl, flurkarte, rechtswert, hochwert,
				  rw_bezug_delta, hw_bezug_delta, winkel, inhalt, real_pos, draw_pos)
		    VALUES (4450, 30940519 , 48035847 , 55130613, 0, 0, 364.673, '290',   
			    GeometryFromText('POINT (4480358.47 5551306.13)',31494),
			    GeometryFromText('POINT (4480358.47 5551306.13)',31494));

COMMIT;

-- noch mal eine Abfrage den Koordinatenwerten einer Punktgeometrie,
-- diesmal aber fuer ein ererbtes Geomeriefeld! 
-- (es ist nicht in der MEtadataTabelle geometry_columns vermerkt)
-- ergebnis sollte sein
--      x      |     y      | srid  
-- ------------+------------+-------
--  4480358.47 | 5551306.13 | 31494
SELECT X(real_pos), Y(real_pos), SRID(real_pos) FROM dfk_symbolsatz WHERE inhalt = '260';

-- noch mal eine Abfrage den Koordinatenwerten einer Punktgeometrie,
-- diesmal aber fuer ein ererbtes Geomeriefeld! 
-- (es ist nicht in der MEtadataTabelle geometry_columns vermerkt)
-- ergebnis sollte sein
--      x      |     y      | srid  
-- ------------+------------+-------
--  4480589.53 | 5551189.38 | 31494
SELECT X(real_pos), Y(real_pos), SRID(real_pos) FROM ONLY dfk_textsatz WHERE flurkarte = 30940519;