<commit_before>###############################################
# Script that creates an SQL script to write
# footprints from a shapefile to a REM database
# Author: M.Haas <EMAIL>
# input: 1) shapefile with footprints
#        2) name of survey in REM db for which
#           these footprints should be used
#        3) optionally: source of footprints
# output: sql file
#############################
import os

shapefile = '/home/mhaas/PhD/DESTRESS/GRVS_Survey_France/osm_buildings_soultz_area.shp'
sql_out = 'fp2rem.sql'
source = 'OSM'
survey_name='destress_google_france2017'

#create a table with the shapefile layer containing postgis geometry
cmd = "shp2pgsql -s 4326 -g the_geom {} temporary_footprint_data > {}".format(shapefile,sql_out)
os.system(cmd)

#write inserts in ve_objects for each row in sql_out
#INSERT INTO asset.ve_object (gid, survey_gid, description, source, accuracy, the_geom, object_id, mat_type, mat_tech, mat_prop, llrs, llrs_duct, height, yr_built, occupy, occupy_dt, "position", plan_shape, str_irreg, str_irreg_dt, str_irreg_type, nonstrcexw, roof_shape, roofcovmat, roofsysmat, roofsystyp, roof_conn, floor_mat, floor_type, floor_conn, foundn_sys, build_type, build_subtype, vuln, rrvs_status, vuln_1, vuln_2, height_1, height_2, object_id1, mat_type_bp, mat_tech_bp, mat_prop_bp,llrs_bp, llrs_duct_bp, height_bp, yr_built_bp, occupy_bp, occupy_dt_bp, position_bp, plan_shape_bp, str_irreg_bp, str_irreg_dt_bp, str_irreg_type_bp, nonstrcexw_bp, roof_shape_bp, roofcovmat_bp, roofsysmat_bp, roofsystyp_bp, roof_conn_bp, floor_mat_bp, floor_type_bp, floor_conn_bp, foundn_sys_bp, build_type_bp, build_subtype_bp, vuln_bp, yr_built_vt, yr_built_vt1, object_id2, mat_type_src, mat_tech_src, mat_prop_src, llrs_src, llrs_duct_src, height_src, yr_built_src, occupy_src,occupy_dt_src, position_src, plan_shape_src, str_irreg_src, str_irreg_dt_src, str_irreg_type_src, nonstrcexw_src, roof_shape_src, roofcovmat_src, roofsysmat_src, roofsystyp_src, roof_conn_src, floor_mat_src, floor_type_src, floor_conn_src, foundn_sys_src, build_type_src, build_subtype_src, vuln_src) SELECT 1724, NULL, NULL, NULL, NULL, the_geom, 1724 , 'MAT99', 'MATT99', 'SPO', 'L99', 'DU99', 'H', 'Y99', 'OC99', 'OCCDT99', 'BP99', 'PLF99', 'IR99', 'IRP99', 'IRT99', 'EW99', 'R99', 'RMT99', 'RSM99', 'RST99', 'RCN99', 'F99', 'FT99', 'FWC99', 'FOS99', NULL, NULL, NULL, 'UNMODIFIED', NULL, NULL, 222, NULL, 1724, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, 1724, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL);
query="INSERT INTO asset.ve_object (survey_gid, source, the_geom, mat_type, mat_tech, mat_prop, llrs, llrs_duct, height, height2, yr_built, occupy, occupy_dt, position, plan_shape, str_irreg, str_irreg_dt, str_irreg_type,str_irreg_2,str_irreg_dt_2,str_irreg_type_2, nonstrcexw, roof_shape, roofcovmat, roofsysmat, roofsystyp, roof_conn, floor_mat, floor_type, floor_conn, foundn_sys, vuln,rrvs_status,height_1,height2_1,year_1) SELECT
survey.survey.gid,'{}',temporary_footprint_data.the_geom,'MAT99','MATT99', 'MO99', 'L99', 'DU99', 'H99', 'HB99','Y99', 'OC99', 'OCCDT99','BP99','PLF99','IR99', 'IRP99', 'IRT99','IRRE','IRPS','IRN','EW99','R99', 'RMT99', 'RSM99', 'RST99', 'RCN99', 'F99', 'FT99', 'FWC99', 'FOS99','V99','UNMODIFIED', 99, 99, 99 FROM temporary_footprint_data,survey.survey WHERE survey.survey.name='{}';".format(source,survey_name)

with open(sql_out,'a') as f:
    f.write(query)
    query = "DROP TABLE temporary_footprint_data"
    f.write(query)<commit_msg>modified fp2rem to commit only buildings (assuming osm data source with buildings field)<commit_after>###############################################
# Script that creates an SQL script to write
# footprints from a shapefile to a REM database
# Author: M.Haas <EMAIL>
# input: 1) shapefile with footprints
#        2) name of survey in REM db for which
#           these footprints should be used
#        3) optionally: source of footprints
# output: sql file
#############################
import os

shapefile = '/home/mhaas/PhD/DESTRESS/GRVS_Survey_France/osm_buildings_soultz_area_test.shp'
sql_out = 'fp2rem.sql'
source = 'OSM'
survey_name='destress_google_france2017'

#create a table with the shapefile layer containing postgis geometry
cmd = "shp2pgsql -s 4326 -W LATIN1 -g the_geom {} temporary_footprint_data > {}".format(shapefile,sql_out)
os.system(cmd)

#write inserts in ve_objects for each row in sql_out
query="INSERT INTO asset.ve_object (survey_gid, source, the_geom, mat_type, mat_tech, mat_prop, llrs, llrs_duct, height, height2, yr_built, occupy, occupy_dt, position, plan_shape, str_irreg, str_irreg_dt, str_irreg_type,str_irreg_2,str_irreg_dt_2,str_irreg_type_2, nonstrcexw, roof_shape, roofcovmat, roofsysmat, roofsystyp, roof_conn, floor_mat, floor_type, floor_conn, foundn_sys, vuln,rrvs_status,height_1,height2_1,year_1) SELECT survey.survey.gid,'{}',temporary_footprint_data.the_geom,'MAT99','MATT99', 'MO99', 'L99', 'DU99', 'H99', 'HB99','Y99', 'OC99', 'OCCDT99','BP99','PLF99','IR99', 'IRP99', 'IRT99','IRRE','IRPS','IRN','EW99','R99', 'RMT99', 'RSM99', 'RST99', 'RCN99', 'F99', 'FT99', 'FWC99', 'FOS99','V99','UNMODIFIED', 99, 99, 99 FROM temporary_footprint_data,survey.survey WHERE survey.survey.name='{}' AND temporary_footprint_data.building NOT LIKE '';".format(source,survey_name)

with open(sql_out,'a') as f:
    f.write(query)
    query = "DROP TABLE temporary_footprint_data"
    f.write(query)
{'fraction_non_alphanumeric': 0.1538190156994179, 'fraction_numerical': 0.040571529370259304, 'mean_word_length': 7.338235294117647, 'pattern_counts': {'":': 0, '<': 5, '<?xml version=': 0, '>': 7, 'https://': 0, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 0, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'dir': 'git-commits-cleaned', 'id': '4505429', 'n_tokens_mistral': 2329, 'n_tokens_neox': 2128, 'n_words': 518}