SQL Create Statement

[sjib]

No description provided.

[andreasneumann]

Open Issues:

• primary key names cannot contain "."
• french apostrophe needs to be escaped with ' , e.g. l''organisation
• many columns in the tables are still missing, e.g. all columns that are referenced by foreign keys
• foreign keys should not be defined multiple times, only once per table

[sjib]

solved - Primary key names cannot contain "."
solved - french apostrophe needs to be escaped with ' , e.g. l''organisation

[sjib]

solved - - Use BEGIN; .... COMMIT;

in progress - Add geometries with specific function from postgis
line 129
SELECT AddGeometryColumn('qgep', 'od_cover', 'location', 21781, 'POINT', 2);

[sjib]

Dear Dennis
I am working on the geometry attribute

Can you tell me how to change the red part with the situation attribute?

COMMENT ON COLUMN qgep.od_reach_point.obj_id IS 'INTERLIS OID mit Postfix/Präfix oder UUOID, see www.interlis.ch';
ALTER TABLE qgep.od_reach_point ADD COLUMN elevation_accuracy
elevation_accuracy varchar(50) ;
COMMENT ON COLUMN qgep.od_reach_point.elevation_accuracy
elevation_accuracy IS 'yyy / Quantifizierung der Genauigkeit der Höhenlage der Kote in Relation zum Höhenfixpunktnetz (z.B. Grundbuchvermessung oder Landesnivellement). / Plage de précision des coordonnées altimétriques du point de tronçon';
ALTER TABLE qgep.od_reach_point ADD COLUMN identifier varchar(20) ;
CREATE UNIQUE INDEX in_od_reach_point_identifier ON qgep.od_reach_point USING btree (identifier);
COMMENT ON COLUMN qgep.od_reach_point.identifier IS ' / / ';
ALTER TABLE qgep.od_reach_point ADD COLUMN level decimal(4,3) ;
COMMENT ON COLUMN qgep.od_reach_point.level IS 'yyy / Sohlenhöhe des Haltungsendes / Cote du radier de la fin du tronçon';
ALTER TABLE qgep.od_reach_point ADD COLUMN outlet_shape varchar(50) ;
COMMENT ON COLUMN qgep.od_reach_point.outlet_shape IS 'yyy Art des Auslaufs / Art des Auslaufs / Types de sortie';
ALTER TABLE qgep.od_reach_point ADD COLUMN position_of_connection smallint ;
COMMENT ON COLUMN qgep.od_reach_point.position_of_connection IS 'yyy / Anschlussstelle bezogen auf Querschnitt im Kanal; in Fliessrichtung (für Haus- und Strassenanschlüsse) / Emplacement de raccordement Référence à la section transversale dans le canal dans le sens d’écoulement (pour les raccordements domestiques et ';
ALTER TABLE qgep.od_reach_point ADD COLUMN remark varchar(80) ;
COMMENT ON COLUMN qgep.od_reach_point.remark IS ' / Allgemeine Bemerkungen / Remarques générales';

ALTER TABLE qgep.od_reach_point ADD COLUMN situation GEOMETRY;
ALTER TABLE qgep.od_reach_point ADD CONSTRAINT enforce_dims_situation CHECK (ndims (lage) = 2);
ALTER TABLE qgep.od_reach_point ADD CONSTRAINT enforce_geotype_situation CHECK (geometrytype (situation ) = 'POINT'::text OR situation IS NULL);
COMMENT ON COLUMN qgep.od_reach_point.situation IS 'national position coordinates (N,E) / Landeskoordinate Ost/Nord / Coordonnées nationales Est/Nord';

ALTER TABLE qgep.od_reach_point ADD COLUMN last_modification timestamp without time zone ;
COMMENT ON COLUMN qgep.od_reach_point.last_modification IS 'Last modification / Letzte_Aenderung / Derniere_modification: INTERLIS_1_DATE';
ALTER TABLE qgep.od_reach_point ADD COLUMN datenherr varchar(50) ;
COMMENT ON COLUMN qgep.od_reach_point.datenherr IS 'Metaattribute Datenherr - this is the person or body who is allowed to delete, change or maintain this object / Metaattribut Datenherr ist diejenige Person oder Stelle, die berechtigt ist, diesen Datensatz zu löschen, zu ändern bzw. zu verwalten / Gestionnaire données gestionnaire de données, qui estla personne ou l''organisation autorisée pour gérer, modifier ou supprimer les données de cette table/classe';

[sjib]

Hoi Andreas
Wie muss ich diesen Code anpassen für eine Polylinie bzw. eine Surface / Area anpassen?

Case "postgres" 'PostGres SQL
Print #1, (" ALTER TABLE " & tablename & " ADD COLUMN " & attrName & "_lage GEOMETRY;")

Print #1, (" ALTER TABLE " & tablenameschema & " ADD CONSTRAINT enforce_dims_" & attrName & "_lage CHECK (ndims (lage) = 2);")

'-- Prüft ob Geometrie = POINT, Alternativen http://postgis.refractions.net/documentation/manual-1.5/ch04.html#RefObject

Print #1, (" ALTER TABLE " & tablenameschema & " ADD CONSTRAINT enforce_geotype_" & attrName & "_lage CHECK (geometrytype (" & attrName & "_lage) = 'POINT'::text OR " & attrName & "_lage IS NULL);")

