Skip to content

Commit 11b5092

Browse files
committedNov 6, 2015
[processing] allow output directly on PostGIS tables
1 parent 22ace3d commit 11b5092

File tree

8 files changed

+256
-21
lines changed

8 files changed

+256
-21
lines changed
 

‎python/plugins/processing/core/outputs.py

Lines changed: 1 addition & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -306,5 +306,5 @@ def getVectorWriter(self, fields, geomType, crs, options=None):
306306

307307
w = VectorWriter(self.value, self.encoding, fields, geomType,
308308
crs, options)
309-
self.memoryLayer = w.memLayer
309+
self.layer = w.layer
310310
return w

‎python/plugins/processing/gui/OutputSelectionPanel.py

Lines changed: 37 additions & 4 deletions
Original file line numberDiff line numberDiff line change
@@ -32,10 +32,12 @@
3232
from PyQt4.QtCore import QCoreApplication, QSettings
3333
from PyQt4.QtGui import QDialog, QMenu, QAction, QCursor, QFileDialog
3434
from qgis.gui import QgsEncodingFileDialog
35+
from qgis.core import *
3536

3637
from processing.core.ProcessingConfig import ProcessingConfig
3738
from processing.core.outputs import OutputVector
3839
from processing.core.outputs import OutputDirectory
40+
from processing.gui.PostgisTableSelector import PostgisTableSelector
3941

4042
pluginPath = os.path.split(os.path.dirname(__file__))[0]
4143
WIDGET, BASE = uic.loadUiType(
@@ -81,12 +83,41 @@ def selectOutput(self):
8183
self.tr('Save to memory layer'), self.btnSelect)
8284
actionSaveToMemory.triggered.connect(self.saveToMemory)
8385
popupMenu.addAction(actionSaveToMemory)
86+
actionSaveToPostGIS = QAction(
87+
self.tr('Save to PostGIS table...'), self.btnSelect)
88+
actionSaveToPostGIS.triggered.connect(self.saveToPostGIS)
89+
settings = QSettings()
90+
settings.beginGroup('/PostgreSQL/connections/')
91+
names = settings.childGroups()
92+
settings.endGroup()
93+
actionSaveToPostGIS.setEnabled(bool(names))
94+
popupMenu.addAction(actionSaveToPostGIS)
8495

8596
popupMenu.exec_(QCursor.pos())
8697

8798
def saveToTemporaryFile(self):
8899
self.leText.setText('')
89100

101+
def saveToPostGIS(self):
102+
dlg = PostgisTableSelector(self, self.output.name.lower())
103+
dlg.exec_()
104+
if dlg.connection:
105+
settings = QSettings()
106+
mySettings = '/PostgreSQL/connections/' + dlg.connection
107+
dbname = settings.value(mySettings + '/database')
108+
user = settings.value(mySettings + '/username')
109+
host = settings.value(mySettings + '/host')
110+
port = settings.value(mySettings + '/port')
111+
password = settings.value(mySettings + '/password')
112+
uri = QgsDataSourceURI()
113+
uri.setConnection(host, str(port), dbname, user, password)
114+
uri.setDataSource(dlg.schema, dlg.table, "the_geom")
115+
connInfo = uri.connectionInfo()
116+
(success, user, passwd ) = QgsCredentials.instance().get(connInfo, None, None)
117+
if success:
118+
QgsCredentials.instance().put(connInfo, user, passwd)
119+
self.leText.setText("postgis:" + uri.uri())
120+
90121
def saveToMemory(self):
91122
self.leText.setText('memory:')
92123

@@ -124,10 +155,8 @@ def selectFile(self):
124155

125156
def selectDirectory(self):
126157
lastDir = ''
127-
128-
dirName = QFileDialog.getExistingDirectory(self,
129-
self.tr('Select directory'), lastDir, QFileDialog.ShowDirsOnly)
130-
158+
dirName = QFileDialog.getExistingDirectory(self,self.tr('Select directory'),
159+
lastDir, QFileDialog.ShowDirsOnly)
131160
self.leText.setText(dirName)
132161

133162
def getValue(self):
@@ -136,10 +165,14 @@ def getValue(self):
136165
value = None
137166
elif fileName.startswith('memory:'):
138167
value = fileName
168+
elif fileName.startswith('postgis:'):
169+
value = fileName
139170
elif not os.path.isabs(fileName):
140171
value = ProcessingConfig.getSetting(
141172
ProcessingConfig.OUTPUT_FOLDER) + os.sep + fileName
142173
else:
143174
value = fileName
144175

