class PostGISExecuteSQL(GeoAlgorithm):
DATABASE = 'DATABASE'
SQL = 'SQL'
def processAlgorithm(self, progress):
connection = self.getParameterValue(self.DATABASE)
settings = QSettings()
mySettings = '/PostgreSQL/connections/' + connection
try:
database = settings.value(mySettings + '/database')
username = settings.value(mySettings + '/username')
host = settings.value(mySettings + '/host')
port = settings.value(mySettings + '/port', type=int)
password = settings.value(mySettings + '/password')
except Exception, e:
raise GeoAlgorithmExecutionException(
self.tr('Wrong database connection name: %s' % connection))
try:
self.db = postgis_utils.GeoDB(host=host,
port=port,
dbname=database,
user=username,
passwd=password)
except postgis_utils.DbError, e:
raise GeoAlgorithmExecutionException(
self.tr("Couldn't connect to database:\n%s" % e.message))
def processAlgorithm(self, progress):
connection = self.getParameterValue(self.DATABASE)
settings = QSettings()
mySettings = '/PostgreSQL/connections/' + connection
try:
database = settings.value(mySettings + '/database')
username = settings.value(mySettings + '/username')
host = settings.value(mySettings + '/host')
port = settings.value(mySettings + '/port', type=int)
password = settings.value(mySettings + '/password')
except Exception as e:
raise GeoAlgorithmExecutionException(
self.tr('Wrong database connection name: %s' % connection))
try:
self.db = postgis_utils.GeoDB(host=host,
port=port,
dbname=database,
user=username,
passwd=password)
except postgis_utils.DbError as e:
raise GeoAlgorithmExecutionException(
self.tr("Couldn't connect to database:\n%s" % e.message))
sql = self.getParameterValue(self.SQL).replace('\n', ' ')
try:
self.db._exec_sql_and_commit(unicode(sql))
except postgis_utils.DbError as e:
raise GeoAlgorithmExecutionException(
self.tr('Error executing SQL:\n%s' % e.message))
class ImportIntoPostGIS(GeoAlgorithm):
DATABASE = 'DATABASE'
TABLENAME = 'TABLENAME'
SCHEMA = 'SCHEMA'
INPUT = 'INPUT'
OVERWRITE = 'OVERWRITE'
CREATEINDEX = 'CREATEINDEX'
GEOMETRY_COLUMN = 'GEOMETRY_COLUMN'
LOWERCASE_NAMES = 'LOWERCASE_NAMES'
DROP_STRING_LENGTH = 'DROP_STRING_LENGTH'
PRIMARY_KEY = 'PRIMARY_KEY'
def processAlgorithm(self, progress):
connection = self.DB_CONNECTIONS[self.getParameterValue(self.DATABASE)]
schema = self.getParameterValue(self.SCHEMA)
overwrite = self.getParameterValue(self.OVERWRITE)
createIndex = self.getParameterValue(self.CREATEINDEX)
convertLowerCase = self.getParameterValue(self.LOWERCASE_NAMES)
dropStringLength = self.getParameterValue(self.DROP_STRING_LENGTH)
primaryKeyField = self.getParameterValue(self.PRIMARY_KEY)
settings = QSettings()
mySettings = '/PostgreSQL/connections/' + connection
try:
database = settings.value(mySettings + '/database')
username = settings.value(mySettings + '/username')
host = settings.value(mySettings + '/host')
port = settings.value(mySettings + '/port', type=int)
password = settings.value(mySettings + '/password')
except Exception, e:
raise GeoAlgorithmExecutionException(
self.tr('Wrong database connection name: %s' % connection))
layerUri = self.getParameterValue(self.INPUT)
layer = dataobjects.getObjectFromUri(layerUri)
table = self.getParameterValue(self.TABLENAME).strip()
if table == '':
table = layer.name().lower()
table.replace(' ', '')
providerName = 'postgres'
try:
db = postgis_utils.GeoDB(host=host,
port=port,
dbname=database,
user=username,
passwd=password)
except postgis_utils.DbError, e:
raise GeoAlgorithmExecutionException(
self.tr("Couldn't connect to database:\n%s" % e.message))
def __init__(self,
destination,
encoding,
fields,
geometryType,
crs,
options=None):
self.destination = destination
self.isNotFileBased = False
self.layer = None
self.writer = None
if encoding is None:
settings = QSettings()
encoding = settings.value('/Processing/encoding',
'System',
type=str)
if self.destination.startswith(self.MEMORY_LAYER_PREFIX):
self.isNotFileBased = True
uri = GEOM_TYPE_MAP[geometryType] + "?uuid=" + unicode(
uuid.uuid4())
if crs.isValid():
uri += '&crs=' + crs.authid()
fieldsdesc = []
for f in fields:
qgsfield = _toQgsField(f)
fieldsdesc.append(
'field=%s:%s' %
(qgsfield.name(),
TYPE_MAP_MEMORY_LAYER.get(qgsfield.type(), "string")))
if fieldsdesc:
uri += '&' + '&'.join(fieldsdesc)
self.layer = QgsVectorLayer(uri, self.destination, 'memory')
self.writer = self.layer.dataProvider()
elif self.destination.startswith(self.POSTGIS_LAYER_PREFIX):
self.isNotFileBased = True
uri = QgsDataSourceURI(
self.destination[len(self.POSTGIS_LAYER_PREFIX):])
connInfo = uri.connectionInfo()
(success, user,
passwd) = QgsCredentials.instance().get(connInfo, None, None)
if success:
QgsCredentials.instance().put(connInfo, user, passwd)
else:
raise GeoAlgorithmExecutionException(
"Couldn't connect to database")
print uri.uri()
try:
db = postgis_utils.GeoDB(host=uri.host(),
port=int(uri.port()),
dbname=uri.database(),
user=user,
passwd=passwd)
except postgis_utils.DbError as e:
raise GeoAlgorithmExecutionException(
"Couldn't connect to database:\n%s" % e.message)
def _runSQL(sql):
try:
db._exec_sql_and_commit(unicode(sql))
except postgis_utils.DbError as e:
raise GeoAlgorithmExecutionException(
'Error creating output PostGIS table:\n%s' % e.message)
fields = [_toQgsField(f) for f in fields]
fieldsdesc = ",".join(
'%s %s' %
(f.name(), TYPE_MAP_POSTGIS_LAYER.get(f.type(), "VARCHAR"))
for f in fields)
_runSQL("CREATE TABLE %s.%s (%s)" %
(uri.schema(), uri.table().lower(), fieldsdesc))
_runSQL(
"SELECT AddGeometryColumn('{schema}', '{table}', 'the_geom', {srid}, '{typmod}', 2)"
.format(table=uri.table().lower(),
schema=uri.schema(),
srid=crs.authid().split(":")[-1],
typmod=GEOM_TYPE_MAP[geometryType].upper()))
self.layer = QgsVectorLayer(uri.uri(), uri.table(), "postgres")
self.writer = self.layer.dataProvider()
elif self.destination.startswith(self.SPATIALITE_LAYER_PREFIX):
self.isNotFileBased = True
uri = QgsDataSourceURI(
self.destination[len(self.SPATIALITE_LAYER_PREFIX):])
print uri.uri()
try:
db = spatialite_utils.GeoDB(uri=uri)
except spatialite_utils.DbError as e:
raise GeoAlgorithmExecutionException(
"Couldn't connect to database:\n%s" % e.message)
def _runSQL(sql):
try:
db._exec_sql_and_commit(unicode(sql))
except spatialite_utils.DbError as e:
raise GeoAlgorithmExecutionException(
'Error creating output Spatialite table:\n%s' %
unicode(e))
fields = [_toQgsField(f) for f in fields]
fieldsdesc = ",".join(
'%s %s' %
(f.name(), TYPE_MAP_SPATIALITE_LAYER.get(f.type(), "VARCHAR"))
for f in fields)
_runSQL("DROP TABLE IF EXISTS %s" % uri.table().lower())
_runSQL("CREATE TABLE %s (%s)" % (uri.table().lower(), fieldsdesc))
_runSQL(
"SELECT AddGeometryColumn('{table}', 'the_geom', {srid}, '{typmod}', 2)"
.format(table=uri.table().lower(),
srid=crs.authid().split(":")[-1],
typmod=GEOM_TYPE_MAP[geometryType].upper()))
self.layer = QgsVectorLayer(uri.uri(), uri.table(), "spatialite")
self.writer = self.layer.dataProvider()
else:
formats = QgsVectorFileWriter.supportedFiltersAndFormats()
OGRCodes = {}
for (key, value) in formats.items():
extension = unicode(key)
extension = extension[extension.find('*.') + 2:]
extension = extension[:extension.find(' ')]
OGRCodes[extension] = value
extension = self.destination[self.destination.rfind('.') + 1:]
if extension not in OGRCodes:
extension = 'shp'
self.destination = self.destination + '.shp'
qgsfields = QgsFields()
for field in fields:
qgsfields.append(_toQgsField(field))
self.writer = QgsVectorFileWriter(self.destination, encoding,
qgsfields, geometryType, crs,
OGRCodes[extension])
def processAlgorithm(self, progress):
connection = self.DB_CONNECTIONS[self.getParameterValue(self.DATABASE)]
schema = self.getParameterValue(self.SCHEMA)
overwrite = self.getParameterValue(self.OVERWRITE)
createIndex = self.getParameterValue(self.CREATEINDEX)
convertLowerCase = self.getParameterValue(self.LOWERCASE_NAMES)
dropStringLength = self.getParameterValue(self.DROP_STRING_LENGTH)
primaryKeyField = self.getParameterValue(self.PRIMARY_KEY)
settings = QSettings()
mySettings = '/PostgreSQL/connections/' + connection
try:
database = settings.value(mySettings + '/database')
username = settings.value(mySettings + '/username')
host = settings.value(mySettings + '/host')
port = settings.value(mySettings + '/port', type=int)
password = settings.value(mySettings + '/password')
except Exception as e:
raise GeoAlgorithmExecutionException(
self.tr('Wrong database connection name: %s' % connection))
layerUri = self.getParameterValue(self.INPUT)
layer = dataobjects.getObjectFromUri(layerUri)
table = self.getParameterValue(self.TABLENAME).strip()
if table == '':
table = layer.name()
table = table.replace(' ', '').lower()
providerName = 'postgres'
try:
db = postgis_utils.GeoDB(host=host,
port=port,
dbname=database,
user=username,
passwd=password)
except postgis_utils.DbError as e:
raise GeoAlgorithmExecutionException(
self.tr("Couldn't connect to database:\n%s" % e.message))
geomColumn = self.getParameterValue(self.GEOMETRY_COLUMN)
if not geomColumn:
geomColumn = 'the_geom'
options = {}
if overwrite:
options['overwrite'] = True
if convertLowerCase:
options['lowercaseFieldNames'] = True
geomColumn = geomColumn.lower()
if dropStringLength:
options['dropStringConstraints'] = True
#clear geometry column for non-geometry tables
if not layer.hasGeometryType():
geomColumn = None
uri = QgsDataSourceURI()
uri.setConnection(host, unicode(port), database, username, password)
if primaryKeyField:
uri.setDataSource(schema, table, geomColumn, '', primaryKeyField)
else:
uri.setDataSource(schema, table, geomColumn, '')
(ret, errMsg) = QgsVectorLayerImport.importLayer(
layer,
uri.uri(),
providerName,
self.crs,
False,
False,
options,
)
if ret != 0:
raise GeoAlgorithmExecutionException(
self.tr('Error importing to PostGIS\n%s' % errMsg))
if geomColumn and createIndex:
db.create_spatial_index(table, schema, geomColumn)
db.vacuum_analyze(table, schema)