Wäre das dann LINESTRING und POLYGON?
Wie heissen dann die CHECK Statements?
analog?
LINESTRING::text OR " & attrName & "_lage IS NULL
POLYGON::text OR " & attrName & "_lage IS NULL

Gruss
Stefan

[andreasneumann]

You can add a geometry column as follows:

There are two different ways:

---

Postgis 1.5 and 2.0:

SELECT AddGeometryColumn('schemaname', 'tablename', 'the_geom', 21781, 'POINT', 2);
CREATE INDEX in_schema_table_column ON schema.table USING GIST ( the_geom GIST_GEOMETRY_OPS );

sample for qgep.od_reach:
SELECT AddGeometryColumn('qgep', 'od_reach', 'the_geom', 21781, 'LINESTRING', 2);
CREATE INDEX in_qgep_od_reach_the_geom ON qgep.od_reach USING GIST ( the_geom GIST_GEOMETRY_OPS );

These statements should be executed after the CREATE TABLE statement.

I believe that for Postgis 2.0 the above syntax also works, but in Postgis 2.0 there is also an easier way:

---

Postgis 2.0 (only)

CREATE TABLE qgep.od_reach
(
obj_id varchar(36) NOT NULL,
the_geom geometry(LINESTRING,21781)
CONSTRAINT pkey_qgep_od_reach_obj_id PRIMARY KEY (obj_id)
)
WITH (
OIDS = False
);

possible geometries are:
POINT, MULTIPOINT, LINESTRING, MULTILINESTRING, POLYGON, MULTIPOLYGON - and more (but QGIS can only use the ones listed here).

Code '21781' is the EPSG-code for the swiss projection system CH1903. LV95 uses the code '2056'

see also http://postgis.refractions.net/docs/AddGeometryColumn.html and http://postgis.refractions.net/docs/using_postgis_dbmanagement.html

I would first try to still use the old way that works in both Postgis 1.5 and Postgis 2.0. Later we may decide to only support Postgis 2.0.

Thanks a lot,
Andreas

PS: Denis, please correct me if I stated something wrong ...

[sjib]

Dear Colleagues
Attached the fourth and latest release of the sql Create Statement
I have changed all errors that Andreas stated. 6 and 7 will stay like this because Detailgeometrie in English is detailed_geometry already and if I add _geometry to the geometry attributes it will be detailed_geometry_geometry - else we have to rename Detailgeometrie to another english name - any suggestions?

@3nids: Could you continue to check the statement, while A is on holidays?

Thanks and I hope we can continue to progress.

Greetings

Merci für das Update.

Nun habe ich noch die folgenden Probleme:

1. Kommentare in SQL sind:

//* ------------ Relationships and Value Tables ----------- *//

sollte also zu

----------------------- Relationships and Value Tables ------------

werden.

1. CONSTRAINT-Namen (primary keys und foreign keys) dürfen keine Punkte beinhalten. z.b.:

CONSTRAINT pkey_qgep.vl_reach_point_outlet_shape_code
-->
CONSTRAINT pkey_qgep_vl_reach_point_outlet_shape_code

Das kommt noch ziemlich häufig vor.

1. COMMENT Zeilen "Weitere Attribute ..." müssen noch raus, z.b.
   COMMENT ON COLUMN qgep.od_benching... IS ' / Weitere Attribute siehe BauwerksTeil / Pour les autres attributs voir sous ELEMENT_OUVRAGE';

1. Kommando AddGeometryColumn muss noch leicht angepasst werden:
   SELECT AddGeometryColumn('qgep', 'qgep.od_cover', 'situation_geometry', 21781, 'POINT', 2);
   -->
   SELECT AddGeometryColumn('qgep', 'od_cover', 'situation_geometry', 21781, 'POINT', 2);

das 2. Argument ist Tabelle ohne Schema - das Schema ist ja schon im ersten Argument angegeben. Kommt auch ein paar mal vor.

1. COMMENT ON COLUMN qgep.od_wastewater_node.situation IS 'national position coordinates (N,E) (decisive reference point for sewer network simulation) / Landeskoordinate Nord/Ost (massgebender Bezugspunkt für die Kanalnetzberechnung ) / Coordonnées nationales Est/Nord (Point de référence déterminant pour le calcu';

müsste heissen:

COMMENT ON COLUMN qgep.od_wastewater_node.situation_geometry IS 'national position coordinates (N,E) (decisive reference point for sewer network simulation) / Landeskoordinate Nord/Ost (massgebender Bezugspunkt für die Kanalnetzberechnung ) / Coordonnées nationales Est/Nord (Point de référence déterminant pour le calcu';

situation --> situation_geometry. Kommt auch mehrmals vor.

1. detail_geometry_geometry --> detail_geometry bei Tabelle qgep.od_wastewater_structure

detail_geometry3d_geometry -> detail_geometry3d

---

[sjib]

Comment on 8.1.2. Arbitrary Precision Numbers will be adapted in next release
http://www.postgresql.org/docs/9.1/interactive/datatype-numeric.html
decimal (6,2) instead (4,2)

Adapted in metadatabase - will be correct in next release

[andreasneumann]

In table qgep.od_organisation we need to translate the columns

• datenherr
• datenlieferant

My proposal:

• data_owner
• data_provider