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