1 : /******************************************************************************
2 : * $Id: ogrpgresultlayer.cpp 19873 2010-06-15 17:51:27Z 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 : #include "ogrpgutility.h"
34 :
35 : CPL_CVSID("$Id: ogrpgresultlayer.cpp 19873 2010-06-15 17:51:27Z rouault $");
36 :
37 :
38 : /************************************************************************/
39 : /* OGRPGResultLayer() */
40 : /************************************************************************/
41 :
42 : OGRPGResultLayer::OGRPGResultLayer( OGRPGDataSource *poDSIn,
43 : const char * pszRawQueryIn,
44 32 : PGresult *hInitialResultIn )
45 : {
46 32 : poDS = poDSIn;
47 :
48 32 : iNextShapeId = 0;
49 :
50 32 : pszRawStatement = CPLStrdup(pszRawQueryIn);
51 :
52 32 : osWHERE = "";
53 :
54 32 : BuildFullQueryStatement();
55 :
56 32 : poFeatureDefn = ReadResultDefinition(hInitialResultIn);
57 :
58 : /* We have to get the SRID of the geometry column, so to be able */
59 : /* to do spatial filtering */
60 32 : if (bHasPostGISGeometry)
61 : {
62 7 : CPLString osGetSRID;
63 7 : osGetSRID += "SELECT getsrid(\"";
64 7 : osGetSRID += pszGeomColumn;
65 7 : osGetSRID += "\") FROM (";
66 7 : osGetSRID += pszRawStatement;
67 7 : osGetSRID += ") AS ogrpggetsrid LIMIT 1";
68 :
69 7 : PGresult* hSRSIdResult = PQexec(poDS->GetPGConn(), osGetSRID );
70 :
71 7 : if( hSRSIdResult && PQresultStatus(hSRSIdResult) == PGRES_TUPLES_OK)
72 : {
73 7 : if ( PQntuples(hSRSIdResult) > 0 )
74 7 : nSRSId = atoi(PQgetvalue(hSRSIdResult, 0, 0));
75 : }
76 : else
77 : {
78 : CPLError( CE_Failure, CPLE_AppDefined,
79 0 : "%s", PQerrorMessage(poDS->GetPGConn()) );
80 : }
81 :
82 7 : OGRPGClearResult(hSRSIdResult);
83 : }
84 25 : else if (bHasPostGISGeography)
85 : {
86 : // FIXME? But for the moment, PostGIS 1.5 only handles SRID:4326.
87 0 : nSRSId = 4326;
88 : }
89 :
90 : /* Now set the cursor that will fetch the first rows */
91 : /* This is usefull when used in situations like */
92 : /* ds->ReleaseResultSet(ds->ExecuteSQL("SELECT AddGeometryColumn(....)")) */
93 : /* when people don't actually try to get elements */
94 32 : SetInitialQueryCursor();
95 32 : }
96 :
97 : /************************************************************************/
98 : /* ~OGRPGResultLayer() */
99 : /************************************************************************/
100 :
101 32 : OGRPGResultLayer::~OGRPGResultLayer()
102 :
103 : {
104 32 : CPLFree( pszRawStatement );
105 32 : }
106 :
107 : /************************************************************************/
108 : /* ReadResultDefinition() */
109 : /* */
110 : /* Build a schema from the current resultset. */
111 : /************************************************************************/
112 :
113 32 : OGRFeatureDefn *OGRPGResultLayer::ReadResultDefinition(PGresult *hInitialResultIn)
114 :
115 : {
116 32 : PGresult *hResult = hInitialResultIn;
117 :
118 : /* -------------------------------------------------------------------- */
119 : /* Parse the returned table information. */
120 : /* -------------------------------------------------------------------- */
121 32 : OGRFeatureDefn *poDefn = new OGRFeatureDefn( "sql_statement" );
122 : int iRawField;
123 :
124 32 : poDefn->Reference();
125 :
126 196 : for( iRawField = 0; iRawField < PQnfields(hResult); iRawField++ )
127 : {
128 164 : OGRFieldDefn oField( PQfname(hResult,iRawField), OFTString);
129 : Oid nTypeOID;
130 :
131 164 : nTypeOID = PQftype(hResult,iRawField);
132 :
133 164 : if( EQUAL(oField.GetNameRef(),"ogc_fid") )
134 : {
135 10 : if (bHasFid)
136 : {
137 : CPLError(CE_Warning, CPLE_AppDefined,
138 0 : "More than one ogc_fid column was found in the result of the SQL request. Only last one will be used");
139 : }
140 10 : bHasFid = TRUE;
141 10 : CPLFree(pszFIDColumn);
142 10 : pszFIDColumn = CPLStrdup(oField.GetNameRef());
143 23 : continue;
144 : }
145 154 : else if( nTypeOID == poDS->GetGeometryOID() ||
146 : nTypeOID == poDS->GetGeographyOID() ||
147 : EQUAL(oField.GetNameRef(),"ST_AsText") ||
148 : EQUAL(oField.GetNameRef(),"ST_AsBinary") ||
149 : EQUAL(oField.GetNameRef(),"AsBinary") ||
150 : EQUAL(oField.GetNameRef(),"asEWKT") ||
151 : EQUAL(oField.GetNameRef(),"asText") )
152 : {
153 7 : if (bHasPostGISGeometry || bHasPostGISGeography )
154 : {
155 : CPLError(CE_Warning, CPLE_AppDefined,
156 0 : "More than one geometry column was found in the result of the SQL request. Only last one will be used");
157 : }
158 7 : if (nTypeOID == poDS->GetGeographyOID())
159 0 : bHasPostGISGeography = TRUE;
160 : else
161 7 : bHasPostGISGeometry = TRUE;
162 7 : CPLFree(pszGeomColumn);
163 7 : pszGeomColumn = CPLStrdup(oField.GetNameRef());
164 : continue;
165 : }
166 147 : else if( EQUAL(oField.GetNameRef(),"WKB_GEOMETRY") )
167 : {
168 6 : bHasWkb = TRUE;
169 6 : if( nTypeOID == OIDOID )
170 0 : bWkbAsOid = TRUE;
171 : continue;
172 : }
173 :
174 141 : if( nTypeOID == BYTEAOID )
175 : {
176 4 : oField.SetType( OFTBinary );
177 : }
178 171 : else if( nTypeOID == CHAROID ||
179 : nTypeOID == TEXTOID ||
180 : nTypeOID == BPCHAROID ||
181 : nTypeOID == VARCHAROID )
182 : {
183 34 : oField.SetType( OFTString );
184 :
185 : /* See http://www.mail-archive.com/pgsql-hackers@postgresql.org/msg57726.html */
186 : /* nTypmod = width + 4 */
187 34 : int nTypmod = PQfmod(hResult, iRawField);
188 34 : if (nTypmod >= 4 && (nTypeOID == BPCHAROID ||
189 : nTypeOID == VARCHAROID ) )
190 : {
191 12 : oField.SetWidth( nTypmod - 4);
192 : }
193 : }
194 103 : else if( nTypeOID == BOOLOID )
195 : {
196 4 : oField.SetType( OFTInteger );
197 4 : oField.SetWidth( 1 );
198 : }
199 99 : else if (nTypeOID == INT2OID )
200 : {
201 4 : oField.SetType( OFTInteger );
202 4 : oField.SetWidth( 5 );
203 : }
204 95 : else if (nTypeOID == INT4OID )
205 : {
206 16 : oField.SetType( OFTInteger );
207 : }
208 79 : else if ( nTypeOID == INT8OID )
209 : {
210 : /* FIXME: OFTInteger can not handle 64bit integers */
211 9 : oField.SetType( OFTInteger );
212 : }
213 88 : else if( nTypeOID == FLOAT4OID ||
214 : nTypeOID == FLOAT8OID )
215 : {
216 18 : oField.SetType( OFTReal );
217 : }
218 52 : else if( nTypeOID == NUMERICOID )
219 : {
220 : /* See http://www.mail-archive.com/pgsql-hackers@postgresql.org/msg57726.html */
221 : /* typmod = (width << 16) + precision + 4 */
222 8 : int nTypmod = PQfmod(hResult, iRawField);
223 8 : if (nTypmod >= 4)
224 : {
225 8 : int nWidth = (nTypmod - 4) >> 16;
226 8 : int nPrecision = (nTypmod - 4) & 0xFFFF;
227 12 : if (nWidth <= 10 && nPrecision == 0)
228 : {
229 4 : oField.SetType( OFTInteger );
230 4 : oField.SetWidth( nWidth );
231 : }
232 : else
233 : {
234 4 : oField.SetType( OFTReal );
235 4 : oField.SetWidth( nWidth );
236 4 : oField.SetPrecision( nPrecision );
237 : }
238 : }
239 : else
240 0 : oField.SetType( OFTReal );
241 : }
242 44 : else if ( nTypeOID == INT4ARRAYOID )
243 : {
244 4 : oField.SetType ( OFTIntegerList );
245 : }
246 52 : else if ( nTypeOID == FLOAT4ARRAYOID ||
247 : nTypeOID == FLOAT8ARRAYOID )
248 : {
249 12 : oField.SetType ( OFTRealList );
250 : }
251 40 : else if ( nTypeOID == TEXTARRAYOID ||
252 : nTypeOID == BPCHARARRAYOID ||
253 : nTypeOID == VARCHARARRAYOID )
254 : {
255 12 : oField.SetType ( OFTStringList );
256 : }
257 16 : else if ( nTypeOID == DATEOID )
258 : {
259 4 : oField.SetType( OFTDate );
260 : }
261 12 : else if ( nTypeOID == TIMEOID )
262 : {
263 4 : oField.SetType( OFTTime );
264 : }
265 16 : else if ( nTypeOID == TIMESTAMPOID ||
266 : nTypeOID == TIMESTAMPTZOID )
267 : {
268 : /* We can't deserialize properly timestamp with time zone */
269 : /* with binary cursors */
270 8 : if (nTypeOID == TIMESTAMPTZOID)
271 4 : bCanUseBinaryCursor = FALSE;
272 :
273 8 : oField.SetType( OFTDateTime );
274 : }
275 : else /* unknown type */
276 : {
277 0 : CPLDebug("PG", "Unhandled OID (%d) for column %d. Defaulting to String.", nTypeOID, iRawField);
278 0 : oField.SetType( OFTString );
279 : }
280 :
281 141 : poDefn->AddFieldDefn( &oField );
282 : }
283 :
284 32 : return poDefn;
285 : }
286 :
287 : /************************************************************************/
288 : /* BuildFullQueryStatement() */
289 : /************************************************************************/
290 :
291 34 : void OGRPGResultLayer::BuildFullQueryStatement()
292 :
293 : {
294 34 : if( pszQueryStatement != NULL )
295 : {
296 2 : CPLFree( pszQueryStatement );
297 2 : pszQueryStatement = NULL;
298 : }
299 :
300 34 : pszQueryStatement = (char*) CPLMalloc(strlen(pszRawStatement) + strlen(osWHERE) + 40);
301 :
302 34 : if (strlen(osWHERE) == 0)
303 32 : strcpy(pszQueryStatement, pszRawStatement);
304 : else
305 : sprintf(pszQueryStatement, "SELECT * FROM (%s) AS ogrpgsubquery %s",
306 2 : pszRawStatement, osWHERE.c_str());
307 34 : }
308 :
309 : /************************************************************************/
310 : /* ResetReading() */
311 : /************************************************************************/
312 :
313 14 : void OGRPGResultLayer::ResetReading()
314 :
315 : {
316 14 : OGRPGLayer::ResetReading();
317 14 : }
318 :
319 : /************************************************************************/
320 : /* GetFeatureCount() */
321 : /************************************************************************/
322 :
323 6 : int OGRPGResultLayer::GetFeatureCount( int bForce )
324 :
325 : {
326 6 : if( TestCapability(OLCFastFeatureCount) == FALSE )
327 2 : return OGRPGLayer::GetFeatureCount( bForce );
328 :
329 4 : PGconn *hPGConn = poDS->GetPGConn();
330 4 : PGresult *hResult = NULL;
331 4 : CPLString osCommand;
332 4 : int nCount = 0;
333 :
334 : osCommand.Printf(
335 : "SELECT count(*) FROM (%s) AS ogrpgcount",
336 4 : pszQueryStatement );
337 :
338 4 : hResult = PQexec(hPGConn, osCommand);
339 4 : if( hResult != NULL && PQresultStatus(hResult) == PGRES_TUPLES_OK )
340 4 : nCount = atoi(PQgetvalue(hResult,0,0));
341 : else
342 0 : CPLDebug( "PG", "%s; failed.", osCommand.c_str() );
343 4 : OGRPGClearResult( hResult );
344 :
345 4 : return nCount;
346 : }
347 :
348 :
349 : /************************************************************************/
350 : /* TestCapability() */
351 : /************************************************************************/
352 :
353 12 : int OGRPGResultLayer::TestCapability( const char * pszCap )
354 :
355 : {
356 12 : if( EQUAL(pszCap,OLCFastFeatureCount) ||
357 : EQUAL(pszCap,OLCFastSetNextByIndex) )
358 : return (m_poFilterGeom == NULL ||
359 8 : ((bHasPostGISGeometry || bHasPostGISGeography) && nSRSId != -2)) && m_poAttrQuery == NULL;
360 :
361 4 : else if( EQUAL(pszCap,OLCFastSpatialFilter) )
362 0 : return ((bHasPostGISGeometry || bHasPostGISGeography) && nSRSId != -2) && m_poAttrQuery == NULL;
363 :
364 4 : else if( EQUAL(pszCap,OLCFastGetExtent) )
365 4 : return (bHasPostGISGeometry && nSRSId != -2) && m_poAttrQuery == NULL;
366 :
367 0 : else if( EQUAL(pszCap,OLCStringsAsUTF8) )
368 0 : return TRUE;
369 :
370 : else
371 0 : return FALSE;
372 : }
373 :
374 :
375 : /************************************************************************/
376 : /* GetNextFeature() */
377 : /************************************************************************/
378 :
379 2075 : OGRFeature *OGRPGResultLayer::GetNextFeature()
380 :
381 : {
382 :
383 2 : for( ; TRUE; )
384 : {
385 : OGRFeature *poFeature;
386 :
387 2075 : poFeature = GetNextRawFeature();
388 2075 : if( poFeature == NULL )
389 14 : return NULL;
390 :
391 2061 : if( (m_poFilterGeom == NULL
392 : || ((bHasPostGISGeometry || bHasPostGISGeography) && nSRSId != -2)
393 : || FilterGeometry( poFeature->GetGeometryRef() ) )
394 : && (m_poAttrQuery == NULL
395 : || m_poAttrQuery->Evaluate( poFeature )) )
396 2059 : return poFeature;
397 :
398 2 : delete poFeature;
399 : }
400 : }
401 :
402 : /************************************************************************/
403 : /* SetSpatialFilter() */
404 : /************************************************************************/
405 :
406 4 : void OGRPGResultLayer::SetSpatialFilter( OGRGeometry * poGeomIn )
407 :
408 : {
409 4 : if( InstallFilter( poGeomIn ) )
410 : {
411 4 : if ((bHasPostGISGeometry || bHasPostGISGeography) && nSRSId != -2)
412 : {
413 2 : if( m_poFilterGeom != NULL)
414 : {
415 : char szBox3D_1[128];
416 : char szBox3D_2[128];
417 : char* pszComma;
418 2 : OGREnvelope sEnvelope;
419 :
420 2 : m_poFilterGeom->getEnvelope( &sEnvelope );
421 2 : snprintf(szBox3D_1, sizeof(szBox3D_1), "%.12f %.12f", sEnvelope.MinX, sEnvelope.MinY);
422 4 : while((pszComma = strchr(szBox3D_1, ',')) != NULL)
423 0 : *pszComma = '.';
424 2 : snprintf(szBox3D_2, sizeof(szBox3D_2), "%.12f %.12f", sEnvelope.MaxX, sEnvelope.MaxY);
425 4 : while((pszComma = strchr(szBox3D_2, ',')) != NULL)
426 0 : *pszComma = '.';
427 : osWHERE.Printf("WHERE \"%s\" && SetSRID('BOX3D(%s, %s)'::box3d,%d) ",
428 2 : pszGeomColumn, szBox3D_1, szBox3D_2, nSRSId );
429 : }
430 : else
431 : {
432 0 : osWHERE = "";
433 : }
434 :
435 2 : BuildFullQueryStatement();
436 : }
437 :
438 4 : ResetReading();
439 : }
440 :
441 4 : }
442 :
443 : /************************************************************************/
444 : /* GetExtent() */
445 : /* */
446 : /* For PostGIS use internal Extend(geometry) function */
447 : /* in other cases we use standard OGRLayer::GetExtent() */
448 : /************************************************************************/
449 :
450 2 : OGRErr OGRPGResultLayer::GetExtent( OGREnvelope *psExtent, int bForce )
451 : {
452 2 : CPLString osCommand;
453 :
454 2 : if ( TestCapability(OLCFastGetExtent) )
455 : {
456 : /* Do not take the spatial filter into account */
457 : osCommand.Printf( "SELECT Extent(\"%s\") FROM (%s) AS ogrpgextent",
458 1 : pszGeomColumn, pszRawStatement );
459 : }
460 1 : else if ( bHasPostGISGeography )
461 : {
462 : /* Probably not very efficient, but more efficient than client-side implementation */
463 : osCommand.Printf( "SELECT Extent(ST_GeomFromWKB(ST_AsBinary(\"%s\"))) FROM (%s) AS ogrpgextent",
464 0 : pszGeomColumn, pszRawStatement );
465 : }
466 :
467 2 : return RunGetExtentRequest(psExtent, bForce, osCommand);
468 : }
|