176+
177+
145178
return value
Lines changed: 92 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,92 @@
1+
import os
2+
from PyQt4 import uic, QtCore, QtGui
3+
from processing.algs.qgis.postgis_utils import GeoDB
4+
from qgis.core import *
5+
from PyQt4.QtGui import QMessageBox
6+
7+
pluginPath = os.path.split(os.path.dirname(__file__))[0]
8+
WIDGET, BASE = uic.loadUiType(
9+
os.path.join(pluginPath, 'ui', 'DlgPostgisTableSelector.ui'))
10+
11+
12+
class PostgisTableSelector(BASE, WIDGET):
13+
14+
def __init__(self, parent, tablename):
15+
super(PostgisTableSelector, self).__init__(parent)
16+
self.connection = None
17+
self.table = None
18+
self.schema = None
19+
self.setupUi(self)
20+
settings = QtCore.QSettings()
21+
settings.beginGroup('/PostgreSQL/connections/')
22+
names = settings.childGroups()
23+
settings.endGroup()
24+
for n in names:
25+
item = ConnectionItem(n)
26+
self.treeConnections.addTopLevelItem(item)
27+
28+
def itemExpanded(item):
29+
try:
30+
item.populateSchemas()
31+
except:
32+
pass
33+
34+
self.treeConnections.itemExpanded.connect(itemExpanded)
35+
36+
self.textTableName.setText(tablename)
37+
38+
self.buttonBox.accepted.connect(self.okPressed)
39+
self.buttonBox.rejected.connect(self.cancelPressed)
40+
41+
def cancelPressed(self):
42+
self.close()
43+
44+
def okPressed(self):
45+
if self.textTableName.text().strip() == "":
46+
self.textTableName.setStyleSheet("QLineEdit{background: yellow}")
47+
return
48+
item = self.treeConnections.currentItem()
49+
if isinstance(item, ConnectionItem):
50+
QMessageBox.warning(self, "Wrong selection", "Select a schema item in the tree")
51+
return
52+
self.schema = item.text(0)
53+
self.table = self.textTableName.text().strip()
54+
self.connection = item.parent().text(0)
55+
self.close()
56+
57+
class ConnectionItem(QtGui.QTreeWidgetItem):
58+
59+
connIcon = QtGui.QIcon(os.path.dirname(__file__) + '/../images/postgis.png')
60+
schemaIcon = QtGui.QIcon(os.path.dirname(__file__) + '/../images/namespace.png')
61+
62+
def __init__(self, connection):
63+
QtGui.QTreeWidgetItem.__init__(self)
64+
self.setChildIndicatorPolicy(QtGui.QTreeWidgetItem.ShowIndicator)
65+
self.connection = connection
66+
self.setText(0, connection)
67+
self.setIcon(0, self.connIcon)
68+
69+
def populateSchemas(self):
70+
if self.childCount() != 0:
71+
return
72+
settings = QtCore.QSettings()
73+
connSettings = '/PostgreSQL/connections/' + self.connection
74+
database = settings.value(connSettings + '/database')
75+
user = settings.value(connSettings + '/username')
76+
host = settings.value(connSettings + '/host')
77+
port = settings.value(connSettings + '/port')
78+
passwd = settings.value(connSettings + '/password')
79+
uri = QgsDataSourceURI()
80+
uri.setConnection(host, str(port), database, user, passwd)
81+
connInfo = uri.connectionInfo()
82+
(success, user, passwd ) = QgsCredentials.instance().get(connInfo, None, None)
83+
if success:
84+
QgsCredentials.instance().put(connInfo, user, passwd)
85+
geodb = GeoDB(host, int(port), database, user, passwd)
86+
schemas = geodb.list_schemas()
87+
for oid, name, owner, perms in schemas:
88+
item = QtGui.QTreeWidgetItem()
89+
item.setText(0, name)
90+
item.setIcon(0, self.schemaIcon)
91+
self.addChild(item)
92+

‎python/plugins/processing/gui/Postprocessing.py

Lines changed: 2 additions & 3 deletions
Original file line numberDiff line numberDiff line change
@@ -59,9 +59,8 @@ def handleAlgorithmResults(alg, progress=None, showResults=True):
5959
continue
6060
if isinstance(out, (OutputRaster, OutputVector, OutputTable)):
6161
try:
62-
if out.value.startswith('memory:'):
63-
layer = out.memoryLayer
64-
QgsMapLayerRegistry.instance().addMapLayers([layer])
62+
if out.layer is not None:
63+
QgsMapLayerRegistry.instance().addMapLayers([out.layer])
6564
else:
6665
if ProcessingConfig.getSetting(
6766
ProcessingConfig.USE_FILENAME_AS_LAYER_NAME):
436 Bytes
Loading
361 Bytes
Loading

