RC5b - UPDATE

[a.furieri]

Hi List,

an updated RC5b is available here:
http://www.gaia-gis.it/spatialite-2.4.0-5/index.html

The main focus is about Spatial Index handling:
- a brand new VirtualSpatialIndex is introduced:
this is intended to allow a more "elegant"
(less painful) access to the R*Tree Spatial Index.
- IMPORTANT NOTICE: a severe corruption cause for
R*Tree has been identified under some quite
exceptional circumstance.
CheckSpatialIndex() and RecoverSpatialIndex() has
been introduced so to mitigate this potential flaw.

For any further explanation about the newest
Spatial Index functions, please read the appropriate
documentation:
http://www.gaia-gis.it/spatialite-2.4.0-5/SpatialIndex-Update.pdf

... several minor bugs fixed here and there ...

bye Sandro

[romain]

Hi Sandro,

Once again, a pretty good work ! Thank you.

R*Tree corruption's problem is a real pain! I think it should be a
great idea to warn users on the official website/documentation/
cookbook (anywhere in fact).
As far as i'm concerned, I rarely create primary keys on result-
tables....
I just do: CREATE TABLE AS SELECT ....

Isn't it possible to create a function createspatialindex in GUI that
creates spatial index AND creates primary key (if doesn't exist)

Bye,

[a.furieri]

Hi Romain,

> As far as i'm concerned, I rarely create primary keys on result-
> tables....
> I just do: CREATE TABLE AS SELECT ....
>

not at all the best strategy :-(

CREATE TABLE (...);
SELECT AddGeometryColumn(...);
INSERT INTO ... (...) SELECT ... FROM ...;
surely this one is a wisest and safest approach :-)

> Isn't it possible to create a function createspatialindex in GUI that
> creates spatial index AND creates primary key (if doesn't exist)
>

sorry, this isn't technically possible: SQLite is completely unable
to add a PRIMARY KEY *after* the initial table creation.
please see: http://www.sqlite.org/lang_altertable.html

bye Sandro

[romain]

I was thinking about something like that: (using both c code and SQL )

IF table CONTAINS a primary key :

create Spatial Index

ELSE : (maybe asking use confirmation before)

CREATE TABLE table_tmp AS SELECT * FROM table ;
DROP TABLE table ;
CREATE table (pk_uid INTEGER PRIMARY KEY, <table_tmp columns names and
constraints>) ;
INSERT INTO table (pk_uid, <table_tmp columns names>) ;
SELECT ROWID as pk_uid, <table_tmp columns> FROM table_tmp ;
SELECT createSpatialIndex( "table","geometry") ;


I don't know C programming, but sometimes I use python. Here are Some
developping ideas:

1/ Use PRAGMA Table_info(mytable) in order to check if a primary key
already exists (column pk=1).

2/ If there is no primary key, Store the CREATE Statement into a
string variable: (advantage: also contains constraints )
SELECT sql FROM sqlite_master
WHERE type = 'table' AND name = mytable

ex:
string= 'CREATE TABLE "mytable" ( "col1" TEXT, "col2" DOUBLE,
"geometry" POLYGON)'

3/ Parse the string variable, cutting after the first "(" :

string1= 'CREATE TABLE "mytable" ('
string2=' "col1" TEXT, "col2" DOUBLE, "geometry" POLYGON)'

4/ Create new create statement:
string=string1+ ' "pk_uid" PRIMARY KEY AUTOINCREMENT, ' + string3

5/ Create a new "mytable" and insert values.


Store create statement from mytable into a string variable
SELECT sql FROM sqlite_master
WHERE type = 'table' AND name = mytable

[Noli Sicad]

Hi Sandro,

RC5b compiled in iOS without a problem.

Here's unit test result for iphone iOS 4.2 i.e. iPhone Simulator/4.2.

The unit test does not have a map display yet. The result is from the
console in Xcode IDE.

~~~~~~~~~~~~~~~~~~~~~
[Session started at 2011-03-11 20:15:50 +1100.]
2011-03-11 20:15:54.926 Spatialite2.4.0_LibEnv[79541:207] Spatialite
version 2.4.0-RC5
SpatiaLite version ..: 2.4.0-RC5 Supported Extensions:
- 'VirtualShape' [direct Shapefile access]
- 'VirtualDbf' [direct DBF access]
- 'VirtualText' [direct CSV/TXT access]
- 'VirtualNetwork' [Dijkstra shortest path]
- 'RTree' [Spatial Index - R*Tree]
- 'MbrCache' [Spatial Index - MBR cache]
- 'VirtualSpatialIndex' [R*Tree metahandler]
- 'VirtualFDO' [FDO-OGR interoperability]
- 'SpatiaLite' [Spatial SQL - OGC]
PROJ.4 version ......: Rel. 4.7.1, 23 September 2009
GEOS version ........: 3.3.0-CAPI-1.7.0
2011-03-11 20:15:54.929 Spatialite2.4.0_LibEnv[79541:207] Opening
/var/root/Library/Application Support/iPhone
Simulator/4.2/Applications/65838C8D-E5A0-47EF-9226-276A0CDE228F/Spatialite2.4.0_LibEnv.app/pacaRoadsIndex.sqlite
2011-03-11 20:15:54.935 Spatialite2.4.0_LibEnv[79541:207] OPEN OK
2011-03-11 20:15:54.936 Spatialite2.4.0_LibEnv[79541:207]
================================ TEST1
2011-03-11 20:15:54.937 Spatialite2.4.0_LibEnv[79541:207] req is
SELECT * FROM Roads Where MbrContains(Geometry, MakePoint(7.013891,
43.557964))
2011-03-11 20:15:55.168 Spatialite2.4.0_LibEnv[79541:207] -
2011-03-11 20:15:55.425 Spatialite2.4.0_LibEnv[79541:207] Avenue Saint Louis
2011-03-11 20:15:55.430 Spatialite2.4.0_LibEnv[79541:207] Avenue Saint-Jean
2011-03-11 20:15:55.433 Spatialite2.4.0_LibEnv[79541:207] -
2011-03-11 20:15:57.142 Spatialite2.4.0_LibEnv[79541:207]
==============================TEST GEOS
2011-03-11 20:15:57.146 Spatialite2.4.0_LibEnv[79541:207]
010100000000000000000024400000000000003440
2011-03-11 20:15:57.148 Spatialite2.4.0_LibEnv[79541:207]
===================GEOS seems working!

Thanks.

Noli

> --
> You received this message because you are subscribed to the Google Groups
> "SpatiaLite Users" group.
> To post to this group, send email to spatiali...@googlegroups.com.
> To unsubscribe from this group, send email to
> spatialite-use...@googlegroups.com.
> For more options, visit this group at
> http://groups.google.com/group/spatialite-users?hl=en.
>
>

[a.furieri]

Hi Noli,

I'm really happy to hear this good new from you.

How do you managed the GEOS build ?
it looks like Junjun still encounters several
troubles about this ...

Anyway, submitting a quick how-to-build guide
for iOS surely would be a big contribution to
the whole community :-)

