Survey points and constructions points are duplicate - merge both in survey points

[haubourg]

Discussed in PSC 2017 03 07 - Survey points miss a layer definition in qgs project and construction points are duplicates of the same concepts. Lausanne uses surveypoints and has extended it by custom fields. Pully uses old construction points.

This is a structural change so I guess we should add this to 1.3.x version target.

• [ ] change surveypoint id to serial
• [ ] merge structure with construction points and lausanne addons
• [ ] add it to qgs project
• [ ] create a form

[haubourg]

Here is Lausanne definition, I think we can pick at least some to extend current survey point definition like precision on altitudes and planar location.

-- Table: qwat_od.surveypoint

-- DROP TABLE qwat_od.surveypoint;

CREATE TABLE qwat_od.surveypoint
(
  id serial NOT NULL,
  fk_survey_type integer NOT NULL,
  fk_worker integer,
  code character varying(50),
  description text,
  date date,
  fk_folder integer,
  altitude numeric(10,3) DEFAULT NULL::numeric,
  geometry geometry(PointZ,21781) NOT NULL,
  usr_session_name character varying(200),
  usr_no_pt character varying(50),
  usr_no_station_ref character varying(50),
  usr_annot_1 character varying(50),
  usr_annot_2 character varying(50),
  usr_measure_ref text,
  usr_fk_table integer,
  fk_object_reference integer,
  usr_fk_network_element integer,
  usr_fiab_plani numeric(10,3),
  usr_fiab_alti numeric(10,3),
  usr_precision numeric(10,3),
  usr_precisionalti numeric(10,3),
  usr_valid boolean,
  usr_valid_date date,
  usr_import_user character varying(100),
  CONSTRAINT surveypoint_pkey PRIMARY KEY (id),
  CONSTRAINT surveypoint_fk_folder FOREIGN KEY (fk_folder)
      REFERENCES qwat_od.folder (id) MATCH FULL
      ON UPDATE NO ACTION ON DELETE NO ACTION,
  CONSTRAINT surveypoint_fk_object_reference FOREIGN KEY (fk_object_reference)
      REFERENCES qwat_vl.object_reference (id) MATCH FULL
      ON UPDATE NO ACTION ON DELETE NO ACTION,
  CONSTRAINT surveypoint_fk_type FOREIGN KEY (fk_survey_type)
      REFERENCES qwat_vl.survey_type (id) MATCH FULL
      ON UPDATE NO ACTION ON DELETE NO ACTION,
  CONSTRAINT surveypoint_fk_worker FOREIGN KEY (fk_worker)
      REFERENCES qwat_od.worker (id) MATCH FULL
      ON UPDATE NO ACTION ON DELETE NO ACTION,
  CONSTRAINT surveypoint_id_fkey FOREIGN KEY (id)
      REFERENCES qwat_od.surveypoint (id) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION,
  CONSTRAINT surveypoint_usr_fk_table FOREIGN KEY (usr_fk_table)
      REFERENCES qwat_sys.doctables (id) MATCH FULL
      ON UPDATE NO ACTION ON DELETE NO ACTION
)
WITH (
  OIDS=FALSE
);
ALTER TABLE qwat_od.surveypoint
  OWNER TO postgres;
GRANT ALL ON TABLE qwat_od.surveypoint TO postgres;
GRANT SELECT ON TABLE qwat_od.surveypoint TO qwat_edit;
GRANT SELECT, REFERENCES, TRIGGER ON TABLE qwat_od.surveypoint TO qwat_viewer;
GRANT ALL ON TABLE qwat_od.surveypoint TO qwat_user;
GRANT ALL ON TABLE qwat_od.surveypoint TO qwat_manager;
COMMENT ON TABLE qwat_od.surveypoint
  IS 'Table for sampling points. Inherits from surveypoint.';

-- Index: qwat_od.fki_surveypoint_fk_folder

-- DROP INDEX qwat_od.fki_surveypoint_fk_folder;

CREATE INDEX fki_surveypoint_fk_folder
  ON qwat_od.surveypoint
  USING btree
  (fk_folder);

-- Index: qwat_od.fki_surveypoint_fk_type

-- DROP INDEX qwat_od.fki_surveypoint_fk_type;

CREATE INDEX fki_surveypoint_fk_type
  ON qwat_od.surveypoint
  USING btree
  (fk_survey_type);

-- Index: qwat_od.fki_surveypoint_fk_worker

