AddGeometryColumn in Spatialite GUI turns zero

[Tara]

Hello,

I have a sqlite database "data.sqlite", which is not converted in spatialite yet. My aim is to open the sqlite database in QGis, with the spatialite plugin. If I import the original sqlite database in QGIS,  I got the message "Failure getting table metadata..." .

To solve the Problem I thougt to create a new table in the spatialite gui and add a Geometry Column and insert the data from sqlite db to the new one. I could load the data.sqlite database in the GUI and I can also do some select queries. But If I execute "AdGeometryColum.." , I got 0 . And as a result the new created table does not have a geometry column.

SELECT AddGeometryColumn('test', 'geom', 4326, 'LINESTRING', 'XY');

I downloaded sptaialite-gui version 4.3.0 here http://www.gaia-gis.it/gaia-sins/windows-bin-amd64/.

Do I have to install or add a spatialite extension?

Regards

[mj10777]

On Wednesday, 17 January 2018 11:04:45 UTC+1, Tara wrote:

Hello,

I have a sqlite database "data.sqlite", which is not converted in spatialite yet. My aim is to open the sqlite database in QGis, with the spatialite plugin. If I import the original sqlite database in QGIS,  I got the message "Failure getting table metadata..." .

If I understand you correctly, your 'data.sqlite' is not a Spatialite-Database and you want to add Spatialite capabilities to this Database.

- if yes, you must first call 'InitSpatialMetaData()', which will add all internal needed to be used, also, as a Spatialite Database

-> creating the 'table metadata' that is missing

see:

http://www.gaia-gis.it/gaia-sins/spatialite-sql-latest.html#p16

for the syntax (there are different options that effect the size, depending on what you need).

After that is completed, you should be able call AddGeometryColumn without any failures.

Mark

[Tara]

Hi Mark,

thanks for the quick answer. I followed your hint. If I call

SELECT InitSpatialMetaData();

in the Spatialite GUI, it turn zero. As u can see in my Attach. what am I doin wrong?

[mj10777]

On Wednesday, 17 January 2018 11:32:57 UTC+1, Tara wrote:

Hi Mark,

thanks for the quick answer. I followed your hint. If I call

SELECT InitSpatialMetaData();

in the Spatialite GUI, it turn zero. As u can see in my Attach. what am I doin wrong?

Good question ... 

Can you try to create a new, empty, Database? 

- open spatialite_gui without a Database (otherwise you must 'disconnect' it beforehand)

-> if that works, all preconditions are fulfilled

Otherwise, if the Database is small enough, try to send it to me.

Mark

[Tara]

if I create a new empty database, how can I call the InitSpatialMetaData(); on my data.sqlite database? Cause I want to add the spatialite capabillities to data.sqlite and not to the empty database.

[mj10777]

On Wednesday, 17 January 2018 11:55:14 UTC+1, Tara wrote:

if I create a new empty database,

That is only to test if everything else works correctly 

It should look something like this:

[Tara]

On the link below I found this note:

"spatialite_gui will automatically perform any required initialization task every time a new database is created:
so (using this tool) there is no need at all to explicitly call this function. "..

https://www.gaia-gis.it/gaia-sins/spatialite-cookbook/html/metadata.html

Can't explain myself why the AddGeometryColumn turns zero.

[mj10777]

On Wednesday, 17 January 2018 13:23:54 UTC+1, Tara wrote:

On the link below I found this note:

"spatialite_gui will automatically perform any required initialization task every time a new database is created:
so (using this tool) there is no need at all to explicitly call this function. "..

When you open your data.sqlite Database in spatialite_gui, it already exists

- otherwise you could not open it, so no 'required initialization task' will be performed

I asked you to create a new Database to test if everything else works correctly

- to rule out any other problems

Calling 'InitSpatialMetaData();' on an existing Database should always work (I do it all the time)

- it will return 0 if the metadata is already there, thus not causing a problem by adding everything twice

What is listed of the left hand side (inside spatialite_gui) inside the 'Metadata' and 'Internal  Data' folders?

- empty or not?

If it not empty, SELECT AddGeometryColumn('test', 'geom', 4326, 'LINESTRING', 'XY');

