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