1 : /******************************************************************************
2 : * $Id: ogrpgresultlayer.cpp 17987 2009-11-10 15:39:16Z 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 17987 2009-11-10 15:39:16Z rouault $");
36 :
37 :
38 : /************************************************************************/
39 : /* OGRPGResultLayer() */
40 : /************************************************************************/
41 :
42 29 : OGRPGResultLayer::OGRPGResultLayer( OGRPGDataSource *poDSIn,
43 : const char * pszRawQueryIn,
44 29 : PGresult *hInitialResultIn )
45 : {
46 29 : poDS = poDSIn;
47 :
48 29 : iNextShapeId = 0;
49 :
50 29 : pszRawStatement = CPLStrdup(pszRawQueryIn);
51 :
52 29 : osWHERE = "";
53 :
54 29 : BuildFullQueryStatement();
55 :
56 29 : 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 29 : 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 22 : 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 29 : SetInitialQueryCursor();
95 29 : }
96 :
97 : /************************************************************************/
98 : /* ~OGRPGResultLayer() */
99 : /************************************************************************/
100 :
101 58 : OGRPGResultLayer::~OGRPGResultLayer()
102 :
103 : {
104 29 : CPLFree( pszRawStatement );
105 58 : }
106 :
107 : /************************************************************************/
108 : /* ReadResultDefinition() */
109 : /* */
110 : /* Build a schema from the current resultset. */
111 : /************************************************************************/
112 :
113 29 : OGRFeatureDefn *OGRPGResultLayer::ReadResultDefinition(PGresult *hInitialResultIn)
114 :
115 : {
116 29 : PGresult *hResult = hInitialResultIn;
117 :
118 : /* -------------------------------------------------------------------- */
119 : /* Parse the returned table information. */
120 : /* -------------------------------------------------------------------- */
121 29 : OGRFeatureDefn *poDefn = new OGRFeatureDefn( "sql_statement" );
122 : int iRawField;
123 :
124 29 : poDefn->Reference();
125 :
126 29 : for( iRawField = 0; iRawField < PQnfields(hResult); iRawField++ )
127 : {
128 153 : OGRFieldDefn oField( PQfname(hResult,iRawField), OFTString);
129 : Oid nTypeOID;
130 :
131 153 : nTypeOID = PQftype(hResult,iRawField);
132 :
133 153 : if( EQUAL(oField.GetNameRef(),"ogc_fid") )
134 : {
135 10 : bHasFid = TRUE;
136 10 : pszFIDColumn = CPLStrdup(oField.GetNameRef());
137 10 : continue;
138 : }
139 143 : else if( nTypeOID == poDS->GetGeometryOID() ||
140 : nTypeOID == poDS->GetGeographyOID() ||
141 : EQUAL(oField.GetNameRef(),"ST_AsText") ||
142 : EQUAL(oField.GetNameRef(),"ST_AsBinary") ||
143 : EQUAL(oField.GetNameRef(),"AsBinary") ||
144 : EQUAL(oField.GetNameRef(),"asEWKT") ||
145 : EQUAL(oField.GetNameRef(),"asText") )
146 : {
147 7 : if (bHasPostGISGeometry || bHasPostGISGeography )
148 : {
149 : CPLError(CE_Warning, CPLE_AppDefined,
150 0 : "More than one geometry column was found in the result of the SQL request. Only last one will be used");
151 : }
152 7 : if (nTypeOID == poDS->GetGeographyOID())
153 0 : bHasPostGISGeography = TRUE;
154 : else
155 7 : bHasPostGISGeometry = TRUE;
156 7 : CPLFree(pszGeomColumn);
157 7 : pszGeomColumn = CPLStrdup(oField.GetNameRef());
158 7 : continue;
159 : }
160 136 : else if( EQUAL(oField.GetNameRef(),"WKB_GEOMETRY") )
161 : {
162 6 : bHasWkb = TRUE;
163 6 : if( nTypeOID == OIDOID )
164 0 : bWkbAsOid = TRUE;
165 6 : continue;
166 : }
167 :
168 130 : if( nTypeOID == BYTEAOID )
169 : {
170 4 : oField.SetType( OFTBinary );
171 : }
172 156 : else if( nTypeOID == CHAROID ||
173 : nTypeOID == TEXTOID ||
174 : nTypeOID == BPCHAROID ||
175 : nTypeOID == VARCHAROID )
176 : {
177 30 : oField.SetType( OFTString );
178 30 : oField.SetWidth( PQfsize(hResult, iRawField) );
179 : }
180 96 : else if( nTypeOID == BOOLOID )
181 : {
182 4 : oField.SetType( OFTInteger );
183 4 : oField.SetWidth( 1 );
184 : }
185 92 : else if (nTypeOID == INT2OID )
186 : {
187 4 : oField.SetType( OFTInteger );
188 4 : oField.SetWidth( 5 );
189 : }
190 88 : else if (nTypeOID == INT4OID )
191 : {
192 16 : oField.SetType( OFTInteger );
193 : }
194 72 : else if ( nTypeOID == INT8OID )
195 : {
196 : /* FIXME: OFTInteger can not handle 64bit integers */
197 6 : oField.SetType( OFTInteger );
198 : }
199 92 : else if( nTypeOID == FLOAT4OID ||
200 : nTypeOID == FLOAT8OID ||
201 : nTypeOID == NUMERICOID )
202 : {
203 26 : oField.SetType( OFTReal );
204 : }
205 40 : else if ( nTypeOID == INT4ARRAYOID )
206 : {
207 4 : oField.SetType ( OFTIntegerList );
208 : }
209 44 : else if ( nTypeOID == FLOAT4ARRAYOID ||
210 : nTypeOID == FLOAT8ARRAYOID )
211 : {
212 8 : oField.SetType ( OFTRealList );
213 : }
214 40 : else if ( nTypeOID == TEXTARRAYOID ||
215 : nTypeOID == BPCHARARRAYOID ||
216 : nTypeOID == VARCHARARRAYOID )
217 : {
218 12 : oField.SetType ( OFTStringList );
219 : }
220 16 : else if ( nTypeOID == DATEOID )
221 : {
222 4 : oField.SetType( OFTDate );
223 : }
224 12 : else if ( nTypeOID == TIMEOID )
225 : {
226 4 : oField.SetType( OFTTime );
227 : }
228 16 : else if ( nTypeOID == TIMESTAMPOID ||
229 : nTypeOID == TIMESTAMPTZOID )
230 : {
231 : /* We can't deserialize properly timestamp with time zone */
232 : /* with binary cursors */
233 8 : if (nTypeOID == TIMESTAMPTZOID)
234 4 : bCanUseBinaryCursor = FALSE;
235 :
236 8 : oField.SetType( OFTDateTime );
237 : }
238 : else /* unknown type */
239 : {
240 0 : CPLDebug("PG", "Unhandled OID (%d) for column %d. Defaulting to String.", nTypeOID, iRawField);
241 0 : oField.SetType( OFTString );
242 : }
243 :
244 130 : poDefn->AddFieldDefn( &oField );
245 : }
246 :
247 29 : return poDefn;
248 : }
249 :
250 : /************************************************************************/
251 : /* BuildFullQueryStatement() */
252 : /************************************************************************/
253 :
254 31 : void OGRPGResultLayer::BuildFullQueryStatement()
255 :
256 : {
257 31 : if( pszQueryStatement != NULL )
258 : {
259 2 : CPLFree( pszQueryStatement );
260 2 : pszQueryStatement = NULL;
261 : }
262 :
263 31 : pszQueryStatement = (char*) CPLMalloc(strlen(pszRawStatement) + strlen(osWHERE) + 40);
264 :
265 31 : if (strlen(osWHERE) == 0)
266 29 : strcpy(pszQueryStatement, pszRawStatement);
267 : else
268 : sprintf(pszQueryStatement, "SELECT * FROM (%s) AS ogrpgsubquery %s",
269 2 : pszRawStatement, osWHERE.c_str());
270 31 : }
271 :
272 : /************************************************************************/
273 : /* ResetReading() */
274 : /************************************************************************/
275 :
276 14 : void OGRPGResultLayer::ResetReading()
277 :
278 : {
279 14 : OGRPGLayer::ResetReading();
280 14 : }
281 :
282 : /************************************************************************/
283 : /* GetFeatureCount() */
284 : /************************************************************************/
285 :
286 6 : int OGRPGResultLayer::GetFeatureCount( int bForce )
287 :
288 : {
289 6 : if( TestCapability(OLCFastFeatureCount) == FALSE )
290 2 : return OGRPGLayer::GetFeatureCount( bForce );
291 :
292 4 : PGconn *hPGConn = poDS->GetPGConn();
293 4 : PGresult *hResult = NULL;
294 4 : CPLString osCommand;
295 4 : int nCount = 0;
296 :
297 : osCommand.Printf(
298 : "SELECT count(*) FROM (%s) AS ogrpgcount",
299 4 : pszQueryStatement );
300 :
301 4 : hResult = PQexec(hPGConn, osCommand);
302 4 : if( hResult != NULL && PQresultStatus(hResult) == PGRES_TUPLES_OK )
303 4 : nCount = atoi(PQgetvalue(hResult,0,0));
304 : else
305 0 : CPLDebug( "PG", "%s; failed.", osCommand.c_str() );
306 4 : OGRPGClearResult( hResult );
307 :
308 4 : return nCount;
309 : }
310 :
311 :
312 : /************************************************************************/
313 : /* TestCapability() */
314 : /************************************************************************/
315 :
316 12 : int OGRPGResultLayer::TestCapability( const char * pszCap )
317 :
318 : {
319 12 : if( EQUAL(pszCap,OLCFastFeatureCount) ||
320 : EQUAL(pszCap,OLCFastSetNextByIndex) )
321 : return (m_poFilterGeom == NULL ||
322 8 : ((bHasPostGISGeometry || bHasPostGISGeography) && nSRSId != -2)) && m_poAttrQuery == NULL;
323 :
324 4 : else if( EQUAL(pszCap,OLCFastSpatialFilter) )
325 0 : return ((bHasPostGISGeometry || bHasPostGISGeography) && nSRSId != -2) && m_poAttrQuery == NULL;
326 :
327 4 : else if( EQUAL(pszCap,OLCFastGetExtent) )
328 4 : return (bHasPostGISGeometry && nSRSId != -2) && m_poAttrQuery == NULL;
329 :
330 0 : else if( EQUAL(pszCap,OLCStringsAsUTF8) )
331 0 : return TRUE;
332 :
333 : else
334 0 : return FALSE;
335 : }
336 :
337 :
338 : /************************************************************************/
339 : /* GetNextFeature() */
340 : /************************************************************************/
341 :
342 2072 : OGRFeature *OGRPGResultLayer::GetNextFeature()
343 :
344 : {
345 :
346 2 : for( ; TRUE; )
347 : {
348 : OGRFeature *poFeature;
349 :
350 2072 : poFeature = GetNextRawFeature();
351 2072 : if( poFeature == NULL )
352 14 : return NULL;
353 :
354 2058 : if( (m_poFilterGeom == NULL
355 : || ((bHasPostGISGeometry || bHasPostGISGeography) && nSRSId != -2)
356 : || FilterGeometry( poFeature->GetGeometryRef() ) )
357 : && (m_poAttrQuery == NULL
358 : || m_poAttrQuery->Evaluate( poFeature )) )
359 2056 : return poFeature;
360 :
361 2 : delete poFeature;
362 : }
363 : }
364 :
365 : /************************************************************************/
366 : /* SetSpatialFilter() */
367 : /************************************************************************/
368 :
369 4 : void OGRPGResultLayer::SetSpatialFilter( OGRGeometry * poGeomIn )
370 :
371 : {
372 4 : if( InstallFilter( poGeomIn ) )
373 : {
374 4 : if ((bHasPostGISGeometry || bHasPostGISGeography) && nSRSId != -2)
375 : {
376 2 : if( m_poFilterGeom != NULL)
377 : {
378 2 : OGREnvelope sEnvelope;
379 :
380 2 : m_poFilterGeom->getEnvelope( &sEnvelope );
381 : osWHERE.Printf("WHERE \"%s\" && SetSRID('BOX3D(%.12f %.12f, %.12f %.12f)'::box3d,%d) ",
382 : pszGeomColumn,
383 : sEnvelope.MinX, sEnvelope.MinY,
384 : sEnvelope.MaxX, sEnvelope.MaxY,
385 2 : nSRSId );
386 : }
387 : else
388 : {
389 0 : osWHERE = "";
390 : }
391 :
392 2 : BuildFullQueryStatement();
393 : }
394 :
395 4 : ResetReading();
396 : }
397 :
398 4 : }
399 :
400 : /************************************************************************/
401 : /* GetExtent() */
402 : /* */
403 : /* For PostGIS use internal Extend(geometry) function */
404 : /* in other cases we use standard OGRLayer::GetExtent() */
405 : /************************************************************************/
406 :
407 2 : OGRErr OGRPGResultLayer::GetExtent( OGREnvelope *psExtent, int bForce )
408 : {
409 2 : CPLString osCommand;
410 :
411 2 : if ( TestCapability(OLCFastGetExtent) )
412 : {
413 : /* Do not take the spatial filter into account */
414 : osCommand.Printf( "SELECT Extent(\"%s\") FROM (%s) AS ogrpgextent",
415 1 : pszGeomColumn, pszRawStatement );
416 : }
417 1 : else if ( bHasPostGISGeography )
418 : {
419 : /* Probably not very efficient, but more efficient than client-side implementation */
420 : osCommand.Printf( "SELECT Extent(ST_GeomFromWKB(ST_AsBinary(\"%s\"))) FROM (%s) AS ogrpgextent",
421 0 : pszGeomColumn, pszRawStatement );
422 : }
423 :
424 2 : return RunGetExtentRequest(psExtent, bForce, osCommand);
425 : }
|
