Skip to content

Instantly share code, notes, and snippets.

Avatar
😎
QWAT and QGEP up and running

Arnaud Poncet-Montanges ponceta

😎
QWAT and QGEP up and running
View GitHub Profile
@ponceta
ponceta / Permalink dev2
Last active Mar 2, 2021
Too long permalink
View Permalink dev2
@ponceta
ponceta / pully_custom.sql
Created May 23, 2019
QGEP : Pully's customization
View pully_custom.sql
--Ajout du matériau pour le radier
ALTER TABLE qgep_od.wastewater_node
ADD COLUMN pully_bottom_material integer;
CREATE TABLE qgep_vl.pully_node_bottom_material () INHERITS (qgep_sys.value_list_base);
ALTER TABLE qgep_vl.pully_node_bottom_material ADD CONSTRAINT pkey_qgep_vl_pully_node_bottom_material_code PRIMARY KEY (code);
INSERT INTO qgep_vl.pully_node_bottom_material (code, vsacode, value_en, value_de, value_fr, value_it, value_ro, abbr_en, abbr_de, abbr_fr, abbr_it, abbr_ro, active) VALUES (4540,4540,'other','andere','autre', 'altro', 'altul', '', '', '', '', '', 'true');
INSERT INTO qgep_vl.pully_node_bottom_material (code, vsacode, value_en, value_de, value_fr, value_it, value_ro, abbr_en, abbr_de, abbr_fr, abbr_it, abbr_ro, active) VALUES (4541,4541,'concrete','Beton','beton', 'zzz_Beton', 'beton', '', '', '', '', '', 'true');
INSERT INTO qgep_vl.pully_node_bottom_material (code, vsacode, value_en, value_de, value_fr, value_it, value_ro, abbr_en, abbr_de, abbr_fr, abbr_it, abbr_ro, active) VALUE
@ponceta
ponceta / vw_qgep_wastewater_structure2.sql
Last active Dec 13, 2018
Alternative view for wastewaterstructure
View vw_qgep_wastewater_structure2.sql
-- View: qgep_od.vw_qgep_wastewater_structure2
-- DROP VIEW qgep_od.vw_qgep_wastewater_structure2;
CREATE OR REPLACE VIEW qgep_od.vw_qgep_wastewater_structure2 AS
SELECT ws.identifier,
CASE
WHEN ma.obj_id IS NOT NULL THEN 'manhole'::text
WHEN ss.obj_id IS NOT NULL THEN 'special_structure'::text
WHEN dp.obj_id IS NOT NULL THEN 'discharge_point'::text
View qwat_od.valve.sql
-- Table: qwat_od.valve
-- DROP TABLE qwat_od.valve;
CREATE TABLE qwat_od.valve
(
id integer NOT NULL DEFAULT nextval('qwat_od.valve_id_seq'::regclass),
fk_valve_type integer NOT NULL,
fk_valve_function integer NOT NULL,
fk_valve_actuation integer NOT NULL,
@ponceta
ponceta / Create_QGEP_Drawing_Scheme.sql
Last active Feb 1, 2018
Create_QGEP_Drawing_Scheme
View Create_QGEP_Drawing_Scheme.sql
CREATE SCHEMA qgep_dr
AUTHORIZATION qgep;
COMMENT ON SCHEMA qgep_dr
IS 'QGEP drawing tools';
/* CREATE TABLE */
CREATE TABLE qgep_dr.constructionpoint (id serial PRIMARY KEY);
COMMENT ON TABLE qgep_dr.constructionpoint IS 'construction points are artificials points to build objects.';
@ponceta
ponceta / migration_TB2_QGEP.sql
Last active Apr 28, 2017
Notes de migration
View migration_TB2_QGEP.sql
SELECT DISTINCT ID_EINSTIEGHILFE
FROM AW_SCHACHT
SQL> select * from AW_SCHACHT_STEIGEISENG_TBD
ID EXTERNAL_ID VALUE DATE_OF_CREATION DESIGNER ACTIVE SHORT_VALUE COMMENTARY
---- ----------- ------------------- ---------------- -------- ------ ----------- ----------
10 autre 1
1 échelle 10.04.2000 SIA 1 EC
2 niches marchepied 10.04.2000 SIA 1 N
View PULLY_Pipe_materials.sql
INSERT INTO qwat_vl.pipe_material (id, vl_active, short_fr, value_fr, _displayname_fr, short_en, value_en, _displayname_en, short_ro, value_ro, _displayname_ro, diameter, diameter_nominal, diameter_internal, diameter_external, code_sire, pressure_nominal, sdr, wall_thickness) VALUES (10000, true, 'AC', 'Acier', 'AC i', '', '', ' ', 'OL', 'Otel', 'OL ', '', NULL, NULL, NULL, 99, NULL, NULL, NULL);
INSERT INTO qwat_vl.pipe_material (id, vl_active, short_fr, value_fr, _displayname_fr, short_en, value_en, _displayname_en, short_ro, value_ro, _displayname_ro, diameter, diameter_nominal, diameter_internal, diameter_external, code_sire, pressure_nominal, sdr, wall_thickness) VALUES (10001, true, 'AC', 'Acier', 'AC 3/4 "', '', '', 'AC 3/4 "', 'OL', 'Otel', 'OL ', '3/4 "', 24, 22, 27, 99, NULL, NULL, NULL);
INSERT INTO qwat_vl.pipe_material (id, vl_active, short_fr, value_fr, _displayname_fr, short_en, value_en, _displayname_en, short_ro, value_ro, _displayname_ro, diameter, diameter_nominal, diameter_internal, diamet
View cover_manhole_mod_pully.sql
WITH netzlinien AS (
SELECT
gid,
-- ST_Fineltra(ST_SetSRID(ST_Point(first(y1),first(x1)),21781), 'chenyx06.chenyx06_triangles', 'the_geom_lv03', 'the_geom_lv95') as from_point,
ST_SetSRID(ST_Point(first(y1), first(x1)),21781) as from_point,
-- ST_Fineltra(ST_SetSRID(ST_Point(last(y1),last(x1)),21781), 'chenyx06.chenyx06_triangles', 'the_geom_lv03', 'the_geom_lv95') as to_point,
ST_SetSRID(ST_Point(last(y1), first(x1)),21781) as to_point,
-- ST_Fineltra(St_SetSRID(ST_GeomFromText('LINESTRINGZ('||string_agg(y1::varchar||' '||x1::varchar||' '||coalesce(z1,0)::varchar, ',' ORDER BY seq)||')'),21781), 'chenyx06.chenyx06_triangles', 'the_geom_lv03', 'the_geom_lv95') AS geometry
ST_SetSRID(ST_GeomFromText('LINESTRINGZ('||string_agg(y1::varchar||' '||x1::varchar||' '||coalesce(z1,0)::varchar, ',' ORDER BY seq)||')'),21781) as geometry
FROM sa.aw_netzlinie_geo
View gist:4dfc75b1610c2d3cc9cec629d1efbbde
pulmexa@ubuntu:~/qwat-data-model/update$ python test_migration.py --pg_service qwat
DataModel is NOT conform
Diff:
47a48
> "qwat_od","vw_element_valve"
50d50
< "qwat_od","vw_export_leak"
53d52
< "qwat_od","vw_export_pipe"
61a61
View gist:9c22efcc115adaf0379c
let mapleader=","
syntax on
set bs=2
set background=dark
set number
"tabs as x spaces and smart indent
set smartindent
set tabstop=2
set shiftwidth=2