should work, assuming that a TABLE test exists [if not that is the cause, because 'AddGeometryColumn' adds a new column (called 'geom') to an existing TABLE (called 'test'].

Mark

[Tara]

In my attach file you can see that inside the 'Metadata' folder and 'internal Data folder there are some files. And I have also a test table in my database, otherwise the "Select AddGeometryColumn .." query wouldn't make sense.

[Kyle Felipe Vieira Roberto]

Hi, good morning...
Here, when i start a new spatial, i use 2 lines...

SELEC load_extension('mod_spatialite');

and then

SELECT InitSpatialMetadata(1);

On my tests here, using InitSpatialMetadata() was taking too long for creating a data base, and InitSpatialMetadata(1) was faster.

U can create a spatialite at qgis also. Layer > New Layer > Spatialite.....

[mj10777]

On Wednesday, 17 January 2018 14:29:56 UTC+1, Tara wrote:

In my attach file you can see that inside the 'Metadata' folder and 'internal Data folder there are some files.

An that is your problem.

This is not a normal (i.e. Non-Spatial) sqlite3 Database, but a Database that has been created or extended by gdal/ogr 

- it contains a 'geometry_columns' and a 'spatial_ref_sys' table created by gdal/ogr

That is why 'InitSpatialMetaData()' returns 0.

Both systems (gdal/ogr and spatialite) should not be mixed.

Make a copy of the Database and with the copy remove the gdal/ogr TABLES

DROP geometry_columns;
DROP spatial_ref_sys;

The '2018011_eur_2017' table is probably a geometry table from gdal/ogr

- the 'fdo_2018011_eur_2017' is from Spatialite using VirtualFdoOgr to emulate gdal/ogr as a spatialite format

-> you probably received a message about this when starting spatialite_gui!

After DROPing those tables, InitSpatialMetaData() and AddGeometryColumn should work

- but you may not be able to use '2018011_eur_2017' table any more. 

The import that from the original is another problem.

But the cause of your problem is now known.

Mark

[mj10777]

On Wednesday, 17 January 2018 14:29:56 UTC+1, Tara wrote:

In my attach file you can see that inside the 'Metadata' folder and 'internal Data folder there are some files.

I have open an issue, with the goal that spatialite_gui will issue a MessageBox informing the User that the mixing Fdo/Ogr with Spatialte is not possible, when such a problem occurs.

https://www.gaia-gis.it/fossil/spatialite_gui/tktview/a78db748a293c19e37661e2768f37d14fff1ca5b

[mj10777]

On Wednesday, 17 January 2018 14:33:31 UTC+1, Kyle Felipe Vieira Roberto wrote:

Hi, good morning...
Here, when i start a new spatial, i use 2 lines...

SELEC load_extension('mod_spatialite');

and then

SELECT InitSpatialMetadata(1);

On my tests here, using InitSpatialMetadata() was taking too long for creating a data base, and InitSpatialMetadata(1) was faster.

In this case that would not of helped, since the Database was a Fdo/Ogr Database which cannot be mixed. 

I made the reference to

http://www.gaia-gis.it/gaia-sins/spatialite-sql-latest.html#p16

so that the different forms of the command could be read. 

Mark

[a.fu...@lqt.it]

On Wed, 17 Jan 2018 06:34:14 -0800 (PST), 'mj10777' via SpatiaLite
Users wrote:
> On Wednesday, 17 January 2018 14:29:56 UTC+1, Tara wrote:
>
>> In my attach file you can see that inside the 'Metadata' folder and
>> 'internal Data folder there are some files.
>

there are at least three different kinds of Spatial Databases
aka "formats" based on SQLite:

a) SpatiaLite
b) FDO/OGR
c) GeoPackage aka GPKG

all them have similar metatables ("geometry_columns" and
"spatial_ref_sys") but such tables have different layouts
for each specific format.

they so remarkably different that we can use each specific
layout as a "fingerprint" useful to precisely identify
which format is adopted by a given DB-file.

when spatialite_gui opens an existing DB-file it will
always check if it adopts the FDO/OGR or GPKG format,
and in this case will automatically enter the appropriate
"wrapping" mode, showing a message panel like the one
exemplified in the attached figure.
and more or less the same is for the spatialite CLI tool.

