Skip to content

Commit bb5f0ff

Browse files
alexbruyalexbruy
committedJan 11, 2016
Merge pull request #2650 from mhugo/processing_vlayer
[processing] new algorithm: Execute SQL with virtual layer
·
ltr-3_40archive/master_2
2 parents 716664e + 9472c98 commit bb5f0ff

File tree

3 files changed

+149
-13
lines changed

3 files changed

+149
-13
lines changed
 

‎python/plugins/processing/algs/qgis/ExecuteSQL.py

Lines changed: 140 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,140 @@
1+
# -*- coding: utf-8 -*-
2+
3+
"""
4+
***************************************************************************
5+
ExecuteSQL.py -- use virtual layers to execute SQL on any sources
6+
---------------------
7+
Date : Jan 2016
8+
Copyright : (C) 2016 by Hugo Mercier
9+
Email : hugo dot mercier at oslandia dot com
10+
***************************************************************************
11+
* *
12+
* This program is free software; you can redistribute it and/or modify *
13+
* it under the terms of the GNU General Public License as published by *
14+
* the Free Software Foundation; either version 2 of the License, or *
15+
* (at your option) any later version. *
16+
* *
17+
***************************************************************************
18+
"""
19+
20+
__author__ = 'Hugo Mercier'
21+
__date__ = 'January 2016'
22+
__copyright__ = '(C) 2016, Hugo Mercier'
23+
24+
# This will get replaced with a git SHA1 when you do a git archive323
25+
26+
__revision__ = '$Format:%H$'
27+
28+
from qgis.core import QGis, QgsGeometry, QgsFeature, QgsVirtualLayerDefinition, QgsVectorLayer, QgsCoordinateReferenceSystem, QgsWKBTypes
29+
from processing.core.GeoAlgorithm import GeoAlgorithm
30+
from processing.core.GeoAlgorithmExecutionException import GeoAlgorithmExecutionException
31+
from processing.core.parameters import ParameterVector, ParameterString, ParameterMultipleInput, ParameterBoolean, ParameterCrs, ParameterSelection
32+
from processing.core.outputs import OutputVector
33+
from processing.tools import dataobjects, vector
34+
35+
36+
class ExecuteSQL(GeoAlgorithm):
37+
38+
""" This algorithm allows to execute an SQL query on a set of input
39+
vector layers thanks to the virtual layer provider
40+
"""
41+
42+
INPUT_DATASOURCES = 'INPUT_DATASOURCES'
43+
INPUT_QUERY = 'INPUT_QUERY'
44+
INPUT_UID_FIELD = 'INPUT_UID_FIELD'
45+
INPUT_GEOMETRY_FIELD = 'INPUT_GEOMETRY_FIELD'
46+
INPUT_GEOMETRY_TYPE = 'INPUT_GEOMETRY_TYPE'
47+
INPUT_GEOMETRY_CRS = 'INPUT_GEOMETRY_CRS'
48+
OUTPUT_LAYER = 'OUTPUT_LAYER'
49+
50+
def defineCharacteristics(self):
51+
self.name, self.i18n_name = self.trAlgorithm('Execute SQL')
52+
self.group, self.i18n_group = self.trAlgorithm('Vector general tools')
53+
54+
self.addParameter(ParameterMultipleInput(name=self.INPUT_DATASOURCES,
55+
description=self.tr('Additional input datasources (called input1, .., inputN in the query)'),
56+
datatype=ParameterMultipleInput.TYPE_VECTOR_ANY,
57+
optional=True))
58+
59+
self.addParameter(ParameterString(name=self.INPUT_QUERY,
60+
description=self.tr('SQL query'),
61+
multiline=True))
62+
63+
self.addParameter(ParameterString(name=self.INPUT_UID_FIELD,
64+
description=self.tr('Unique identifier field'), optional=True))
65+
66+
self.addParameter(ParameterString(name=self.INPUT_GEOMETRY_FIELD,
67+
description=self.tr('Geometry field'), optional=True))
68+
69+
self.geometryTypes = [
70+
self.tr('Autodetect'),
71+
self.tr('No geometry'),
72+
'Point',
73+
'LineString',
74+
'Polygon',
75+
'MultiPoint',
76+
'MultiLineString',
77+
'MultiPolygon']
78+
self.addParameter(ParameterSelection(self.INPUT_GEOMETRY_TYPE,
79+
self.tr('Geometry type'), self.geometryTypes, optional=True))
80+
81+
self.addParameter(ParameterCrs(self.INPUT_GEOMETRY_CRS,
82+
self.tr('CRS'), optional=True))
83+
84+
self.addOutput(OutputVector(self.OUTPUT_LAYER, self.tr('Output')))
85+
86+
def processAlgorithm(self, progress):
87+
layers = self.getParameterValue(self.INPUT_DATASOURCES)
88+
query = self.getParameterValue(self.INPUT_QUERY)
89+
uid_field = self.getParameterValue(self.INPUT_UID_FIELD)
90+
geometry_field = self.getParameterValue(self.INPUT_GEOMETRY_FIELD)
91+
geometry_type = self.getParameterValue(self.INPUT_GEOMETRY_TYPE)
92+
geometry_crs = self.getParameterValue(self.INPUT_GEOMETRY_CRS)
93+
94+
df = QgsVirtualLayerDefinition()
95+
layerIdx = 1
96+
if layers:
97+
for layerSource in layers.split(';'):
98+
layer = dataobjects.getObjectFromUri(layerSource)
99+
if layer:
100+
df.addSource('input{}'.format(layerIdx), layer.id())
101+
102+
if query == '':
103+
raise GeoAlgorithmExecutionException(
104+
self.tr('Empty SQL. Please enter valid SQL expression and try again.'))
105+
else:
106+
df.setQuery(query)
107+
108+
if uid_field:
109+
df.setUid(uid_field)
110+
111+
if geometry_type == 1: # no geometry
112+
df.setGeometryWkbType(QgsWKBTypes.NoGeometry)
113+
else:
114+
if geometry_field:
115+
df.setGeometryField(geometry_field)
116+
if geometry_type > 1:
117+
df.setGeometryWkbType(geometry_type - 1)
118+
if geometry_crs:
119+
crs = QgsCoordinateReferenceSystem(geometry_crs)
120+
if crs.isValid():
121+
df.setGeometrySrid(crs.postgisSrid())
122+
123+
vLayer = QgsVectorLayer(df.toString(), "temp_vlayer", "virtual")
124+
if not vLayer.isValid():
125+
raise GeoAlgorithmExecutionException(vLayer.dataProvider().error().message())
126+
127+
writer = self.getOutputFromName(self.OUTPUT_LAYER).getVectorWriter(
128+
vLayer.pendingFields().toList(),
129+
# create a point layer (without any points) if 'no geometry' is chosen
130+
vLayer.wkbType() if geometry_type != 1 else 1,
131+
vLayer.crs())
132+
133+
features = vector.features(vLayer)
134+
outFeat = QgsFeature()
135+
for inFeat in features:
136+
outFeat.setAttributes(inFeat.attributes())
137+
if geometry_type != 1:
138+
outFeat.setGeometry(inFeat.geometry())
139+
writer.addFeature(outFeat)
140+
del writer

