SQL reorg [was] Re: Next steps

[Chris Holmes]

Hey Jason, just looked over the spec, looks good.

One thought though, which I think you were getting at, is that it probably would be good to put the table creation sql statements in the main spec. Was looking at mbtiles, and they have that there (though they don't have the table of columns). So maybe try putting just the table creation sql in? And definitely simplify it to not have the constraints on it.

On Thu, Apr 11, 2013 at 10:55 PM, Chris Holmes <cho...@opengeo.org> wrote:

On Thu, Apr 11, 2013 at 7:20 PM, Jason Poffenberger <jpo...@yahoo.com> wrote:

OK. Got the first revision done.

Awesome. Did you push to github? I couldn't find it, let me know when you do and I can check it out directly. More responses inline.

 

I would suggest that the triggers sections get removed and some general rules defined instead. For example a statement such as

CREATE TRIGGER 'raster_columns_r_raster_column_insert' 

BEFORE INSERT ON 'raster_columns'

FOR EACH ROW BEGIN

SELECT RAISE(ROLLBACK,'insert on raster_columns violates constraint: r_raster_column value must not contain a single quote')

WHERE NEW.r_raster_column LIKE ('%''%');

SELECT RAISE(ROLLBACK,'insert on raster_columns violates constraint: r_raster_column value must not contain a double quote')

WHERE NEW.r_raster_column LIKE ('%"%');

SELECT RAISE(ROLLBACK,'insert on raster_columns violates constraint: r_raster_column value must be lower case')

WHERE NEW.r_raster_column <> lower(NEW.r_raster_column);

END

Could become a note such as

r_raster column should be in all lower case without single or double quotes

I'm not sure that we even need that note? But yeah, I guess if we want to put something in there that could make sense. But I feel like we could just assume people know how to create a proper column, and follow the cases we write in the spec.

 

Also, I would make the create statements more generic such as

CREATE TABLE raster_columns (

r_table_name TEXT NOT NULL,

r_raster_column TEXT NOT NULL,

compr_qual_factor INTEGER NOT NULL DEFAULT -1,

georectification INTEGER NOT NULL DEFAULT -1,

srid INTEGER NOT NULL DEFAULT 0,

CONSTRAINT pk_rc PRIMARY KEY (r_table_name, r_raster_column)

ON CONFLICT ROLLBACK,

CONSTRAINT fk_rc_r_srid FOREIGN KEY (srid)

REFERENCES spatial_ref_sys(srid),

CONSTRAINT fk_rc_r_gc FOREIGN KEY (r_table_name) REFERENCES geopackage_contents(table_name))

Could become

CREATE TABLE raster_columns (

table_name TEXT NOT NULL,

raster_column TEXT NOT NULL,

compression_quality INTEGER NOT NULL DEFAULT -1,

georectification INTEGER NOT NULL DEFAULT -1,

spatial_reference_identifier INTEGER NOT NULL DEFAULT 0,

)

and make a note that spatial_reference_identifier is a foreign key.

Makes sense. But yeah, I feel like if we're not defining the constraints in the main spec then there's no reason to define the create statement at all. People should be able to make it. But we can put the whole thing in the gpkg-safe side spec, for people who want to define tables with proper constraints.

 

As a matter of fact, the whole raster_column table should go out the door. It should look more like the MBTiles table with maybe georectification, spatial_reference_identifier, and possibly compression_quality but I don't know why compression_quality would matter. Anyway, I would rework almost the whole sql spec to be more MBTiles like.

Definitely. But let's hold off on that until we get the first few milestones I laid out. First just factor out all the extraneous pieces (sql statements, requirements, weird notes on implementation) to side specs. So we have at least an mbtiles style spec. And then we can start doing pull requests on real changes, that we can track. 

Though I guess if you want to try it early, before the formatting changes, you could make a branch just for the full changes and PR that. May get out of date though.

But yeah, I agree on getting it down to super similar to MBTiles. But first want to show how even their sort of weird existing spec could become a whole lot more readable. 

 

Jason

---

From: Chris Holmes <cho...@opengeo.org>

To: Jason Poffenberger <jpo...@yahoo.com>
Cc: Scott Clark <sct...@gmail.com>; "gpkg-til...@googlegroups.com" <gpkg-til...@googlegroups.com>; Benjamin T Tuttle NGA-IIG USA CIV <Benjamin...@nga.mil>

Sent: Thursday, April 11, 2013 6:37 PM
Subject: Re: Next steps

Cool, yeah, I just assigned a ticket to you. And on that branch there should be a safe-gpkg.md file, where you can dump all the sql.

Thanks!

On Thu, Apr 11, 2013 at 6:34 PM, Jason Poffenberger <jpo...@yahoo.com> wrote:

I can go ahead and put in the sql statements on the branch right now. I'll try directly on your branch. If anyone else is doing it, please stop me.

---

From: Chris Holmes <cho...@opengeo.org>

To: Scott Clark <sct...@gmail.com>
Cc: "gpkg-til...@googlegroups.com" <gpkg-til...@googlegroups.com>; Jason Poffenberger <jpo...@yahoo.com>; Benjamin T Tuttle NGA-IIG USA CIV <Benjamin...@nga.mil>
Sent: Thursday, April 11, 2013 5:56 PM
Subject: Re: Next steps

Ok, just put out 0.1.0. 

https://github.com/cholmes/gpkg-tiles/tree/v0.1.0

Now we can start rearranging much more. 

I'm going to make branches for 'sql reorg' and 'requirements reorg'. I think that if I create the branches on the command line then everyone should be able to edit directly online on the branch. Just have to switch to the branch in the dropdown. Let me know if that doesn't work. 

Plan should be to put both all the sql statements and then all the requirement statements in side documents.

On Thu, Apr 11, 2013 at 4:33 PM, Chris Holmes <cho...@opengeo.org> wrote:

Ok, so I think the best we can do is to use headings. If you use like heading 5 or 6 it can be pretty small. So we could like turn all the table header text in to headings, and then we'd be able to link to them internally. Linking internally is easy, you just do (#internal-id) for the link.

On Thu, Apr 11, 2013 at 3:56 PM, Chris Holmes <cho...@opengeo.org> wrote:

Hrm, yeah, that doesn't actually get us what I was hoping for. It's just to put the link references at the bottom of the page...

On Thu, Apr 11, 2013 at 12:04 PM, Chris Holmes <cho...@opengeo.org> wrote:

I haven't played with it yet or got it working, but see https://github.com/adam-p/markdown-here/wiki/Markdown-Cheatsheet#wiki-links

On Thu, Apr 11, 2013 at 10:56 AM, Scott Clark <sct...@gmail.com> wrote:

Notes maybe we can put at the end of the document, and do internal links to them?

And the requirements / conformance class stuff makes sense to me in another annex.

Thinking we can just do each annex as a separate document in the same repo.

Writing this up, I was wondering how to link to elements within the doc (using the markup).  There are a lot of cross-references (to some table or some clause) and it would be ideal that those are all hyperlinked.  Being new to markup, I couldn't find a way to tag those elements for cross-reference.

Scott

[Jason Poffenberger]

Sure. I'm knee deep in updating Android maps to version 2 right now and I won't get to it until tonight. If you can wait I can do it tonight.

What I was thinking about the design of this thing, mull this over. Right now the spec assumes there will be a named overlay, much like how you name an overlay in ArcCatalog. That's what the table_name represents, the name of the layer such as "Rivers_In_Africa" and in all the sql statements it's a foreign key back to that named layer. However, MBTiles doesn't go by named overlays, just a grid number and zoom level. If we move towards MBTiles where there are no named overlays, just a grid, then most of the current sql will have to be modified, most likely extensively. Are we planning on moving away from named layers and just assume a grid system?

Jason

---

From: Chris Holmes <cho...@opengeo.org>
To: Jason Poffenberger <jpo...@yahoo.com>
Cc: Scott Clark <sct...@gmail.com>; "gpkg-til...@googlegroups.com" <gpkg-til...@googlegroups.com>; Benjamin T Tuttle NGA-IIG USA CIV <Benjamin...@nga.mil>

Sent: Friday, April 12, 2013 12:07 PM
Subject: SQL reorg [was] Re: Next steps

[Chris Holmes]

On Fri, Apr 12, 2013 at 12:25 PM, Jason Poffenberger <jpo...@yahoo.com> wrote:

Sure. I'm knee deep in updating Android maps to version 2 right now and I won't get to it until tonight. If you can wait I can do it tonight.

Yeah, no problem.

 

What I was thinking about the design of this thing, mull this over. Right now the spec assumes there will be a named overlay, much like how you name an overlay in ArcCatalog. That's what the table_name represents, the name of the layer such as "Rivers_In_Africa" and in all the sql statements it's a foreign key back to that named layer. However, MBTiles doesn't go by named overlays, just a grid number and zoom level. If we move towards MBTiles where there are no named overlays, just a grid, then most of the current sql will have to be modified, most likely extensively. Are we planning on moving away from named layers and just assume a grid system?

I'm not sure. I'd say for changes like this we should make a branch and experiment with the changes to the spec. Then can discuss real changes, instead of in the abstract. And there's great facilities to do discussions on pull requests.

I haven't really dug in to MBtiles - one thing I am wondering now is if you can have more than one tileset in a single MBTile file? Or is it just a representation of exactly one tile set?

[Jason Poffenberger]

I put the create sql back into the main spec and formatted the sql in both the appendix and create blocks in main spec.

Here is the table creation for MBTiles

CREATE TABLE tiles (zoom_level integer, tile_column integer, tile_row integer, tile_data blob);

It appears there is only one possible image for a row/column per zoom level. They break up the world into rows and columns. You can't have multiple layers of imagery such as what ArcMap does. I don't know if that's what we want or not.

Jason

---

From: Chris Holmes <cho...@opengeo.org>
To: Jason Poffenberger <jpo...@yahoo.com>

Cc: "gpkg-til...@googlegroups.com" <gpkg-til...@googlegroups.com>
Sent: Friday, April 12, 2013 12:58 PM
Subject: Re: SQL reorg [was] Re: Next steps

[Chris Holmes]

Cool. You want to make a pull request from that branch? I'll then merge it in.

I'd say next step would be to refactor the 'notes' somehow. I feel like they get in the way of the main specification, with random justifications of why they did things. Perhaps make it another separate document, with headings so we can put in direct links. If you want to take a shot at that then go for it, on a branch.

And then I'd say the next major thing is refactoring the raster stuff in to its own spec, so the tile stuff stands alone. That will take really reading through the spec. But could also make a branch for that and try to start on it.

[Chris Holmes]

(You forgot to cc the list in your reply, let's keep all communication going there so future people can track what we talked about. I'm just including your reply in full, responses inline)

On Wed, Apr 17, 2013 at 8:17 PM, Jason Poffenberger <jpo...@yahoo.com> wrote:

Chris,

So are we thinking of having two separate types of imagery, rasters and tiles? If so, would the tiles be specifically for background maps laid out in grid format and the rasters use arbitrary geometry and not for use as the background in maps and more as an overlay?

So what I'm thinking is that ideally we have a core 'tiles' spec, and then 'raster' builds on it. Raster would still 'implement' tiles, but with a view instead of a real table. So you could access both through the laid out grid format. But rasters could have more operations done on them. I think ideally both tiles and rasters could be used for base layers and overlay - that the spec doesn't really distinguish.

 

I saw you merged the sql back into the main trunk. Do I still make a pull request?

Naw, I just made it, wanted everything merged, and seemed good enough. And wanted to see how merging two branches worked. Had to resolve some conflicts, but it worked out.

 

If we make a separate tiles spec, there are probably some things to do different than MBTiles. I was talking to Ben and his opinion is instead of just one and only one image tile per grid per zoom level, there could be the possibility of more than one tile per grid per zoom level. Also, there could be more than one projection. Does that sound about right? Should I work up a model for that? If we are splitting out tiles from general rasters, I think it may be beneficial to have the data models a little different.

Yeah, that sounds right.

 

Is there anything specific you want me to do right now? If not, I wouldn't mind working a smallish data model sort of as an enhancement to the MBTiles model. I find I can write better if I already have an idea of a working model.

Yeah, go for it with that. I think the last 'formatting' thing I'm contemplating is moving the 'notes' that litter the main spec out of the main flow. Then the next step will be the tile / raster refactor. So yeah, go ahead and make the model and let me know how it goes.

I think we'll want to make a nice branch to try things out there. I would like to try to stick as close to possible to how they laid things out, like no radical model changes yet. Like first get a refactor of tiles and raster in their own documents, even if that is a bit 'broken'. And then another branch on improvements. I may start on that branch, but you can feel free to as well if you get comfortable with your new model. 

Thanks!

Chris

 

Jason

---

From: Chris Holmes <cho...@opengeo.org>
To: Jason Poffenberger <jpo...@yahoo.com>

Cc: "gpkg-til...@googlegroups.com" <gpkg-til...@googlegroups.com>; Tuttle Benjamin - Ben- T NGA- IIG USA CIV <benjamin...@nga.mil>
Sent: Monday, April 15, 2013 4:29 PM