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 : /* ISetSpatialFilter() */
285 : /************************************************************************/
286 :
287 67 : OGRErr OGRPGResultLayer::ISetSpatialFilter(int iGeomField,
288 : const OGRGeometry *poGeomIn)
289 :
290 : {
291 67 : m_iGeomFieldFilter = iGeomField;
292 :
293 : OGRPGGeomFieldDefn *poGeomFieldDefn =
294 67 : poFeatureDefn->GetGeomFieldDefn(m_iGeomFieldFilter);
295 67 : if (InstallFilter(poGeomIn))
296 : {
297 29 : if (poGeomFieldDefn->ePostgisType == GEOM_TYPE_GEOMETRY ||
298 14 : poGeomFieldDefn->ePostgisType == GEOM_TYPE_GEOGRAPHY)
299 : {
300 15 : if (m_poFilterGeom != nullptr)
301 : {
302 : char szBox3D_1[128];
303 : char szBox3D_2[128];
304 11 : OGREnvelope sEnvelope;
305 :
306 11 : m_poFilterGeom->getEnvelope(&sEnvelope);
307 11 : if (poGeomFieldDefn->ePostgisType == GEOM_TYPE_GEOGRAPHY)
308 : {
309 0 : if (sEnvelope.MinX < -180.0)
310 0 : sEnvelope.MinX = -180.0;
311 0 : if (sEnvelope.MinY < -90.0)
312 0 : sEnvelope.MinY = -90.0;
313 0 : if (sEnvelope.MaxX > 180.0)
314 0 : sEnvelope.MaxX = 180.0;
315 0 : if (sEnvelope.MaxY > 90.0)
316 0 : sEnvelope.MaxY = 90.0;
317 : }
318 11 : CPLsnprintf(szBox3D_1, sizeof(szBox3D_1), "%.17g %.17g",
319 : sEnvelope.MinX, sEnvelope.MinY);
320 11 : CPLsnprintf(szBox3D_2, sizeof(szBox3D_2), "%.17g %.17g",
321 : sEnvelope.MaxX, sEnvelope.MaxY);
322 : osWHERE.Printf(
323 : "WHERE %s && %s('BOX3D(%s, %s)'::box3d,%d) ",
324 22 : OGRPGEscapeColumnName(poGeomFieldDefn->GetNameRef())
325 : .c_str(),
326 11 : (poDS->sPostGISVersion.nMajor >= 2) ? "ST_SetSRID"
327 : : "SetSRID",
328 22 : szBox3D_1, szBox3D_2, poGeomFieldDefn->nSRSId);
329 : }
330 : else
331 : {
332 4 : osWHERE = "";
333 : }
334 :
335 15 : BuildFullQueryStatement();
336 : }
337 :
338 29 : ResetReading();
339 : }
340 67 : return OGRERR_NONE;
341 : }
342 :
343 : /************************************************************************/
344 : /* ResolveSRID() */
345 : /************************************************************************/
346 :
347 894 : void OGRPGResultLayer::ResolveSRID(const OGRPGGeomFieldDefn *poGFldDefn)
348 :
349 : {
350 : /* We have to get the SRID of the geometry column, so to be able */
351 : /* to do spatial filtering */
352 894 : int nSRSId = UNDETERMINED_SRID;
353 :
354 894 : if (poGFldDefn->ePostgisType == GEOM_TYPE_GEOGRAPHY)
355 : {
356 1 : if (!(poDS->sPostGISVersion.nMajor >= 3 ||
357 0 : (poDS->sPostGISVersion.nMajor == 2 &&
358 0 : poDS->sPostGISVersion.nMinor >= 2)))
359 : {
360 : // EPSG:4326 was a requirement for geography before PostGIS 2.2
361 0 : nSRSId = 4326;
362 : }
363 : }
364 :
365 894 : if (nSRSId == UNDETERMINED_SRID &&
366 894 : (poGFldDefn->ePostgisType == GEOM_TYPE_GEOMETRY ||
367 850 : poGFldDefn->ePostgisType == GEOM_TYPE_GEOGRAPHY))
368 : {
369 45 : if (pszGeomTableName != nullptr)
370 : {
371 32 : CPLString osName(pszGeomTableSchemaName);
372 16 : osName += ".";
373 16 : osName += pszGeomTableName;
374 : OGRPGLayer *poBaseLayer =
375 16 : cpl::down_cast<OGRPGLayer *>(poDS->GetLayerByName(osName));
376 16 : if (poBaseLayer)
377 : {
378 32 : int iBaseIdx = poBaseLayer->GetLayerDefn()->GetGeomFieldIndex(
379 16 : poGFldDefn->GetNameRef());
380 16 : if (iBaseIdx >= 0)
381 : {
382 : const OGRPGGeomFieldDefn *poBaseGFldDefn =
383 16 : poBaseLayer->GetLayerDefn()->GetGeomFieldDefn(iBaseIdx);
384 : poBaseGFldDefn
385 16 : ->GetSpatialRef(); /* To make sure nSRSId is resolved */
386 16 : nSRSId = poBaseGFldDefn->nSRSId;
387 : }
388 : }
389 : }
390 :
391 45 : if (nSRSId == UNDETERMINED_SRID)
392 : {
393 58 : CPLString osGetSRID;
394 :
395 29 : const char *psGetSRIDFct =
396 29 : poDS->sPostGISVersion.nMajor >= 2 ? "ST_SRID" : "getsrid";
397 :
398 29 : osGetSRID += "SELECT ";
399 29 : osGetSRID += psGetSRIDFct;
400 29 : osGetSRID += "(";
401 29 : osGetSRID += OGRPGEscapeColumnName(poGFldDefn->GetNameRef());
402 29 : if (poDS->sPostGISVersion.nMajor > 2 ||
403 0 : (poDS->sPostGISVersion.nMajor == 2 &&
404 0 : poDS->sPostGISVersion.nMinor >= 2))
405 29 : osGetSRID += "::geometry";
406 29 : osGetSRID += ") FROM (";
407 29 : osGetSRID += pszRawStatement;
408 29 : osGetSRID += ") AS ogrpggetsrid WHERE (";
409 29 : osGetSRID += OGRPGEscapeColumnName(poGFldDefn->GetNameRef());
410 29 : osGetSRID += " IS NOT NULL) LIMIT 1";
411 :
412 29 : PGresult *hSRSIdResult = OGRPG_PQexec(poDS->GetPGConn(), osGetSRID);
413 :
414 29 : nSRSId = poDS->GetUndefinedSRID();
415 :
416 29 : if (hSRSIdResult && PQresultStatus(hSRSIdResult) == PGRES_TUPLES_OK)
417 : {
418 29 : if (PQntuples(hSRSIdResult) > 0)
419 29 : nSRSId = atoi(PQgetvalue(hSRSIdResult, 0, 0));
420 : }
421 : else
422 : {
423 0 : CPLError(CE_Failure, CPLE_AppDefined, "%s",
424 0 : PQerrorMessage(poDS->GetPGConn()));
425 : }
426 :
427 29 : OGRPGClearResult(hSRSIdResult);
428 : }
429 : }
430 :
431 894 : poGFldDefn->nSRSId = nSRSId;
432 894 : }
|