I'm unable to see any reference to this message box in
the Tara's posts, and it's really hard to imagine that
a so striking message could pass unnoticed.
so I can only suppose that this specific DB-file isn't
a genuine FDO/OGR ... it looks more likely to be just a
broken DB-file presenting inconsistent metadata tables.

> I have open an issue, with the goal that spatialite_gui will issue a
> MessageBox informing the User that the mixing Fdo/Ogr with Spatialte
> is not possible, when such a problem occurs.
>
> https://www.gaia-gis.it/fossil/spatialite_gui/tktview/a78db748a293c19e37661e2768f37d14fff1ca5b
>

spatialite_gui already notices the user in a very
evident way when a valid FDO/OGR or GPKG DB-file
is going to be connected.
the suggested patch seems to be redundant and
not strictly required (and will surely not
work in the case of malformed DB-files, as the
one we are apparently discussing about).

bye Sandro

[Tara]

ja I overlooked the message box, as you can see in my attach file

[a.fu...@lqt.it]

On Wed, 17 Jan 2018 07:57:19 -0800 (PST), Tara wrote:
> ja I overlooked the message box, as you can see in my attach file
>

all right,

this incident is now definitely closed.

1. it's definitely confirmed that it was a perfectly valid
DB-file adopting the well-known FDO/OGR format.
2. spatialite_gui correctly detected the FDO/OGR format
when connecting the DB-file, and adequately informed
the user about such an exceptional condition.
3. then the user decided to completely ignore this very
critical information, and wrongly continued to assume
it was just a plain SQLite DB-file lacking any Spatial
metadata.
all following errors and unexpected troubles were just
the most obvious consequences of such a gross
misunderstanding.

very short final conclusion:
----------------------------
Tara, a DB-file can support only one "spatial format";
if your DB-file is already based on FDO/OGR there is
absolutely nothing you can do in order to add spatialite
support to the same DB-file.
you necessarily have to create a brand new DB-file adopting
the spatialite's own format, then eventually copying data
between the two DB-files.

bye Sandro

[David J. Bakeman]

You probably want to check out the gdal/ogr documentation. You should
be able to use ogr2ogr command with the appropriate flags to convert the
DB in FDO?OGR to spatialite. Look at the sqlite/spatialite format
details to get the flags.
>
> bye Sandro
>

[a.fu...@lqt.it]

On Thu, 18 Jan 2018 04:54:19 -0800 (PST), Tara wrote:
> And if I open the new database in spatialite_gui and execute
> following query:
>
> SELECT ST_AsText(ST_GeomFromWKB(GEOMETRY)) FROM 'my_table';
>
> I got NULL as result as u can see in my attach file.
>

hi Tara,

your query is wrong, it should simply be:

SELECT ST_AsText(GEOMETRY) FROM 'my_table';

short rationale: "geometry" already contains values encoded
accordingly to the SpatiaLite's own binary BLOB format, there
is absolutely no need to call ST_GeomFromWKB().
even worst: ST_GeomFromWKB() expects to receive a WKB formatted
BLOB, but you are passing to this function a SpatiaLites's own
binary BLOB, thus causing a NULL to be returned.

hint: spending just a couple of hours in studying the very
elementary foundations of SpatiaLite and Spatial SQL could
probably help you to avoid the most trivial errors.

http://www.gaia-gis.it/gaia-sins/spatialite-cookbook/index.html

bye Sandro

[Tara]

ya I deleted my post cause I handled my error. I am very new to spatialite. sry for my bad questions :/

If i connect the db to db manager in QGIS and execute this command

[Tara]

I hope this is my last question. I am still not able to view my data in QGIS. So what I did is, I used the ogr2ogr command to add spatialite capabillities to my original sqlite db. I couldnt display my data cause after following query:

SELECT * FROM geometry_columns
WHERE f_table_name = 'mytable';

I realized that my srid column was empty so I updated my srid (I followed this link update srid)

1. First question, can I add in my ogr2ogr command the srid?
2. If I execute a simple query like select * from 'mytable' where ogc_fid=1; 
and try to create a view of my result I got following error message in QGIS


insert on geometry_columns violates constraint: f_geometry_column value must be lower case

any ideas?

On Thursday, 18 January 2018 14:10:11 UTC+1, sandro furieri wrote:

[Tara]

If I try to set my GEOMETRY column to lower case:

