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?