vw_qgep_reaches does not export all the entites with QGIS

[ponceta]

Describe the bug
QGIS Export fail to export all reaches to GPKG and SHAPEFILE

To Reproduce
Exact steps to reproduce the behavior:

1. Go to the vw_qgep_reaches layer
2. Right click on the layer : Export to ...
3. Save layer to GPKG or SHAPEFILE
4. Look for differences

Expected behavior
All entites are exported to the destination file.

Screenshots / data

Desktop (please complete the following information):

• QGEP plugin version
• QGEP datamodel version
• QGEP project file version -
• QGIS Version 3.22 LTR, 3.28 Latest and 3.29 Nightly
• OS

Additional context
Following #737

[domi4484]

Small update: seems not a QGIS issue. Exporting the layer with ogr2ogr fails with the same error ERROR 1: ERROR: division by zero.

But I think QGIS should at least warn the user that not all data could be exported due to an error.

[sjib]

Small update: seems not a QGIS issue. Exporting the layer with ogr2ogr fails with the same error ERROR 1: ERROR: division by zero.
@domi4484 Can you open an issue at the ogr2ogr github(?) and link it?

[domi4484]

@sjib at a first glance it seems an error in the view. It needs be investigated a little more before opening a GDAL issue.

[ponceta]

This is probably a slope calculation issue in the view. This is linked to data and data exposition through the view.

+1 on a warning message on QGIS side when the selection fails.

In PGAdmin the selection fails.

ERREUR : ERREUR: division par zéro
CONTEXT: processus parallèle

État SQL : 22012

[ponceta]

Confirmed with removing the slope column.

SELECT re.obj_id,
    re.clear_height,
    re.material,
    ch.usage_current AS ch_usage_current,
    ch.function_hierarchic AS ch_function_hierarchic,
    ws.status AS ws_status,
    ws.fk_owner AS ws_fk_owner,
    ch.function_hydraulic AS ch_function_hydraulic,
	/*
        CASE
            WHEN pp.height_width_ratio IS NOT NULL THEN round(re.clear_height::numeric * pp.height_width_ratio)::smallint::integer
            ELSE re.clear_height
        END AS width,
        CASE
            WHEN rp_from.level > 0::numeric AND rp_to.level > 0::numeric THEN round((rp_from.level - rp_to.level) / re.length_effective * 1000::numeric, 1)
            ELSE NULL::numeric
        END AS _slope_per_mill,
    */
	re.coefficient_of_friction,
    re.elevation_determination,
    re.horizontal_positioning,
    re.inside_coating,
    re.length_effective,
    re.progression_geometry,
    re.reliner_material,
    re.reliner_nominal_size,
    re.relining_construction,
    re.relining_kind,
    re.ring_stiffness,
    re.slope_building_plan,
    re.wall_roughness,
    re.fk_pipe_profile,
    ne.identifier,
    ne.remark,
    ne.last_modification,
    ne.fk_dataowner,
    ne.fk_provider,
    ch.bedding_encasement AS ch_bedding_encasement,
    ch.connection_type AS ch_connection_type,
    ch.jetting_interval AS ch_jetting_interval,
    ch.pipe_length AS ch_pipe_length,
    ch.usage_planned AS ch_usage_planned,
    ws.obj_id AS ws_obj_id,
    ws.accessibility AS ws_accessibility,
    ws.contract_section AS ws_contract_section,
    ws.financing AS ws_financing,
    ws.gross_costs AS ws_gross_costs,
    ws.identifier AS ws_identifier,
    ws.inspection_interval AS ws_inspection_interval,
    ws.location_name AS ws_location_name,
    ws.records AS ws_records,
    ws.remark AS ws_remark,
    ws.renovation_necessity AS ws_renovation_necessity,
    ws.replacement_value AS ws_replacement_value,
    ws.rv_base_year AS ws_rv_base_year,
    ws.rv_construction_type AS ws_rv_construction_type,
    ws.structure_condition AS ws_structure_condition,
    ws.subsidies AS ws_subsidies,
    ws.year_of_construction AS ws_year_of_construction,
    ws.year_of_replacement AS ws_year_of_replacement,
    ws.last_modification AS ws_last_modification,
    ws.fk_operator AS ws_fk_operator,
    ws.pully_id_topobase AS ws_pully_id_topobase,
    ws.pully_table_topobase AS ws_pully_table_topobase,
    ws.pully_db_topobase AS ws_pully_db_topobase,
    ws.pully_validation AS ws_pully_validation,
    ws.pully_fk_chantier AS ws_pully_fk_chantier,
    ws.pully_controle_video_date AS ws_pully_controle_video_date,
    ws.pully_controle_terrain_date AS ws_pully_controle_terrain_date,
    ws._cover_label AS ws__cover_label,
    ws._input_label AS ws__input_label,
    ws._output_label AS ws__output_label,
    ws._bottom_label AS ws__bottom_label,
    rp_from.obj_id AS rp_from_obj_id,
    rp_from.elevation_accuracy AS rp_from_elevation_accuracy,
    rp_from.identifier AS rp_from_identifier,
    rp_from.level AS rp_from_level,
    rp_from.outlet_shape AS rp_from_outlet_shape,
    rp_from.position_of_connection AS rp_from_position_of_connection,
    rp_from.remark AS rp_from_remark,
    rp_from.last_modification AS rp_from_last_modification,
    rp_from.fk_dataowner AS rp_from_fk_dataowner,
    rp_from.fk_provider AS rp_from_fk_provider,
    rp_from.fk_wastewater_networkelement AS rp_from_fk_wastewater_networkelement,
    rp_to.obj_id AS rp_to_obj_id,
    rp_to.elevation_accuracy AS rp_to_elevation_accuracy,
    rp_to.identifier AS rp_to_identifier,
    rp_to.level AS rp_to_level,
    rp_to.outlet_shape AS rp_to_outlet_shape,
    rp_to.position_of_connection AS rp_to_position_of_connection,
    rp_to.remark AS rp_to_remark,
    rp_to.last_modification AS rp_to_last_modification,
    rp_to.fk_dataowner AS rp_to_fk_dataowner,
    rp_to.fk_provider AS rp_to_fk_provider,
    rp_to.fk_wastewater_networkelement AS rp_to_fk_wastewater_networkelement
   FROM qgep_od.reach re
     LEFT JOIN qgep_od.wastewater_networkelement ne ON ne.obj_id::text = re.obj_id::text
     LEFT JOIN qgep_od.reach_point rp_from ON rp_from.obj_id::text = re.fk_reach_point_from::text
     LEFT JOIN qgep_od.reach_point rp_to ON rp_to.obj_id::text = re.fk_reach_point_to::text
     LEFT JOIN qgep_od.wastewater_structure ws ON ne.fk_wastewater_structure::text = ws.obj_id::text
     LEFT JOIN qgep_od.channel ch ON ch.obj_id::text = ws.obj_id::text
     LEFT JOIN qgep_od.pipe_profile pp ON re.fk_pipe_profile::text = pp.obj_id::text;

[ponceta]

SELECT *, ST_ASTEXT(progression_geometry)
FROM qgep_od.reach
WHERE ST_LENGTH(progression_geometry) = 0

LINESTRING Z (540455.526 151211.61 419.652,540455.526 151211.61 419.652)
LINESTRING Z (540412.2042754601 151231.83930089042 0,540412.2042754601 151231.83930089042 0)

We sould constraint st_length(progression_geometry) != 0

[ponceta]

Duplicate with #639