1 : /******************************************************************************
2 : * $Id: vfkdatablocksqlite.cpp 24217 2012-04-11 14:25:09Z martinl $
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 2 : int VFKDataBlockSQLite::LoadGeometryPoint()
46 : {
47 : int nInvalid;
48 : double x, y;
49 :
50 2 : CPLString osSQL;
51 : sqlite3_stmt *hStmt;
52 :
53 : VFKFeatureSQLite *poFeature;
54 : VFKReaderSQLite *poReader;
55 :
56 2 : nInvalid = 0;
57 2 : poReader = (VFKReaderSQLite*) m_poReader;
58 :
59 2 : osSQL.Printf("SELECT SOURADNICE_Y,SOURADNICE_X FROM '%s'", m_pszName);
60 2 : hStmt = poReader->PrepareStatement(osSQL.c_str());
61 :
62 2 : ResetReading();
63 28 : while(poReader->ExecuteSQL(hStmt) == OGRERR_NONE) {
64 26 : poFeature = (VFKFeatureSQLite *) GetNextFeature(); // assert
65 26 : x = -1.0 * sqlite3_column_double(hStmt, 0);
66 26 : y = -1.0 * sqlite3_column_double(hStmt, 1);
67 26 : OGRPoint pt(x, y);
68 26 : if (!poFeature->SetGeometry(&pt))
69 0 : nInvalid++;
70 : }
71 2 : ResetReading();
72 :
73 2 : return nInvalid;
74 : }
75 :
76 : /*!
77 : \brief Load geometry (linestring SBP layer)
78 :
79 : \return number of invalid features
80 : */
81 2 : int VFKDataBlockSQLite::LoadGeometryLineStringSBP()
82 : {
83 : int nInvalid;
84 : int rowId;
85 : GUIntBig id, ipcb;
86 :
87 2 : CPLString osSQL;
88 : sqlite3_stmt *hStmt;
89 :
90 : VFKReaderSQLite *poReader;
91 : VFKDataBlockSQLite *poDataBlockPoints;
92 : VFKFeatureSQLite *poFeature, *poPoint, *poLine;
93 :
94 2 : OGRLineString oOGRLine;
95 :
96 2 : nInvalid = 0;
97 2 : poReader = (VFKReaderSQLite*) m_poReader;
98 2 : poLine = NULL;
99 :
100 2 : poDataBlockPoints = (VFKDataBlockSQLite *) m_poReader->GetDataBlock("SOBR");
101 2 : if (NULL == poDataBlockPoints) {
102 : CPLError(CE_Failure, CPLE_NotSupported,
103 0 : "Data block %s not found.\n", m_pszName);
104 0 : return nInvalid;
105 : }
106 :
107 2 : poDataBlockPoints->LoadGeometry();
108 :
109 6 : for (int i = 0; i < 2; i++) {
110 4 : if (i == 0)
111 : osSQL.Printf("SELECT BP_ID,PORADOVE_CISLO_BODU,_rowid_,ID FROM '%s' WHERE "
112 : "HP_ID IS NOT NULL OR OB_ID IS NOT NULL OR DPM_ID IS NOT NULL "
113 2 : "ORDER BY HP_ID,OB_ID,DPM_ID,PORADOVE_CISLO_BODU", m_pszName);
114 : else
115 : osSQL.Printf("SELECT BP_ID,PORADOVE_CISLO_BODU,_rowid_,ID FROM '%s' WHERE "
116 : "OB_ID IS NULL AND HP_ID IS NULL AND DPM_ID IS NULL "
117 2 : "ORDER BY ID,PORADOVE_CISLO_BODU", m_pszName);
118 :
119 4 : hStmt = poReader->PrepareStatement(osSQL.c_str());
120 :
121 60 : while(poReader->ExecuteSQL(hStmt) == OGRERR_NONE) {
122 52 : id = sqlite3_column_double(hStmt, 0);
123 52 : ipcb = sqlite3_column_double(hStmt, 1);
124 52 : rowId = sqlite3_column_int(hStmt, 2) - 1;
125 52 : poFeature = (VFKFeatureSQLite *) GetFeatureByIndex(rowId);
126 52 : poFeature->SetGeometry(NULL);
127 :
128 52 : if (ipcb == 1) {
129 26 : if (!oOGRLine.IsEmpty()) {
130 24 : oOGRLine.setCoordinateDimension(2); /* force 2D */
131 24 : if (poLine)
132 : {
133 24 : if (!poLine->SetGeometry(&oOGRLine))
134 0 : nInvalid++;
135 : }
136 24 : oOGRLine.empty(); /* restore line */
137 : }
138 26 : poLine = poFeature;
139 : }
140 : else {
141 26 : poFeature->SetGeometryType(wkbUnknown);
142 : }
143 52 : poPoint = (VFKFeatureSQLite *) poDataBlockPoints->GetFeature("ID", id);
144 52 : if (!poPoint)
145 0 : continue;
146 52 : OGRPoint *pt = (OGRPoint *) poPoint->GetGeometry();
147 52 : if (!pt)
148 0 : continue;
149 52 : oOGRLine.addPoint(pt);
150 : }
151 : /* add last line */
152 4 : oOGRLine.setCoordinateDimension(2); /* force 2D */
153 4 : if (poLine) {
154 4 : if (!poLine->SetGeometry(&oOGRLine))
155 0 : nInvalid++;
156 : }
157 : }
158 :
159 2 : return nInvalid;
160 : }
161 :
162 : /*!
163 : \brief Load geometry (linestring HP/DPM layer)
164 :
165 : \return number of invalid features
166 : */
167 2 : int VFKDataBlockSQLite::LoadGeometryLineStringHP()
168 : {
169 : int nInvalid;
170 : int rowId;
171 :
172 2 : 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 2 : nInvalid = 0;
183 2 : poReader = (VFKReaderSQLite*) m_poReader;
184 :
185 2 : poDataBlockLines = (VFKDataBlockSQLite *) m_poReader->GetDataBlock("SBP");
186 2 : 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 2 : poDataBlockLines->LoadGeometry();
193 2 : osColumn.Printf("%s_ID", m_pszName);
194 2 : vrColumn[0] = osColumn.c_str();
195 2 : vrColumn[1] = "PORADOVE_CISLO_BODU";
196 2 : vrValue[1] = 1; /* reduce to first segment */
197 :
198 2 : osSQL.Printf("SELECT ID,_rowid_ FROM '%s'", m_pszName);
199 2 : hStmt = poReader->PrepareStatement(osSQL.c_str());
200 :
201 30 : while(poReader->ExecuteSQL(hStmt) == OGRERR_NONE) {
202 26 : vrValue[0] = sqlite3_column_double(hStmt, 0);
203 26 : rowId = sqlite3_column_int(hStmt, 1) - 1;
204 26 : poFeature = (VFKFeatureSQLite *) GetFeatureByIndex(rowId);
205 :
206 26 : poLine = poDataBlockLines->GetFeature(vrColumn, vrValue, 2);
207 26 : if (!poLine || !poLine->GetGeometry())
208 0 : continue;
209 26 : if (!poFeature->SetGeometry(poLine->GetGeometry()))
210 0 : nInvalid++;
211 : }
212 :
213 2 : return nInvalid;
214 : }
215 :
216 : /*!
217 : \brief Load geometry (polygon BUD/PAR layers)
218 :
219 : \return number of invalid features
220 : */
221 2 : int VFKDataBlockSQLite::LoadGeometryPolygon()
222 : {
223 : int nInvalid;
224 : int rowId, nCount, nCountMax;
225 : bool bIsPar, bNewRing, bFound;
226 :
227 2 : 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 2 : VFKFeatureSQLiteList poLineList;
239 : /* first is to be considered as exterior */
240 2 : PointListArray poRingList;
241 :
242 2 : OGRLinearRing ogrRing;
243 2 : OGRPolygon ogrPolygon;
244 :
245 2 : nInvalid = 0;
246 2 : poReader = (VFKReaderSQLite*) m_poReader;
247 :
248 2 : if (EQUAL (m_pszName, "PAR")) {
249 2 : poDataBlockLines1 = (VFKDataBlockSQLite *) m_poReader->GetDataBlock("HP");
250 2 : poDataBlockLines2 = poDataBlockLines1;
251 2 : 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 2 : 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 2 : poDataBlockLines1->LoadGeometry();
265 2 : poDataBlockLines2->LoadGeometry();
266 :
267 2 : if (bIsPar) {
268 2 : vrColumn[0] = "PAR_ID_1";
269 2 : 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 2 : osSQL.Printf("SELECT ID,_rowid_ FROM '%s'", m_pszName);
278 2 : hStmt = poReader->PrepareStatement(osSQL.c_str());
279 :
280 6 : while(poReader->ExecuteSQL(hStmt) == OGRERR_NONE) {
281 2 : id = sqlite3_column_double(hStmt, 0);
282 2 : rowId = sqlite3_column_int(hStmt, 1) - 1;
283 2 : poFeature = (VFKFeatureSQLite *) GetFeatureByIndex(rowId);
284 2 : if (bIsPar) {
285 2 : vrValue[0] = vrValue[1] = id;
286 2 : 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 2 : if (poLineList.size() < 1)
306 0 : continue;
307 :
308 : /* clear */
309 2 : ogrPolygon.empty();
310 2 : poRingList.clear();
311 :
312 : /* collect rings (points) */
313 2 : bFound = FALSE;
314 2 : nCount = 0;
315 2 : nCountMax = poLineList.size() * 2;
316 30 : while (poLineList.size() > 0 && nCount < nCountMax) {
317 26 : bNewRing = !bFound ? TRUE : FALSE;
318 26 : bFound = FALSE;
319 82 : for (VFKFeatureSQLiteList::iterator iHp = poLineList.begin(), eHp = poLineList.end();
320 : iHp != eHp; ++iHp) {
321 82 : const OGRLineString *pLine = (OGRLineString *) (*iHp)->GetGeometry();
322 82 : if (pLine && AppendLineToRing(&poRingList, pLine, bNewRing)) {
323 26 : bFound = TRUE;
324 26 : poLineList.erase(iHp);
325 26 : break;
326 : }
327 : }
328 26 : nCount++;
329 : }
330 :
331 2 : 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 4 : for (PointListArray::const_iterator iRing = poRingList.begin(), eRing = poRingList.end();
340 : iRing != eRing; ++iRing) {
341 2 : PointList *poList = *iRing;
342 2 : ogrRing.empty();
343 30 : for (PointList::iterator iPoint = poList->begin(), ePoint = poList->end();
344 : iPoint != ePoint; ++iPoint) {
345 28 : ogrRing.addPoint(&(*iPoint));
346 : }
347 2 : ogrPolygon.addRing(&ogrRing);
348 : }
349 :
350 : /* set polygon */
351 2 : ogrPolygon.setCoordinateDimension(2); /* force 2D */
352 2 : if (!poFeature->SetGeometry(&ogrPolygon))
353 0 : nInvalid++;
354 : }
355 :
356 : /* free ring list */
357 4 : for (PointListArray::iterator iRing = poRingList.begin(), eRing = poRingList.end();
358 : iRing != eRing; ++iRing) {
359 2 : delete (*iRing);
360 2 : *iRing = NULL;
361 : }
362 :
363 2 : 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
373 : \return NULL on failure (not found)
374 : */
375 52 : VFKFeatureSQLite *VFKDataBlockSQLite::GetFeature(const char *column, GUIntBig value)
376 : {
377 : int idx;
378 52 : CPLString osSQL;
379 : VFKReaderSQLite *poReader;
380 :
381 : sqlite3_stmt *hStmt;
382 :
383 52 : poReader = (VFKReaderSQLite*) m_poReader;
384 :
385 52 : osSQL.Printf("SELECT _rowid_ from '%s' WHERE %s = %llu", m_pszName, column, value);
386 52 : hStmt = poReader->PrepareStatement(osSQL.c_str());
387 52 : if (poReader->ExecuteSQL(hStmt) != OGRERR_NONE)
388 0 : return NULL;
389 :
390 52 : idx = sqlite3_column_int(hStmt, 0) - 1;
391 52 : if (idx < 0 || idx >= m_nFeatureCount) // ? assert
392 0 : return NULL;
393 :
394 52 : sqlite3_finalize(hStmt);
395 :
396 52 : return (VFKFeatureSQLite *) GetFeatureByIndex(idx);
397 : }
398 :
399 : /*!
400 : \brief Get first found feature based on it's properties (AND)
401 :
402 : \param column array of property names
403 : \param value array of property values
404 : \param num number of array items
405 :
406 : \return pointer to feature definition
407 : \return NULL on failure (not found)
408 : */
409 26 : VFKFeatureSQLite *VFKDataBlockSQLite::GetFeature(const char **column, GUIntBig *value, int num)
410 : {
411 : int idx;
412 26 : CPLString osSQL, osItem;
413 : VFKReaderSQLite *poReader;
414 :
415 : sqlite3_stmt *hStmt;
416 :
417 26 : poReader = (VFKReaderSQLite*) m_poReader;
418 :
419 26 : osSQL.Printf("SELECT _rowid_ from '%s' WHERE ", m_pszName);
420 78 : for (int i = 0; i < num; i++) {
421 52 : if (i > 0)
422 26 : osItem.Printf(" AND %s = %llu", column[i], value[i]);
423 : else
424 26 : osItem.Printf("%s = %llu", column[i], value[i]);
425 52 : osSQL += osItem;
426 : }
427 :
428 26 : hStmt = poReader->PrepareStatement(osSQL.c_str());
429 26 : if (poReader->ExecuteSQL(hStmt) != OGRERR_NONE)
430 0 : return NULL;
431 :
432 26 : idx = sqlite3_column_int(hStmt, 0) - 1;
433 :
434 26 : if (idx < 0 || idx >= m_nFeatureCount) // ? assert
435 0 : return NULL;
436 :
437 26 : sqlite3_finalize(hStmt);
438 :
439 26 : return (VFKFeatureSQLite *) GetFeatureByIndex(idx);
440 : }
441 :
442 : /*!
443 : \brief Get features based on properties
444 :
445 : \param column array of property names
446 : \param value array of property values
447 : \param num number of array items
448 :
449 : \return list of features
450 : */
451 2 : VFKFeatureSQLiteList VFKDataBlockSQLite::GetFeatures(const char **column, GUIntBig *value, int num)
452 : {
453 : int idx;
454 2 : CPLString osSQL, osItem;
455 :
456 : VFKReaderSQLite *poReader;
457 2 : VFKFeatureSQLiteList fList;
458 :
459 : sqlite3_stmt *hStmt;
460 :
461 2 : poReader = (VFKReaderSQLite*) m_poReader;
462 :
463 2 : osSQL.Printf("SELECT _rowid_ from '%s' WHERE ", m_pszName);
464 6 : for (int i = 0; i < num; i++) {
465 4 : if (i > 0)
466 2 : osItem.Printf(" OR %s = %llu", column[i], value[i]);
467 : else
468 2 : osItem.Printf("%s = %llu", column[i], value[i]);
469 4 : osSQL += osItem;
470 : }
471 :
472 2 : hStmt = poReader->PrepareStatement(osSQL.c_str());
473 30 : while (poReader->ExecuteSQL(hStmt) == OGRERR_NONE) {
474 26 : idx = sqlite3_column_int(hStmt, 0) - 1;
475 26 : if (idx < 0 || idx >= m_nFeatureCount)
476 0 : continue; // assert?
477 26 : fList.push_back((VFKFeatureSQLite *)GetFeatureByIndex(idx));
478 : }
479 :
480 2 : return fList;
481 : }
482 :
483 : #endif // HAVE_SQLITE
|
