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 :
298 : OGRPGGeomFieldDefn *poGeomFieldDefn =
299 75 : poFeatureDefn->GetGeomFieldDefn(m_iGeomFieldFilter);
300 75 : if (InstallFilter(poGeomIn))
301 : {
302 37 : if (poDS->sPostGISVersion.nMajor >= 0 &&
303 30 : (poGeomFieldDefn->ePostgisType == GEOM_TYPE_GEOMETRY ||
304 13 : poGeomFieldDefn->ePostgisType == GEOM_TYPE_GEOGRAPHY))
305 : {
306 19 : poGeomFieldDefn->GetSpatialRef(); // make sure nSRSId is resolved
307 19 : if (m_poFilterGeom != nullptr)
308 : {
309 19 : if (poGeomFieldDefn->nSRSId > 0 &&
310 4 : !poDS->IsSpatialFilterIntersectionLocal())
311 : {
312 4 : char *pszHexEWKB = OGRGeometryToHexEWKB(
313 2 : m_poFilterGeom, poGeomFieldDefn->nSRSId,
314 2 : poDS->sPostGISVersion.nMajor,
315 2 : poDS->sPostGISVersion.nMinor);
316 : // Note that we purposely do ::GEOMETRY intersection even
317 : // on geography case
318 : osWHERE.Printf(
319 : "WHERE ST_Intersects(%s::GEOMETRY, '%s'::GEOMETRY) ",
320 4 : OGRPGEscapeColumnName(poGeomFieldDefn->GetNameRef())
321 : .c_str(),
322 4 : pszHexEWKB);
323 2 : CPLFree(pszHexEWKB);
324 : }
325 : else
326 : {
327 13 : OGREnvelope sEnvelope;
328 :
329 13 : m_poFilterGeom->getEnvelope(&sEnvelope);
330 13 : if (poGeomFieldDefn->ePostgisType == GEOM_TYPE_GEOGRAPHY)
331 : {
332 1 : if (sEnvelope.MinX < -180.0)
333 0 : sEnvelope.MinX = -180.0;
334 1 : if (sEnvelope.MinY < -90.0)
335 0 : sEnvelope.MinY = -90.0;
336 1 : if (sEnvelope.MaxX > 180.0)
337 0 : sEnvelope.MaxX = 180.0;
338 1 : if (sEnvelope.MaxY > 90.0)
339 0 : sEnvelope.MaxY = 90.0;
340 : }
341 : // Avoid +/- infinity
342 13 : sEnvelope.MinX = std::max(
343 13 : sEnvelope.MinX, -std::numeric_limits<double>::max());
344 13 : sEnvelope.MinY = std::max(
345 13 : sEnvelope.MinY, -std::numeric_limits<double>::max());
346 13 : sEnvelope.MaxX = std::min(
347 13 : sEnvelope.MaxX, std::numeric_limits<double>::max());
348 13 : sEnvelope.MaxY = std::min(
349 13 : sEnvelope.MaxY, std::numeric_limits<double>::max());
350 : osWHERE.Printf(
351 : "WHERE %s::GEOMETRY && "
352 : "ST_MakeEnvelope(%.17g,%.17g,%.17g,%.17g) ",
353 26 : OGRPGEscapeColumnName(poGeomFieldDefn->GetNameRef())
354 : .c_str(),
355 : sEnvelope.MinX, sEnvelope.MinY, sEnvelope.MaxX,
356 26 : sEnvelope.MaxY);
357 : }
358 : }
359 : else
360 : {
361 4 : osWHERE = "";
362 : }
363 :
364 19 : BuildFullQueryStatement();
365 : }
366 :
367 37 : ResetReading();
368 : }
369 75 : return OGRERR_NONE;
370 : }
371 :
372 : /************************************************************************/
373 : /* ResolveSRID() */
374 : /************************************************************************/
375 :
376 910 : void OGRPGResultLayer::ResolveSRID(const OGRPGGeomFieldDefn *poGFldDefn)
377 :
378 : {
379 : /* We have to get the SRID of the geometry column, so to be able */
380 : /* to do spatial filtering */
381 910 : int nSRSId = UNDETERMINED_SRID;
382 :
383 910 : if (poGFldDefn->ePostgisType == GEOM_TYPE_GEOGRAPHY)
384 : {
385 3 : if (!(poDS->sPostGISVersion.nMajor >= 3 ||
386 0 : (poDS->sPostGISVersion.nMajor == 2 &&
387 0 : poDS->sPostGISVersion.nMinor >= 2)))
388 : {
389 : // EPSG:4326 was a requirement for geography before PostGIS 2.2
390 0 : nSRSId = 4326;
391 : }
392 : }
393 :
394 910 : if (nSRSId == UNDETERMINED_SRID &&
395 910 : (poGFldDefn->ePostgisType == GEOM_TYPE_GEOMETRY ||
396 861 : poGFldDefn->ePostgisType == GEOM_TYPE_GEOGRAPHY))
397 : {
398 52 : if (pszGeomTableName != nullptr)
399 : {
400 46 : CPLString osName(pszGeomTableSchemaName);
401 23 : osName += ".";
402 23 : osName += pszGeomTableName;
403 : OGRPGLayer *poBaseLayer =
404 23 : cpl::down_cast<OGRPGLayer *>(poDS->GetLayerByName(osName));
405 23 : if (poBaseLayer)
406 : {
407 46 : int iBaseIdx = poBaseLayer->GetLayerDefn()->GetGeomFieldIndex(
408 23 : poGFldDefn->GetNameRef());
409 23 : if (iBaseIdx >= 0)
410 : {
411 : const OGRPGGeomFieldDefn *poBaseGFldDefn =
412 23 : poBaseLayer->GetLayerDefn()->GetGeomFieldDefn(iBaseIdx);
413 : poBaseGFldDefn
414 23 : ->GetSpatialRef(); /* To make sure nSRSId is resolved */
415 23 : nSRSId = poBaseGFldDefn->nSRSId;
416 : }
417 : }
418 : }
419 :
420 52 : if (nSRSId == UNDETERMINED_SRID)
421 : {
422 58 : CPLString osGetSRID;
423 :
424 29 : const char *psGetSRIDFct =
425 29 : poDS->sPostGISVersion.nMajor >= 2 ? "ST_SRID" : "getsrid";
426 :
427 29 : osGetSRID += "SELECT ";
428 29 : osGetSRID += psGetSRIDFct;
429 29 : osGetSRID += "(";
430 29 : osGetSRID += OGRPGEscapeColumnName(poGFldDefn->GetNameRef());
431 29 : if (poDS->sPostGISVersion.nMajor > 2 ||
432 0 : (poDS->sPostGISVersion.nMajor == 2 &&
433 0 : poDS->sPostGISVersion.nMinor >= 2))
434 29 : osGetSRID += "::geometry";
435 29 : osGetSRID += ") FROM (";
436 29 : osGetSRID += pszRawStatement;
437 29 : osGetSRID += ") AS ogrpggetsrid WHERE (";
438 29 : osGetSRID += OGRPGEscapeColumnName(poGFldDefn->GetNameRef());
439 29 : osGetSRID += " IS NOT NULL) LIMIT 1";
440 :
441 29 : PGresult *hSRSIdResult = OGRPG_PQexec(poDS->GetPGConn(), osGetSRID);
442 :
443 29 : nSRSId = poDS->GetUndefinedSRID();
444 :
445 29 : if (hSRSIdResult && PQresultStatus(hSRSIdResult) == PGRES_TUPLES_OK)
446 : {
447 29 : if (PQntuples(hSRSIdResult) > 0)
448 29 : nSRSId = atoi(PQgetvalue(hSRSIdResult, 0, 0));
449 : }
450 : else
451 : {
452 0 : CPLError(CE_Failure, CPLE_AppDefined, "%s",
453 0 : PQerrorMessage(poDS->GetPGConn()));
454 : }
455 :
456 29 : OGRPGClearResult(hSRSIdResult);
457 : }
458 : }
459 :
460 910 : poGFldDefn->nSRSId = nSRSId;
461 910 : }
|