‎python/plugins/processing/algs/qgis/QGISAlgorithmProvider.py

Lines changed: 2 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -133,6 +133,7 @@
133133
from OrientedMinimumBoundingBox import OrientedMinimumBoundingBox
134134
from Smooth import Smooth
135135
from ReverseLineDirection import ReverseLineDirection
136+
from ExecuteSQL import ExecuteSQL
136137

137138
pluginPath = os.path.normpath(os.path.join(
138139
os.path.split(os.path.dirname(__file__))[0], os.pardir))
@@ -182,7 +183,7 @@ def __init__(self):
182183
SplitLinesWithLines(), CreateConstantRaster(),
183184
FieldsMapper(), SelectByAttributeSum(), Datasources2Vrt(),
184185
CheckValidity(), OrientedMinimumBoundingBox(), Smooth(),
185-
ReverseLineDirection()
186+
ReverseLineDirection(), ExecuteSQL()
186187
]
187188

188189
if hasMatplotlib:

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

Lines changed: 7 additions & 12 deletions
Original file line numberDiff line numberDiff line change
@@ -248,18 +248,13 @@ def getObjectFromUri(uri, forceLoad=True):
248248
settings.setValue('/Projections/defaultBehaviour', '')
249249

250250
# If is not opened, we open it
251-
layer = QgsVectorLayer(uri, uri, 'ogr')
252-
if layer.isValid():
253-
if prjSetting:
254-
settings.setValue('/Projections/defaultBehaviour', prjSetting)
255-
_loadedLayers[normalizeLayerSource(layer.source())] = layer
256-
return layer
257-
layer = QgsVectorLayer(uri, uri, 'postgres')
258-
if layer.isValid():
259-
if prjSetting:
260-
settings.setValue('/Projections/defaultBehaviour', prjSetting)
261-
_loadedLayers[normalizeLayerSource(layer.source())] = layer
262-
return layer
251+
for provider in ['ogr', 'postgres', 'spatialite', 'virtual']:
252+
layer = QgsVectorLayer(uri, uri, provider)
253+
if layer.isValid():
254+
if prjSetting:
255+
settings.setValue('/Projections/defaultBehaviour', prjSetting)
256+
_loadedLayers[normalizeLayerSource(layer.source())] = layer
257+
return layer
263258
layer = QgsRasterLayer(uri, uri)
264259
if layer.isValid():
265260
if prjSetting:

0 commit comments

Comments
 (0)
Please sign in to comment.