Load Geotiff files to db without access to command line

[nigel donald]

Hi Users,

Is it at all possible to load geotiffs into a db without having access to command line? Unfortunately I have no administrative privilages on my work PC and cant seem to find an alternative method to loading them in. Any ifo is greatly appreciated

Thanks

Nigel 

[mj10777]

On Tuesday, 29 November 2016 16:13:23 UTC+1, nigel donald wrote:

Hi Users,

Is it at all possible to load geotiffs into a db without having access to command line? Unfortunately I have no administrative privilages on my work PC and cant seem to find an alternative method to loading them in. Any ifo is greatly appreciated

From where do you want to load it? 

- spatialite_gui? 

SELECT BlobFromFile('path/to_my_geotiff.tif');

normally needs:

SPATIALITE_SECURITY=relaxed

which is often set in the terminal  before calling.

Mark

Thanks

Nigel 

[nigel donald]

Hi Mark,

Yes I am using Spatialite gui. I must first apologise for my lack of ability with this, but if I go to Query/View Composer then SELECT blob (then) FROM file enter file path ?? or is this command all on one line? 

As you can tell complete newbie!!

Thanks again 

Nigel 

[Jukka Rahkonen]

Hi,

Use the SQL space of the spatialite_gui. First you need a table for your
data.

CREATE TABLE my_images (image_data);

Then you can import and save an image as BLOB with

INSERT INTO my_images (image_data)
SELECT BlobFromFile('c:\data\test.tif');


-Jukka Rahkonen-

> --
> 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.

[mj10777]

On Wednesday, 7 December 2016 16:12:45 UTC+1, Jukka Rahkonen wrote:

Hi,

Use the SQL space of the spatialite_gui. First you need a table for your
data.

CREATE TABLE my_images (image_data);

Then you can import and save an image as BLOB with

INSERT INTO my_images (image_data)
SELECT BlobFromFile('c:\data\test.tif');

As a 'newbie' I would suggest to use a more appropriate syntax:

CREATE TABLE IF NOT EXISTS "my_undiscriptive_name"

( -- TODO: give the TABLE a useful, meaningful name!

 id_image INTEGER PRIMARY KEY AUTOINCREMENT,

 file_name TEXT DEFAULT '',

 image_data BLOB

);

INSERT INTO "my_undiscriptive_name" (file_name,image_data) 

SELECT "test.tif", BlobFromFile('c:\data\test.tif'); 

Believe me, in the future you will be glad that you learned to do such things correctly 

- avoiding most (but not all) of the traps that exist

Mark

[nigel donald]

Hi Guys,

Ok I am making progress, able to create table and run the Insert/select query. I am getting the message - SQL query returned an empty ResultSet

This is not an error

Could anyone let me know if I have successfully inserted 'mytest .tif'? I cant see anything in the table should I be able to load the tif into QGIS?

Again all help to now is very much appreciated

Thanks

Nigel 

[mj10777]

On Tuesday, 13 December 2016 13:08:25 UTC+1, nigel donald wrote:

Hi Guys,

Ok I am making progress, able to create table and run the Insert/select query. I am getting the message - SQL query returned an empty ResultSet

This is not an error

When INSERTing this is normal.

- but remember: you are INSERTING binary data into a 'BLOB' field

--> a BLOB can contain anything, thus as general rule: nobody will assume what it contains

Geometries are also stored in BLOBs

- but there are special TABLEs that tells a reading application (that asks) that BLOB x is a geometry

So when OGR (which QGIS uses) knows how to read this BLOB, thus it knows what to expect.

spatialite_gui will attempt to analyse an, otherwise unknown, BLOB

- and will tell you what it can determine correctly when listing a TABLE

Most programs will ignore BLOBs.

However if you do a SELECT on the table (i.e: 'SELECT * FROM "my_undiscriptive_name"')

- you MUST see a result

Could anyone let me know if I have successfully inserted 'mytest .tif'? I cant see anything in the table should I be able to load the tif into QGIS?

QGIS will have no idea what you have placed in your BLOB

- so will, most likely, ignore it

Mark

[Jukka Rahkonen]

Hi,

If your final goal is to load GeoTIFF files into a database and show
them in QGIS, perhaps you should consider using GeoPackage
http://www.gdal.org/drv_geopackage_raster.html.

-Jukka Rahkonen-

nigel donald wrote 2016-12-13 14:08:
> Hi Guys,
>
> Ok I am making progress, able to create table and run the

> Insert/select query. I am getting the message - _SQL query returned an
> empty ResultSet_
>
> _This is not an error_

>>> https://groups.google.com/group/spatialite-users [1].
>>>> For more options, visit https://groups.google.com/d/optout [2].
>
>
> Links:
> ------
> [1] https://groups.google.com/group/spatialite-users
> [2] https://groups.google.com/d/optout

[nigel donald]

Mark,

 Yes I can see my tif when I run the 'select from'. Brilliant thank you! One down ~350 tifs to go!!

Nigel 

[nigel donald]

Thanks Jukka will look into this

Nigel