[gdal-dev] Appending to an existing GeoPackage raster table

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
4 messages Options
Reply | Threaded
Open this post in threaded view
|

[gdal-dev] Appending to an existing GeoPackage raster table

bradh
I'm trying to find a way to append data to an existing geopackage raster
layer using GDAL.

The specific case is to use different sources (or different configurations
of a source like WMS) at different zoom levels, but extending an existing
table at the same zoom might also be useful.

As an example, consider a rendered map (like a OSM bright or humanitarian
style), where the level of detail you want at level 12 is different to level
8 or 15.
I can easily create a table at level 12, and add overviews, but the
overviews are just the same data made smaller. Instead, I want to render it
differently at each zoom level.

I tried using APPEND_DATASET, which does work for a new table, but not for
adding data to an existing table:
bradh@marginata:~/related_tables$ gdal_translate -projwin -68.1109 18.6664
-65.0914 17.7307 -projwin_srs EPSG:4326 -co APPEND_SUBDATASET=yes -co
TILING_SCHEME=GoogleMapsCompatible  wmssrc.xml -co RASTER_TABLE=out -of GPKG
out.gpkg
Input file size is 524288, 524288
ERROR 1: sqlite3_exec(CREATE TABLE "out" (id INTEGER PRIMARY KEY
AUTOINCREMENT,zoom_level INTEGER NOT NULL,tile_column INTEGER NOT
NULL,tile_row INTEGER NOT NULL,tile_data BLOB NOT NULL,UNIQUE (zoom_level,
tile_column, tile_row));CREATE TRIGGER "out_zoom_insert" BEFORE INSERT ON
"out" FOR EACH ROW BEGIN SELECT RAISE(ABORT, 'insert on table ''out''
violates constraint: zoom_level not specified for table in
gpkg_tile_matrix') WHERE NOT (NEW.zoom_level IN (SELECT zoom_level FROM
gpkg_tile_matrix WHERE lower(table_name) = lower('out'))) ; END; CREATE
TRIGGER "out_zoom_update" BEFORE UPDATE OF zoom_level ON "out" FOR EACH ROW
BEGIN SELECT RAISE(ABORT, 'update on table ''out'' violates constraint:
zoom_level not specified for table in gpkg_tile_matrix') WHERE NOT
(NEW.zoom_level IN (SELECT zoom_level FROM gpkg_tile_matrix WHERE
lower(table_name) = lower('out'))) ; END; CREATE TRIGGER
"out_tile_column_insert" BEFORE INSERT ON "out" FOR EACH ROW BEGIN SELECT
RAISE(ABORT, 'insert on table ''out'' violates constraint: tile_column
cannot be < 0') WHERE (NEW.tile_column < 0) ; SELECT RAISE(ABORT, 'insert on
table ''out'' violates constraint: tile_column must by < matrix_width
specified for table and zoom level in gpkg_tile_matrix') WHERE NOT
(NEW.tile_column < (SELECT matrix_width FROM gpkg_tile_matrix WHERE
lower(table_name) = lower('out') AND zoom_level = NEW.zoom_level)); END;
CREATE TRIGGER "out_tile_column_update" BEFORE UPDATE OF tile_column ON
"out" FOR EACH ROW BEGIN SELECT RAISE(ABORT, 'update on table ''out''
violates constraint: tile_column cannot be < 0') WHERE (NEW.tile_column < 0)
; SELECT RAISE(ABORT, 'update on table ''out'' violates constraint:
tile_column must by < matrix_width specified for table and zoom level in
gpkg_tile_matrix') WHERE NOT (NEW.tile_column < (SELECT matrix_width FROM
gpkg_tile_matrix WHERE lower(table_name) = lower('out') AND zoom_level =
NEW.zoom_level)); END; CREATE TRIGGER "out_tile_row_insert" BEFORE INSERT ON
"out" FOR EACH ROW BEGIN SELECT RAISE(ABORT, 'insert on table ''out''
violates constraint: tile_row cannot be < 0') WHERE (NEW.tile_row < 0) ;
SELECT RAISE(ABORT, 'insert on table ''out'' violates constraint: tile_row
must by < matrix_height specified for table and zoom level in
gpkg_tile_matrix') WHERE NOT (NEW.tile_row < (SELECT matrix_height FROM
gpkg_tile_matrix WHERE lower(table_name) = lower('out') AND zoom_level =
NEW.zoom_level)); END; CREATE TRIGGER "out_tile_row_update" BEFORE UPDATE OF
tile_row ON "out" FOR EACH ROW BEGIN SELECT RAISE(ABORT, 'update on table
''out'' violates constraint: tile_row cannot be < 0') WHERE (NEW.tile_row <
0) ; SELECT RAISE(ABORT, 'update on table ''out'' violates constraint:
tile_row must by < matrix_height specified for table and zoom level in
gpkg_tile_matrix') WHERE NOT (NEW.tile_row < (SELECT matrix_height FROM
gpkg_tile_matrix WHERE lower(table_name) = lower('out') AND zoom_level =
NEW.zoom_level)); END; ) failed: table "out" already exists
ERROR 1: Raster table out not correctly initialized due to missing call to
SetGeoTransform()