-- DROP INDEX qwat_od.fki_surveypoint_fk_worker;

CREATE INDEX fki_surveypoint_fk_worker
  ON qwat_od.surveypoint
  USING btree
  (fk_worker);

-- Index: qwat_od.surveypoint_geoidx

-- DROP INDEX qwat_od.surveypoint_geoidx;

CREATE INDEX surveypoint_geoidx
  ON qwat_od.surveypoint
  USING gist
  (geometry);

-- Trigger: audit_trigger_row on qwat_od.surveypoint

-- DROP TRIGGER audit_trigger_row ON qwat_od.surveypoint;

CREATE TRIGGER audit_trigger_row
  AFTER INSERT OR UPDATE OR DELETE
  ON qwat_od.surveypoint
  FOR EACH ROW
  EXECUTE PROCEDURE qwat_sys.if_modified_func('true');
ALTER TABLE qwat_od.surveypoint DISABLE TRIGGER audit_trigger_row;

-- Trigger: audit_trigger_stm on qwat_od.surveypoint

-- DROP TRIGGER audit_trigger_stm ON qwat_od.surveypoint;

CREATE TRIGGER audit_trigger_stm
  AFTER TRUNCATE
  ON qwat_od.surveypoint
  FOR EACH STATEMENT
  EXECUTE PROCEDURE qwat_sys.if_modified_func('true');
ALTER TABLE qwat_od.surveypoint DISABLE TRIGGER audit_trigger_stm;

-- Trigger: surveypoint_altitude_insert_trigger on qwat_od.surveypoint

-- DROP TRIGGER surveypoint_altitude_insert_trigger ON qwat_od.surveypoint;

CREATE TRIGGER surveypoint_altitude_insert_trigger
  BEFORE INSERT
  ON qwat_od.surveypoint
  FOR EACH ROW
  EXECUTE PROCEDURE qwat_od.ft_geom3d_altitude();
COMMENT ON TRIGGER surveypoint_altitude_insert_trigger ON qwat_od.surveypoint IS 'Trigger: when updating, check if altitude or Z value of geometry changed and synchronize them.';

-- Trigger: surveypoint_altitude_update_trigger on qwat_od.surveypoint

-- DROP TRIGGER surveypoint_altitude_update_trigger ON qwat_od.surveypoint;

CREATE TRIGGER surveypoint_altitude_update_trigger
  BEFORE UPDATE OF altitude, geometry
  ON qwat_od.surveypoint
  FOR EACH ROW
  WHEN (((new.altitude <> old.altitude) OR (st_z(new.geometry) <> st_z(old.geometry))))
  EXECUTE PROCEDURE qwat_od.ft_geom3d_altitude();
COMMENT ON TRIGGER surveypoint_altitude_update_trigger ON qwat_od.surveypoint IS 'Trigger: when updating, check if altitude or Z value of geometry changed and synchronize them.';

[ponceta]

Related to data-model https://github.com/qwat/qwat-data-model/issues/139

[tudorbarascu]

@haubourg How is this going? Is someone working on it? I wish to add for now the fk_object_reference + precision and precisionalti fields and add the survey_points layer to the trunk project.

[tudorbarascu]

@dsavary Hello, can you detail on the difference between (usr_fiab_plani, usr_fiab_alti) and usr_precision, usr_precisionalti.

[tudorbarascu]

@dsavary gentle ping regarding the above question.

[ponceta]

About id to serial: https://github.com/qwat/qwat-data-model/commit/34add9bfc04845e32ba046301140f38bdec71f9a

@haubourg How is this going? Is someone working on it? I wish to add for now the fk_object_reference + precision and precisionalti fields and add the survey_points layer to the trunk project. https://github.com/qwat/qwat-data-model/commit/f6f5b245b098332034a589e23f8f4e49f168334d

Construction and survey points are not exactly the same and are sometimes used differently, merging these is IMHO not a great idea.

We will see this more in detail when we will get into the GPS import part.

[ponceta]

@tudorbarascu if I understand it well for @dsavary users:

precision means technical quality of the instrument fiability means personnal feeling of how the measurement went and how "trustable it is"

@dsavary tu me corriges si jamais! :)

[dsavary]

@tudorbarascu sorry I completely forgot to answer you @ponceta your explanation is correct For Lausanne, the surveypoinnts will no longer be managing in networks database (like QWAT) but in a specific archive database (QSOUT). the table "constructionpoint" is not to store survey points, but points manually ceated by the users for the different pipe network