update '20180111_eur_2017' set geometry = lower(GEOMETRY);

I got an error message as you can see in my attach file. what am I doing wrong?

On Thursday, 18 January 2018 14:10:11 UTC+1, sandro furieri wrote:

[a.fu...@lqt.it]

On Thu, 18 Jan 2018 07:22:30 -0800 (PST), Tara wrote:
> If I try to set my GEOMETRY column to lower case:
>
> update '20180111_eur_2017' set geometry = lower(GEOMETRY);
>
> I got an error message as you can see in my attach file. what am I
> doing wrong?
>

Tara,

you are continuing to make trivial errors; I'm really sorry to
say this, but your specific competences about SQL seems to be
absolutely inadequate, even for a beginner.

you absolutely need to strengthen your basic knowledge about
SQL before attempting to adventure yourself in writing your
own queries, even the most simple and elementary.
take a breath, and begin studying with due attention at
least the minimal core of the SQL syntax before attempting
to go further away.

your error explained: the "lower" function will attempt
to transform a text string value into full lowercase.
but the GEOMETRY column will obviously contain only
BLOB values, so lower() will constantly fail returning
NULL (a BLOB is by definition a binary object, and has
the concept of upper- and lower-case letters is obviously
not applicable).
you are badly confusing column-names and column-values.

bye Sandro

[Jukka Rahkonen]

Hi,

1) You can assing a SRID by doing
ogr2ogr -f sqlite -dsco spatialite=yes -a_srs epsg:4326
"C:\Users\test_spatialite.sqlite" "C:\Users\test.sqlite"

You naturally use the epsg code that is correct for you. This is
documented in http://www.gdal.org/ogr2ogr.html. Keep the document
available, you will need it in the future. Another document you will
need is http://www.gdal.org/drv_sqlite.html. For example you seem to
have problems with QGIS and upper case GEOMETRY but drv_sqlite document
and therefore you should be interested in layer creation option
GEOMETRY_NAME.

2) By your description I do not understand what you are doing. Are you
creating a view with some special tool that is included in QGIS?

-Jukka Rahkonen-

Tara kirjoitti 2018-01-18 17:05:
> I hope this is my last question. I am still not able to view my data
> in QGIS. So what I did is, I used the ogr2ogr command to add
> spatialite capabillities to my original sqlite db. I couldnt display
> my data cause after following query:
>
> SELECT * FROM geometry_columns
> WHERE f_table_name = 'mytable';
>
> I realized that my srid column was empty so I updated my srid (I

> followed this link update srid [2])

>> http://www.gaia-gis.it/gaia-sins/spatialite-cookbook/index.html [1]
>>
>> bye Sandro
>
> --
> You received this message because you are subscribed to the Google
> Groups "SpatiaLite Users" group.
> To unsubscribe from this group and stop receiving emails from it, send
> an email to spatialite-use...@googlegroups.com.
> To post to this group, send email to
> spatiali...@googlegroups.com.
> Visit this group at https://groups.google.com/group/spatialite-users.
> For more options, visit https://groups.google.com/d/optout.
>
>
> Links:
> ------
> [1] http://www.gaia-gis.it/gaia-sins/spatialite-cookbook/index.html
> [2]
> https://gis.stackexchange.com/questions/130258/setting-srid-in-spatialite-table

[Jukka Rahkonen]

Hi,

What your SQL tries to do is to update your data table
'20180111_eur_2017' so that what you have in the field named "geometry"
would be updated in the value that you get from reading the contents of
field "GEOMETRY" after it is pushed through the function "lower".

I apologize but it does not make any sense. I appreciate that you seem
to be very eager in learning new things but it is hard to learn higher
level stuff without knowing the basics of SQL. What you want to do is to
change name of the column called 'GEOMETRY' into 'geometry'. If you knew
the basics of SQL you would know that it is done with ALTER TABLE
command. Later, when you know the basics of SQLite you will learn that
renaming columns is not really supported in SQLite but you need to use
workarounds https://www.sqlite.org/lang_altertable.html. That gets
complicated. Study the ogr2ogr document, drv_sqlite document and create
a new database with lowercase 'geometry'.

It feels also that DB Manager in QGIS does not work really well. Try to
add Spatialite layers through the Add vector layer button, it may work
better.

-Jukka Rahkonen-