At this stage, the only thing I can think of is to create a bunch of tables
and merge them in some manual SQL. Any other suggestions?


_______________________________________________
gdal-dev mailing list
[hidden email]
https://lists.osgeo.org/mailman/listinfo/gdal-dev
Reply | Threaded
Open this post in threaded view
|

Re: Appending to an existing GeoPackage raster table

Even Rouault-2

Hi,

 

> I'm trying to find a way to append data to an existing geopackage raster

> layer using GDAL.

>

> The specific case is to use different sources (or different configurations

> of a source like WMS) at different zoom levels, but extending an existing

> table at the same zoom might also be useful.

>

> As an example, consider a rendered map (like a OSM bright or humanitarian

> style), where the level of detail you want at level 12 is different to level

> 8 or 15.

> I can easily create a table at level 12, and add overviews, but the

> overviews are just the same data made smaller. Instead, I want to render it

> differently at each zoom level.

 

you can do that with "gdalwarp yoursource existing.gpkg -doo ZOOM_LEVEL=xxx"

This will insert the data at the requested zoom level.

 

>

> I tried using APPEND_DATASET, which does work for a new table, but not for

> adding data to an existing table:

 

Yes, APPEND_SUBDATASET is to add new raster tables.

 

Even

 

--

Spatialys - Geospatial professional services

http://www.spatialys.com


_______________________________________________
gdal-dev mailing list
[hidden email]
https://lists.osgeo.org/mailman/listinfo/gdal-dev
Reply | Threaded
Open this post in threaded view
|

Re: Appending to an existing GeoPackage raster table

mj10777
>> I'm trying to find a way to append data to an existing geopackage raster
layer using GDAL.
My information on GeoPackages may be outdated, since the specification has changed many times since I read it last.
But as far as I remember, the main goal was that a GeoPackage is a complete/finished collection of data ready for deployment.

Even though it may be internally a Database, it should not be treated as such.
So the word 'append' is inappropriate in this concept.

If the condition on which the original raster coverage was created no longer applies, then a new raster coverage must be created that fulfill these new conditions.

In this case you are extending the bounds of the raster coverage, which may work when extending in a East/West direction, but not in a North/West direction (assuming that North/West is 0,0). With mbtiles this would be no problem since the use a world wide tms numbering for the tiles.

>> violates constraint: tile_column cannot be < 0'
>> violates constraint: tile_row cannot be < 0'

These errors imply that this is the case: the new tiles are out of bounds.

>> At this stage, the only thing I can think of is to create a bunch of tables
and merge them in some manual SQL

Creating a new raster coverage, with new extent/bounds and sources should be the idea.

On the fly I would say using a virtual file would be the best approach
- source 1: original GeoPackage
- source 2: the wms source

(Creating a GeoPackage of the wms source with the desired extent and use that as 'source 2' might be a good idea)

Determine the final extent that will be needed for all zoom levels and use gdalwarp (using the bounds parameters) to create a new GeoPackage raster coverage from the virtual file with the new sources.

This should create the tiles correctly, merging the 2 sources together and numbering the tiles correctly based on the given extent.

Theoretically this should work.

Mark Johnson, Berlin Germany

_______________________________________________
gdal-dev mailing list
[hidden email]
https://lists.osgeo.org/mailman/listinfo/gdal-dev
Reply | Threaded
Open this post in threaded view
|

Re: Appending to an existing GeoPackage raster table

bradh
> My information on GeoPackages may be outdated, since the specification has
> changed many times since I read it last.
> But as far as I remember, the main goal was that a GeoPackage is a
> complete/finished collection of data ready for deployment.
I'm reasonably familiar with GeoPackage, and don't recall it ever only being
considered a "finished" product. The current interop experiment (related
tables) is actually about recording extra observations in GeoPackage.

> Even though it may be internally a Database, it should not be treated as
> such.
> So the word 'append' is inappropriate in this concept.
"Citation needed" :-)

> If the condition on which the original raster coverage was created no longer
> applies, then a new raster coverage must be created that fulfill these new
> conditions.
That is a narrow and inappropriate view. In any case, I'm happy to build the
whole raster, just need a way to do it.

> In this case you are extending the bounds of the raster coverage, which may
> work when extending in a East/West direction, but not in a North/West
> direction (assuming that North/West is 0,0). With mbtiles this would be no
> problem since the use a world wide tms numbering for the tiles.
You can do this with GeoPackage too, supported by GDAL as one of the tiling
schemes (http://www.gdal.org/drv_geopackage_raster.html)

> Creating a new raster coverage, with new extent/bounds and sources should be
> the idea.
This is my main need. I was only looking for how to create a raster coverage
with different content at different zoom levels.  I will try the gdalwarp
solution next, although I did make it work with a simple bash loop and some
spatialite SQL commands.

Brad



_______________________________________________
gdal-dev mailing list
[hidden email]
https://lists.osgeo.org/mailman/listinfo/gdal-dev