spatial-indexpyqgis

Create a spatial Index of the shapefiles of a schema in PostGIS using pyQGIS


I am creating a spatial Index for each shapefile I have and then I import them in a schema and the spatial index is missing. How do I create the spatial Index again in the schema?

layers_fimport = QgsProject.instance().mapLayers().values()

for a in layers_fimport: 
    a.setCrs(QgsCoordinateReferenceSystem(2056))
    a.dataProvider().createSpatialIndex()

for layer in layers_fimport:
    mytable=layer.name()
    con_string = "dbname='xxxx' host='xxxxx' port='xxx' user='xxxx' password='xxxxx' key=id type=POLYLINE table='"+project_name+"'." + mytable + " (geom)"
    err = QgsVectorLayerExporter.exportLayer(layer, con_string, 'postgres', QgsCoordinateReferenceSystem(2056), False)

Spatial_Index_missing


Solution

  • So after some research I found a way to use SQL through pyQGIS and I created the spatial indexes.

    import psycopg2
    # Create a Spatial Index for the tables in the schema
    #-----------------------------------------------------------------------
    connection = psycopg2.connect (dbname = "xxxx",
                               user = "xxxxx",
                               password = "xxxxxx",
                               host = "xxxxxxxx",
                               )
    cursor = connection.cursor()
    
    #--No capital letters allowed (schema + tables)
    #-------------------------------------------------------------- 
    
    cursor.execute("CREATE INDEX sidx_l_abluft_geom ON test.l_abluft(geom);")
    connection.commit()
    print("Query successful")