SQL error on Analyser_Merge CREATE TEMP TABLE with datagouv shape source

[flacombe]

Dear maintainers

I'm currently intending to provide merge analyzer for such datasets: https://www.data.gouv.fr/fr/datasets/appuis-aeriens-enedis-utilises-dans-le-cadre-du-deploiement-de-la-fibre-sur-le-rip-de-la-dordogne/

See code here: https://github.com/flacombe/osmose-backend/blob/feature/grace/analysers/analyser_merge_power_pole_FR_gracethd_dordogne.py

When loading open data in TEMP TABLE, I'm geting the following output:

2024-03-21 23:27:12 run osmosis all analyser Analyser_Merge_power_pole_FR_gracethd_dordogne ['t_ptech_appuis_enedis_smpn.cpg', 't_ptech_appuis_enedis_smpn.dbf', 't_ptech_appuis_enedis_smpn.prj', 't_ptech_appuis_enedis_smpn.shp', 't_ptech_appuis_enedis_smpn.shx'] ['t_ptech_appuis_enedis_smpn.cpg', 't_ptech_appuis_enedis_smpn.dbf', 't_ptech_appuis_enedis_smpn.prj', 't_ptech_appuis_enedis_smpn.shp', 't_ptech_appuis_enedis_smpn.shx'] 2024-03-21 23:27:12 Analyser_Merge.py:1278 sql ['t_ptech_appuis_enedis_smpn.cpg', 't_ptech_appuis_enedis_smpn.dbf', 't_ptech_appuis_enedis_smpn.prj', 't_ptech_appuis_enedis_smpn.shp', 't_ptech_appuis_enedis_smpn.shx'] ['t_ptech_appuis_enedis_smpn.cpg', 't_ptech_appuis_enedis_smpn.dbf', 't_ptech_appuis_enedis_smpn.prj', 't_ptech_appuis_enedis_smpn.shp', 't_ptech_appuis_enedis_smpn.shx'] 2024-03-21 23:27:12 Analyser_Merge.py:863 sql 2024-03-21 23:27:12 Analyser_Merge.py:875 sql 2024-03-21 23:27:12 Load raw data into database ['t_ptech_appuis_enedis_smpn.cpg', 't_ptech_appuis_enedis_smpn.dbf', 't_ptech_appuis_enedis_smpn.prj', 't_ptech_appuis_enedis_smpn.shp', 't_ptech_appuis_enedis_smpn.shx'] ['t_ptech_appuis_enedis_smpn.cpg', 't_ptech_appuis_enedis_smpn.dbf', 't_ptech_appuis_enedis_smpn.prj', 't_ptech_appuis_enedis_smpn.shp', 't_ptech_appuis_enedis_smpn.shx'] 2024-03-21 23:27:12 Analyser_Merge.py:893 sql 2024-03-21 23:27:12 Analyser_Merge.py:898 sql 2024-03-21 23:27:12 error: sql=CREATE TEMP TABLE "power_pole_fr_gracethd_dordogne" ("'" VARCHAR) 2024-03-21 23:27:12 error: error on analyse merge_power_pole_FR_gracethd_dordogne... 2024-03-21 23:27:12 Traceback (most recent call last): 2024-03-21 23:27:12 File "/opt/osmose-backend/./osmose_run.py", line 275, in execc 2024-03-21 23:27:12 analyser_obj.analyser() 2024-03-21 23:27:12 File "/opt/osmose-backend/analysers/Analyser_Osmosis.py", line 285, in analyser 2024-03-21 23:27:12 self.analyser_osmosis_common() 2024-03-21 23:27:12 File "/opt/osmose-backend/analysers/Analyser_Merge.py", line 1436, in analyser_osmosis_common 2024-03-21 23:27:12 table = super().analyser_osmosis_common() 2024-03-21 23:27:12 ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^ 2024-03-21 23:27:12 File "/opt/osmose-backend/analysers/Analyser_Merge.py", line 1280, in analyser_osmosis_common 2024-03-21 23:27:12 table = self.load.run(self, self.conflate, self.config.db_user, self.class.name.lower()[15:], self.analyser_version()) 2024-03-21 23:27:12 ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^ 2024-03-21 23:27:12 File "/opt/osmose-backend/analysers/Analyser_Merge.py", line 1038, in run 2024-03-21 23:27:12 return super().run(osmosis, conflate, db_schema, default_table_base_name, version) 2024-03-21 23:27:12 ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^ 2024-03-21 23:27:12 File "/opt/osmose-backend/analysers/Analyser_Merge.py", line 898, in run 2024-03-21 23:27:12 osmosis.run("CREATE TEMP TABLE "{0}" ({1})".format(table, self.create)) 2024-03-21 23:27:12 File "/opt/osmose-backend/analysers/Analyser_Osmosis.py", line 584, in run 2024-03-21 23:27:12 self.run00(sql) 2024-03-21 23:27:12 File "/opt/osmose-backend/analysers/Analyser_Osmosis.py", line 530, in run00 2024-03-21 23:27:12 self.giscurs.execute(sql) 2024-03-21 23:27:12 File "/usr/local/lib/python3.11/site-packages/psycopg2/extras.py", line 146, in execute 2024-03-21 23:27:12 return super().execute(query, vars) 2024-03-21 23:27:12 ^^^^^^^^^^^^^^^^^^^^^^^^^^^^ 2024-03-21 23:27:12 psycopg2.errors.SyntaxError: unterminated quoted identifier at or near """ 2024-03-21 23:27:12 LINE 1: CREATE TEMP TABLE "power_pole_fr_gracethd_dordogne" (" 2024-03-21 23:27:12 ^ 2024-03-21 23:27:12

It sounds like to produce bogus SQL query: CREATE TEMP TABLE "power_pole_fr_gracethd_dordogne" ("'" VARCHAR)

I understand it fails to get appropriate fields names from shapefile. Is it because shapes in zip aren't properly supported?

Best regards

[frodrigo]

Yes shp in zip is supported eg https://github.com/osm-fr/osmose-backend/blob/dev/analysers/analyser_merge_bicycle_parking_ES_madrid.py#L36-L39

The shapefile is loaded using ogr2ogr https://github.com/osm-fr/osmose-backend/blob/dev/analysers/Analyser_Merge.py#L804

You can check your zip/shp file with ogrinfo.

[flacombe]

Hi

Shapefile looks good with ogrinfo:

$> ogrinfo t_ptech_appuis_enedis_smpn.shp INFO: Open of t_ptech_appuis_enedis_smpn.shp' using driver ESRI Shapefile' successful. 1: t_ptech_appuis_enedis_smpn (Point)

is it normal Analyser_Merge can't manage to find fields?

[frodrigo]

You try to load SHP as CSV here https://github.com/flacombe/osmose-backend/blob/feature/grace/analysers/Analyser_Merge_power_pole_FR_gracethd.py#L40

[flacombe]

Indeed, how stupid (now fixed on my side).

Isn't it possible to throw an exception here to warn a binary file can't be open as text? Don't know how to do that in Python https://github.com/osm-fr/osmose-backend/blob/dev/analysers/Analyser_Merge.py#L607