SQLite Forum

Are the rowid values in a Geopoly table stable?

Are the rowid values in a Geopoly table stable?

(1) By Simon Willison (simonw) on 2023-09-12 00:14:52 [link] [source]

I know that rowid columns in regular tables are not stable identifiers - they can change as a result of a VACUUM.

But how about those columns in a Geopoly table?

The docs at https://www.sqlite.org/geopoly.html say:

Every geopoly table contains a built-in integer "rowid" column and a "_shape" column that contains the polygon associated with that row of the table.

Is it safe to treat that rowid as a stable identifier? I've tried some experiments and VACUUM doesn't seem to change those rowids, but I want to be really confident.

If it's not safe to treat rowid as stable, is there a way to add an alternative primary key to the Geopoly table?

(If they are stable, I suggest updating the Geopoly documentation to mention that.)

(2) By Simon Willison (simonw) on 2023-09-12 01:25:20 in reply to 1 [source]

Tiny related bug report: https://www.sqlite.org/geopoly.html#the_geopoly_regular_x_y_r_n_function says "As an example, the following graphic:" but the graphic (an SVG) is currently missing from the page.

It should be displaying this: https://gist.github.com/simonw/828981e70e581a44ae5253d016c283f8

(3) By Richard Hipp (drh) on 2023-09-12 13:40:33 in reply to 2 [link] [source]

Thanks for the bug report. Should now be fixed.

(4) By Simon Willison (simonw) on 2023-09-18 20:06:45 in reply to 3 [link] [source]

Yes the page works for me now.

Can you confirm if rowid on those GeoPoly tables is a stable identifier or not?