bye Sandro

[Noli Sicad]

Hi Sandro,

As I mentioned in the previous post that I use the build_iOS script to build

https://github.com/mweisman/ShapeKit/tree/master/lib_src

1. Proj4.7.1
2. GEOS 3.3.0 trunk - March 9

Then, I created Xcode project and use the static libraries of Proj
4.7.1 and GEOS 3.3.0 to build Spatiailite.

I included libiconv.dylib from iOS framework (built-in) and added
localcharset.h from navit

http://navit.svn.sourceforge.net/viewvc/navit/trunk/navit/intl/localcharset.h?view=markup&content-type=text%2Fvnd.viewcvs-markup&pathrev=290

1 warning.

/Volumes/SPARE/SpatialiteRouteMe/Tests/SpatialiteLibEnv25RCb/Spatialite_LibEnv/../libspatialite-amalgamation-2.4.0_5/spatialite.c:
In function 'yy_destructor':
/Volumes/SPARE/SpatialiteRouteMe/Tests/SpatialiteLibEnv25RCb/Spatialite_LibEnv/../libspatialite-amalgamation-2.4.0_5/spatialite.c:59665:
warning: unused variable 'result'

Noli

[a.furieri]

Hi Romain,

yes, we surely can implement something like this:
more or less, this one corresponds to the approach
followed in spatialite_gui to implement the
menu commands "Drop column" and "Rename column".

