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 : * SPDX-License-Identifier: MIT
13 : ****************************************************************************/
14 :
15 : #include "cpl_conv.h"
16 : #include "ogr_pg.h"
17 : #include "ogr_p.h"
18 :
19 : #include <algorithm>
20 : #include <limits>
21 :
22 : #define PQexec this_is_an_error
23 :
24 : /************************************************************************/
25 : /* OGRPGResultLayer() */
26 : /************************************************************************/
27 :
28 128 : OGRPGResultLayer::OGRPGResultLayer(OGRPGDataSource *poDSIn,
29 : const char *pszRawQueryIn,
30 128 : PGresult *hInitialResultIn)
31 128 : : pszRawStatement(CPLStrdup(pszRawQueryIn))
32 : {
33 128 : poDS = poDSIn;
34 :
35 128 : iNextShapeId = 0;
36 :
37 128 : BuildFullQueryStatement();
38 :
39 128 : ReadResultDefinition(hInitialResultIn);
40 :
41 : /* Find at which index the geometry column is */
42 : /* and prepare a request to identify not-nullable fields */
43 128 : int iGeomCol = -1;
44 256 : CPLString osRequest;
45 256 : std::map<std::pair<int, int>, int> oMapAttributeToFieldIndex;
46 :
47 559 : for (int iRawField = 0; iRawField < PQnfields(hInitialResultIn);
48 : iRawField++)
49 : {
50 767 : if (poFeatureDefn->GetGeomFieldCount() == 1 &&
51 336 : strcmp(PQfname(hInitialResultIn, iRawField),
52 336 : poFeatureDefn->GetGeomFieldDefn(0)->GetNameRef()) == 0)
53 : {
54 77 : iGeomCol = iRawField;
55 : }
56 :
57 431 : Oid tableOID = PQftable(hInitialResultIn, iRawField);
58 431 : int tableCol = PQftablecol(hInitialResultIn, iRawField);
59 431 : if (tableOID != InvalidOid && tableCol > 0)
60 : {
61 391 : if (!osRequest.empty())
62 302 : osRequest += " OR ";
63 391 : osRequest += "(attrelid = ";
64 391 : osRequest += CPLSPrintf("%d", tableOID);
65 391 : osRequest += " AND attnum = ";
66 391 : osRequest += CPLSPrintf("%d)", tableCol);
67 391 : oMapAttributeToFieldIndex[std::pair<int, int>(tableOID, tableCol)] =
68 : iRawField;
69 : }
70 : }
71 :
72 256 : CPLString osQuery(pszRawQueryIn);
73 : // Only a INNER JOIN can guarantee that the non-nullability of source
74 : // columns will be valid for the result of the join.
75 217 : if (!osRequest.empty() && osQuery.ifind("LEFT JOIN") == std::string::npos &&
76 306 : osQuery.ifind("RIGHT JOIN") == std::string::npos &&
77 89 : osQuery.ifind("OUTER JOIN") == std::string::npos)
78 : {
79 : osRequest = "SELECT attnum, attrelid FROM pg_attribute WHERE "
80 89 : "attnotnull = 't' AND (" +
81 89 : osRequest + ")";
82 89 : PGresult *hResult = OGRPG_PQexec(poDS->GetPGConn(), osRequest);
83 89 : if (hResult && PQresultStatus(hResult) == PGRES_TUPLES_OK)
84 : {
85 146 : for (int iCol = 0; iCol < PQntuples(hResult); iCol++)
86 : {
87 57 : const char *pszAttNum = PQgetvalue(hResult, iCol, 0);
88 57 : const char *pszAttRelid = PQgetvalue(hResult, iCol, 1);
89 0 : int iRawField = oMapAttributeToFieldIndex[std::pair<int, int>(
90 57 : atoi(pszAttRelid), atoi(pszAttNum))];
91 57 : const char *pszFieldname = PQfname(hInitialResultIn, iRawField);
92 57 : int iFieldIdx = poFeatureDefn->GetFieldIndex(pszFieldname);
93 57 : if (iFieldIdx >= 0)
94 15 : poFeatureDefn->GetFieldDefn(iFieldIdx)->SetNullable(FALSE);
95 : else
96 : {
97 42 : iFieldIdx = poFeatureDefn->GetGeomFieldIndex(pszFieldname);
98 42 : if (iFieldIdx >= 0)
99 1 : poFeatureDefn->GetGeomFieldDefn(iFieldIdx)->SetNullable(
100 : FALSE);
101 : }
102 : }
103 : }
104 89 : OGRPGClearResult(hResult);
105 : }
106 :
107 : /* Determine the table from which the geometry column is extracted */
108 128 : if (iGeomCol != -1)
109 : {
110 77 : Oid tableOID = PQftable(hInitialResultIn, iGeomCol);
111 77 : if (tableOID != InvalidOid)
112 : {
113 100 : CPLString osGetTableName;
114 : osGetTableName.Printf(
115 : "SELECT c.relname, n.nspname FROM pg_class c "
116 : "JOIN pg_namespace n ON c.relnamespace=n.oid WHERE c.oid = %d ",
117 50 : tableOID);
118 : PGresult *hTableNameResult =
119 50 : OGRPG_PQexec(poDS->GetPGConn(), osGetTableName);
120 50 : if (hTableNameResult &&
121 50 : PQresultStatus(hTableNameResult) == PGRES_TUPLES_OK)
122 : {
123 50 : if (PQntuples(hTableNameResult) > 0)
124 : {
125 50 : pszGeomTableName =
126 50 : CPLStrdup(PQgetvalue(hTableNameResult, 0, 0));
127 50 : pszGeomTableSchemaName =
128 50 : CPLStrdup(PQgetvalue(hTableNameResult, 0, 1));
129 : }
130 : }
131 50 : OGRPGClearResult(hTableNameResult);
132 : }
133 : }
134 128 : }
135 :
136 : /************************************************************************/
137 : /* ~OGRPGResultLayer() */
138 : /************************************************************************/
139 :
140 256 : OGRPGResultLayer::~OGRPGResultLayer()
141 :
142 : {
143 128 : CPLFree(pszRawStatement);
144 128 : CPLFree(pszGeomTableName);
145 128 : CPLFree(pszGeomTableSchemaName);
146 256 : }
147 :
148 : /************************************************************************/
149 : /* BuildFullQueryStatement() */
150 : /************************************************************************/
151 :
152 147 : void OGRPGResultLayer::BuildFullQueryStatement()
153 :
154 : {
155 147 : if (pszQueryStatement != nullptr)
156 : {
157 19 : CPLFree(pszQueryStatement);
158 19 : pszQueryStatement = nullptr;
159 : }
160 :
161 147 : const size_t nLen = strlen(pszRawStatement) + osWHERE.size() + 40;
162 147 : pszQueryStatement = static_cast<char *>(CPLMalloc(nLen));
163 :
164 147 : if (osWHERE.empty())
165 132 : strcpy(pszQueryStatement, pszRawStatement);
166 : else
167 15 : snprintf(pszQueryStatement, nLen,
168 : "SELECT * FROM (%s) AS ogrpgsubquery %s", pszRawStatement,
169 : osWHERE.c_str());
170 147 : }
171 :
172 : /************************************************************************/
173 : /* ResetReading() */
174 : /************************************************************************/
175 :
176 188 : void OGRPGResultLayer::ResetReading()
177 :
178 : {
179 188 : OGRPGLayer::ResetReading();
180 188 : }
181 :
182 : /************************************************************************/
183 : /* GetFeatureCount() */
184 : /************************************************************************/
185 :
186 38 : GIntBig OGRPGResultLayer::GetFeatureCount(int bForce)
187 :
188 : {
189 38 : if (TestCapability(OLCFastFeatureCount) == FALSE)
190 12 : return OGRPGLayer::GetFeatureCount(bForce);
191 :
192 26 : PGconn *hPGConn = poDS->GetPGConn();
193 26 : CPLString osCommand;
194 26 : int nCount = 0;
195 :
196 : osCommand.Printf("SELECT count(*) FROM (%s) AS ogrpgcount",
197 26 : pszQueryStatement);
198 :
199 26 : PGresult *hResult = OGRPG_PQexec(hPGConn, osCommand);
200 26 : if (hResult != nullptr && PQresultStatus(hResult) == PGRES_TUPLES_OK)
201 26 : nCount = atoi(PQgetvalue(hResult, 0, 0));
202 : else
203 0 : CPLDebug("PG", "%s; failed.", osCommand.c_str());
204 26 : OGRPGClearResult(hResult);
205 :
206 26 : return nCount;
207 : }
208 :
209 : /************************************************************************/
210 : /* TestCapability() */
211 : /************************************************************************/
212 :
213 125 : int OGRPGResultLayer::TestCapability(const char *pszCap) const
214 :
215 : {
216 125 : GetLayerDefn();
217 :
218 125 : if (EQUAL(pszCap, OLCFastFeatureCount) ||
219 87 : EQUAL(pszCap, OLCFastSetNextByIndex))
220 : {
221 48 : OGRPGGeomFieldDefn *poGeomFieldDefn = nullptr;
222 48 : if (poFeatureDefn->GetGeomFieldCount() > 0)
223 : poGeomFieldDefn =
224 38 : poFeatureDefn->GetGeomFieldDefn(m_iGeomFieldFilter);
225 12 : return (m_poFilterGeom == nullptr || poGeomFieldDefn == nullptr ||
226 12 : poGeomFieldDefn->ePostgisType == GEOM_TYPE_GEOMETRY ||
227 96 : poGeomFieldDefn->ePostgisType == GEOM_TYPE_GEOGRAPHY) &&
228 90 : m_poAttrQuery == nullptr;
229 : }
230 77 : else if (EQUAL(pszCap, OLCFastSpatialFilter))
231 : {
232 0 : OGRPGGeomFieldDefn *poGeomFieldDefn = nullptr;
233 0 : if (poFeatureDefn->GetGeomFieldCount() > 0)
234 : poGeomFieldDefn =
235 0 : poFeatureDefn->GetGeomFieldDefn(m_iGeomFieldFilter);
236 0 : return (poGeomFieldDefn == nullptr ||
237 0 : poGeomFieldDefn->ePostgisType == GEOM_TYPE_GEOMETRY ||
238 0 : poGeomFieldDefn->ePostgisType == GEOM_TYPE_GEOGRAPHY) &&
239 0 : m_poAttrQuery == nullptr;
240 : }
241 :
242 77 : else if (EQUAL(pszCap, OLCFastGetExtent))
243 : {
244 13 : OGRPGGeomFieldDefn *poGeomFieldDefn = nullptr;
245 13 : if (poFeatureDefn->GetGeomFieldCount() > 0)
246 13 : poGeomFieldDefn = poFeatureDefn->GetGeomFieldDefn(0);
247 13 : return (poGeomFieldDefn == nullptr ||
248 20 : poGeomFieldDefn->ePostgisType == GEOM_TYPE_GEOMETRY) &&
249 20 : m_poAttrQuery == nullptr;
250 : }
251 64 : else if (EQUAL(pszCap, OLCStringsAsUTF8))
252 20 : return TRUE;
253 44 : else if (EQUAL(pszCap, OLCZGeometries))
254 6 : return TRUE;
255 : else
256 38 : return FALSE;
257 : }
258 :
259 : /************************************************************************/
260 : /* GetNextFeature() */
261 : /************************************************************************/
262 :
263 3658 : OGRFeature *OGRPGResultLayer::GetNextFeature()
264 :
265 : {
266 3658 : OGRPGGeomFieldDefn *poGeomFieldDefn = nullptr;
267 3658 : if (poFeatureDefn->GetGeomFieldCount() != 0)
268 3593 : poGeomFieldDefn = poFeatureDefn->GetGeomFieldDefn(m_iGeomFieldFilter);
269 :
270 : while (true)
271 : {
272 3797 : OGRFeature *poFeature = GetNextRawFeature();
273 3797 : if (poFeature == nullptr)
274 108 : return nullptr;
275 :
276 185 : if ((m_poFilterGeom == nullptr || poGeomFieldDefn == nullptr ||
277 185 : (poGeomFieldDefn->nSRSId > 0 &&
278 6 : poDS->sPostGISVersion.nMajor >= 0 &&
279 6 : !poDS->IsSpatialFilterIntersectionLocal()) ||
280 7501 : FilterGeometry(poFeature->GetGeomFieldRef(m_iGeomFieldFilter))) &&
281 3629 : (m_poAttrQuery == nullptr || m_poAttrQuery->Evaluate(poFeature)))
282 3550 : return poFeature;
283 :
284 139 : delete poFeature;
285 139 : }
286 : }
287 :
288 : /************************************************************************/
289 : /* ISetSpatialFilter() */
290 : /************************************************************************/
291 :
292 75 : OGRErr OGRPGResultLayer::ISetSpatialFilter(int iGeomField,
293 : const OGRGeometry *poGeomIn)
294 :
295 : {
296 75 : m_iGeomFieldFilter = iGeomField;
297 121 : if (iGeomField == 0 && poGeomIn == nullptr &&
298 46 : GetLayerDefn()->GetGeomFieldCount() == 0)
299 : {
300 0 : return OGRERR_NONE;
301 : }
302 :
303 : OGRPGGeomFieldDefn *poGeomFieldDefn =
304 75 : poFeatureDefn->GetGeomFieldDefn(m_iGeomFieldFilter);
305 75 : if (InstallFilter(poGeomIn))
306 : {
307 37 : if (poDS->sPostGISVersion.nMajor >= 0 &&
308 30 : (poGeomFieldDefn->ePostgisType == GEOM_TYPE_GEOMETRY ||
309 13 : poGeomFieldDefn->ePostgisType == GEOM_TYPE_GEOGRAPHY))
310 : {
311 19 : poGeomFieldDefn->GetSpatialRef(); // make sure nSRSId is resolved
312 19 : if (m_poFilterGeom != nullptr)
313 : {
314 19 : if (poGeomFieldDefn->nSRSId > 0 &&
315 4 : !poDS->IsSpatialFilterIntersectionLocal())
316 : {
317 4 : char *pszHexEWKB = OGRGeometryToHexEWKB(
318 2 : m_poFilterGeom, poGeomFieldDefn->nSRSId,
319 2 : poDS->sPostGISVersion.nMajor,
320 2 : poDS->sPostGISVersion.nMinor);
321 : // Note that we purposely do ::GEOMETRY intersection even
322 : // on geography case
323 : osWHERE.Printf(
324 : "WHERE ST_Intersects(%s::GEOMETRY, '%s'::GEOMETRY) ",
325 4 : OGRPGEscapeColumnName(poGeomFieldDefn->GetNameRef())
326 : .c_str(),
327 4 : pszHexEWKB);
328 2 : CPLFree(pszHexEWKB);
329 : }
330 : else
331 : {
332 13 : OGREnvelope sEnvelope;
333 :
334 13 : m_poFilterGeom->getEnvelope(&sEnvelope);
335 13 : if (poGeomFieldDefn->ePostgisType == GEOM_TYPE_GEOGRAPHY)
336 : {
337 1 : if (sEnvelope.MinX < -180.0)
338 0 : sEnvelope.MinX = -180.0;
339 1 : if (sEnvelope.MinY < -90.0)
340 0 : sEnvelope.MinY = -90.0;
341 1 : if (sEnvelope.MaxX > 180.0)
342 0 : sEnvelope.MaxX = 180.0;
343 1 : if (sEnvelope.MaxY > 90.0)
344 0 : sEnvelope.MaxY = 90.0;
345 : }
346 : // Avoid +/- infinity
347 13 : sEnvelope.MinX = std::max(
348 13 : sEnvelope.MinX, -std::numeric_limits<double>::max());
349 13 : sEnvelope.MinY = std::max(
350 13 : sEnvelope.MinY, -std::numeric_limits<double>::max());
351 13 : sEnvelope.MaxX = std::min(
352 13 : sEnvelope.MaxX, std::numeric_limits<double>::max());
353 13 : sEnvelope.MaxY = std::min(
354 13 : sEnvelope.MaxY, std::numeric_limits<double>::max());
355 : osWHERE.Printf(
356 : "WHERE %s::GEOMETRY && "
357 : "ST_MakeEnvelope(%.17g,%.17g,%.17g,%.17g) ",
358 26 : OGRPGEscapeColumnName(poGeomFieldDefn->GetNameRef())
359 : .c_str(),
360 : sEnvelope.MinX, sEnvelope.MinY, sEnvelope.MaxX,
361 26 : sEnvelope.MaxY);
362 : }
363 : }
364 : else
365 : {
366 4 : osWHERE = "";
367 : }
368 :
369 19 : BuildFullQueryStatement();
370 : }
371 :
372 37 : ResetReading();
373 : }
374 75 : return OGRERR_NONE;
375 : }
376 :
377 : /************************************************************************/
378 : /* ResolveSRID() */
379 : /************************************************************************/
380 :
381 910 : void OGRPGResultLayer::ResolveSRID(const OGRPGGeomFieldDefn *poGFldDefn)
382 :
383 : {
384 : /* We have to get the SRID of the geometry column, so to be able */
385 : /* to do spatial filtering */
386 910 : int nSRSId = UNDETERMINED_SRID;
387 :
388 910 : if (poGFldDefn->ePostgisType == GEOM_TYPE_GEOGRAPHY)
389 : {
390 3 : if (!(poDS->sPostGISVersion.nMajor >= 3 ||
391 0 : (poDS->sPostGISVersion.nMajor == 2 &&
392 0 : poDS->sPostGISVersion.nMinor >= 2)))
393 : {
394 : // EPSG:4326 was a requirement for geography before PostGIS 2.2
395 0 : nSRSId = 4326;
396 : }
397 : }
398 :
399 910 : if (nSRSId == UNDETERMINED_SRID &&
400 910 : (poGFldDefn->ePostgisType == GEOM_TYPE_GEOMETRY ||
401 861 : poGFldDefn->ePostgisType == GEOM_TYPE_GEOGRAPHY))
402 : {
403 52 : if (pszGeomTableName != nullptr)
404 : {
405 46 : CPLString osName(pszGeomTableSchemaName);
406 23 : osName += ".";
407 23 : osName += pszGeomTableName;
408 : OGRPGLayer *poBaseLayer =
409 23 : cpl::down_cast<OGRPGLayer *>(poDS->GetLayerByName(osName));
410 23 : if (poBaseLayer)
411 : {
412 46 : int iBaseIdx = poBaseLayer->GetLayerDefn()->GetGeomFieldIndex(
413 23 : poGFldDefn->GetNameRef());
414 23 : if (iBaseIdx >= 0)
415 : {
416 : const OGRPGGeomFieldDefn *poBaseGFldDefn =
417 23 : poBaseLayer->GetLayerDefn()->GetGeomFieldDefn(iBaseIdx);
418 : poBaseGFldDefn
419 23 : ->GetSpatialRef(); /* To make sure nSRSId is resolved */
420 23 : nSRSId = poBaseGFldDefn->nSRSId;
421 : }
422 : }
423 : }
424 :
425 52 : if (nSRSId == UNDETERMINED_SRID)
426 : {
427 58 : CPLString osGetSRID;
428 :
429 29 : const char *psGetSRIDFct =
430 29 : poDS->sPostGISVersion.nMajor >= 2 ? "ST_SRID" : "getsrid";
431 :
432 29 : osGetSRID += "SELECT ";
433 29 : osGetSRID += psGetSRIDFct;
434 29 : osGetSRID += "(";
435 29 : osGetSRID += OGRPGEscapeColumnName(poGFldDefn->GetNameRef());
436 29 : if (poDS->sPostGISVersion.nMajor > 2 ||
437 0 : (poDS->sPostGISVersion.nMajor == 2 &&
438 0 : poDS->sPostGISVersion.nMinor >= 2))
439 29 : osGetSRID += "::geometry";
440 29 : osGetSRID += ") FROM (";
441 29 : osGetSRID += pszRawStatement;
442 29 : osGetSRID += ") AS ogrpggetsrid WHERE (";
443 29 : osGetSRID += OGRPGEscapeColumnName(poGFldDefn->GetNameRef());
444 29 : osGetSRID += " IS NOT NULL) LIMIT 1";
445 :
446 29 : PGresult *hSRSIdResult = OGRPG_PQexec(poDS->GetPGConn(), osGetSRID);
447 :
448 29 : nSRSId = poDS->GetUndefinedSRID();
449 :
450 29 : if (hSRSIdResult && PQresultStatus(hSRSIdResult) == PGRES_TUPLES_OK)
451 : {
452 29 : if (PQntuples(hSRSIdResult) > 0)
453 29 : nSRSId = atoi(PQgetvalue(hSRSIdResult, 0, 0));
454 : }
455 : else
456 : {
457 0 : CPLError(CE_Failure, CPLE_AppDefined, "%s",
458 0 : PQerrorMessage(poDS->GetPGConn()));
459 : }
460 :
461 29 : OGRPGClearResult(hSRSIdResult);
462 : }
463 : }
464 :
465 910 : poGFldDefn->nSRSId = nSRSId;
466 910 : }
|
