LCOV - code coverage report
Current view: top level - ogr/ogrsf_frmts/pg - ogrpgresultlayer.cpp (source / functions) Hit Total Coverage
Test: gdal_filtered.info Lines: 190 214 88.8 %
Date: 2025-02-18 14:19:29 Functions: 10 10 100.0 %

          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 : }

Generated by: LCOV version 1.14