Line data Source code
1 : /******************************************************************************
2 : *
3 : * Project: OpenGIS Simple Features Reference Implementation
4 : * Purpose: Implements OGRPGResultLayer class, access the resultset from
5 : * a particular select query done via ExecuteSQL().
6 : * Author: Frank Warmerdam, warmerdam@pobox.com
7 : *
8 : ******************************************************************************
9 : * Copyright (c) 2002, Frank Warmerdam
10 : * Copyright (c) 2008-2013, Even Rouault <even dot rouault at spatialys.com>
11 : *
12 : * Permission is hereby granted, free of charge, to any person obtaining a
13 : * copy of this software and associated documentation files (the "Software"),
14 : * to deal in the Software without restriction, including without limitation
15 : * the rights to use, copy, modify, merge, publish, distribute, sublicense,
16 : * and/or sell copies of the Software, and to permit persons to whom the
17 : * Software is furnished to do so, subject to the following conditions:
18 : *
19 : * The above copyright notice and this permission notice shall be included
20 : * in all copies or substantial portions of the Software.
21 : *
22 : * THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND, EXPRESS
23 : * OR IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES OF MERCHANTABILITY,
24 : * FITNESS FOR A PARTICULAR PURPOSE AND NONINFRINGEMENT. IN NO EVENT SHALL
25 : * THE AUTHORS OR COPYRIGHT HOLDERS BE LIABLE FOR ANY CLAIM, DAMAGES OR OTHER
26 : * LIABILITY, WHETHER IN AN ACTION OF CONTRACT, TORT OR OTHERWISE, ARISING
27 : * FROM, OUT OF OR IN CONNECTION WITH THE SOFTWARE OR THE USE OR OTHER
28 : * DEALINGS IN THE SOFTWARE.
29 : ****************************************************************************/
30 :
31 : #include "cpl_conv.h"
32 : #include "ogr_pg.h"
33 :
34 : #define PQexec this_is_an_error
35 :
36 : /************************************************************************/
37 : /* OGRPGResultLayer() */
38 : /************************************************************************/
39 :
40 107 : OGRPGResultLayer::OGRPGResultLayer(OGRPGDataSource *poDSIn,
41 : const char *pszRawQueryIn,
42 107 : PGresult *hInitialResultIn)
43 107 : : pszRawStatement(CPLStrdup(pszRawQueryIn))
44 : {
45 107 : poDS = poDSIn;
46 :
47 107 : iNextShapeId = 0;
48 :
49 107 : BuildFullQueryStatement();
50 :
51 107 : ReadResultDefinition(hInitialResultIn);
52 :
53 : /* Find at which index the geometry column is */
54 : /* and prepare a request to identify not-nullable fields */
55 107 : int iGeomCol = -1;
56 214 : CPLString osRequest;
57 214 : std::map<std::pair<int, int>, int> oMapAttributeToFieldIndex;
58 :
59 503 : for (int iRawField = 0; iRawField < PQnfields(hInitialResultIn);
60 : iRawField++)
61 : {
62 716 : if (poFeatureDefn->GetGeomFieldCount() == 1 &&
63 320 : strcmp(PQfname(hInitialResultIn, iRawField),
64 320 : poFeatureDefn->GetGeomFieldDefn(0)->GetNameRef()) == 0)
65 : {
66 69 : iGeomCol = iRawField;
67 : }
68 :
69 396 : Oid tableOID = PQftable(hInitialResultIn, iRawField);
70 396 : int tableCol = PQftablecol(hInitialResultIn, iRawField);
71 396 : if (tableOID != InvalidOid && tableCol > 0)
72 : {
73 356 : if (!osRequest.empty())
74 288 : osRequest += " OR ";
75 356 : osRequest += "(attrelid = ";
76 356 : osRequest += CPLSPrintf("%d", tableOID);
77 356 : osRequest += " AND attnum = ";
78 356 : osRequest += CPLSPrintf("%d)", tableCol);
79 356 : oMapAttributeToFieldIndex[std::pair<int, int>(tableOID, tableCol)] =
80 : iRawField;
81 : }
82 : }
83 :
84 214 : CPLString osQuery(pszRawQueryIn);
85 : // Only a INNER JOIN can guarantee that the non-nullability of source
86 : // columns will be valid for the result of the join.
87 175 : if (!osRequest.empty() && osQuery.ifind("LEFT JOIN") == std::string::npos &&
88 243 : osQuery.ifind("RIGHT JOIN") == std::string::npos &&
89 68 : osQuery.ifind("OUTER JOIN") == std::string::npos)
90 : {
91 : osRequest = "SELECT attnum, attrelid FROM pg_attribute WHERE "
92 68 : "attnotnull = 't' AND (" +
93 68 : osRequest + ")";
94 68 : PGresult *hResult = OGRPG_PQexec(poDS->GetPGConn(), osRequest);
95 68 : if (hResult && PQresultStatus(hResult) == PGRES_TUPLES_OK)
96 : {
97 111 : for (int iCol = 0; iCol < PQntuples(hResult); iCol++)
98 : {
99 43 : const char *pszAttNum = PQgetvalue(hResult, iCol, 0);
100 43 : const char *pszAttRelid = PQgetvalue(hResult, iCol, 1);
101 0 : int iRawField = oMapAttributeToFieldIndex[std::pair<int, int>(
102 43 : atoi(pszAttRelid), atoi(pszAttNum))];
103 43 : const char *pszFieldname = PQfname(hInitialResultIn, iRawField);
104 43 : int iFieldIdx = poFeatureDefn->GetFieldIndex(pszFieldname);
105 43 : if (iFieldIdx >= 0)
106 9 : poFeatureDefn->GetFieldDefn(iFieldIdx)->SetNullable(FALSE);
107 : else
108 : {
109 34 : iFieldIdx = poFeatureDefn->GetGeomFieldIndex(pszFieldname);
110 34 : if (iFieldIdx >= 0)
111 1 : poFeatureDefn->GetGeomFieldDefn(iFieldIdx)->SetNullable(
112 : FALSE);
113 : }
114 : }
115 : }
116 68 : OGRPGClearResult(hResult);
117 : }
118 :
119 : /* Determine the table from which the geometry column is extracted */
120 107 : if (iGeomCol != -1)
121 : {
122 69 : Oid tableOID = PQftable(hInitialResultIn, iGeomCol);
123 69 : if (tableOID != InvalidOid)
124 : {
125 84 : CPLString osGetTableName;
126 : osGetTableName.Printf(
127 : "SELECT c.relname, n.nspname FROM pg_class c "
128 : "JOIN pg_namespace n ON c.relnamespace=n.oid WHERE c.oid = %d ",
129 42 : tableOID);
130 : PGresult *hTableNameResult =
131 42 : OGRPG_PQexec(poDS->GetPGConn(), osGetTableName);
132 42 : if (hTableNameResult &&
133 42 : PQresultStatus(hTableNameResult) == PGRES_TUPLES_OK)
134 : {
135 42 : if (PQntuples(hTableNameResult) > 0)
136 : {
137 42 : pszGeomTableName =
138 42 : CPLStrdup(PQgetvalue(hTableNameResult, 0, 0));
139 42 : pszGeomTableSchemaName =
140 42 : CPLStrdup(PQgetvalue(hTableNameResult, 0, 1));
141 : }
142 : }
143 42 : OGRPGClearResult(hTableNameResult);
144 : }
145 : }
146 107 : }
147 :
148 : /************************************************************************/
149 : /* ~OGRPGResultLayer() */
150 : /************************************************************************/
151 :
152 214 : OGRPGResultLayer::~OGRPGResultLayer()
153 :
154 : {
155 107 : CPLFree(pszRawStatement);
156 107 : CPLFree(pszGeomTableName);
157 107 : CPLFree(pszGeomTableSchemaName);
158 214 : }
159 :
160 : /************************************************************************/
161 : /* BuildFullQueryStatement() */
162 : /************************************************************************/
163 :
164 122 : void OGRPGResultLayer::BuildFullQueryStatement()
165 :
166 : {
167 122 : if (pszQueryStatement != nullptr)
168 : {
169 15 : CPLFree(pszQueryStatement);
170 15 : pszQueryStatement = nullptr;
171 : }
172 :
173 122 : const size_t nLen = strlen(pszRawStatement) + osWHERE.size() + 40;
174 122 : pszQueryStatement = static_cast<char *>(CPLMalloc(nLen));
175 :
176 122 : if (osWHERE.empty())
177 111 : strcpy(pszQueryStatement, pszRawStatement);
178 : else
179 11 : snprintf(pszQueryStatement, nLen,
180 : "SELECT * FROM (%s) AS ogrpgsubquery %s", pszRawStatement,
181 : osWHERE.c_str());
182 122 : }
183 :
184 : /************************************************************************/
185 : /* ResetReading() */
186 : /************************************************************************/
187 :
188 177 : void OGRPGResultLayer::ResetReading()
189 :
190 : {
191 177 : OGRPGLayer::ResetReading();
192 177 : }
193 :
194 : /************************************************************************/
195 : /* GetFeatureCount() */
196 : /************************************************************************/
197 :
198 36 : GIntBig OGRPGResultLayer::GetFeatureCount(int bForce)
199 :
200 : {
201 36 : if (TestCapability(OLCFastFeatureCount) == FALSE)
202 12 : return OGRPGLayer::GetFeatureCount(bForce);
203 :
204 24 : PGconn *hPGConn = poDS->GetPGConn();
205 24 : CPLString osCommand;
206 24 : int nCount = 0;
207 :
208 : osCommand.Printf("SELECT count(*) FROM (%s) AS ogrpgcount",
209 24 : pszQueryStatement);
210 :
211 24 : PGresult *hResult = OGRPG_PQexec(hPGConn, osCommand);
212 24 : if (hResult != nullptr && PQresultStatus(hResult) == PGRES_TUPLES_OK)
213 24 : nCount = atoi(PQgetvalue(hResult, 0, 0));
214 : else
215 0 : CPLDebug("PG", "%s; failed.", osCommand.c_str());
216 24 : OGRPGClearResult(hResult);
217 :
218 24 : return nCount;
219 : }
220 :
221 : /************************************************************************/
222 : /* TestCapability() */
223 : /************************************************************************/
224 :
225 121 : int OGRPGResultLayer::TestCapability(const char *pszCap)
226 :
227 : {
228 121 : GetLayerDefn();
229 :
230 121 : if (EQUAL(pszCap, OLCFastFeatureCount) ||
231 85 : EQUAL(pszCap, OLCFastSetNextByIndex))
232 : {
233 46 : OGRPGGeomFieldDefn *poGeomFieldDefn = nullptr;
234 46 : if (poFeatureDefn->GetGeomFieldCount() > 0)
235 : poGeomFieldDefn =
236 38 : poFeatureDefn->GetGeomFieldDefn(m_iGeomFieldFilter);
237 12 : return (m_poFilterGeom == nullptr || poGeomFieldDefn == nullptr ||
238 12 : poGeomFieldDefn->ePostgisType == GEOM_TYPE_GEOMETRY ||
239 92 : poGeomFieldDefn->ePostgisType == GEOM_TYPE_GEOGRAPHY) &&
240 86 : m_poAttrQuery == nullptr;
241 : }
242 75 : else if (EQUAL(pszCap, OLCFastSpatialFilter))
243 : {
244 0 : OGRPGGeomFieldDefn *poGeomFieldDefn = nullptr;
245 0 : if (poFeatureDefn->GetGeomFieldCount() > 0)
246 : poGeomFieldDefn =
247 0 : poFeatureDefn->GetGeomFieldDefn(m_iGeomFieldFilter);
248 0 : return (poGeomFieldDefn == nullptr ||
249 0 : poGeomFieldDefn->ePostgisType == GEOM_TYPE_GEOMETRY ||
250 0 : poGeomFieldDefn->ePostgisType == GEOM_TYPE_GEOGRAPHY) &&
251 0 : m_poAttrQuery == nullptr;
252 : }
253 :
254 75 : else if (EQUAL(pszCap, OLCFastGetExtent))
255 : {
256 13 : OGRPGGeomFieldDefn *poGeomFieldDefn = nullptr;
257 13 : if (poFeatureDefn->GetGeomFieldCount() > 0)
258 13 : poGeomFieldDefn = poFeatureDefn->GetGeomFieldDefn(0);
259 13 : return (poGeomFieldDefn == nullptr ||
260 20 : poGeomFieldDefn->ePostgisType == GEOM_TYPE_GEOMETRY) &&
261 20 : m_poAttrQuery == nullptr;
262 : }
263 62 : else if (EQUAL(pszCap, OLCStringsAsUTF8))
264 20 : return TRUE;
265 42 : else if (EQUAL(pszCap, OLCZGeometries))
266 6 : return TRUE;
267 : else
268 36 : return FALSE;
269 : }
270 :
271 : /************************************************************************/
272 : /* GetNextFeature() */
273 : /************************************************************************/
274 :
275 3625 : OGRFeature *OGRPGResultLayer::GetNextFeature()
276 :
277 : {
278 3625 : OGRPGGeomFieldDefn *poGeomFieldDefn = nullptr;
279 3625 : if (poFeatureDefn->GetGeomFieldCount() != 0)
280 3571 : poGeomFieldDefn = poFeatureDefn->GetGeomFieldDefn(m_iGeomFieldFilter);
281 :
282 : while (true)
283 : {
284 3758 : OGRFeature *poFeature = GetNextRawFeature();
285 3758 : if (poFeature == nullptr)
286 86 : return nullptr;
287 :
288 171 : if ((m_poFilterGeom == nullptr || poGeomFieldDefn == nullptr ||
289 171 : poGeomFieldDefn->ePostgisType == GEOM_TYPE_GEOMETRY ||
290 104 : poGeomFieldDefn->ePostgisType == GEOM_TYPE_GEOGRAPHY ||
291 7394 : FilterGeometry(poFeature->GetGeomFieldRef(m_iGeomFieldFilter))) &&
292 3618 : (m_poAttrQuery == nullptr || m_poAttrQuery->Evaluate(poFeature)))
293 3539 : return poFeature;
294 :
295 133 : delete poFeature;
296 133 : }
297 : }
298 :
299 : /************************************************************************/
300 : /* SetSpatialFilter() */
301 : /************************************************************************/
302 :
303 71 : void OGRPGResultLayer::SetSpatialFilter(int iGeomField, OGRGeometry *poGeomIn)
304 :
305 : {
306 138 : if (iGeomField < 0 || iGeomField >= GetLayerDefn()->GetGeomFieldCount() ||
307 67 : CPLAssertNotNull(GetLayerDefn()->GetGeomFieldDefn(iGeomField))
308 67 : ->GetType() == wkbNone)
309 : {
310 4 : if (iGeomField != 0)
311 : {
312 4 : CPLError(CE_Failure, CPLE_AppDefined,
313 : "Invalid geometry field index : %d", iGeomField);
314 : }
315 4 : return;
316 : }
317 67 : m_iGeomFieldFilter = iGeomField;
318 :
319 : OGRPGGeomFieldDefn *poGeomFieldDefn =
320 67 : poFeatureDefn->GetGeomFieldDefn(m_iGeomFieldFilter);
321 67 : if (InstallFilter(poGeomIn))
322 : {
323 29 : if (poGeomFieldDefn->ePostgisType == GEOM_TYPE_GEOMETRY ||
324 14 : poGeomFieldDefn->ePostgisType == GEOM_TYPE_GEOGRAPHY)
325 : {
326 15 : if (m_poFilterGeom != nullptr)
327 : {
328 : char szBox3D_1[128];
329 : char szBox3D_2[128];
330 11 : OGREnvelope sEnvelope;
331 :
332 11 : m_poFilterGeom->getEnvelope(&sEnvelope);
333 11 : if (poGeomFieldDefn->ePostgisType == GEOM_TYPE_GEOGRAPHY)
334 : {
335 0 : if (sEnvelope.MinX < -180.0)
336 0 : sEnvelope.MinX = -180.0;
337 0 : if (sEnvelope.MinY < -90.0)
338 0 : sEnvelope.MinY = -90.0;
339 0 : if (sEnvelope.MaxX > 180.0)
340 0 : sEnvelope.MaxX = 180.0;
341 0 : if (sEnvelope.MaxY > 90.0)
342 0 : sEnvelope.MaxY = 90.0;
343 : }
344 11 : CPLsnprintf(szBox3D_1, sizeof(szBox3D_1), "%.18g %.18g",
345 : sEnvelope.MinX, sEnvelope.MinY);
346 11 : CPLsnprintf(szBox3D_2, sizeof(szBox3D_2), "%.18g %.18g",
347 : sEnvelope.MaxX, sEnvelope.MaxY);
348 : osWHERE.Printf(
349 : "WHERE %s && %s('BOX3D(%s, %s)'::box3d,%d) ",
350 22 : OGRPGEscapeColumnName(poGeomFieldDefn->GetNameRef())
351 : .c_str(),
352 11 : (poDS->sPostGISVersion.nMajor >= 2) ? "ST_SetSRID"
353 : : "SetSRID",
354 22 : szBox3D_1, szBox3D_2, poGeomFieldDefn->nSRSId);
355 : }
356 : else
357 : {
358 4 : osWHERE = "";
359 : }
360 :
361 15 : BuildFullQueryStatement();
362 : }
363 :
364 29 : ResetReading();
365 : }
366 : }
367 :
368 : /************************************************************************/
369 : /* ResolveSRID() */
370 : /************************************************************************/
371 :
372 894 : void OGRPGResultLayer::ResolveSRID(const OGRPGGeomFieldDefn *poGFldDefn)
373 :
374 : {
375 : /* We have to get the SRID of the geometry column, so to be able */
376 : /* to do spatial filtering */
377 894 : int nSRSId = UNDETERMINED_SRID;
378 :
379 894 : if (poGFldDefn->ePostgisType == GEOM_TYPE_GEOGRAPHY)
380 : {
381 1 : if (!(poDS->sPostGISVersion.nMajor >= 3 ||
382 0 : (poDS->sPostGISVersion.nMajor == 2 &&
383 0 : poDS->sPostGISVersion.nMinor >= 2)))
384 : {
385 : // EPSG:4326 was a requirement for geography before PostGIS 2.2
386 0 : nSRSId = 4326;
387 : }
388 : }
389 :
390 894 : if (nSRSId == UNDETERMINED_SRID &&
391 894 : (poGFldDefn->ePostgisType == GEOM_TYPE_GEOMETRY ||
392 850 : poGFldDefn->ePostgisType == GEOM_TYPE_GEOGRAPHY))
393 : {
394 45 : if (pszGeomTableName != nullptr)
395 : {
396 32 : CPLString osName(pszGeomTableSchemaName);
397 16 : osName += ".";
398 16 : osName += pszGeomTableName;
399 : OGRPGLayer *poBaseLayer =
400 16 : cpl::down_cast<OGRPGLayer *>(poDS->GetLayerByName(osName));
401 16 : if (poBaseLayer)
402 : {
403 32 : int iBaseIdx = poBaseLayer->GetLayerDefn()->GetGeomFieldIndex(
404 16 : poGFldDefn->GetNameRef());
405 16 : if (iBaseIdx >= 0)
406 : {
407 : const OGRPGGeomFieldDefn *poBaseGFldDefn =
408 16 : poBaseLayer->GetLayerDefn()->GetGeomFieldDefn(iBaseIdx);
409 : poBaseGFldDefn
410 16 : ->GetSpatialRef(); /* To make sure nSRSId is resolved */
411 16 : nSRSId = poBaseGFldDefn->nSRSId;
412 : }
413 : }
414 : }
415 :
416 45 : if (nSRSId == UNDETERMINED_SRID)
417 : {
418 58 : CPLString osGetSRID;
419 :
420 29 : const char *psGetSRIDFct =
421 29 : poDS->sPostGISVersion.nMajor >= 2 ? "ST_SRID" : "getsrid";
422 :
423 29 : osGetSRID += "SELECT ";
424 29 : osGetSRID += psGetSRIDFct;
425 29 : osGetSRID += "(";
426 29 : osGetSRID += OGRPGEscapeColumnName(poGFldDefn->GetNameRef());
427 29 : if (poDS->sPostGISVersion.nMajor > 2 ||
428 0 : (poDS->sPostGISVersion.nMajor == 2 &&
429 0 : poDS->sPostGISVersion.nMinor >= 2))
430 29 : osGetSRID += "::geometry";
431 29 : osGetSRID += ") FROM (";
432 29 : osGetSRID += pszRawStatement;
433 29 : osGetSRID += ") AS ogrpggetsrid WHERE (";
434 29 : osGetSRID += OGRPGEscapeColumnName(poGFldDefn->GetNameRef());
435 29 : osGetSRID += " IS NOT NULL) LIMIT 1";
436 :
437 29 : PGresult *hSRSIdResult = OGRPG_PQexec(poDS->GetPGConn(), osGetSRID);
438 :
439 29 : nSRSId = poDS->GetUndefinedSRID();
440 :
441 29 : if (hSRSIdResult && PQresultStatus(hSRSIdResult) == PGRES_TUPLES_OK)
442 : {
443 29 : if (PQntuples(hSRSIdResult) > 0)
444 29 : nSRSId = atoi(PQgetvalue(hSRSIdResult, 0, 0));
445 : }
446 : else
447 : {
448 0 : CPLError(CE_Failure, CPLE_AppDefined, "%s",
449 0 : PQerrorMessage(poDS->GetPGConn()));
450 : }
451 :
452 29 : OGRPGClearResult(hSRSIdResult);
453 : }
454 : }
455 :
456 894 : poGFldDefn->nSRSId = nSRSId;
457 894 : }
|
