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