1 : /******************************************************************************
2 : * $Id: vfkdatablocksqlite.cpp 25340 2012-12-21 20:30:21Z rouault $
3 : *
4 : * Project: VFK Reader - Data block definition (SQLite)
5 : * Purpose: Implements VFKDataBlockSQLite
6 : * Author: Martin Landa, landa.martin gmail.com
7 : *
8 : ******************************************************************************
9 : * Copyright (c) 2012, Martin Landa <landa.martin gmail.com>
10 : *
11 : * Permission is hereby granted, free of charge, to any person
12 : * obtaining a copy of this software and associated documentation
13 : * files (the "Software"), to deal in the Software without
14 : * restriction, including without limitation the rights to use, copy,
15 : * modify, merge, publish, distribute, sublicense, and/or sell copies
16 : * of the Software, and to permit persons to whom the Software is
17 : * furnished to do so, subject to the following conditions:
18 : *
19 : * The above copyright notice and this permission notice shall be
20 : * included in all copies or substantial portions of the Software.
21 : *
22 : * THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND,
23 : * EXPRESS OR IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES OF
24 : * MERCHANTABILITY, FITNESS FOR A PARTICULAR PURPOSE AND
25 : * NONINFRINGEMENT. IN NO EVENT SHALL THE AUTHORS OR COPYRIGHT HOLDERS
26 : * BE LIABLE FOR ANY CLAIM, DAMAGES OR OTHER LIABILITY, WHETHER IN AN
27 : * ACTION OF CONTRACT, TORT OR OTHERWISE, ARISING FROM, OUT OF OR IN
28 : * CONNECTION WITH THE SOFTWARE OR THE USE OR OTHER DEALINGS IN THE
29 : * SOFTWARE.
30 : ****************************************************************************/
31 :
32 : #include "vfkreader.h"
33 : #include "vfkreaderp.h"
34 :
35 : #include "cpl_conv.h"
36 : #include "cpl_error.h"
37 :
38 : #ifdef HAVE_SQLITE
39 :
40 : /*!
41 : \brief Load geometry (point layers)
42 :
43 : \return number of invalid features
44 : */
45 1 : int VFKDataBlockSQLite::LoadGeometryPoint()
46 : {
47 : int nInvalid;
48 : double x, y;
49 :
50 1 : CPLString osSQL;
51 : sqlite3_stmt *hStmt;
52 :
53 : VFKFeatureSQLite *poFeature;
54 : VFKReaderSQLite *poReader;
55 :
56 1 : nInvalid = 0;
57 1 : poReader = (VFKReaderSQLite*) m_poReader;
58 :
59 1 : osSQL.Printf("SELECT SOURADNICE_Y,SOURADNICE_X FROM '%s'", m_pszName);
60 1 : hStmt = poReader->PrepareStatement(osSQL.c_str());
61 :
62 1 : ResetReading();
63 14 : while(poReader->ExecuteSQL(hStmt) == OGRERR_NONE) {
64 13 : poFeature = (VFKFeatureSQLite *) GetNextFeature();
65 13 : CPLAssert(NULL != poFeature);
66 :
67 13 : x = -1.0 * sqlite3_column_double(hStmt, 0);
68 13 : y = -1.0 * sqlite3_column_double(hStmt, 1);
69 13 : OGRPoint pt(x, y);
70 13 : if (!poFeature->SetGeometry(&pt))
71 0 : nInvalid++;
72 : }
73 1 : ResetReading();
74 :
75 1 : return nInvalid;
76 : }
77 :
78 : /*!
79 : \brief Load geometry (linestring SBP layer)
80 :
81 : \return number of invalid features
82 : */
83 1 : int VFKDataBlockSQLite::LoadGeometryLineStringSBP()
84 : {
85 : int nInvalid;
86 : int rowId;
87 : GUIntBig id, ipcb;
88 :
89 1 : CPLString osSQL;
90 : sqlite3_stmt *hStmt;
91 :
92 : VFKReaderSQLite *poReader;
93 : VFKDataBlockSQLite *poDataBlockPoints;
94 : VFKFeatureSQLite *poFeature, *poPoint, *poLine;
95 :
96 1 : OGRLineString oOGRLine;
97 :
98 1 : nInvalid = 0;
99 1 : poReader = (VFKReaderSQLite*) m_poReader;
100 1 : poLine = NULL;
101 :
102 1 : poDataBlockPoints = (VFKDataBlockSQLite *) m_poReader->GetDataBlock("SOBR");
103 1 : if (NULL == poDataBlockPoints) {
104 : CPLError(CE_Failure, CPLE_NotSupported,
105 0 : "Data block %s not found.\n", m_pszName);
106 0 : return nInvalid;
107 : }
108 :
109 1 : poDataBlockPoints->LoadGeometry();
110 :
111 3 : for (int i = 0; i < 2; i++) {
112 2 : if (i == 0)
113 : osSQL.Printf("SELECT BP_ID,PORADOVE_CISLO_BODU,_rowid_,ID FROM '%s' WHERE "
114 : "HP_ID IS NOT NULL OR OB_ID IS NOT NULL OR DPM_ID IS NOT NULL "
115 1 : "ORDER BY HP_ID,OB_ID,DPM_ID,PORADOVE_CISLO_BODU", m_pszName);
116 : else
117 : osSQL.Printf("SELECT BP_ID,PORADOVE_CISLO_BODU,_rowid_,ID FROM '%s' WHERE "
118 : "OB_ID IS NULL AND HP_ID IS NULL AND DPM_ID IS NULL "
119 1 : "ORDER BY ID,PORADOVE_CISLO_BODU", m_pszName);
120 :
121 2 : hStmt = poReader->PrepareStatement(osSQL.c_str());
122 :
123 30 : while(poReader->ExecuteSQL(hStmt) == OGRERR_NONE) {
124 26 : id = sqlite3_column_double(hStmt, 0);
125 26 : ipcb = sqlite3_column_double(hStmt, 1);
126 26 : rowId = sqlite3_column_int(hStmt, 2) - 1;
127 26 : poFeature = (VFKFeatureSQLite *) GetFeatureByIndex(rowId);
128 26 : if (!poFeature)
129 0 : continue;
130 26 : poFeature->SetGeometry(NULL);
131 :
132 26 : if (ipcb == 1) {
133 13 : if (!oOGRLine.IsEmpty()) {
134 12 : oOGRLine.setCoordinateDimension(2); /* force 2D */
135 12 : if (poLine && !poLine->SetGeometry(&oOGRLine))
136 0 : nInvalid++;
137 12 : oOGRLine.empty(); /* restore line */
138 : }
139 13 : poLine = poFeature;
140 : }
141 : else {
142 13 : poFeature->SetGeometryType(wkbUnknown);
143 : }
144 :
145 26 : poPoint = (VFKFeatureSQLite *) poDataBlockPoints->GetFeature("ID", id);
146 26 : if (!poPoint)
147 0 : continue;
148 26 : OGRPoint *pt = (OGRPoint *) poPoint->GetGeometry();
149 26 : if (!pt)
150 0 : continue;
151 26 : oOGRLine.addPoint(pt);
152 : }
153 : /* add last line */
154 2 : oOGRLine.setCoordinateDimension(2); /* force 2D */
155 2 : if (poLine && !poLine->SetGeometry(&oOGRLine))
156 0 : nInvalid++;
157 : }
158 :
159 1 : return nInvalid;
160 : }
161 :
162 : /*!
163 : \brief Load geometry (linestring HP/DPM layer)
164 :
165 : \return number of invalid features
166 : */
167 1 : int VFKDataBlockSQLite::LoadGeometryLineStringHP()
168 : {
169 : int nInvalid;
170 : int rowId;
171 :
172 1 : CPLString osColumn, osSQL;
173 : const char *vrColumn[2];
174 : GUIntBig vrValue[2];
175 :
176 : sqlite3_stmt *hStmt;
177 :
178 : VFKReaderSQLite *poReader;
179 : VFKDataBlockSQLite *poDataBlockLines;
180 : VFKFeatureSQLite *poFeature, *poLine;
181 :
182 1 : nInvalid = 0;
183 1 : poReader = (VFKReaderSQLite*) m_poReader;
184 :
185 1 : poDataBlockLines = (VFKDataBlockSQLite *) m_poReader->GetDataBlock("SBP");
186 1 : if (NULL == poDataBlockLines) {
187 : CPLError(CE_Failure, CPLE_NotSupported,
188 0 : "Data block %s not found.\n", m_pszName);
189 0 : return nInvalid;
190 : }
191 :
192 1 : poDataBlockLines->LoadGeometry();
193 1 : osColumn.Printf("%s_ID", m_pszName);
194 1 : vrColumn[0] = osColumn.c_str();
195 1 : vrColumn[1] = "PORADOVE_CISLO_BODU";
196 1 : vrValue[1] = 1; /* reduce to first segment */
197 :
198 1 : osSQL.Printf("SELECT ID,_rowid_ FROM '%s'", m_pszName);
199 1 : hStmt = poReader->PrepareStatement(osSQL.c_str());
200 :
201 15 : while(poReader->ExecuteSQL(hStmt) == OGRERR_NONE) {
202 13 : vrValue[0] = sqlite3_column_double(hStmt, 0);
203 13 : rowId = sqlite3_column_int(hStmt, 1) - 1;
204 13 : poFeature = (VFKFeatureSQLite *) GetFeatureByIndex(rowId);
205 :
206 13 : poLine = poDataBlockLines->GetFeature(vrColumn, vrValue, 2);
207 13 : if (!poLine || !poLine->GetGeometry())
208 0 : continue;
209 13 : if (!poFeature->SetGeometry(poLine->GetGeometry()))
210 0 : nInvalid++;
211 : }
212 :
213 1 : return nInvalid;
214 : }
215 :
216 : /*!
217 : \brief Load geometry (polygon BUD/PAR layers)
218 :
219 : \return number of invalid features
220 : */
221 1 : int VFKDataBlockSQLite::LoadGeometryPolygon()
222 : {
223 : int nInvalid;
224 : int rowId, nCount, nCountMax;
225 : bool bIsPar, bNewRing, bFound;
226 :
227 1 : CPLString osSQL;
228 : const char *vrColumn[2];
229 : GUIntBig vrValue[2];
230 : GUIntBig id, idOb;
231 :
232 : sqlite3_stmt *hStmt;
233 :
234 : VFKReaderSQLite *poReader;
235 : VFKDataBlockSQLite *poDataBlockLines1, *poDataBlockLines2;
236 : VFKFeatureSQLite *poFeature;
237 :
238 1 : VFKFeatureSQLiteList poLineList;
239 : /* first is to be considered as exterior */
240 1 : PointListArray poRingList;
241 :
242 1 : OGRLinearRing ogrRing;
243 1 : OGRPolygon ogrPolygon;
244 :
245 1 : nInvalid = 0;
246 1 : poReader = (VFKReaderSQLite*) m_poReader;
247 :
248 1 : if (EQUAL (m_pszName, "PAR")) {
249 1 : poDataBlockLines1 = (VFKDataBlockSQLite *) m_poReader->GetDataBlock("HP");
250 1 : poDataBlockLines2 = poDataBlockLines1;
251 1 : bIsPar = TRUE;
252 : }
253 : else {
254 0 : poDataBlockLines1 = (VFKDataBlockSQLite *) m_poReader->GetDataBlock("OB");
255 0 : poDataBlockLines2 = (VFKDataBlockSQLite *) m_poReader->GetDataBlock("SBP");
256 0 : bIsPar = FALSE;
257 : }
258 1 : if (NULL == poDataBlockLines1 || NULL == poDataBlockLines2) {
259 : CPLError(CE_Failure, CPLE_NotSupported,
260 0 : "Data block %s not found.\n", m_pszName);
261 0 : return nInvalid;
262 : }
263 :
264 1 : poDataBlockLines1->LoadGeometry();
265 1 : poDataBlockLines2->LoadGeometry();
266 :
267 1 : if (bIsPar) {
268 1 : vrColumn[0] = "PAR_ID_1";
269 1 : vrColumn[1] = "PAR_ID_2";
270 : }
271 : else {
272 0 : vrColumn[0] = "OB_ID";
273 0 : vrColumn[1] = "PORADOVE_CISLO_BODU";
274 0 : vrValue[1] = 1;
275 : }
276 :
277 1 : osSQL.Printf("SELECT ID,_rowid_ FROM '%s'", m_pszName);
278 1 : hStmt = poReader->PrepareStatement(osSQL.c_str());
279 :
280 3 : while(poReader->ExecuteSQL(hStmt) == OGRERR_NONE) {
281 1 : id = sqlite3_column_double(hStmt, 0);
282 1 : rowId = sqlite3_column_int(hStmt, 1) - 1;
283 1 : poFeature = (VFKFeatureSQLite *) GetFeatureByIndex(rowId);
284 1 : if (bIsPar) {
285 1 : vrValue[0] = vrValue[1] = id;
286 1 : poLineList = poDataBlockLines1->GetFeatures(vrColumn, vrValue, 2);
287 : }
288 : else {
289 : VFKFeatureSQLite *poLineSbp;
290 0 : std::vector<VFKFeatureSQLite *> poLineListOb;
291 : sqlite3_stmt *hStmtOb;
292 :
293 : osSQL.Printf("SELECT ID FROM '%s' WHERE BUD_ID = %llu",
294 0 : poDataBlockLines1->GetName(), id);
295 0 : hStmtOb = poReader->PrepareStatement(osSQL.c_str());
296 :
297 0 : while(poReader->ExecuteSQL(hStmtOb) == OGRERR_NONE) {
298 0 : idOb = sqlite3_column_double(hStmtOb, 0);
299 0 : vrValue[0] = idOb;
300 0 : poLineSbp = poDataBlockLines2->GetFeature(vrColumn, vrValue, 2);
301 0 : if (poLineSbp)
302 0 : poLineList.push_back(poLineSbp);
303 0 : }
304 : }
305 1 : if (poLineList.size() < 1)
306 0 : continue;
307 :
308 : /* clear */
309 1 : ogrPolygon.empty();
310 1 : poRingList.clear();
311 :
312 : /* collect rings (points) */
313 1 : bFound = FALSE;
314 1 : nCount = 0;
315 1 : nCountMax = poLineList.size() * 2;
316 15 : while (poLineList.size() > 0 && nCount < nCountMax) {
317 13 : bNewRing = !bFound ? TRUE : FALSE;
318 13 : bFound = FALSE;
319 18 : for (VFKFeatureSQLiteList::iterator iHp = poLineList.begin(), eHp = poLineList.end();
320 : iHp != eHp; ++iHp) {
321 18 : const OGRLineString *pLine = (OGRLineString *) (*iHp)->GetGeometry();
322 18 : if (pLine && AppendLineToRing(&poRingList, pLine, bNewRing)) {
323 13 : bFound = TRUE;
324 13 : poLineList.erase(iHp);
325 13 : break;
326 : }
327 : }
328 13 : nCount++;
329 : }
330 :
331 1 : if (poLineList.size() > 0) {
332 : CPLError(CE_Warning, CPLE_AppDefined,
333 : "Unable to collect rings for feature %llu (%s).\n",
334 0 : id, m_pszName);
335 0 : continue;
336 : }
337 :
338 : /* create rings */
339 2 : for (PointListArray::const_iterator iRing = poRingList.begin(), eRing = poRingList.end();
340 : iRing != eRing; ++iRing) {
341 1 : PointList *poList = *iRing;
342 1 : ogrRing.empty();
343 15 : for (PointList::iterator iPoint = poList->begin(), ePoint = poList->end();
344 : iPoint != ePoint; ++iPoint) {
345 14 : ogrRing.addPoint(&(*iPoint));
346 : }
347 1 : ogrPolygon.addRing(&ogrRing);
348 : }
349 :
350 : /* set polygon */
351 1 : ogrPolygon.setCoordinateDimension(2); /* force 2D */
352 1 : if (!poFeature->SetGeometry(&ogrPolygon))
353 0 : nInvalid++;
354 : }
355 :
356 : /* free ring list */
357 2 : for (PointListArray::iterator iRing = poRingList.begin(), eRing = poRingList.end();
358 : iRing != eRing; ++iRing) {
359 1 : delete (*iRing);
360 1 : *iRing = NULL;
361 : }
362 :
363 1 : return nInvalid;
364 : }
365 :
366 : /*!
367 : \brief Get first found feature based on it's property
368 :
369 : \param column property name
370 : \param value property value
371 :
372 : \return pointer to feature definition or NULL on failure (not found)
373 : */
374 26 : VFKFeatureSQLite *VFKDataBlockSQLite::GetFeature(const char *column, GUIntBig value)
375 : {
376 : int idx;
377 26 : CPLString osSQL;
378 : VFKReaderSQLite *poReader;
379 :
380 : sqlite3_stmt *hStmt;
381 :
382 26 : poReader = (VFKReaderSQLite*) m_poReader;
383 :
384 26 : osSQL.Printf("SELECT _rowid_ from '%s' WHERE %s = %llu", m_pszName, column, value);
385 26 : hStmt = poReader->PrepareStatement(osSQL.c_str());
386 26 : if (poReader->ExecuteSQL(hStmt) != OGRERR_NONE)
387 0 : return NULL;
388 :
389 26 : idx = sqlite3_column_int(hStmt, 0) - 1;
390 26 : if (idx < 0 || idx >= m_nFeatureCount) // ? assert
391 0 : return NULL;
392 :
393 26 : sqlite3_finalize(hStmt);
394 :
395 26 : return (VFKFeatureSQLite *) GetFeatureByIndex(idx);
396 : }
397 :
398 : /*!
399 : \brief Get first found feature based on it's properties (AND)
400 :
401 : \param column array of property names
402 : \param value array of property values
403 : \param num number of array items
404 :
405 : \return pointer to feature definition or NULL on failure (not found)
406 : */
407 13 : VFKFeatureSQLite *VFKDataBlockSQLite::GetFeature(const char **column, GUIntBig *value, int num)
408 : {
409 : int idx;
410 13 : CPLString osSQL, osItem;
411 : VFKReaderSQLite *poReader;
412 :
413 : sqlite3_stmt *hStmt;
414 :
415 13 : poReader = (VFKReaderSQLite*) m_poReader;
416 :
417 13 : osSQL.Printf("SELECT _rowid_ from '%s' WHERE ", m_pszName);
418 39 : for (int i = 0; i < num; i++) {
419 26 : if (i > 0)
420 13 : osItem.Printf(" AND %s = %llu", column[i], value[i]);
421 : else
422 13 : osItem.Printf("%s = %llu", column[i], value[i]);
423 26 : osSQL += osItem;
424 : }
425 :
426 13 : hStmt = poReader->PrepareStatement(osSQL.c_str());
427 13 : if (poReader->ExecuteSQL(hStmt) != OGRERR_NONE)
428 0 : return NULL;
429 :
430 13 : idx = sqlite3_column_int(hStmt, 0) - 1;
431 :
432 13 : if (idx < 0 || idx >= m_nFeatureCount) // ? assert
433 0 : return NULL;
434 :
435 13 : sqlite3_finalize(hStmt);
436 :
437 13 : return (VFKFeatureSQLite *) GetFeatureByIndex(idx);
438 : }
439 :
440 : /*!
441 : \brief Get features based on properties
442 :
443 : \param column array of property names
444 : \param value array of property values
445 : \param num number of array items
446 :
447 : \return list of features
448 : */
449 1 : VFKFeatureSQLiteList VFKDataBlockSQLite::GetFeatures(const char **column, GUIntBig *value, int num)
450 : {
451 : int idx;
452 1 : CPLString osSQL, osItem;
453 :
454 : VFKReaderSQLite *poReader;
455 1 : VFKFeatureSQLiteList fList;
456 :
457 : sqlite3_stmt *hStmt;
458 :
459 1 : poReader = (VFKReaderSQLite*) m_poReader;
460 :
461 1 : osSQL.Printf("SELECT _rowid_ from '%s' WHERE ", m_pszName);
462 3 : for (int i = 0; i < num; i++) {
463 2 : if (i > 0)
464 1 : osItem.Printf(" OR %s = %llu", column[i], value[i]);
465 : else
466 1 : osItem.Printf("%s = %llu", column[i], value[i]);
467 2 : osSQL += osItem;
468 : }
469 :
470 1 : hStmt = poReader->PrepareStatement(osSQL.c_str());
471 15 : while (poReader->ExecuteSQL(hStmt) == OGRERR_NONE) {
472 13 : idx = sqlite3_column_int(hStmt, 0) - 1;
473 13 : if (idx < 0 || idx >= m_nFeatureCount)
474 0 : continue; // assert?
475 13 : fList.push_back((VFKFeatureSQLite *)GetFeatureByIndex(idx));
476 : }
477 :
478 1 : return fList;
479 : }
480 :
481 : #endif // HAVE_SQLITE
|
