4

I would like to use the ST_SingleSidedBuffer SpatiaLite SQL function to create one-sided buffers around all lines in a layer. The following functions works fine, until the 64rd iteration. Then the error ERROR: Too many connections: max 64 occurs and the variable bufferLayer is empty. Is there a workaround in order to be able to use the ST_SingleSidedBuffer function more than 64 times?

I found this related thread that describes the SpatiaLite limitation in more detail.

def createBuffer(self, datasource, layer, bufferDistance, side):
    # Create memory layer
    memoryDriver = ogr.GetDriverByName('MEMORY')
    memoryDatasource = memoryDriver.CreateDataSource('memoryData')
    memoryLayer = memoryDatasource.CreateLayer('memoryLayer')

    # Create ID field in memory layer
    AttributeCalculation.createField(None, memoryLayer, "ID", ogr.OFTInteger, 10, None)

    # Loop over each feature in input layer
    for feature in layer:
        # Read ID and geometry from input feature
        geometry = feature.GetGeometryRef()
        IDValue = int(feature.GetField('ID'))

        # SQL query to create single sided buffers
        bufferLayer = datasource.ExecuteSQL("SELECT ST_SingleSidedBuffer(ST_GeomFromText('%s'), %.2f , %i)"
                                            % (geometry, bufferDistance, side),
                                            dialect = 'SQLite')

        # Add buffer feature to memory layer
        outFeature = ogr.Feature(memoryLayer.GetLayerDefn())
        bufferFeature = bufferLayer.GetFeature(0)
        bufferGeometry = bufferFeature.GetGeometryRef()
        outFeature.SetGeometry(bufferGeometry)
        outFeature.SetField('ID', IDValue)
        memoryLayer.CreateFeature(outFeature)

    return memoryLayer

enter link description here

  • I believe that you try to do something too complex. You do not need to loop but you can handle the whole layer with one SQL. If you used ogr2ogr you'd do it like ogr2ogr -f my_format -dialect SQLite -sql "SELECT ID, ST_SingleSidedBuffer(geometry, 10,1) AS geometry from my_layer" output.data input.data. trac.osgeo.org/gdal/browser/trunk/autotest/ogr/… from line 227 may be useful for Python. But if you want to loop you should either close the connections or re-use an existing. Unfortunately I can't tell how to do that. – user30184 May 8 '18 at 20:29
  • @user30184: I also thought about buffering the lines in a less complex way by not iterating over each feature. However, ST_SingleSidedBuffer requires a single linestring geometry as input, I tried providing a list with all geometries without success. Further, I need each buffer polygon to have the same ID as the line that is is generated for. So I concluded looping over each feature is inevitable here. Please correct me if not. Further, I am not sure if I understand the benefit of using ogr2ogr to call the sqlite function. Would it allow to run the function on a list of geometries? – Sophie Crommelinck May 9 '18 at 8:18
  • May I guess that you are not yet very experienced with SQL? I used the ogr2ogr as an example and for holding the proper SQL SELECT ID, ST_SingleSidedBuffer(geometry, 10,1). It does what you want for the whole layer: takes one be one ID and the corresponding geometry, keeps ID and creates single side buffered geometry and writes the results as new features into a new layer. – user30184 May 9 '18 at 8:31
  • @user30184 You're right that I am not very experienced with SQL and that an SQL query can return the geometries and attributes of a layer at once. However, in this example I still don't understand how this can be done since ST_SingleSidedBuffer requires a single linestring geometry as input. What would the parameter geometry in your example hold? Your suggestion is to run datasource.ExecuteSQL("SELECT ID, ST_SingleSidedBuffer(geometry, 10,1)", dialect='SQLite') to retrieve ID attributes and buffer geometries at once, right? – Sophie Crommelinck May 14 '18 at 6:20
  • SQL query SELECT ID, ST_SingleSidedBuffer(geometry, 10,1) selects an ID of the object in the source table and the geometry that belongs to that object with the selected ID (one geometry), and passes the geometry on to ST_SingleSidedBuffer function. The output is a row that contains the ID and the buffered geometry. Then the object with the next ID is processed in the same way. Finally you will have a new layer that contains all the IDs and their buffered geometries. The parameter "geometry" is the name of the geometry column in your source data, just an in the query that you have written. – user30184 May 14 '18 at 6:37
2

I solved it by deallocating the BufferLayer in the for-loop after the block # Add buffer feature to memory layer with:

datasource.ReleaseResultSet(bufferLayer)  

enter code here

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.