SQLite doesn't supports such options: but nothing
forbids implementing a quite complex and sophisticated
C procedure emulating the same overall effect.

So we can easily imagine a further spatialite_gui menu
item (e.g. "Check/Add Primary Key"):
- if a PK is already defined, then simply an info
message will be displayed with no other effect
- otherwise a "new" table will be created (supporting
an AUTOINCREMENT PK), the "old" table will be copied
into the "new" one, and finally the "old" table will
be dropped.

what do you think about this ?

bye Sandro

[Pepijn Van Eeckhoudt]

Since there's so much talk about iOS I thought I would contribute my
Android stuff as well :)

I've updated my local repository to 2.4.0 RC5 and merged it with my
android branch. The attached patch can be applied to the canonical
source tarball and then you can build spatialite using the android NDK.
An Android.mk file is included for this purpose.

The patchset includes the following changes:
- Adds jni/Android.mk and jni/Application.mk
- Introduces debug/info/error functions which are used instead of
fprintf/printf. On Android these redirect output to logcat.
- Removes duplicate init code from spatialite.c

Note that I'm currently compiling a minimal spatialite. Proj, geos and
iconv are not included which means a lot of features are dropped. Proj
and geos could probably be added rather easily, but I don't have a need
for this myself just yet.

Regards,

Pepijn

[moovida]

Hi Pepijn,
I really like your android patch (as in: will there finally be
spatialite on android?).

Can I ask you how you use it inside Android? Do you substitute the
internal sqlite? How do you query from the applications (I assume raw
queries)?
I would have tons of qustions :)
Do you have any docs on how to try your spatialite support on android?

Thanks,
Andrea

On 11 Mar, 11:44, Pepijn Van Eeckhoudt <pep...@vaneeckhoudt.net>
wrote:

>  android.patch
> 129KVisualizzaScarica

[Pepijn Van Eeckhoudt]

On 11/03/2011 13:03, moovida wrote:
> Can I ask you how you use it inside Android? Do you substitute the
> internal sqlite? How do you query from the applications (I assume raw
> queries)?

Actually getting it to run on Android devices is a bit of a hack :) I
took the r-tree extension from the latest sqlite release and compiled
that using the NDK. I also compile the sqlite itself using the NDK, but
this is only for linking purposes. In other words, the spatialite and
rtree Android.mk files include sqlite as a shared library, but as they
only use sqlite3ext.h there are no real dependencies on libsqlite.so.

The result of running ndk-build is a librtree.so, libspatialite.so and
libsqlite.so. The latter one I don't include in my apk to make sure I
use the platform sqlite library.

Then all you need to do is load rtree and spatialite as sqlite
extensions using the android.database.sqlite API. And this is where the
hacking starts. The sqlite version that is shipped with Android has
extension support built-in but not enabled by default. You need to
somehow call sqlite3_enable_load_extension with the right sqlite3
handle. Turns out this value is actually stored in the
android.database.sqlite.SQLiteDatabase instances as the field
mNativeHandle. It's not public but you can read the value using the
following bit of code

private int getDBHandle( SQLiteDatabase db ) {
try {
Field field = db.getClass().getDeclaredField( "mNativeHandle" );
field.setAccessible( true );
return field.getInt( db );
} catch ( NoSuchFieldException e ) {
return -1;
} catch ( IllegalAccessException e ) {
return -1;
}
}

All that remains to be done is to write a small JNI library that takes
in the sqlite handle and calls sqlite3_enable_load_extension. Once that
call has been done you can load the extension using 'SELECT
load_extension...'

Needless to say all the above is completely unsupported and could break
quite easily in the future, but for now it does work (tested on eclair
and froyo).

Pepijn

[Junjun Yin]

Dear Noli,

I am wondering how was the build for the device. When build for the simulator, I have a few warnings. But for the device built, there will be something different. Would you please tell me how was it in your case for the device built. Thanks

Cheers

Junjun