‎python/plugins/processing/tools/vector.py

Lines changed: 60 additions & 13 deletions
Original file line numberDiff line numberDiff line change
@@ -16,6 +16,7 @@
1616
* *
1717
***************************************************************************
1818
"""
19+
from processing.algs.qgis import postgis_utils
1920

2021
__author__ = 'Victor Olaya'
2122
__date__ = 'February 2013'
@@ -33,6 +34,9 @@
3334
from PyQt4.QtCore import QVariant, QSettings
3435
from qgis.core import QGis, QgsFields, QgsField, QgsGeometry, QgsRectangle, QgsSpatialIndex, QgsMapLayerRegistry, QgsMapLayer, QgsVectorLayer, QgsVectorFileWriter, QgsDistanceArea
3536
from processing.core.ProcessingConfig import ProcessingConfig
37+
from PyQt4 import QtSql
38+
from processing.core.GeoAlgorithmExecutionException import GeoAlgorithmExecutionException
39+
from qgis.core import *
3640

3741

3842
GEOM_TYPE_MAP = {
@@ -58,6 +62,13 @@
5862
QVariant.Int: "integer"
5963
}
6064

65+
TYPE_MAP_POSTGIS_LAYER = {
66+
QVariant.String: "VARCHAR",
67+
QVariant.Double: "REAL",
68+
QVariant.Int: "INTEGER",
69+
QVariant.Bool: "BOOLEAN"
70+
}
71+
6172

6273
def features(layer):
6374
"""This returns an iterator over features in a vector layer,
@@ -411,20 +422,21 @@ def bufferedBoundingBox(bbox, buffer_size):
411422
class VectorWriter:
412423

413424
MEMORY_LAYER_PREFIX = 'memory:'
425+
POSTGIS_LAYER_PREFIX = 'postgis:'
414426

