1 : /******************************************************************************
2 : * $Id: ogrpgresultlayer.cpp 25647 2013-02-12 18:40:15Z rouault $
3 : *
4 : * Project: OpenGIS Simple Features Reference Implementation
5 : * Purpose: Implements OGRPGResultLayer class, access the resultset from
6 : * a particular select query done via ExecuteSQL().
7 : * Author: Frank Warmerdam, warmerdam@pobox.com
8 : *
9 : ******************************************************************************
10 : * Copyright (c) 2002, Frank Warmerdam
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 25647 2013-02-12 18:40:15Z rouault $");
35 :
36 : #define PQexec this_is_an_error
37 :
38 : /************************************************************************/
39 : /* OGRPGResultLayer() */
40 : /************************************************************************/
41 :
42 45 : OGRPGResultLayer::OGRPGResultLayer( OGRPGDataSource *poDSIn,
43 : const char * pszRawQueryIn,
44 45 : PGresult *hInitialResultIn )
45 : {
46 45 : poDS = poDSIn;
47 :
48 45 : iNextShapeId = 0;
49 :
50 45 : pszRawStatement = CPLStrdup(pszRawQueryIn);
51 :
52 45 : osWHERE = "";
53 :
54 45 : BuildFullQueryStatement();
55 :
56 45 : poFeatureDefn = ReadResultDefinition(hInitialResultIn);
57 :
58 45 : pszGeomTableName = NULL;
59 45 : pszGeomTableSchemaName = NULL;
60 :
61 : /* Find at which index the geometry column is */
62 45 : int iGeomCol = -1;
63 45 : if (pszGeomColumn != NULL)
64 : {
65 : int iRawField;
66 39 : for( iRawField = 0; iRawField < PQnfields(hInitialResultIn); iRawField++ )
67 : {
68 39 : if( strcmp(PQfname(hInitialResultIn,iRawField), pszGeomColumn) == 0 )
69 : {
70 18 : iGeomCol = iRawField;
71 18 : break;
72 : }
73 : }
74 : }
75 :
76 : #ifndef PG_PRE74
77 : /* Determine the table from which the geometry column is extracted */
78 45 : if (iGeomCol != -1)
79 : {
80 18 : Oid tableOID = PQftable(hInitialResultIn, iGeomCol);
81 18 : if (tableOID != InvalidOid)
82 : {
83 15 : CPLString osGetTableName;
84 : osGetTableName.Printf("SELECT c.relname, n.nspname FROM pg_class c "
85 15 : "JOIN pg_namespace n ON c.relnamespace=n.oid WHERE c.oid = %d ", tableOID);
86 15 : PGresult* hTableNameResult = OGRPG_PQexec(poDS->GetPGConn(), osGetTableName );
87 15 : if( hTableNameResult && PQresultStatus(hTableNameResult) == PGRES_TUPLES_OK)
88 : {
89 15 : if ( PQntuples(hTableNameResult) > 0 )
90 : {
91 15 : pszGeomTableName = CPLStrdup(PQgetvalue(hTableNameResult,0,0));
92 15 : pszGeomTableSchemaName = CPLStrdup(PQgetvalue(hTableNameResult,0,1));
93 : }
94 : }
95 15 : OGRPGClearResult( hTableNameResult );
96 : }
97 : }
98 : #endif
99 :
100 45 : if (bHasPostGISGeography)
101 : {
102 : // FIXME? But for the moment, PostGIS 1.5 only handles SRID:4326.
103 1 : nSRSId = 4326;
104 : }
105 45 : }
106 :
107 : /************************************************************************/
108 : /* ~OGRPGResultLayer() */
109 : /************************************************************************/
110 :
111 45 : OGRPGResultLayer::~OGRPGResultLayer()
112 :
113 : {
114 45 : CPLFree( pszRawStatement );
115 45 : CPLFree( pszGeomTableName );
116 45 : CPLFree( pszGeomTableSchemaName );
117 45 : }
118 :
119 :
120 : /************************************************************************/
121 : /* BuildFullQueryStatement() */
122 : /************************************************************************/
123 :
124 50 : void OGRPGResultLayer::BuildFullQueryStatement()
125 :
126 : {
127 50 : if( pszQueryStatement != NULL )
128 : {
129 5 : CPLFree( pszQueryStatement );
130 5 : pszQueryStatement = NULL;
131 : }
132 :
133 50 : pszQueryStatement = (char*) CPLMalloc(strlen(pszRawStatement) + strlen(osWHERE) + 40);
134 :
135 50 : if (strlen(osWHERE) == 0)
136 46 : strcpy(pszQueryStatement, pszRawStatement);
137 : else
138 : sprintf(pszQueryStatement, "SELECT * FROM (%s) AS ogrpgsubquery %s",
139 4 : pszRawStatement, osWHERE.c_str());
140 50 : }
141 :
142 : /************************************************************************/
143 : /* ResetReading() */
144 : /************************************************************************/
145 :
146 68 : void OGRPGResultLayer::ResetReading()
147 :
148 : {
149 68 : OGRPGLayer::ResetReading();
150 68 : }
151 :
152 : /************************************************************************/
153 : /* GetFeatureCount() */
154 : /************************************************************************/
155 :
156 24 : int OGRPGResultLayer::GetFeatureCount( int bForce )
157 :
158 : {
159 24 : if( TestCapability(OLCFastFeatureCount) == FALSE )
160 8 : return OGRPGLayer::GetFeatureCount( bForce );
161 :
162 16 : PGconn *hPGConn = poDS->GetPGConn();
163 16 : PGresult *hResult = NULL;
164 16 : CPLString osCommand;
165 16 : int nCount = 0;
166 :
167 : osCommand.Printf(
168 : "SELECT count(*) FROM (%s) AS ogrpgcount",
169 16 : pszQueryStatement );
170 :
171 16 : hResult = OGRPG_PQexec(hPGConn, osCommand);
172 16 : if( hResult != NULL && PQresultStatus(hResult) == PGRES_TUPLES_OK )
173 16 : nCount = atoi(PQgetvalue(hResult,0,0));
174 : else
175 0 : CPLDebug( "PG", "%s; failed.", osCommand.c_str() );
176 16 : OGRPGClearResult( hResult );
177 :
178 16 : return nCount;
179 : }
180 :
181 :
182 : /************************************************************************/
183 : /* TestCapability() */
184 : /************************************************************************/
185 :
186 60 : int OGRPGResultLayer::TestCapability( const char * pszCap )
187 :
188 : {
189 60 : if( EQUAL(pszCap,OLCFastFeatureCount) ||
190 : EQUAL(pszCap,OLCFastSetNextByIndex) )
191 : return (m_poFilterGeom == NULL ||
192 36 : ((bHasPostGISGeometry || bHasPostGISGeography) && nSRSId != UNDETERMINED_SRID)) && m_poAttrQuery == NULL;
193 :
194 24 : else if( EQUAL(pszCap,OLCFastSpatialFilter) )
195 0 : return ((bHasPostGISGeometry || bHasPostGISGeography) && nSRSId != UNDETERMINED_SRID) && m_poAttrQuery == NULL;
196 :
197 24 : else if( EQUAL(pszCap,OLCFastGetExtent) )
198 10 : return (bHasPostGISGeometry && nSRSId != UNDETERMINED_SRID) && m_poAttrQuery == NULL;
199 :
200 14 : else if( EQUAL(pszCap,OLCStringsAsUTF8) )
201 2 : return TRUE;
202 :
203 : else
204 12 : return FALSE;
205 : }
206 :
207 :
208 : /************************************************************************/
209 : /* GetNextFeature() */
210 : /************************************************************************/
211 :
212 2507 : OGRFeature *OGRPGResultLayer::GetNextFeature()
213 :
214 : {
215 2507 : if( (bHasPostGISGeometry || bHasPostGISGeography) && nSRSId == UNDETERMINED_SRID )
216 : {
217 11 : GetSpatialRef(); /* make sure that we fetch the SRID if not already done */
218 : }
219 :
220 134 : for( ; TRUE; )
221 : {
222 : OGRFeature *poFeature;
223 :
224 2641 : poFeature = GetNextRawFeature();
225 2641 : if( poFeature == NULL )
226 40 : return NULL;
227 :
228 2601 : if( (m_poFilterGeom == NULL
229 : || ((bHasPostGISGeometry || bHasPostGISGeography) && nSRSId != UNDETERMINED_SRID)
230 : || FilterGeometry( poFeature->GetGeometryRef() ) )
231 : && (m_poAttrQuery == NULL
232 : || m_poAttrQuery->Evaluate( poFeature )) )
233 2467 : return poFeature;
234 :
235 134 : delete poFeature;
236 : }
237 : }
238 :
239 : /************************************************************************/
240 : /* SetSpatialFilter() */
241 : /************************************************************************/
242 :
243 16 : void OGRPGResultLayer::SetSpatialFilter( OGRGeometry * poGeomIn )
244 :
245 : {
246 16 : if( InstallFilter( poGeomIn ) )
247 : {
248 10 : if( (bHasPostGISGeometry || bHasPostGISGeography) && nSRSId == UNDETERMINED_SRID )
249 : {
250 1 : GetSpatialRef(); /* make sure that we fetch the SRID if not already done */
251 : }
252 :
253 10 : if ((bHasPostGISGeometry || bHasPostGISGeography) && nSRSId != UNDETERMINED_SRID)
254 : {
255 5 : if( m_poFilterGeom != NULL)
256 : {
257 : char szBox3D_1[128];
258 : char szBox3D_2[128];
259 : char* pszComma;
260 4 : OGREnvelope sEnvelope;
261 :
262 4 : m_poFilterGeom->getEnvelope( &sEnvelope );
263 4 : snprintf(szBox3D_1, sizeof(szBox3D_1), "%.12f %.12f", sEnvelope.MinX, sEnvelope.MinY);
264 8 : while((pszComma = strchr(szBox3D_1, ',')) != NULL)
265 0 : *pszComma = '.';
266 4 : snprintf(szBox3D_2, sizeof(szBox3D_2), "%.12f %.12f", sEnvelope.MaxX, sEnvelope.MaxY);
267 8 : while((pszComma = strchr(szBox3D_2, ',')) != NULL)
268 0 : *pszComma = '.';
269 : osWHERE.Printf("WHERE %s && %s('BOX3D(%s, %s)'::box3d,%d) ",
270 : OGRPGEscapeColumnName(pszGeomColumn).c_str(),
271 : (poDS->sPostGISVersion.nMajor >= 2) ? "ST_SetSRID" : "SetSRID",
272 4 : szBox3D_1, szBox3D_2, nSRSId );
273 : }
274 : else
275 : {
276 1 : osWHERE = "";
277 : }
278 :
279 5 : BuildFullQueryStatement();
280 : }
281 :
282 10 : ResetReading();
283 : }
284 :
285 16 : }
286 :
287 : /************************************************************************/
288 : /* GetExtent() */
289 : /* */
290 : /* For PostGIS use internal Extend(geometry) function */
291 : /* in other cases we use standard OGRLayer::GetExtent() */
292 : /************************************************************************/
293 :
294 5 : OGRErr OGRPGResultLayer::GetExtent( OGREnvelope *psExtent, int bForce )
295 : {
296 5 : CPLString osCommand;
297 :
298 : const char* pszExtentFct;
299 5 : if (poDS->sPostGISVersion.nMajor >= 2)
300 0 : pszExtentFct = "ST_Extent";
301 : else
302 5 : pszExtentFct = "Extent";
303 :
304 5 : if( (bHasPostGISGeometry || bHasPostGISGeography) && nSRSId == UNDETERMINED_SRID )
305 : {
306 1 : GetSpatialRef(); /* make sure that we fetch the SRID if not already done */
307 : }
308 :
309 5 : if ( TestCapability(OLCFastGetExtent) )
310 : {
311 : /* Do not take the spatial filter into account */
312 : osCommand.Printf( "SELECT %s(%s) FROM (%s) AS ogrpgextent",
313 : pszExtentFct, OGRPGEscapeColumnName(pszGeomColumn).c_str(),
314 2 : pszRawStatement );
315 : }
316 3 : else if ( bHasPostGISGeography )
317 : {
318 : /* Probably not very efficient, but more efficient than client-side implementation */
319 : osCommand.Printf( "SELECT %s(ST_GeomFromWKB(ST_AsBinary(%s))) FROM (%s) AS ogrpgextent",
320 : pszExtentFct, OGRPGEscapeColumnName(pszGeomColumn).c_str(),
321 1 : pszRawStatement );
322 : }
323 :
324 5 : return RunGetExtentRequest(psExtent, bForce, osCommand);
325 : }
326 :
327 : /************************************************************************/
328 : /* GetSpatialRef() */
329 : /* */
330 : /* We override this to try and fetch the table SRID from the */
331 : /* geometry_columns table if the srsid is UNDETERMINED_SRID */
332 : /* (meaning we haven't yet even looked for it). */
333 : /************************************************************************/
334 :
335 18 : OGRSpatialReference *OGRPGResultLayer::GetSpatialRef()
336 :
337 : {
338 18 : if( nSRSId == UNDETERMINED_SRID )
339 : {
340 : /* We have to get the SRID of the geometry column, so to be able */
341 : /* to do spatial filtering */
342 18 : if (bHasPostGISGeometry)
343 : {
344 17 : if (pszGeomTableName != NULL)
345 : {
346 14 : CPLString osName(pszGeomTableSchemaName);
347 14 : osName += ".";
348 14 : osName += pszGeomTableName;
349 14 : OGRPGLayer* poBaseLayer = (OGRPGLayer*) poDS->GetLayerByName(osName);
350 14 : if (poBaseLayer)
351 : {
352 14 : nSRSId = poBaseLayer->GetSRID();
353 14 : }
354 : }
355 :
356 17 : if( nSRSId == UNDETERMINED_SRID )
357 : {
358 3 : CPLString osGetSRID;
359 :
360 : const char* psGetSRIDFct;
361 3 : if (poDS->sPostGISVersion.nMajor >= 2)
362 0 : psGetSRIDFct = "ST_SRID";
363 : else
364 3 : psGetSRIDFct = "getsrid";
365 :
366 3 : osGetSRID += "SELECT ";
367 3 : osGetSRID += psGetSRIDFct;
368 3 : osGetSRID += "(";
369 3 : osGetSRID += OGRPGEscapeColumnName(pszGeomColumn);
370 3 : osGetSRID += ") FROM(";
371 3 : osGetSRID += pszRawStatement;
372 3 : osGetSRID += ") AS ogrpggetsrid LIMIT 1";
373 :
374 3 : PGresult* hSRSIdResult = OGRPG_PQexec(poDS->GetPGConn(), osGetSRID );
375 :
376 3 : nSRSId = -1;
377 :
378 3 : if( hSRSIdResult && PQresultStatus(hSRSIdResult) == PGRES_TUPLES_OK)
379 : {
380 3 : if ( PQntuples(hSRSIdResult) > 0 )
381 3 : nSRSId = atoi(PQgetvalue(hSRSIdResult, 0, 0));
382 : }
383 : else
384 : {
385 : CPLError( CE_Failure, CPLE_AppDefined,
386 0 : "%s", PQerrorMessage(poDS->GetPGConn()) );
387 : }
388 :
389 3 : OGRPGClearResult(hSRSIdResult);
390 : }
391 : }
392 : }
393 :
394 18 : return OGRPGLayer::GetSpatialRef();
395 : }
|
