1 /*
2
3 srs_init.c -- populating the SPATIAL_REF_SYS table
4
5 version 5.0, 2020 August 1
6
7 Author: Sandro Furieri a.furieri@lqt.it
8
9 -----------------------------------------------------------------------------
10
11 Version: MPL 1.1/GPL 2.0/LGPL 2.1
12
13 The contents of this file are subject to the Mozilla Public License Version
14 1.1 (the "License"); you may not use this file except in compliance with
15 the License. You may obtain a copy of the License at
16 http://www.mozilla.org/MPL/
17
18 Software distributed under the License is distributed on an "AS IS" basis,
19 WITHOUT WARRANTY OF ANY KIND, either express or implied. See the License
20 for the specific language governing rights and limitations under the
21 License.
22
23 The Original Code is the SpatiaLite library
24
25 The Initial Developer of the Original Code is Alessandro Furieri
26
27 Portions created by the Initial Developer are Copyright (C) 2008-2020
28 the Initial Developer. All Rights Reserved.
29
30 Contributor(s):
31
32 Alternatively, the contents of this file may be used under the terms of
33 either the GNU General Public License Version 2 or later (the "GPL"), or
34 the GNU Lesser General Public License Version 2.1 or later (the "LGPL"),
35 in which case the provisions of the GPL or the LGPL are applicable instead
36 of those above. If you wish to allow use of your version of this file only
37 under the terms of either the GPL or the LGPL, and not to allow others to
38 use your version of this file under the terms of the MPL, indicate your
39 decision by deleting the provisions above and replace them with the notice
40 and other provisions required by the GPL or the LGPL. If you do not delete
41 the provisions above, a recipient may use your version of this file under
42 the terms of any one of the MPL, the GPL or the LGPL.
43
44 */
45
46 #include <stdlib.h>
47 #include <stdio.h>
48 #include <string.h>
49
50 #include <spatialite/sqlite.h>
51 #include <spatialite/debug.h>
52
53 #ifdef _WIN32
54 #define strcasecmp _stricmp
55 #endif /* not WIN32 */
56
57 #include <spatialite.h>
58 #include <spatialite_private.h>
59
60 static void
free_epsg_def(struct epsg_defs * ptr)61 free_epsg_def (struct epsg_defs *ptr)
62 {
63 /* memory cleanup - destroying an EPSG def item */
64 if (ptr->auth_name)
65 free (ptr->auth_name);
66 if (ptr->ref_sys_name)
67 free (ptr->ref_sys_name);
68 if (ptr->proj4text)
69 free (ptr->proj4text);
70 if (ptr->srs_wkt)
71 free (ptr->srs_wkt);
72 if (ptr->spheroid)
73 free (ptr->spheroid);
74 if (ptr->prime_meridian)
75 free (ptr->prime_meridian);
76 if (ptr->datum)
77 free (ptr->datum);
78 if (ptr->projection)
79 free (ptr->projection);
80 if (ptr->unit)
81 free (ptr->unit);
82 if (ptr->axis_1)
83 free (ptr->axis_1);
84 if (ptr->orientation_1)
85 free (ptr->orientation_1);
86 if (ptr->axis_2)
87 free (ptr->axis_2);
88 if (ptr->orientation_2)
89 free (ptr->orientation_2);
90 free (ptr);
91 }
92
93 SPATIALITE_PRIVATE struct epsg_defs *
add_epsg_def(int filter_srid,struct epsg_defs ** first,struct epsg_defs ** last,int srid,const char * auth_name,int auth_srid,const char * ref_sys_name)94 add_epsg_def (int filter_srid, struct epsg_defs **first,
95 struct epsg_defs **last, int srid, const char *auth_name,
96 int auth_srid, const char *ref_sys_name)
97 {
98 /* appending an EPSG def to the list */
99 int len;
100 struct epsg_defs *p;
101 if (filter_srid == GAIA_EPSG_NONE)
102 return NULL;
103 if (filter_srid == GAIA_EPSG_ANY || filter_srid == GAIA_EPSG_WGS84_ONLY)
104 ;
105 else if (srid != filter_srid)
106 return NULL;
107 p = malloc (sizeof (struct epsg_defs));
108 if (!p)
109 return NULL;
110 p->srid = srid;
111 p->auth_name = NULL;
112 p->auth_srid = auth_srid;
113 p->ref_sys_name = NULL;
114 p->proj4text = NULL;
115 p->srs_wkt = NULL;
116 p->next = NULL;
117 if (auth_name)
118 {
119 len = strlen (auth_name);
120 if (len > 0)
121 {
122 p->auth_name = malloc (len + 1);
123 if (p->auth_name == NULL)
124 goto error;
125 strcpy (p->auth_name, auth_name);
126 }
127 }
128 if (ref_sys_name)
129 {
130 len = strlen (ref_sys_name);
131 if (len > 0)
132 {
133 p->ref_sys_name = malloc (len + 1);
134 if (p->ref_sys_name == NULL)
135 goto error;
136 strcpy (p->ref_sys_name, ref_sys_name);
137 }
138 }
139 p->is_geographic = -1;
140 p->flipped_axes = -1;
141 p->spheroid = NULL;
142 p->prime_meridian = NULL;
143 p->datum = NULL;
144 p->projection = NULL;
145 p->unit = NULL;
146 p->axis_1 = NULL;
147 p->orientation_1 = NULL;
148 p->axis_2 = NULL;
149 p->orientation_2 = NULL;
150 if (*first == NULL)
151 *first = p;
152 if (*last != NULL)
153 (*last)->next = p;
154 *last = p;
155 return p;
156 error:
157 free_epsg_def (p);
158 return NULL;
159 }
160
161 SPATIALITE_PRIVATE struct epsg_defs *
add_epsg_def_ex(int filter_srid,struct epsg_defs ** first,struct epsg_defs ** last,int srid,const char * auth_name,int auth_srid,const char * ref_sys_name,int is_geographic,int flipped_axes,const char * spheroid,const char * prime_meridian,const char * datum,const char * projection,const char * unit,const char * axis_1,const char * orientation_1,const char * axis_2,const char * orientation_2)162 add_epsg_def_ex (int filter_srid, struct epsg_defs **first,
163 struct epsg_defs **last, int srid, const char *auth_name,
164 int auth_srid, const char *ref_sys_name, int is_geographic,
165 int flipped_axes, const char *spheroid,
166 const char *prime_meridian, const char *datum,
167 const char *projection, const char *unit, const char *axis_1,
168 const char *orientation_1, const char *axis_2,
169 const char *orientation_2)
170 {
171 /* appending an EPSG def to the list */
172 int len;
173 struct epsg_defs *p;
174 if (filter_srid == GAIA_EPSG_NONE)
175 return NULL;
176 if (filter_srid == GAIA_EPSG_ANY || filter_srid == GAIA_EPSG_WGS84_ONLY)
177 ;
178 else if (srid != filter_srid)
179 return NULL;
180 p = malloc (sizeof (struct epsg_defs));
181 if (!p)
182 return NULL;
183 p->srid = srid;
184 p->auth_name = NULL;
185 p->auth_srid = auth_srid;
186 p->ref_sys_name = NULL;
187 p->proj4text = NULL;
188 p->srs_wkt = NULL;
189 p->spheroid = NULL;
190 p->prime_meridian = NULL;
191 p->datum = NULL;
192 p->unit = NULL;
193 p->axis_1 = NULL;
194 p->orientation_1 = NULL;
195 p->axis_2 = NULL;
196 p->orientation_2 = NULL;
197 p->next = NULL;
198 if (auth_name)
199 {
200 len = strlen (auth_name);
201 if (len > 0)
202 {
203 p->auth_name = malloc (len + 1);
204 if (p->auth_name == NULL)
205 goto error;
206 strcpy (p->auth_name, auth_name);
207 }
208 }
209 if (ref_sys_name)
210 {
211 len = strlen (ref_sys_name);
212 if (len > 0)
213 {
214 p->ref_sys_name = malloc (len + 1);
215 if (p->ref_sys_name == NULL)
216 goto error;
217 strcpy (p->ref_sys_name, ref_sys_name);
218 }
219 }
220 p->is_geographic = is_geographic;
221 p->flipped_axes = flipped_axes;
222 if (spheroid)
223 {
224 len = strlen (spheroid);
225 p->spheroid = malloc (len + 1);
226 if (p->spheroid == NULL)
227 goto error;
228 strcpy (p->spheroid, spheroid);
229 }
230 if (prime_meridian)
231 {
232 len = strlen (prime_meridian);
233 p->prime_meridian = malloc (len + 1);
234 if (p->prime_meridian == NULL)
235 goto error;
236 strcpy (p->prime_meridian, prime_meridian);
237 }
238 if (datum)
239 {
240 len = strlen (datum);
241 p->datum = malloc (len + 1);
242 if (p->datum == NULL)
243 goto error;
244 strcpy (p->datum, datum);
245 }
246 if (projection)
247 {
248 len = strlen (projection);
249 p->projection = malloc (len + 1);
250 if (p->projection == NULL)
251 goto error;
252 strcpy (p->projection, projection);
253 }
254 if (unit)
255 {
256 len = strlen (unit);
257 p->unit = malloc (len + 1);
258 if (p->unit == NULL)
259 goto error;
260 strcpy (p->unit, unit);
261 }
262 if (axis_1)
263 {
264 len = strlen (axis_1);
265 p->axis_1 = malloc (len + 1);
266 if (p->axis_1 == NULL)
267 goto error;
268 strcpy (p->axis_1, axis_1);
269 }
270 if (orientation_1)
271 {
272 len = strlen (orientation_1);
273 p->orientation_1 = malloc (len + 1);
274 if (p->orientation_1 == NULL)
275 goto error;
276 strcpy (p->orientation_1, orientation_1);
277 }
278 if (axis_2)
279 {
280 len = strlen (axis_2);
281 p->axis_2 = malloc (len + 1);
282 if (p->axis_2 == NULL)
283 goto error;
284 strcpy (p->axis_2, axis_2);
285 }
286 if (orientation_2)
287 {
288 len = strlen (orientation_2);
289 p->orientation_2 = malloc (len + 1);
290 if (p->orientation_2 == NULL)
291 goto error;
292 strcpy (p->orientation_2, orientation_2);
293 }
294 if (*first == NULL)
295 *first = p;
296 if (*last != NULL)
297 (*last)->next = p;
298 *last = p;
299 return p;
300 error:
301 free_epsg_def (p);
302 return NULL;
303 }
304
305 SPATIALITE_PRIVATE void
add_proj4text(struct epsg_defs * p,int count,const char * text)306 add_proj4text (struct epsg_defs *p, int count, const char *text)
307 {
308 /* creating the PROJ4TEXT string */
309 int len;
310 int olen;
311 char *string;
312 if (p == NULL || text == NULL)
313 return;
314 len = strlen (text);
315 if (!count)
316 {
317 p->proj4text = malloc (len + 1);
318 if (p->proj4text == NULL)
319 return;
320 strcpy (p->proj4text, text);
321 return;
322 }
323 if (p->proj4text == NULL)
324 return;
325 olen = strlen (p->proj4text);
326 string = malloc (len + olen + 1);
327 if (string == NULL)
328 return;
329 strcpy (string, p->proj4text);
330 free (p->proj4text);
331 p->proj4text = string;
332 strcat (p->proj4text, text);
333 }
334
335 SPATIALITE_PRIVATE void
add_srs_wkt(struct epsg_defs * p,int count,const char * text)336 add_srs_wkt (struct epsg_defs *p, int count, const char *text)
337 {
338 /* creating the SRS_WKT string */
339 int len;
340 int olen;
341 char *string;
342 if (p == NULL || text == NULL)
343 return;
344 len = strlen (text);
345 if (!count)
346 {
347 p->srs_wkt = malloc (len + 1);
348 if (p->srs_wkt == NULL)
349 return;
350 strcpy (p->srs_wkt, text);
351 return;
352 }
353 if (p->srs_wkt == NULL)
354 return;
355 olen = strlen (p->srs_wkt);
356 string = malloc (len + olen + 1);
357 if (string == NULL)
358 return;
359 strcpy (string, p->srs_wkt);
360 free (p->srs_wkt);
361 p->srs_wkt = string;
362 strcat (p->srs_wkt, text);
363 }
364
365 SPATIALITE_PRIVATE void
free_epsg(struct epsg_defs * first)366 free_epsg (struct epsg_defs *first)
367 {
368 /* memory cleanup - destroying the EPSG list */
369 struct epsg_defs *p = first;
370 struct epsg_defs *pn;
371 while (p)
372 {
373 pn = p->next;
374 free_epsg_def (p);
375 p = pn;
376 }
377 }
378
379 SPATIALITE_PRIVATE int
create_spatial_ref_sys_aux(sqlite3 * handle)380 create_spatial_ref_sys_aux (sqlite3 * handle)
381 {
382 /* just in case, we'll create the SPATIAL_REF_SYS_AUX table */
383 int ret;
384 const char *sql = "CREATE TABLE IF NOT EXISTS spatial_ref_sys_aux (\n"
385 "\tsrid INTEGER NOT NULL PRIMARY KEY,\n"
386 "\tis_geographic INTEGER,\n"
387 "\thas_flipped_axes INTEGER,\n"
388 "\tspheroid TEXT,\n"
389 "\tprime_meridian TEXT,\n"
390 "\tdatum TEXT,\n"
391 "\tprojection TEXT,\n"
392 "\tunit TEXT,\n"
393 "\taxis_1_name TEXT,\n"
394 "\taxis_1_orientation TEXT,\n"
395 "\taxis_2_name TEXT,\n"
396 "\taxis_2_orientation TEXT,\n"
397 "\tCONSTRAINT fk_sprefsys FOREIGN KEY (srid) "
398 "\tREFERENCES spatial_ref_sys (srid))";
399 ret = sqlite3_exec (handle, sql, NULL, NULL, NULL);
400 if (ret != SQLITE_OK)
401 return 0;
402 /* creating the SPATIAL_REF_SYS_ALL view */
403 sql = "CREATE VIEW IF NOT EXISTS spatial_ref_sys_all AS\n"
404 "SELECT a.srid AS srid, a.auth_name AS auth_name, "
405 "a.auth_srid AS auth_srid, a.ref_sys_name AS ref_sys_name,\n"
406 "b.is_geographic AS is_geographic, "
407 "b.has_flipped_axes AS has_flipped_axes, "
408 "b.spheroid AS spheroid, b.prime_meridian AS prime_meridian, "
409 "b.datum AS datum, b.projection AS projection, b.unit AS unit,\n"
410 "b.axis_1_name AS axis_1_name, "
411 "b.axis_1_orientation AS axis_1_orientation,\n"
412 "b.axis_2_name AS axis_2_name, "
413 "b.axis_2_orientation AS axis_2_orientation,\n"
414 "a.proj4text AS proj4text, a.srtext AS srtext\n"
415 "FROM spatial_ref_sys AS a\n"
416 "LEFT JOIN spatial_ref_sys_aux AS b ON (a.srid = b.srid)";
417 ret = sqlite3_exec (handle, sql, NULL, NULL, NULL);
418 if (ret != SQLITE_OK)
419 return 0;
420 return 1;
421 }
422
423 static int
populate_spatial_ref_sys(sqlite3 * handle,int mode,int metadata)424 populate_spatial_ref_sys (sqlite3 * handle, int mode, int metadata)
425 {
426 /* populating the EPSG dataset into the SPATIAL_REF_SYS table */
427 struct epsg_defs *first = NULL;
428 struct epsg_defs *last = NULL;
429 struct epsg_defs *p;
430 char sql[1024];
431 int ret;
432 sqlite3_stmt *stmt = NULL;
433 sqlite3_stmt *stmt_aux = NULL;
434 int ok_aux;
435
436 /* initializing the EPSG defs list */
437 initialize_epsg (mode, &first, &last);
438
439 /* preparing the SQL parameterized statement (main) */
440 strcpy (sql, "INSERT INTO spatial_ref_sys ");
441
442 /*
443 * the following code has been contributed by Mark Johnson <mj10777@googlemail.com>
444 * on 2019-01-27
445 */
446 switch (metadata)
447 {
448 case 1: /* SpatiaLite-Pre-Legacy Versions 2.0 to 2.3.0 */
449 strcat (sql,
450 "(srid, auth_name, auth_srid, ref_sys_name, proj4text) ");
451 strcat (sql, "VALUES (?, ?, ?, ?, ?)");
452 break;
453 case 2: /* SpatiaLite-Legacy 2.4.0 - 3.0.1 */
454 strcat (sql,
455 "(srid, auth_name, auth_srid, ref_sys_name, proj4text, srs_wkt) ");
456 strcat (sql, "VALUES (?, ?, ?, ?, ?, ?)");
457 break;
458 case 3: /* SpatiaLite 4.0.0 - present */
459 strcat (sql,
460 "(srid, auth_name, auth_srid, ref_sys_name, proj4text, srtext) ");
461 strcat (sql, "VALUES (?, ?, ?, ?, ?, ?)");
462 create_spatial_ref_sys_aux (handle);
463 break;
464 }
465
466 ret = sqlite3_prepare_v2 (handle, sql, strlen (sql), &stmt, NULL);
467 if (ret != SQLITE_OK)
468 {
469 spatialite_e ("%s\n", sqlite3_errmsg (handle));
470 goto error;
471 }
472
473 /* preparing the SQL parameterized statement (aux) */
474 if (metadata >= 3)
475 {
476 /* SpatiaLite 4.0.0 - present */
477 strcpy (sql, "INSERT INTO spatial_ref_sys_aux ");
478 strcat (sql,
479 "(srid, is_geographic, has_flipped_axes, spheroid, prime_meridian, ");
480 strcat (sql,
481 "datum, projection, unit, axis_1_name, axis_1_orientation, ");
482 strcat (sql, "axis_2_name, axis_2_orientation) ");
483 strcat (sql, "VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)");
484 ret = sqlite3_prepare_v2 (handle, sql, strlen (sql), &stmt_aux, NULL);
485 if (ret != SQLITE_OK)
486 {
487 spatialite_e ("%s\n", sqlite3_errmsg (handle));
488 goto error;
489 }
490 }
491
492 p = first;
493 while (p)
494 {
495 if (p->auth_name == NULL)
496 break;
497 /* inserting into SPATIAL_REF_SYS */
498 sqlite3_reset (stmt);
499 sqlite3_clear_bindings (stmt);
500 sqlite3_bind_int (stmt, 1, p->srid);
501 sqlite3_bind_text (stmt, 2, p->auth_name, strlen (p->auth_name),
502 SQLITE_STATIC);
503 sqlite3_bind_int (stmt, 3, p->auth_srid);
504 sqlite3_bind_text (stmt, 4, p->ref_sys_name, strlen (p->ref_sys_name),
505 SQLITE_STATIC);
506 sqlite3_bind_text (stmt, 5, p->proj4text, strlen (p->proj4text),
507 SQLITE_STATIC);
508 if (metadata >= 2)
509 {
510 /* SpatiaLite-Legacy 2.4.0 - 3.0.1 and SpatiaLite 4.0.0 - present */
511 if (strlen (p->srs_wkt) == 0)
512 sqlite3_bind_text (stmt, 6, "Undefined", 9, SQLITE_STATIC);
513 else
514 sqlite3_bind_text (stmt, 6, p->srs_wkt, strlen (p->srs_wkt),
515 SQLITE_STATIC);
516 ret = sqlite3_step (stmt);
517 if (ret == SQLITE_DONE || ret == SQLITE_ROW)
518 ;
519 else
520 {
521 spatialite_e ("%s\n", sqlite3_errmsg (handle));
522 goto error;
523 }
524 }
525
526 /* inserting into SPATIAL_REF_SYS_AUX */
527 if (metadata >= 3)
528 { /* SpatiaLite 4.0.0 - present */
529 ok_aux = 0;
530 sqlite3_reset (stmt_aux);
531 sqlite3_clear_bindings (stmt_aux);
532 sqlite3_bind_int (stmt_aux, 1, p->srid);
533 if (p->is_geographic < 0)
534 sqlite3_bind_null (stmt_aux, 2);
535 else
536 {
537 sqlite3_bind_int (stmt_aux, 2, p->is_geographic);
538 ok_aux = 1;
539 }
540 if (p->flipped_axes < 0)
541 sqlite3_bind_null (stmt_aux, 3);
542 else
543 {
544 sqlite3_bind_int (stmt_aux, 3, p->flipped_axes);
545 ok_aux = 1;
546 }
547 if (p->spheroid == NULL)
548 sqlite3_bind_null (stmt_aux, 4);
549 else
550 {
551 sqlite3_bind_text (stmt_aux, 4, p->spheroid,
552 strlen (p->spheroid), SQLITE_STATIC);
553 ok_aux = 1;
554 }
555 if (p->prime_meridian == NULL)
556 sqlite3_bind_null (stmt_aux, 5);
557 else
558 {
559 sqlite3_bind_text (stmt_aux, 5, p->prime_meridian,
560 strlen (p->prime_meridian),
561 SQLITE_STATIC);
562 ok_aux = 1;
563 }
564 if (p->datum == NULL)
565 sqlite3_bind_null (stmt_aux, 6);
566 else
567 {
568 sqlite3_bind_text (stmt_aux, 6, p->datum,
569 strlen (p->datum), SQLITE_STATIC);
570 ok_aux = 1;
571 }
572 if (p->projection == NULL)
573 sqlite3_bind_null (stmt_aux, 7);
574 else
575 {
576 sqlite3_bind_text (stmt_aux, 7, p->projection,
577 strlen (p->projection), SQLITE_STATIC);
578 ok_aux = 1;
579 }
580 if (p->unit == NULL)
581 sqlite3_bind_null (stmt_aux, 8);
582 else
583 {
584 sqlite3_bind_text (stmt_aux, 8, p->unit, strlen (p->unit),
585 SQLITE_STATIC);
586 ok_aux = 1;
587 }
588 if (p->axis_1 == NULL)
589 sqlite3_bind_null (stmt_aux, 9);
590 else
591 {
592 sqlite3_bind_text (stmt_aux, 9, p->axis_1,
593 strlen (p->axis_1), SQLITE_STATIC);
594 ok_aux = 1;
595 }
596 if (p->orientation_1 == NULL)
597 sqlite3_bind_null (stmt_aux, 10);
598 else
599 {
600 sqlite3_bind_text (stmt_aux, 10, p->orientation_1,
601 strlen (p->orientation_1),
602 SQLITE_STATIC);
603 ok_aux = 1;
604 }
605 if (p->axis_2 == NULL)
606 sqlite3_bind_null (stmt_aux, 11);
607 else
608 {
609 sqlite3_bind_text (stmt_aux, 11, p->axis_2,
610 strlen (p->axis_2), SQLITE_STATIC);
611 ok_aux = 1;
612 }
613 if (p->orientation_2 == NULL)
614 sqlite3_bind_null (stmt_aux, 12);
615 else
616 {
617 sqlite3_bind_text (stmt_aux, 12, p->orientation_2,
618 strlen (p->orientation_2),
619 SQLITE_STATIC);
620 ok_aux = 1;
621 }
622 if (ok_aux)
623 {
624 ret = sqlite3_step (stmt_aux);
625 if (ret == SQLITE_DONE || ret == SQLITE_ROW)
626 ;
627 else
628 {
629 spatialite_e ("%s\n", sqlite3_errmsg (handle));
630 goto error;
631 }
632 }
633 }
634 /* end Mark Johnson 2019-01-27 */
635 p = p->next;
636 }
637 sqlite3_finalize (stmt);
638 sqlite3_finalize (stmt_aux);
639
640 /* freeing the EPSG defs list */
641 free_epsg (first);
642 return 1;
643
644 error:
645 if (stmt)
646 sqlite3_finalize (stmt);
647 if (stmt_aux)
648 sqlite3_finalize (stmt_aux);
649 /* freeing the EPSG defs list */
650 free_epsg (first);
651
652 return 0;
653 }
654
655 SPATIALITE_PRIVATE int
exists_spatial_ref_sys(void * p_sqlite)656 exists_spatial_ref_sys (void *p_sqlite)
657 {
658 /* checking if the SPATIAL_REF_SYS table exists */
659 int ret;
660 int ok = 0;
661 char sql[1024];
662 char **results;
663 int n_rows;
664 int n_columns;
665 char *err_msg = NULL;
666
667 sqlite3 *handle = (sqlite3 *) p_sqlite;
668
669 strcpy (sql,
670 "SELECT name FROM sqlite_master WHERE type = 'table' AND name LIKE 'spatial_ref_sys'");
671 ret =
672 sqlite3_get_table (handle, sql, &results, &n_rows, &n_columns,
673 &err_msg);
674 if (ret != SQLITE_OK)
675 {
676 /* some error occurred */
677 spatialite_e ("XX %s\n", err_msg);
678 sqlite3_free (err_msg);
679 return 0;
680 }
681 if (n_rows > 0)
682 ok = 1;
683 sqlite3_free_table (results);
684 return ok;
685 }
686
687 static int
check_spatial_ref_sys(sqlite3 * handle)688 check_spatial_ref_sys (sqlite3 * handle)
689 {
690 /* checking if the SPATIAL_REF_SYS table has an appropriate layout */
691 int ret;
692 int i;
693 const char *name;
694 char sql[1024];
695 char **results;
696 int n_rows;
697 int n_columns;
698 char *err_msg = NULL;
699 int rs_srid = 0;
700 int auth_name = 0;
701 int auth_srid = 0;
702 int ref_sys_name = 0;
703 int proj4text = 0;
704 int srtext = 0;
705 int srs_wkt = 0;
706
707 strcpy (sql, "PRAGMA table_info(spatial_ref_sys)");
708 ret =
709 sqlite3_get_table (handle, sql, &results, &n_rows, &n_columns,
710 &err_msg);
711 if (ret != SQLITE_OK)
712 {
713 /* some error occurred */
714 spatialite_e ("%s\n", err_msg);
715 sqlite3_free (err_msg);
716 return 0;
717 }
718 if (n_rows > 0)
719 {
720 for (i = 1; i <= n_rows; i++)
721 {
722 name = results[(i * n_columns) + 1];
723 if (strcasecmp (name, "srid") == 0)
724 rs_srid = 1;
725 if (strcasecmp (name, "auth_name") == 0)
726 auth_name = 1;
727 if (strcasecmp (name, "auth_srid") == 0)
728 auth_srid = 1;
729 if (strcasecmp (name, "ref_sys_name") == 0)
730 ref_sys_name = 1;
731 if (strcasecmp (name, "proj4text") == 0)
732 proj4text = 1;
733 if (strcasecmp (name, "srtext") == 0)
734 srtext = 1;
735 /*
736 * the following code has been contributed by Mark Johnson <mj10777@googlemail.com>
737 * on 2019-01-27
738 */
739 if (strcasecmp (name, "srs_wkt") == 0)
740 srs_wkt = 1;
741 /* end Mark Johnson 2019-01-26 */
742 }
743 }
744 sqlite3_free_table (results);
745 if (rs_srid && auth_name && auth_srid && ref_sys_name && proj4text
746 && srtext)
747 /*
748 * the following code has been contributed by Mark Johnson <mj10777@googlemail.com>
749 * on 2019-01-27
750 */
751 ret = 3; /* SpatiaLite 4.0.0 - present */
752 else if (rs_srid && auth_name && auth_srid && ref_sys_name && proj4text
753 && srs_wkt)
754 ret = 2; /* SpatiaLite-Legacy 2.4.0 - 3.0.1 */
755 else if (rs_srid && auth_name && auth_srid && ref_sys_name && proj4text
756 && srs_wkt == 0)
757 ret = 1; /* SpatiaLite-Pre-Legacy Versions 2.0 to 2.3.0 */
758 /* end Mark Johnson 2019-01-26 */
759 else
760 ret = 0;
761 return ret;
762 }
763
764 static int
spatial_ref_sys_count(sqlite3 * handle)765 spatial_ref_sys_count (sqlite3 * handle)
766 {
767 /* checking if the SPATIAL_REF_SYS table is empty */
768 int ret;
769 int i;
770 int count = 0;
771 char sql[1024];
772 char **results;
773 int n_rows;
774 int n_columns;
775 char *err_msg = NULL;
776
777 strcpy (sql, "SELECT Count(*) FROM spatial_ref_sys");
778 ret =
779 sqlite3_get_table (handle, sql, &results, &n_rows, &n_columns,
780 &err_msg);
781 if (ret != SQLITE_OK)
782 {
783 /* some error occurred */
784 spatialite_e ("%s\n", err_msg);
785 sqlite3_free (err_msg);
786 return 0;
787 }
788 if (n_rows > 0)
789 {
790 for (i = 1; i <= n_rows; i++)
791 {
792 count = atoi (results[(i * n_columns) + 0]);
793 }
794 }
795 sqlite3_free_table (results);
796 return count;
797 }
798
799 SPATIALITE_DECLARE int
spatial_ref_sys_init(sqlite3 * handle,int verbose)800 spatial_ref_sys_init (sqlite3 * handle, int verbose)
801 {
802 /*
803 / deprecated function
804 / [still supported simply not to break API-level back-compatibility]
805 */
806 return spatial_ref_sys_init2 (handle, GAIA_EPSG_ANY, verbose);
807 }
808
809 SPATIALITE_DECLARE int
spatial_ref_sys_init2(sqlite3 * handle,int mode,int verbose)810 spatial_ref_sys_init2 (sqlite3 * handle, int mode, int verbose)
811 {
812 /* populating the EPSG dataset into the SPATIAL_REF_SYS table */
813 int metadata = 0;
814 if (!exists_spatial_ref_sys (handle))
815 {
816 if (verbose)
817 spatialite_e ("the SPATIAL_REF_SYS table doesn't exists\n");
818 return 0;
819 }
820
821 /*
822 * the following code has been contributed by Mark Johnson <mj10777@googlemail.com>
823 * on 2019-01-27
824 */
825 metadata = check_spatial_ref_sys (handle);
826 if (metadata < 1)
827 /* end Mark Johnson 2019-01-27 */
828 {
829 if (verbose)
830 spatialite_e
831 ("the SPATIAL_REF_SYS table has an unsupported layout\n");
832 return 0;
833 }
834 if (spatial_ref_sys_count (handle))
835 {
836 if (verbose)
837 spatialite_e
838 ("the SPATIAL_REF_SYS table already contains some row(s)\n");
839 return 0;
840 }
841 if (mode == GAIA_EPSG_ANY || mode == GAIA_EPSG_NONE
842 || mode == GAIA_EPSG_WGS84_ONLY)
843 ;
844 else
845 mode = GAIA_EPSG_ANY;
846 if (mode == GAIA_EPSG_NONE)
847 return 1;
848 if (populate_spatial_ref_sys (handle, mode, metadata)) /* Mark Johnson 2019-01-27 */
849 {
850 if (verbose)
851 spatialite_e
852 ("OK: the SPATIAL_REF_SYS table was successfully populated\n");
853 return 1;
854 }
855 return 0;
856 }
857
858 SPATIALITE_DECLARE int
insert_epsg_srid(sqlite3 * handle,int srid)859 insert_epsg_srid (sqlite3 * handle, int srid)
860 {
861 /* inserting a single EPSG definition into the SPATIAL_REF_SYS table */
862 struct epsg_defs *first = NULL;
863 struct epsg_defs *last = NULL;
864 char sql[1024];
865 int ret;
866 int error = 0;
867 int metadata = 0;
868 sqlite3_stmt *stmt = NULL;
869 sqlite3_stmt *stmt_aux = NULL;
870 int ok_aux;
871
872 if (!exists_spatial_ref_sys (handle))
873 {
874 spatialite_e ("the SPATIAL_REF_SYS table doesn't exists\n");
875 return 0;
876 }
877 /*
878 * the following code has been contributed by Mark Johnson <mj10777@googlemail.com>
879 * on 2019-01-27
880 */
881 metadata = check_spatial_ref_sys (handle);
882 if (metadata < 1)
883 {
884 spatialite_e
885 ("the SPATIAL_REF_SYS table has an unsupported layout\n");
886 return 0;
887 }
888
889 /* initializing the EPSG defs list */
890 initialize_epsg (srid, &first, &last);
891 if (first == NULL)
892 {
893 spatialite_e ("SRID=%d isn't defined in the EPSG inlined dataset\n",
894 srid);
895 return 0;
896 }
897
898 /* preparing the SQL parameterized statement */
899
900 /*
901 * the following code has been contributed by Mark Johnson <mj10777@googlemail.com>
902 * on 2019-01-27
903 */
904 strcpy (sql, "INSERT INTO spatial_ref_sys ");
905 switch (metadata)
906 {
907 case 1: /* SpatiaLite-Pre-Legacy Versions 2.0 to 2.3.0 */
908 strcat (sql,
909 "(srid, auth_name, auth_srid, ref_sys_name, proj4text) ");
910 strcat (sql, "VALUES (?, ?, ?, ?, ?)");
911 break;
912 case 2: /* SpatiaLite-Legacy 2.4.0 - 3.0.1 */
913 strcat (sql,
914 "(srid, auth_name, auth_srid, ref_sys_name, proj4text, srs_wkt) ");
915 strcat (sql, "VALUES (?, ?, ?, ?, ?, ?)");
916 break;
917 case 3: /* SpatiaLite 4.0.0 - present */
918 strcat (sql,
919 "(srid, auth_name, auth_srid, ref_sys_name, proj4text, srtext) ");
920 strcat (sql, "VALUES (?, ?, ?, ?, ?, ?)");
921 create_spatial_ref_sys_aux (handle);
922 break;
923 }
924 ret = sqlite3_prepare_v2 (handle, sql, strlen (sql), &stmt, NULL);
925 if (ret != SQLITE_OK)
926 {
927 spatialite_e ("%s\n", sqlite3_errmsg (handle));
928 error = 1;
929 goto stop;
930 }
931 if (metadata >= 3)
932 { /* SpatiaLite 4.0.0 - present */
933 /* preparing the SQL parameterized statement (aux) */
934 strcpy (sql, "INSERT INTO spatial_ref_sys_aux ");
935 strcat (sql,
936 "(srid, is_geographic, has_flipped_axes, spheroid, prime_meridian, ");
937 strcat (sql,
938 "datum, projection, unit, axis_1_name, axis_1_orientation, ");
939 strcat (sql, "axis_2_name, axis_2_orientation) ");
940 strcat (sql, "VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)");
941 ret = sqlite3_prepare_v2 (handle, sql, strlen (sql), &stmt_aux, NULL);
942 if (ret != SQLITE_OK)
943 {
944 spatialite_e ("%s\n", sqlite3_errmsg (handle));
945 error = 1;
946 goto stop;
947 }
948 }
949
950 /* inserting into SPATIAL_REF_SYS */
951 sqlite3_reset (stmt);
952 sqlite3_clear_bindings (stmt);
953 sqlite3_bind_int (stmt, 1, first->srid);
954 sqlite3_bind_text (stmt, 2, first->auth_name, strlen (first->auth_name),
955 SQLITE_STATIC);
956 sqlite3_bind_int (stmt, 3, first->auth_srid);
957 sqlite3_bind_text (stmt, 4, first->ref_sys_name,
958 strlen (first->ref_sys_name), SQLITE_STATIC);
959 sqlite3_bind_text (stmt, 5, first->proj4text, strlen (first->proj4text),
960 SQLITE_STATIC);
961 if (metadata >= 2)
962 {
963 /* SpatiaLite-Legacy 2.4.0 - 3.0.1 and SpatiaLite 4.0.0 - present */
964 if (strlen (first->srs_wkt) == 0)
965 sqlite3_bind_text (stmt, 6, "Undefined", 9, SQLITE_STATIC);
966 else
967 sqlite3_bind_text (stmt, 6, first->srs_wkt,
968 strlen (first->srs_wkt), SQLITE_STATIC);
969 }
970 ret = sqlite3_step (stmt);
971 if (ret == SQLITE_DONE || ret == SQLITE_ROW)
972 ;
973 else
974 {
975 spatialite_e ("%s\n", sqlite3_errmsg (handle));
976 error = 1;
977 goto stop;
978 }
979
980 if (metadata >= 3)
981 { /* SpatiaLite 4.0.0 - present */
982 /* inserting into SPATIAL_REF_SYS_AUX */
983 ok_aux = 0;
984 sqlite3_reset (stmt_aux);
985 sqlite3_clear_bindings (stmt_aux);
986 sqlite3_bind_int (stmt_aux, 1, first->srid);
987 if (first->is_geographic < 0)
988 sqlite3_bind_null (stmt_aux, 2);
989 else
990 {
991 sqlite3_bind_int (stmt_aux, 2, first->is_geographic);
992 ok_aux = 1;
993 }
994 if (first->flipped_axes < 0)
995 sqlite3_bind_null (stmt_aux, 3);
996 else
997 {
998 sqlite3_bind_int (stmt_aux, 3, first->flipped_axes);
999 ok_aux = 1;
1000 }
1001 if (first->spheroid == NULL)
1002 sqlite3_bind_null (stmt_aux, 4);
1003 else
1004 {
1005 sqlite3_bind_text (stmt_aux, 4, first->spheroid,
1006 strlen (first->spheroid), SQLITE_STATIC);
1007 ok_aux = 1;
1008 }
1009 if (first->prime_meridian == NULL)
1010 sqlite3_bind_null (stmt_aux, 5);
1011 else
1012 {
1013 sqlite3_bind_text (stmt_aux, 5, first->prime_meridian,
1014 strlen (first->prime_meridian),
1015 SQLITE_STATIC);
1016 ok_aux = 1;
1017 }
1018 if (first->datum == NULL)
1019 sqlite3_bind_null (stmt_aux, 6);
1020 else
1021 {
1022 sqlite3_bind_text (stmt_aux, 6, first->datum,
1023 strlen (first->datum), SQLITE_STATIC);
1024 ok_aux = 1;
1025 }
1026 if (first->projection == NULL)
1027 sqlite3_bind_null (stmt_aux, 7);
1028 else
1029 {
1030 sqlite3_bind_text (stmt_aux, 7, first->projection,
1031 strlen (first->projection), SQLITE_STATIC);
1032 ok_aux = 1;
1033 }
1034 if (first->unit == NULL)
1035 sqlite3_bind_null (stmt_aux, 8);
1036 else
1037 {
1038 sqlite3_bind_text (stmt_aux, 8, first->unit,
1039 strlen (first->unit), SQLITE_STATIC);
1040 ok_aux = 1;
1041 }
1042 if (first->axis_1 == NULL)
1043 sqlite3_bind_null (stmt_aux, 9);
1044 else
1045 {
1046 sqlite3_bind_text (stmt_aux, 9, first->axis_1,
1047 strlen (first->axis_1), SQLITE_STATIC);
1048 ok_aux = 1;
1049 }
1050 if (first->orientation_1 == NULL)
1051 sqlite3_bind_null (stmt_aux, 10);
1052 else
1053 {
1054 sqlite3_bind_text (stmt_aux, 10, first->orientation_1,
1055 strlen (first->orientation_1),
1056 SQLITE_STATIC);
1057 ok_aux = 1;
1058 }
1059 if (first->axis_2 == NULL)
1060 sqlite3_bind_null (stmt_aux, 11);
1061 else
1062 {
1063 sqlite3_bind_text (stmt_aux, 11, first->axis_2,
1064 strlen (first->axis_2), SQLITE_STATIC);
1065 ok_aux = 1;
1066 }
1067 if (first->orientation_2 == NULL)
1068 sqlite3_bind_null (stmt_aux, 11);
1069 else
1070 {
1071 sqlite3_bind_text (stmt_aux, 11, first->orientation_2,
1072 strlen (first->orientation_2),
1073 SQLITE_STATIC);
1074 ok_aux = 1;
1075 }
1076 if (ok_aux)
1077 {
1078 ret = sqlite3_step (stmt_aux);
1079 if (ret == SQLITE_DONE || ret == SQLITE_ROW)
1080 ;
1081 else
1082 {
1083 spatialite_e ("%s\n", sqlite3_errmsg (handle));
1084 goto stop;
1085 }
1086 }
1087 }
1088 /* end Mark Johnson 2019-01-27 */
1089 stop:
1090 if (stmt != NULL)
1091 sqlite3_finalize (stmt);
1092 if (stmt_aux != NULL)
1093 sqlite3_finalize (stmt_aux);
1094
1095 /* freeing the EPSG defs list */
1096 free_epsg (first);
1097 if (error)
1098 return 0;
1099 return 1;
1100 }
1101