Spatialite database created at runtime

[ssppgit]

Hi again!

I know that you can do spatialite database with spatialite-gui. However, i need to do my spatialite database at runtime, i need to create it and insert record i get of a WebService. I have followed this code (https://code.google.com/p/spatialite-android/source/browse/spatialite-android-test/src/com/spatialite/test/spatialite/RTreeTest.java) to test but with my two tables and three records.

All the database is created well but when i see the tables in the Map, my two points and one linestring is moved a bit up.

You can see it in this two images (the first is database created at runtime and the second is created with spatialite-gui).

I must see the first image like the second I have used the same SQL statements and i don't know what's happen.

Any idea?

[mj10777]

Difficult to say without seeing the original Databases.

• if they both have the same values, there would be no reason why it not show the same position

[ssppgit]

Hi!

Here (http://box.jisko.net/d/84421175) you can download the database created with spatialite 1.7.1 Here (http://box.jisko.net/d/79e3e21d) you can download the database created at runtime.

I have used the sames SQL statements. Any idea? >.< Thanks for your answers!

[mj10777]

with spatialite 1.7.1

• this is malformed, I cannot read it

Mark

2014-05-16 9:29 GMT+02:00 ssppgit notifications@github.com:

Hi!

Here (http://box.jisko.net/d/84421175) you can download the database created with spatialite 1.7.1 Here (http://box.jisko.net/d/79e3e21d) you can download the database created at runtime.

I have used the sames SQL statements. Any idea? >.< Thanks for your answers!

— Reply to this email directly or view it on GitHubhttps://github.com/geopaparazzi/geopaparazzi/issues/157#issuecomment-43304509 .

[ssppgit]

Sorry Mark,

Here (http://box.jisko.net/d/260a0ff4) you can download the database created with spatialite 1.7.1. This one is seen well, db created at runtime is seen wrong.

Thnks!

[mj10777]

Well, you are a very unlucky person.

It seems that at some point (starting with spatialite 4.0.0)

• the definition of ED50 / UTM zone 30N has changed
  • there is now an extra definition:
  • +towgs84=-87,-98,-121,0,0,0,0
    • this is the cause of the shift

http://spatialreference.org/ref/epsg/ed50-utm-zone-30n/

• still shows the older values used by spatialite 3.0.1
  • dbcreateatruntime.sqlite

This site is well known for not updating their information

• the value used in db_spatialite_gui.sqlite
  • should be considered correct [second image]
  • it is the value used in the present proj4
    • which does the transformation

Mark

[ssppgit]

T_T...And can i correct this from code of any way??Because i need to create database at runtime...

Thanks!

[mj10777]

On further not:

• the transformation is based on the value stored in the active Database
  • if you update the Databases with the older values
  • spatial_ref_sys
    • proj4text
    • srtext
  • with the values from the Database with the correct values
  • spatial_ref_sys
    • proj4text
    • srs_wkt

then they should show up as in the second image, which is correct

SELECT * FROM spatial_ref_sys WHERE auth_srid=23030

[mj10777]

You are using an old driver, thus it creates the old values

• either switch to a new driver
  • https://github.com/geopaparazzi/libjsqlite-spatialite-android/wiki/Android_4.1.1.mk
• or update the database
  • with the correct values

[ssppgit]

I am going to use this https://github.com/geopaparazzi/libjsqlite-spatialite-android/blob/master/archive/20140105.libjsqlite.4.1.1.tar.bz2

And thank you very much for your help!!!

[mj10777]

2014-05-16 12:19 GMT+02:00 ssppgit notifications@github.com:

I am going to use this https://github.com/geopaparazzi/libjsqlite-spatialite-android/blob/master/archive/20140105.libjsqlite.4.1.1.tar.bz2

And thank you very much for your help!!!

Yes, I have been working with that since January.

• it will be part of the next geopaparazzi

— Reply to this email directly or view it on GitHubhttps://github.com/geopaparazzi/geopaparazzi/issues/157#issuecomment-43317033 .

[mj10777]

but remember, older databases will still have the old values

• they should be updated

2014-05-16 12:20 GMT+02:00 Mark Johnson mj10777@googlemail.com:

2014-05-16 12:19 GMT+02:00 ssppgit notifications@github.com:

I am going to use this

https://github.com/geopaparazzi/libjsqlite-spatialite-android/blob/master/archive/20140105.libjsqlite.4.1.1.tar.bz2

And thank you very much for your help!!!

Yes, I have been working with that since January.

• it will be part of the next geopaparazzi

  — Reply to this email directly or view it on GitHubhttps://github.com/geopaparazzi/geopaparazzi/issues/157#issuecomment-43317033 .

[ssppgit]

Yes yes! I'll update older databases :)