415-
def __init__(self, fileName, encoding, fields, geometryType,
427+
def __init__(self, destination, encoding, fields, geometryType,
416428
crs, options=None):
417-
self.fileName = fileName
418-
self.isMemory = False
419-
self.memLayer = None
429+
self.destination = destination
430+
self.isNotFileBased = False
431+
self.layer = None
420432
self.writer = None
421433

422434
if encoding is None:
423435
settings = QSettings()
424436
encoding = settings.value('/Processing/encoding', 'System', type=str)
425437

426-
if self.fileName.startswith(self.MEMORY_LAYER_PREFIX):
427-
self.isMemory = True
438+
if self.destination.startswith(self.MEMORY_LAYER_PREFIX):
439+
self.isNotFileBased = True
428440

429441
uri = GEOM_TYPE_MAP[geometryType] + "?uuid=" + unicode(uuid.uuid4())
430442
if crs.isValid():
@@ -437,8 +449,44 @@ def __init__(self, fileName, encoding, fields, geometryType,
437449
if fieldsdesc:
438450
uri += '&' + '&'.join(fieldsdesc)
439451

440-
self.memLayer = QgsVectorLayer(uri, self.fileName, 'memory')
441-
self.writer = self.memLayer.dataProvider()
452+
self.layer = QgsVectorLayer(uri, self.destination, 'memory')
453+
self.writer = self.layer.dataProvider()
454+
elif self.destination.startswith(self.POSTGIS_LAYER_PREFIX):
455+
self.isNotFileBased = True
456+
uri = QgsDataSourceURI(self.destination[len(self.POSTGIS_LAYER_PREFIX):])
457+
connInfo = uri.connectionInfo()
458+
(success, user, passwd ) = QgsCredentials.instance().get(connInfo, None, None)
459+
if success:
460+
QgsCredentials.instance().put(connInfo, user, passwd)
461+
else:
462+
raise GeoAlgorithmExecutionException("Couldn't connect to database")
463+
print uri.uri()
464+
try:
465+
db = postgis_utils.GeoDB(host=uri.host(), port=int(uri.port()),
466+
dbname=uri.database(), user=user, passwd=passwd)
467+
except postgis_utils.DbError as e:
468+
raise GeoAlgorithmExecutionException(
469+
"Couldn't connect to database:\n%s" % e.message)
470+
471+
def _runSQL(sql):
472+
try:
473+
db._exec_sql_and_commit(unicode(sql))
474+
except postgis_utils.DbError as e:
475+
raise GeoAlgorithmExecutionException(
476+
'Error creating output PostGIS table:\n%s' % e.message)
477+
478+
fields = [_toQgsField(f) for f in fields]
479+
fieldsdesc = ",".join('%s %s' % (f.name(),
480+
TYPE_MAP_POSTGIS_LAYER.get(f.type(), "VARCHAR"))
481+
for f in fields)
482+
483+
_runSQL("CREATE TABLE %s.%s (%s)" % (uri.schema(), uri.table().lower(), fieldsdesc))
484+
_runSQL("SELECT AddGeometryColumn('{schema}', '{table}', 'the_geom', {srid}, '{typmod}', 2)".format(
485+
table=uri.table().lower(), schema=uri.schema(), srid=crs.authid().split(":")[-1],
486+
typmod=GEOM_TYPE_MAP[geometryType].upper()))
487+
488+
self.layer = QgsVectorLayer(uri.uri(), uri.table(), "postgres")
489+
self.writer = self.layer.dataProvider()
442490
else:
443491
formats = QgsVectorFileWriter.supportedFiltersAndFormats()
444492
OGRCodes = {}
@@ -448,21 +496,20 @@ def __init__(self, fileName, encoding, fields, geometryType,
448496
extension = extension[:extension.find(' ')]
449497
OGRCodes[extension] = value
450498

451-
extension = self.fileName[self.fileName.rfind('.') + 1:]
499+
extension = self.destination[self.destination.rfind('.') + 1:]
452500
if extension not in OGRCodes:
453501
extension = 'shp'
454-
self.filename = self.filename + 'shp'
502+
self.destination = self.destination + '.shp'
455503

456504
qgsfields = QgsFields()
457505
for field in fields:
458506
qgsfields.append(_toQgsField(field))
459507

460-
self.writer = QgsVectorFileWriter(
461-
self.fileName, encoding,
508+
self.writer = QgsVectorFileWriter(self.destination, encoding,
462509
qgsfields, geometryType, crs, OGRCodes[extension])
463510

464511
def addFeature(self, feature):
465-
if self.isMemory:
512+
if self.isNotFileBased:
466513
self.writer.addFeatures([feature])
467514
else:
468515
self.writer.addFeature(feature)
Lines changed: 64 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,64 @@
1+
<?xml version="1.0" encoding="UTF-8"?>
2+
<ui version="4.0">
3+
<class>Dialog</class>
4+
<widget class="QDialog" name="Dialog">
5+
<property name="geometry">
6+
<rect>
7+
<x>0</x>
8+
<y>0</y>
9+
<width>464</width>
10+
<height>395</height>
11+
</rect>
12+
</property>
13+
<property name="windowTitle">
14+
<string> output table</string>
15+
</property>
16+
<layout class="QVBoxLayout" name="verticalLayout">
17+
<item>
18+
<widget class="QLabel" name="label_3">
19+
<property name="text">
20+
<string>Select connection and schema</string>
21+
</property>
22+
</widget>
23+
</item>
24+
<item>
25+
<widget class="QTreeWidget" name="treeConnections">
26+
<attribute name="headerVisible">
27+
<bool>false</bool>
28+
</attribute>
29+
<column>
30+
<property name="text">
31+
<string notr="true">1</string>
32+
</property>
33+
</column>
34+
</widget>
35+
</item>
36+
<item>
37+
<layout class="QHBoxLayout" name="horizontalLayout">
38+
<item>
39+
<widget class="QLabel" name="label_2">
40+
<property name="text">
41+
<string>Table name</string>
42+
</property>
43+
</widget>
44+
</item>
45+
<item>
46+
<widget class="QLineEdit" name="textTableName"/>
47+
</item>
48+
</layout>
49+
</item>
50+
<item>
51+
<widget class="QDialogButtonBox" name="buttonBox">
52+
<property name="orientation">
53+
<enum>Qt::Horizontal</enum>
54+
</property>
55+
<property name="standardButtons">
56+
<set>QDialogButtonBox::Cancel|QDialogButtonBox::Ok</set>
57+
</property>
58+
</widget>
59+
</item>
60+
</layout>
61+
</widget>
62+
<resources/>
63+
<connections/>
64+
</ui>

0 commit comments

Comments
 (0)
Please sign in to comment.