I have a Postgres DB with a lot of lines that are too long and so now I want to split those lines into smaller ones.
This is why I started writing a little python script for that job. Basically it fetches my lines like this:
cursor.execute("SELECT ogc_fid, id, ST_AsGeoJSON(wkb_geometry) as geometry, ST_Length(wkb_geometry) as length, height FROM cont OFFSET " + str(offset) + " LIMIT " + str(limit) )
Here's my first problem. In order to further work with the data I have to select the geometry as JSON. I'll come to that later.
Now I iterate over the lines and make something like this:
sqlCommand = "INSERT INTO " + table + " (ogc_fid, id, wkb_geometry, height) VALUES(" + ogc_fid + ", " + row_id + ", ST_Line_Substring(ST_GeomFromGeoJSON(\'" + geometry + "\'), " + str(rangeFrom) + ", " + str(min(rangeFrom + stepWidth, 1)) + "), " + str(height) + ");" cursor.execute(sqlCommand)
So if I didn't use
ST_AsGeoJSON in the select, I couldn't append it to my command cause it can only append a string.
That way my whole script is very slow. It always has to convert my geometries into JSON and then back from JSON.
Is there a datatype that python can handle?
Is there a more efficient way of dealing with geometry fields?
I heard of GeoDjango. Could that help here?