You: tell me about ways to use spatialite
Assistant: SpatiaLite is an extension to SQLite that adds spatial data types and functions, allowing you to work with geographic and spatial data directly within a SQLite database. Here are some ways to use SpatiaLite:
Spatial Indexing:
- Adding a Geometry Column: You can add a geometry column to an existing table that stores latitude and longitude columns, then populate this geometry column and create a spatial index for efficient spatial queries.
import sqlite3 conn = sqlite3.connect('museums.db') conn.enable_load_extension(True) conn.load_extension('/usr/local/lib/mod_spatialite.dylib') conn.execute('select InitSpatialMetadata(1)') conn.execute("SELECT AddGeometryColumn('museums', 'point_geom', 4326, 'POINT', 2);") conn.execute(''' UPDATE museums SET point_geom = GeomFromText('POINT('||"longitude"||' '||"latitude"||')',4326); ''') conn.execute('select CreateSpatialIndex("museums", "point_geom");') conn.commit() conn.close()Making Use of a Spatial Index:
- Once you have created a spatial index, you can perform efficient bounding box queries using it. For example, you can retrieve rows using the following pattern:
select * from museums where museums.rowid in ( SELECT pkid FROM idx_museums_point_geom where xmin > :bbox_minx and xmax < :bbox_maxx and ymin > :bbox_miny and ymax < :bbox_maxy );Importing Shapefiles:
- SpatiaLite supports importing shapefiles, a common format for geospatial data. You can use the
spatialitecommand-line tool to create a new database table from a shapefile.This command loads the shapefile into a new database table called$ spatialite rivers-database.db spatialite> .loadshp narivs rivers CP1252 23032rivers.Importing GeoJSON Polygons:
- You can import GeoJSON polygons using the Shapely library in Python. This involves converting GeoJSON data to Well Known Text (WKT) format and inserting it into a SpatiaLite database.
import sqlite3 from shapely.geometry import shape import requests conn = sqlite3.connect('places.db') conn.enable_load_extension(True) conn.load_extension('/usr/local/lib/mod_spatialite.dylib') conn.execute('select InitSpatialMetadata(1)') conn.execute('create table places (id integer primary key, name text);') conn.execute("SELECT AddGeometryColumn('places', 'geom', 4326, 'MULTIPOLYGON', 2);") conn.execute("SELECT CreateSpatialIndex('places', 'geom');") geojson = requests.get('https://data.whosonfirst.org/404/227/475/404227475.geojson').json() wkt = shape(geojson['geometry']).wkt conn.execute("INSERT INTO places (id, name, geom) VALUES(null, ?, GeomFromText(?, 4326))", ("Wales", wkt)) conn.commit()Querying with Spatial Functions:
- You can use spatial SQL functions such as
within()to check if a point is within a geometry. This can be optimized using a spatial index.select name from places where within(GeomFromText('POINT(-3.1724366 51.4704448)'), places.geom) and rowid in ( SELECT pkid FROM idx_places_geom where xmin < -3.1724366 and xmax > -3.1724366 and ymin < 51.4704448 and ymax > 51.4704448 );These examples illustrate the powerful capabilities that SpatiaLite brings to SQLite for handling and querying spatial and geographic data. Each of these functionalities can be useful in various applications involving mapping, spatial analysis, and geographic information systems (GIS).
File Search Results
{ 'content': [ { 'text': 'apt install spatialite-bin libsqlite3-mod-spatialite\n'
'\n'
'Depending on your distribution, you should be able to run Datasette something like this:\n'
'\n'
'datasette --load-extension=/usr/lib/x86_64-linux-gnu/mod_spatialite.so\n'
'\n'
'If you are unsure of the location of the module, try running locate mod_spatialite and see '
'what comes back.\n'
'\n'
'1.15.2 Spatial indexing latitude/longitude columns\n'
'\n'
'Here’s a recipe for taking a table with existing latitude and longitude columns, adding a '
'SpatiaLite POINT geometry\n'
'column to that table, populating the new column and then populating a spatial index:\n'
'\n'
'import sqlite3\n'
"conn = sqlite3.connect('museums.db')\n"
'# Lead the spatialite extension:\n'
'conn.enable_load_extension(True)\n'
"conn.load_extension('/usr/local/lib/mod_spatialite.dylib')\n"
'# Initialize spatial metadata for this database:\n'
"conn.execute('select InitSpatialMetadata(1)')\n"
'# Add a geometry column called point_geom to our museums table:\n'
'conn.execute("SELECT AddGeometryColumn(\'museums\', \'point_geom\', 4326, \'POINT\', 2);")\n'
'# Now update that geometry column with the lat/lon points\n'
"conn.execute('''\n"
'\n'
'UPDATE museums SET\n'
'point_geom = GeomFromText(\'POINT(\'||"longitude"||\' \'||"latitude"||\')\',4326);\n'
'\n'
"''')\n"
'# Now add a spatial index to that column\n'
'conn.execute(\'select CreateSpatialIndex("museums", "point_geom");\')\n'
"# If you don't commit your changes will not be persisted:\n"
'conn.commit()\n'
'conn.close()\n'
'\n'
'56 Chapter 1. Contents\n'
'\n'
'https://brew.sh/\n'
'\n'
'\n'
'Datasette Documentation\n'
'\n'
'1.15.3 Making use of a spatial index\n'
'\n'
'SpatiaLite spatial indexes are R*Trees. They allow you to run efficient bounding box queries '
'using a sub-select, with\n'
'a similar pattern to that used for Searches using custom SQL.\n'
'\n'
'In the above example, the resulting index will be called idx_museums_point_geom. This takes '
'the form of a\n'
'SQLite virtual table. You can inspect its contents using the following query:\n'
'\n'
'select * from idx_museums_point_geom limit 10;\n'
'\n'
'Here’s a live example: timezones-api.datasette.io/timezones/idx_timezones_Geometry\n'
'\n'
'pkid xmin xmax ymin ymax\n'
'1 -8.601725578308105 -2.4930307865142822 4.162120819091797 10.74019718170166\n'
'2 -3.2607860565185547 1.27329421043396 4.539252281188965 11.174856185913086\n'
'3 32.997581481933594 47.98238754272461 3.3974475860595703 14.894054412841797\n'
'4 -8.66890811920166 11.997337341308594 18.9681453704834 37.296207427978516\n'
'5 36.43336486816406 43.300174713134766 12.354820251464844 18.070993423461914\n'
'\n'
'You can now construct efficient bounding box queries that will make use of the index like '
'this:\n'
'\n'
'select * from museums where museums.rowid in (\n'
'SELECT pkid FROM idx_museums_point_geom\n'
'-- left-hand-edge of point > left-hand-edge of bbox (minx)\n'
'where xmin > :bbox_minx\n'
'-- right-hand-edge of point < right-hand-edge of bbox (maxx)\n'
'and xmax < :bbox_maxx\n'
'-- bottom-edge of point > bottom-edge of bbox (miny)\n'
'and ymin > :bbox_miny\n'
'-- top-edge of point < top-edge of bbox (maxy)\n'
'and ymax < :bbox_maxy\n'
'\n'
');',
'type': 'text'}],
'file_id': 'file-FeX0b3otKd4tjKQ9mZm98Gyv',
'file_name': 'docs-datasette-io-en-0.59.1.pdf',
'score': 0.8814549080805522}
{ 'content': [ { 'text': 'FTS5 is the most advanced module but may not be available in the SQLite version that is '
'bundled with your Python\n'
'installation. Most importantly, FTS5 is the only version that has the ability to order by '
'search relevance without\n'
'needing extra code.\n'
'\n'
'If you can’t be sure that FTS5 will be available, you should use FTS4.\n'
'\n'
'1.15 SpatiaLite\n'
'\n'
'The SpatiaLite module for SQLite adds features for handling geographic and spatial data. For '
'an example of what you\n'
'can do with it, see the tutorial Building a location to time zone API with SpatiaLite, '
'OpenStreetMap and Datasette.\n'
'\n'
'To use it with Datasette, you need to install the mod_spatialite dynamic library. This can '
'then be loaded into\n'
'Datasette using the --load-extension command-line option.\n'
'\n'
'Datasette can look for SpatiaLite in common installation locations if you run it like this:\n'
'\n'
'datasette --load-extension=spatialite\n'
'\n'
'If SpatiaLite is in another location, use the full path to the extension instead:\n'
'\n'
'datasette --load-extension=/usr/local/lib/mod_spatialite.dylib\n'
'\n'
'1.15. SpatiaLite 55\n'
'\n'
'https://www.gaia-gis.it/fossil/libspatialite/index\n'
'https://simonwillison.net/2017/Dec/12/location-time-zone-api/\n'
'\n'
'\n'
'Datasette Documentation\n'
'\n'
'1.15.1 Installation\n'
'\n'
'Installing SpatiaLite on OS X\n'
'\n'
'The easiest way to install SpatiaLite on OS X is to use Homebrew.\n'
'\n'
'brew update\n'
'brew install spatialite-tools\n'
'\n'
'This will install the spatialite command-line tool and the mod_spatialite dynamic library.\n'
'\n'
'You can now run Datasette like so:\n'
'\n'
'datasette --load-extension=spatialite\n'
'\n'
'Installing SpatiaLite on Linux\n'
'\n'
'SpatiaLite is packaged for most Linux distributions.\n'
'\n'
'apt install spatialite-bin libsqlite3-mod-spatialite\n'
'\n'
'Depending on your distribution, you should be able to run Datasette something like this:\n'
'\n'
'datasette --load-extension=/usr/lib/x86_64-linux-gnu/mod_spatialite.so\n'
'\n'
'If you are unsure of the location of the module, try running locate mod_spatialite and see '
'what comes back.\n'
'\n'
'1.15.2 Spatial indexing latitude/longitude columns\n'
'\n'
'Here’s a recipe for taking a table with existing latitude and longitude columns, adding a '
'SpatiaLite POINT geometry\n'
'column to that table, populating the new column and then populating a spatial index:\n'
'\n'
'import sqlite3\n'
"conn = sqlite3.connect('museums.db')\n"
'# Lead the spatialite extension:\n'
'conn.enable_load_extension(True)\n'
"conn.load_extension('/usr/local/lib/mod_spatialite.dylib')\n"
'# Initialize spatial metadata for this database:\n'
"conn.execute('select InitSpatialMetadata(1)')\n"
'# Add a geometry column called point_geom to our museums table:\n'
'conn.execute("SELECT AddGeometryColumn(\'museums\', \'point_geom\', 4326, \'POINT\', 2);")\n'
'# Now update that geometry column with the lat/lon points\n'
"conn.execute('''\n"
'\n'
'UPDATE museums SET\n'
'point_geom = GeomFromText(\'POINT(\'||"longitude"||\' \'||"latitude"||\')\',4326);\n'
'\n'
"''')\n"
'# Now add a spatial index to that column\n'
'conn.execute(\'select CreateSpatialIndex("museums", "point_geom");\')\n'
"# If you don't commit your changes will not be persisted:\n"
'conn.commit()\n'
'conn.close()\n'
'\n'
'56 Chapter 1. Contents\n'
'\n'
'https://brew.sh/\n'
'\n'
'\n'
'Datasette Documentation\n'
'\n'
'1.15.3 Making use of a spatial index\n'
'\n'
'SpatiaLite spatial indexes are R*Trees. They allow you to run efficient bounding box queries '
'using a sub-select, with\n'
'a similar pattern to that used for Searches using custom SQL.',
'type': 'text'}],
'file_id': 'file-FeX0b3otKd4tjKQ9mZm98Gyv',
'file_name': 'docs-datasette-io-en-0.59.1.pdf',
'score': 0.8277095293827835}
{ 'content': [ { 'text': '1.15 SpatiaLite . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . '
'. . . . . . . . . . . 55\n'
'1.15.1 Installation . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . '
'. . . . . . . 56\n'
'1.15.2 Spatial indexing latitude/longitude columns . . . . . . . . . . . . . . . . . . . . . '
'. . . . . 56\n'
'1.15.3 Making use of a spatial index . . . . . . . . . . . . . . . . . . . . . . . . . . . . '
'. . . . . . 57\n'
'1.15.4 Importing shapefiles into SpatiaLite . . . . . . . . . . . . . . . . . . . . . . . . '
'. . . . . . 57\n'
'1.15.5 Importing GeoJSON polygons using Shapely . . . . . . . . . . . . . . . . . . . . . . '
'. . . 58\n'
'1.15.6 Querying polygons using within() . . . . . . . . . . . . . . . . . . . . . . . . . . '
'. . . . . 59\n'
'\n'
'1.16 Metadata . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . '
'. . . . . . . . . . 59\n'
'1.16.1 Per-database and per-table metadata . . . . . . . . . . . . . . . . . . . . . . . . . '
'. . . . . 60\n'
'1.16.2 Source, license and about . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . '
'. . . . . . 60\n'
'1.16.3 Column descriptions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . '
'. . . . . 61\n'
'1.16.4 Specifying units for a column . . . . . . . . . . . . . . . . . . . . . . . . . . . . '
'. . . . . . 61\n'
'1.16.5 Setting a default sort order . . . . . . . . . . . . . . . . . . . . . . . . . . . . '
'. . . . . . . 62\n'
'1.16.6 Setting a custom page size . . . . . . . . . . . . . . . . . . . . . . . . . . . . . '
'. . . . . . 62\n'
'1.16.7 Setting which columns can be used for sorting . . . . . . . . . . . . . . . . . . . . '
'. . . . . 63\n'
'1.16.8 Specifying the label column for a table . . . . . . . . . . . . . . . . . . . . . . . '
'. . . . . . 63\n'
'1.16.9 Hiding tables . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . '
'. . . . . . 64',
'type': 'text'}],
'file_id': 'file-FeX0b3otKd4tjKQ9mZm98Gyv',
'file_name': 'docs-datasette-io-en-0.59.1.pdf',
'score': 0.7975703160989429}
{ 'content': [ { 'text': 'The easiest way to turn this into semi-readable data is to use the SpatiaLite AsGeoJSON '
'function. Try the following\n'
'using the SQL query interface at http://localhost:8001/rivers-database:\n'
'\n'
'select *, AsGeoJSON(Geometry) from rivers limit 10;\n'
'\n'
'This will give you back an additional column of GeoJSON. You can copy and paste GeoJSON from '
'this column into\n'
'the debugging tool at geojson.io to visualize it on a map.\n'
'\n'
'To see a more interesting example, try ordering the records with the longest geometry first. '
'Since there are 467,000\n'
'rows in the table you will first need to increase the SQL time limit imposed by Datasette:\n'
'\n'
'datasette rivers-database.db \\\n'
'--load-extension=/usr/local/lib/mod_spatialite.dylib \\\n'
'--setting sql_time_limit_ms 10000\n'
'\n'
'Now try the following query:\n'
'\n'
'select *, AsGeoJSON(Geometry) from rivers\n'
'order by length(Geometry) desc limit 10;\n'
'\n'
'1.15.5 Importing GeoJSON polygons using Shapely\n'
'\n'
'Another common form of polygon data is the GeoJSON format. This can be imported into '
'SpatiaLite directly, or by\n'
'using the Shapely Python library.\n'
'\n'
'Who’s On First is an excellent source of openly licensed GeoJSON polygons. Let’s import the '
'geographical polygon\n'
'for Wales. First, we can use the Who’s On First Spelunker tool to find the record for '
'Wales:\n'
'\n'
'spelunker.whosonfirst.org/id/404227475\n'
'\n'
'That page includes a link to the GeoJSON record, which can be accessed here:\n'
'\n'
'data.whosonfirst.org/404/227/475/404227475.geojson\n'
'\n'
'Here’s Python code to create a SQLite database, enable SpatiaLite, create a places table and '
'then add a record for\n'
'Wales:\n'
'\n'
'import sqlite3\n'
"conn = sqlite3.connect('places.db')\n"
'# Enable SpatialLite extension\n'
'conn.enable_load_extension(True)\n'
"conn.load_extension('/usr/local/lib/mod_spatialite.dylib')\n"
'# Create the masic countries table\n'
"conn.execute('select InitSpatialMetadata(1)')\n"
"conn.execute('create table places (id integer primary key, name text);')\n"
'# Add a MULTIPOLYGON Geometry column\n'
'conn.execute("SELECT AddGeometryColumn(\'places\', \'geom\', 4326, \'MULTIPOLYGON\', 2);")\n'
'# Add a spatial index against the new column\n'
'\n'
'(continues on next page)\n'
'\n'
'58 Chapter 1. Contents\n'
'\n'
'https://www.gaia-gis.it/gaia-sins/BLOB-Geometry.html\n'
'https://geojson.io/\n'
'https://pypi.org/project/Shapely/\n'
'https://whosonfirst.org/\n'
'https://spelunker.whosonfirst.org/id/404227475/\n'
'https://data.whosonfirst.org/404/227/475/404227475.geojson\n'
'\n'
'\n'
'Datasette Documentation\n'
'\n'
'(continued from previous page)\n'
'\n'
'conn.execute("SELECT CreateSpatialIndex(\'places\', \'geom\');")\n'
'# Now populate the table\n'
'from shapely.geometry.multipolygon import MultiPolygon\n'
'from shapely.geometry import shape\n'
'import requests\n'
"geojson = requests.get('https://data.whosonfirst.org/404/227/475/404227475.geojson').\n"
'→˓json()\n'
'# Convert to "Well Known Text" format\n'
"wkt = shape(geojson['geometry']).wkt\n"
'# Insert and commit the record\n'
'conn.execute("INSERT INTO places (id, name, geom) VALUES(null, ?, GeomFromText(?,\n'
'→˓4326))", (\n'
'\n'
'"Wales", wkt\n'
'))\n'
'conn.commit()\n'
'\n'
'1.15.6 Querying polygons using within()\n'
'\n'
'The within() SQL function can be used to check if a point is within a geometry:\n'
'\n'
'select\n'
'name\n'
'\n'
'from\n'
'places',
'type': 'text'}],
'file_id': 'file-FeX0b3otKd4tjKQ9mZm98Gyv',
'file_name': 'docs-datasette-io-en-0.59.1.pdf',
'score': 0.7765844610457463}
{ 'content': [ { 'text': 'In the above example, the resulting index will be called idx_museums_point_geom. This takes '
'the form of a\n'
'SQLite virtual table. You can inspect its contents using the following query:\n'
'\n'
'select * from idx_museums_point_geom limit 10;\n'
'\n'
'Here’s a live example: timezones-api.datasette.io/timezones/idx_timezones_Geometry\n'
'\n'
'pkid xmin xmax ymin ymax\n'
'1 -8.601725578308105 -2.4930307865142822 4.162120819091797 10.74019718170166\n'
'2 -3.2607860565185547 1.27329421043396 4.539252281188965 11.174856185913086\n'
'3 32.997581481933594 47.98238754272461 3.3974475860595703 14.894054412841797\n'
'4 -8.66890811920166 11.997337341308594 18.9681453704834 37.296207427978516\n'
'5 36.43336486816406 43.300174713134766 12.354820251464844 18.070993423461914\n'
'\n'
'You can now construct efficient bounding box queries that will make use of the index like '
'this:\n'
'\n'
'select * from museums where museums.rowid in (\n'
'SELECT pkid FROM idx_museums_point_geom\n'
'-- left-hand-edge of point > left-hand-edge of bbox (minx)\n'
'where xmin > :bbox_minx\n'
'-- right-hand-edge of point < right-hand-edge of bbox (maxx)\n'
'and xmax < :bbox_maxx\n'
'-- bottom-edge of point > bottom-edge of bbox (miny)\n'
'and ymin > :bbox_miny\n'
'-- top-edge of point < top-edge of bbox (maxy)\n'
'and ymax < :bbox_maxy\n'
'\n'
');\n'
'\n'
'Spatial indexes can be created against polygon columns as well as point columns, in which '
'case they will represent\n'
'the minimum bounding rectangle of that polygon. This is useful for accelerating within '
'queries, as seen in the\n'
'Timezones API example.\n'
'\n'
'1.15.4 Importing shapefiles into SpatiaLite\n'
'\n'
'The shapefile format is a common format for distributing geospatial data. You can use the '
'spatialite command-\n'
'line tool to create a new database table from a shapefile.\n'
'\n'
'Try it now with the North America shapefile available from the University of North Carolina '
'Global River Database\n'
'project. Download the file and unzip it (this will create files called narivs.dbf, '
'narivs.prj, narivs.shp\n'
'and narivs.shx in the current directory), then run the following:\n'
'\n'
'$ spatialite rivers-database.db\n'
'SpatiaLite version ..: 4.3.0a Supported Extensions:\n'
'...\n'
'spatialite> .loadshp narivs rivers CP1252 23032\n'
'========\n'
"Loading shapefile at 'narivs' into SQLite table 'rivers'\n"
'...\n'
"Inserted 467973 rows into 'rivers' from SHAPEFILE\n"
'\n'
'1.15. SpatiaLite 57\n'
'\n'
'https://timezones-api.datasette.io/timezones/idx_timezones_Geometry\n'
'https://en.wikipedia.org/wiki/Shapefile\n'
'http://gaia.geosci.unc.edu/rivers/\n'
'\n'
'\n'
'Datasette Documentation\n'
'\n'
'This will load the data from the narivs shapefile into a new database table called rivers.\n'
'\n'
'Exit out of spatialite (using Ctrl+D) and run Datasette against your new database like '
'this:\n'
'\n'
'datasette rivers-database.db \\\n'
'--load-extension=/usr/local/lib/mod_spatialite.dylib',
'type': 'text'}],
'file_id': 'file-FeX0b3otKd4tjKQ9mZm98Gyv',
'file_name': 'docs-datasette-io-en-0.59.1.pdf',
'score': 0.7745055121888779}
{ 'content': [ { 'text': ');\n'
'\n'
'Spatial indexes can be created against polygon columns as well as point columns, in which '
'case they will represent\n'
'the minimum bounding rectangle of that polygon. This is useful for accelerating within '
'queries, as seen in the\n'
'Timezones API example.\n'
'\n'
'1.15.4 Importing shapefiles into SpatiaLite\n'
'\n'
'The shapefile format is a common format for distributing geospatial data. You can use the '
'spatialite command-\n'
'line tool to create a new database table from a shapefile.\n'
'\n'
'Try it now with the North America shapefile available from the University of North Carolina '
'Global River Database\n'
'project. Download the file and unzip it (this will create files called narivs.dbf, '
'narivs.prj, narivs.shp\n'
'and narivs.shx in the current directory), then run the following:\n'
'\n'
'$ spatialite rivers-database.db\n'
'SpatiaLite version ..: 4.3.0a Supported Extensions:\n'
'...\n'
'spatialite> .loadshp narivs rivers CP1252 23032\n'
'========\n'
"Loading shapefile at 'narivs' into SQLite table 'rivers'\n"
'...\n'
"Inserted 467973 rows into 'rivers' from SHAPEFILE\n"
'\n'
'1.15. SpatiaLite 57\n'
'\n'
'https://timezones-api.datasette.io/timezones/idx_timezones_Geometry\n'
'https://en.wikipedia.org/wiki/Shapefile\n'
'http://gaia.geosci.unc.edu/rivers/\n'
'\n'
'\n'
'Datasette Documentation\n'
'\n'
'This will load the data from the narivs shapefile into a new database table called rivers.\n'
'\n'
'Exit out of spatialite (using Ctrl+D) and run Datasette against your new database like '
'this:\n'
'\n'
'datasette rivers-database.db \\\n'
'--load-extension=/usr/local/lib/mod_spatialite.dylib\n'
'\n'
'If you browse to http://localhost:8001/rivers-database/rivers you will see the new table... '
'but\n'
'the Geometry column will contain unreadable binary data (SpatiaLite uses a custom format '
'based on WKB).\n'
'\n'
'The easiest way to turn this into semi-readable data is to use the SpatiaLite AsGeoJSON '
'function. Try the following\n'
'using the SQL query interface at http://localhost:8001/rivers-database:\n'
'\n'
'select *, AsGeoJSON(Geometry) from rivers limit 10;\n'
'\n'
'This will give you back an additional column of GeoJSON. You can copy and paste GeoJSON from '
'this column into\n'
'the debugging tool at geojson.io to visualize it on a map.\n'
'\n'
'To see a more interesting example, try ordering the records with the longest geometry first. '
'Since there are 467,000\n'
'rows in the table you will first need to increase the SQL time limit imposed by Datasette:\n'
'\n'
'datasette rivers-database.db \\\n'
'--load-extension=/usr/local/lib/mod_spatialite.dylib \\\n'
'--setting sql_time_limit_ms 10000\n'
'\n'
'Now try the following query:\n'
'\n'
'select *, AsGeoJSON(Geometry) from rivers\n'
'order by length(Geometry) desc limit 10;\n'
'\n'
'1.15.5 Importing GeoJSON polygons using Shapely\n'
'\n'
'Another common form of polygon data is the GeoJSON format. This can be imported into '
'SpatiaLite directly, or by\n'
'using the Shapely Python library.\n'
'\n'
'Who’s On First is an excellent source of openly licensed GeoJSON polygons. Let’s import the '
'geographical polygon\n'
'for Wales. First, we can use the Who’s On First Spelunker tool to find the record for '
'Wales:\n'
'\n'
'spelunker.whosonfirst.org/id/404227475\n'
'\n'
'That page includes a link to the GeoJSON record, which can be accessed here:\n'
'\n'
'data.whosonfirst.org/404/227/475/404227475.geojson',
'type': 'text'}],
'file_id': 'file-FeX0b3otKd4tjKQ9mZm98Gyv',
'file_name': 'docs-datasette-io-en-0.59.1.pdf',
'score': 0.7665465373150298}
{ 'content': [ { 'text': '1.14 Full-text search . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . '
'. . . . . . . . . . . 51\n'
'1.14.1 The table page and table view API . . . . . . . . . . . . . . . . . . . . . . . . . . '
'. . . . . 52\n'
'1.14.2 Advanced SQLite search queries . . . . . . . . . . . . . . . . . . . . . . . . . . . '
'. . . . . 52\n'
'1.14.3 Configuring full-text search for a table or view . . . . . . . . . . . . . . . . . . '
'. . . . . . 52\n'
'1.14.4 Searches using custom SQL . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . '
'. . . . 53\n'
'1.14.5 Enabling full-text search for a SQLite table . . . . . . . . . . . . . . . . . . . . '
'. . . . . . 54\n'
'1.14.6 FTS versions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . '
'. . . . . . 55\n'
'\n'
'1.15 SpatiaLite . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . '
'. . . . . . . . . . . 55\n'
'1.15.1 Installation . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . '
'. . . . . . . 56\n'
'1.15.2 Spatial indexing latitude/longitude columns . . . . . . . . . . . . . . . . . . . . . '
'. . . . . 56\n'
'1.15.3 Making use of a spatial index . . . . . . . . . . . . . . . . . . . . . . . . . . . . '
'. . . . . . 57\n'
'1.15.4 Importing shapefiles into SpatiaLite . . . . . . . . . . . . . . . . . . . . . . . . '
'. . . . . . 57\n'
'1.15.5 Importing GeoJSON polygons using Shapely . . . . . . . . . . . . . . . . . . . . . . '
'. . . 58\n'
'1.15.6 Querying polygons using within() . . . . . . . . . . . . . . . . . . . . . . . . . . '
'. . . . . 59',
'type': 'text'}],
'file_id': 'file-FeX0b3otKd4tjKQ9mZm98Gyv',
'file_name': 'docs-datasette-io-en-0.59.1.pdf',
'score': 0.7021923478419617}
{ 'content': [ { 'text': ');\n'
'\n'
'This creates a set of tables to power full-text search against items. The new items_fts '
'table will be detected by\n'
'Datasette as the fts_table for the items table.\n'
'\n'
'Creating the table is not enough: you also need to populate it with a copy of the data that '
'you wish to make searchable.\n'
'You can do that using the following SQL:\n'
'\n'
'54 Chapter 1. Contents\n'
'\n'
'https://www.sqlite.org/fts3.html#_external_content_fts4_tables_\n'
'https://sqlite-utils.datasette.io/\n'
'https://sqlite-utils.datasette.io/en/latest/python-api.html#enabling-full-text-search\n'
'https://sqlite-utils.datasette.io/en/latest/cli.html#configuring-full-text-search\n'
'https://github.com/simonw/csvs-to-sqlite\n'
'https://sqlite-utils.datasette.io/\n'
'\n'
'\n'
'Datasette Documentation\n'
'\n'
'INSERT INTO "items_fts" (rowid, name, description)\n'
'SELECT rowid, name, description FROM items;\n'
'\n'
'If your table has columns that are foreign key references to other tables you can include '
'that data in your full-text\n'
'search index using a join. Imagine the items table has a foreign key column called '
'category_id which refers to\n'
'a categories table - you could create a full-text search table like this:\n'
'\n'
'CREATE VIRTUAL TABLE "items_fts" USING FTS4 (\n'
'name,\n'
'description,\n'
'category_name,\n'
'content="items"\n'
'\n'
');\n'
'\n'
'And then populate it like this:\n'
'\n'
'INSERT INTO "items_fts" (rowid, name, description, category_name)\n'
'SELECT items.rowid,\n'
'items.name,\n'
'items.description,\n'
'categories.name\n'
'FROM items JOIN categories ON items.category_id=categories.id;\n'
'\n'
'You can use this technique to populate the full-text search index from any combination of '
'tables and joins that makes\n'
'sense for your project.\n'
'\n'
'1.14.6 FTS versions\n'
'\n'
'There are three different versions of the SQLite FTS module: FTS3, FTS4 and FTS5. You can '
'tell which versions are\n'
'supported by your instance of Datasette by checking the /-/versions page.\n'
'\n'
'FTS5 is the most advanced module but may not be available in the SQLite version that is '
'bundled with your Python\n'
'installation. Most importantly, FTS5 is the only version that has the ability to order by '
'search relevance without\n'
'needing extra code.\n'
'\n'
'If you can’t be sure that FTS5 will be available, you should use FTS4.\n'
'\n'
'1.15 SpatiaLite\n'
'\n'
'The SpatiaLite module for SQLite adds features for handling geographic and spatial data. For '
'an example of what you\n'
'can do with it, see the tutorial Building a location to time zone API with SpatiaLite, '
'OpenStreetMap and Datasette.\n'
'\n'
'To use it with Datasette, you need to install the mod_spatialite dynamic library. This can '
'then be loaded into\n'
'Datasette using the --load-extension command-line option.\n'
'\n'
'Datasette can look for SpatiaLite in common installation locations if you run it like this:\n'
'\n'
'datasette --load-extension=spatialite\n'
'\n'
'If SpatiaLite is in another location, use the full path to the extension instead:\n'
'\n'
'datasette --load-extension=/usr/local/lib/mod_spatialite.dylib\n'
'\n'
'1.15. SpatiaLite 55\n'
'\n'
'https://www.gaia-gis.it/fossil/libspatialite/index\n'
'https://simonwillison.net/2017/Dec/12/location-time-zone-api/\n'
'\n'
'\n'
'Datasette Documentation\n'
'\n'
'1.15.1 Installation\n'
'\n'
'Installing SpatiaLite on OS X\n'
'\n'
'The easiest way to install SpatiaLite on OS X is to use Homebrew.\n'
'\n'
'brew update\n'
'brew install spatialite-tools\n'
'\n'
'This will install the spatialite command-line tool and the mod_spatialite dynamic library.\n'
'\n'
'You can now run Datasette like so:\n'
'\n'
'datasette --load-extension=spatialite',
'type': 'text'}],
'file_id': 'file-FeX0b3otKd4tjKQ9mZm98Gyv',
'file_name': 'docs-datasette-io-en-0.59.1.pdf',
'score': 0.6984300597164749}
{ 'content': [ { 'text': '• max_csv_mb - maximum size of a returned CSV file in MB. Defaults to 100MB, set to 0 to '
'disable this limit.\n'
'\n'
'Control HTTP caching with ?_ttl=\n'
'\n'
'You can now customize the HTTP max-age header that is sent on a per-URL basis, using the new '
'?_ttl= query\n'
'string parameter.\n'
'\n'
'You can set this to any value in seconds, or you can set it to 0 to disable HTTP caching '
'entirely.\n'
'\n'
'Consider for example this query which returns a randomly selected member of the Avengers:\n'
'\n'
'select * from [avengers/avengers] order by random() limit 1\n'
'\n'
'If you hit the following page repeatedly you will get the same result, due to HTTP caching:\n'
'\n'
'/fivethirtyeight?sql=select+*+from+%5Bavengers%2Favengers%5D+order+by+random%28%29+limit+1\n'
'\n'
'By adding ?_ttl=0 to the zero you can ensure the page will not be cached and get back a '
'different super hero every\n'
'time:\n'
'\n'
'/fivethirtyeight?sql=select+*+from+%5Bavengers%2Favengers%5D+order+by+random%28%29+limit+1&_ttl=0\n'
'\n'
'Improved support for SpatiaLite\n'
'\n'
'The SpatiaLite module for SQLite adds robust geospatial features to the database.\n'
'\n'
'Getting SpatiaLite working can be tricky, especially if you want to use the most recent '
'alpha version (with support for\n'
'K-nearest neighbor).\n'
'\n'
'Datasette now includes extensive documentation on SpatiaLite, and thanks to Ravi Kotecha our '
'GitHub repo includes\n'
'a Dockerfile that can build the latest SpatiaLite and configure it for use with Datasette.\n'
'\n'
'1.26. Changelog 159\n'
'\n'
'https://www.sqlite.org/pragma.html#pragma_cache_size\n'
'https://fivethirtyeight.datasettes.com/fivethirtyeight?sql=select+*+from+%5Bavengers%2Favengers%5D+order+by+random%28%29+limit+1\n'
'https://fivethirtyeight.datasettes.com/fivethirtyeight?sql=select+*+from+%5Bavengers%2Favengers%5D+order+by+random%28%29+limit+1&_ttl=0\n'
'https://www.gaia-gis.it/fossil/libspatialite/index\n'
'https://github.com/r4vi\n'
'https://github.com/simonw/datasette/blob/master/Dockerfile\n'
'\n'
'\n'
'Datasette Documentation\n'
'\n'
'The datasette publish and datasette package commands now accept a new --spatialite argument\n'
'which causes them to install and configure SpatiaLite as part of the container they deploy.\n'
'\n'
'latest.datasette.io\n'
'\n'
'Every commit to Datasette master is now automatically deployed by Travis CI to '
'https://latest.datasette.io/ - ensuring\n'
'there is always a live demo of the latest version of the software.\n'
'\n'
'The demo uses the fixtures from our unit tests, ensuring it demonstrates the same range of '
'functionality that is covered\n'
'by the tests.\n'
'\n'
'You can see how the deployment mechanism works in our .travis.yml file.\n'
'\n'
'Miscellaneous\n'
'\n'
'• Got JSON data in one of your columns? Use the new ?_json=COLNAME argument to tell '
'Datasette to return\n'
'that JSON value directly rather than encoding it as a string.\n'
'\n'
'• If you just want an array of the first value of each row, use the new ?_shape=arrayfirst '
'option - example.\n'
'\n'
'1.26.69 0.22.1 (2018-05-23)\n'
'\n'
'Bugfix release, plus we now use versioneer for our version numbers.\n'
'\n'
'• Faceting no longer breaks pagination, fixes #282',
'type': 'text'}],
'file_id': 'file-FeX0b3otKd4tjKQ9mZm98Gyv',
'file_name': 'docs-datasette-io-en-0.59.1.pdf',
'score': 0.5412825939845766}
{ 'content': [ { 'text': 'To upgrade to the most recent release of Datasette, run the following:\n'
'\n'
'docker pull datasetteproject/datasette\n'
'\n'
'Loading SpatiaLite\n'
'\n'
'The datasetteproject/datasette image includes a recent version of the SpatiaLite extension '
'for SQLite. To\n'
'load and enable that module, use the following command:\n'
'\n'
'docker run -p 8001:8001 -v `pwd`:/mnt \\\n'
'datasetteproject/datasette \\\n'
'datasette -p 8001 -h 0.0.0.0 /mnt/fixtures.db \\\n'
'--load-extension=spatialite\n'
'\n'
'You can confirm that SpatiaLite is successfully loaded by visiting '
'http://127.0.0.1:8001/-/versions\n'
'\n'
'Installing plugins\n'
'\n'
'If you want to install plugins into your local Datasette Docker image you can do so using '
'the following recipe. This\n'
'will install the plugins and then save a brand new local image called '
'datasette-with-plugins:\n'
'\n'
'docker run datasetteproject/datasette \\\n'
'pip install datasette-vega\n'
'\n'
'docker commit $(docker ps -lq) datasette-with-plugins\n'
'\n'
'You can now run the new custom image like so:\n'
'\n'
'10 Chapter 1. Contents\n'
'\n'
'https://hub.docker.com/r/datasetteproject/datasette/\n'
'https://hub.docker.com/r/datasetteproject/datasette/\n'
'https://www.docker.com/docker-mac\n'
'http://127.0.0.1:8001/\n'
'https://latest.datasette.io/fixtures.db\n'
'http://127.0.0.1:8001/-/versions\n'
'\n'
'\n'
'Datasette Documentation\n'
'\n'
'docker run -p 8001:8001 -v `pwd`:/mnt \\\n'
'datasette-with-plugins \\\n'
'datasette -p 8001 -h 0.0.0.0 /mnt/fixtures.db\n'
'\n'
'You can confirm that the plugins are installed by visiting http://127.0.0.1:8001/-/plugins\n'
'\n'
'Some plugins such as datasette-ripgrep may need additional system packages. You can install '
'these by running apt-get\n'
'install inside the container:\n'
'\n'
"docker run datasette-057a0 bash -c '\n"
'apt-get update &&\n'
'apt-get install ripgrep &&\n'
"pip install datasette-ripgrep'\n"
'\n'
'docker commit $(docker ps -lq) datasette-with-ripgrep\n'
'\n'
'1.3 The Datasette Ecosystem\n'
'\n'
'Datasette sits at the center of a growing ecosystem of open source tools aimed at making it '
'as easy as possible to\n'
'gather, analyze and publish interesting data.\n'
'\n'
'These tools are divided into two main groups: tools for building SQLite databases (for use '
'with Datasette) and plugins\n'
'that extend Datasette’s functionality.\n'
'\n'
'The Datasette project website includes a directory of plugins and a directory of tools:\n'
'\n'
'• Plugins directory on datasette.io\n'
'\n'
'• Tools directory on datasette.io\n'
'\n'
'1.3.1 sqlite-utils\n'
'\n'
'sqlite-utils is a key building block for the wider Datasette ecosystem. It provides a '
'collection of utilities for manipu-\n'
'lating SQLite databases, both as a Python library and a command-line utility. Features '
'include:\n'
'\n'
'• Insert data into a SQLite database from JSON, CSV or TSV, automatically creating tables '
'with the correct\n'
'schema or altering existing tables to add missing columns.\n'
'\n'
'• Configure tables for use with SQLite full-text search, including creating triggers needed '
'to keep the search index\n'
'up-to-date.\n'
'\n'
'• Modify tables in ways that are not supported by SQLite’s default ALTER TABLE syntax - for '
'example changing\n'
'the types of columns or selecting a new primary key for a table.\n'
'\n'
'• Adding foreign keys to existing database tables.\n'
'\n'
'• Extracting columns of data into a separate lookup table.\n'
'\n'
'1.3.2 Dogsheep',
'type': 'text'}],
'file_id': 'file-FeX0b3otKd4tjKQ9mZm98Gyv',
'file_name': 'docs-datasette-io-en-0.59.1.pdf',
'score': 0.5253422060821253}
{ 'content': [ { 'text': '}\n'
']\n'
'\n'
'$ pipx runpip datasette install -U datasette-vega\n'
'Collecting datasette-vega\n'
'Downloading datasette_vega-0.6.2-py3-none-any.whl (1.8 MB)\n'
'\n'
'|| 1.8 MB 2.0 MB/s\n'
'...\n'
'Installing collected packages: datasette-vega\n'
'Attempting uninstall: datasette-vega\n'
'\n'
'Found existing installation: datasette-vega 0.6\n'
'Uninstalling datasette-vega-0.6:\n'
'Successfully uninstalled datasette-vega-0.6\n'
'\n'
'Successfully installed datasette-vega-0.6.2\n'
'\n'
'$ datasette plugins\n'
'[\n'
'\n'
'{\n'
'"name": "datasette-vega",\n'
'"static": true,\n'
'\n'
'(continues on next page)\n'
'\n'
'1.2. Installation 9\n'
'\n'
'\n'
'\n'
'Datasette Documentation\n'
'\n'
'(continued from previous page)\n'
'\n'
'"templates": false,\n'
'"version": "0.6.2"\n'
'\n'
'}\n'
']\n'
'\n'
'Using Docker\n'
'\n'
'A Docker image containing the latest release of Datasette is published to Docker Hub here: '
'https://hub.docker.com/r/\n'
'datasetteproject/datasette/\n'
'\n'
'If you have Docker installed (for example with Docker for Mac on OS X) you can download and '
'run this image like\n'
'so:\n'
'\n'
'docker run -p 8001:8001 -v `pwd`:/mnt \\\n'
'datasetteproject/datasette \\\n'
'datasette -p 8001 -h 0.0.0.0 /mnt/fixtures.db\n'
'\n'
'This will start an instance of Datasette running on your machine’s port 8001, serving the '
'fixtures.db file in your\n'
'current directory.\n'
'\n'
'Now visit http://127.0.0.1:8001/ to access Datasette.\n'
'\n'
'(You can download a copy of fixtures.db from https://latest.datasette.io/fixtures.db )\n'
'\n'
'To upgrade to the most recent release of Datasette, run the following:\n'
'\n'
'docker pull datasetteproject/datasette\n'
'\n'
'Loading SpatiaLite\n'
'\n'
'The datasetteproject/datasette image includes a recent version of the SpatiaLite extension '
'for SQLite. To\n'
'load and enable that module, use the following command:\n'
'\n'
'docker run -p 8001:8001 -v `pwd`:/mnt \\\n'
'datasetteproject/datasette \\\n'
'datasette -p 8001 -h 0.0.0.0 /mnt/fixtures.db \\\n'
'--load-extension=spatialite\n'
'\n'
'You can confirm that SpatiaLite is successfully loaded by visiting '
'http://127.0.0.1:8001/-/versions\n'
'\n'
'Installing plugins\n'
'\n'
'If you want to install plugins into your local Datasette Docker image you can do so using '
'the following recipe. This\n'
'will install the plugins and then save a brand new local image called '
'datasette-with-plugins:\n'
'\n'
'docker run datasetteproject/datasette \\\n'
'pip install datasette-vega\n'
'\n'
'docker commit $(docker ps -lq) datasette-with-plugins\n'
'\n'
'You can now run the new custom image like so:\n'
'\n'
'10 Chapter 1. Contents\n'
'\n'
'https://hub.docker.com/r/datasetteproject/datasette/\n'
'https://hub.docker.com/r/datasetteproject/datasette/\n'
'https://www.docker.com/docker-mac\n'
'http://127.0.0.1:8001/\n'
'https://latest.datasette.io/fixtures.db\n'
'http://127.0.0.1:8001/-/versions\n'
'\n'
'\n'
'Datasette Documentation\n'
'\n'
'docker run -p 8001:8001 -v `pwd`:/mnt \\\n'
'datasette-with-plugins \\\n'
'datasette -p 8001 -h 0.0.0.0 /mnt/fixtures.db\n'
'\n'
'You can confirm that the plugins are installed by visiting http://127.0.0.1:8001/-/plugins',
'type': 'text'}],
'file_id': 'file-FeX0b3otKd4tjKQ9mZm98Gyv',
'file_name': 'docs-datasette-io-en-0.59.1.pdf',
'score': 0.5171347566024148}
{ 'content': [ { 'text': 'Datasette Documentation\n'
'\n'
'The datasette publish and datasette package commands now accept a new --spatialite argument\n'
'which causes them to install and configure SpatiaLite as part of the container they deploy.\n'
'\n'
'latest.datasette.io\n'
'\n'
'Every commit to Datasette master is now automatically deployed by Travis CI to '
'https://latest.datasette.io/ - ensuring\n'
'there is always a live demo of the latest version of the software.\n'
'\n'
'The demo uses the fixtures from our unit tests, ensuring it demonstrates the same range of '
'functionality that is covered\n'
'by the tests.\n'
'\n'
'You can see how the deployment mechanism works in our .travis.yml file.\n'
'\n'
'Miscellaneous\n'
'\n'
'• Got JSON data in one of your columns? Use the new ?_json=COLNAME argument to tell '
'Datasette to return\n'
'that JSON value directly rather than encoding it as a string.\n'
'\n'
'• If you just want an array of the first value of each row, use the new ?_shape=arrayfirst '
'option - example.\n'
'\n'
'1.26.69 0.22.1 (2018-05-23)\n'
'\n'
'Bugfix release, plus we now use versioneer for our version numbers.\n'
'\n'
'• Faceting no longer breaks pagination, fixes #282\n'
'\n'
'• Add __version_info__ derived from __version__ [Robert Gieseke]\n'
'\n'
'This might be tuple of more than two values (major and minor version) if commits have been '
'made after a\n'
'release.\n'
'\n'
'• Add version number support with Versioneer. [Robert Gieseke]\n'
'\n'
'Versioneer Licence: Public Domain (CC0-1.0)\n'
'\n'
'Closes #273\n'
'\n'
'• Refactor inspect logic [Russ Garrett]\n'
'\n'
'1.26.70 0.22 (2018-05-20)\n'
'\n'
'The big new feature in this release is Facets. Datasette can now apply faceted browse to any '
'column in any table. It\n'
'will also suggest possible facets. See the Datasette Facets announcement post for more '
'details.\n'
'\n'
'In addition to the work on facets:\n'
'\n'
'• Added docs for introspection endpoints\n'
'\n'
'• New --config option, added --help-config, closes #274\n'
'\n'
'Removed the --page_size= argument to datasette serve in favour of:\n'
'\n'
'datasette serve --config default_page_size:50 mydb.db\n'
'\n'
'Added new help section:\n'
'\n'
'160 Chapter 1. Contents\n'
'\n'
'https://latest.datasette.io/\n'
'https://github.com/simonw/datasette/blob/master/tests/fixtures.py\n'
'https://github.com/simonw/datasette/blob/master/.travis.yml\n'
'https://latest.datasette.io/fixtures.json?sql=select+neighborhood+from+facetable+order+by+pk+limit+101&_shape=arrayfirst\n'
'https://github.com/warner/python-versioneer\n'
'https://github.com/simonw/datasette/issues/282\n'
'https://github.com/simonw/datasette/issues/273\n'
'https://simonwillison.net/2018/May/20/datasette-facets/\n'
'https://docs.datasette.io/en/stable/introspection.html\n'
'https://github.com/simonw/datasette/issues/274\n'
'\n'
'\n'
'Datasette Documentation\n'
'\n'
'$ datasette --help-config\n'
'Config options:\n'
'default_page_size Default page size for the table view\n'
'\n'
'(default=100)\n'
'max_returned_rows Maximum rows that can be returned from a table\n'
'\n'
'or custom query (default=1000)\n'
'sql_time_limit_ms Time limit for a SQL query in milliseconds\n'
'\n'
'(default=1000)\n'
'default_facet_size Number of values to return for requested facets\n'
'\n'
'(default=30)\n'
'facet_time_limit_ms Time limit for calculating a requested facet\n'
'\n'
'(default=200)\n'
'facet_suggest_time_limit_ms Time limit for calculating a suggested facet\n'
'\n'
'(default=50)\n'
'\n'
'• Only apply responsive table styles to .rows-and-column',
'type': 'text'}],
'file_id': 'file-FeX0b3otKd4tjKQ9mZm98Gyv',
'file_name': 'docs-datasette-io-en-0.59.1.pdf',
'score': 0.5041726684235329}
{ 'content': [ { 'text': 'Datasette Documentation\n'
'\n'
'Foreign key expansions\n'
'\n'
'When Datasette detects a foreign key reference it attempts to resolve a label for that '
'reference (automatically or using\n'
'the Specifying the label column for a table metadata option) so it can display a link to the '
'associated row.\n'
'\n'
'This expansion is now also available for JSON and CSV representations of the table, using '
'the new _labels=on\n'
'query string option. See Expanding foreign key references for more details.\n'
'\n'
'New configuration settings\n'
'\n'
'Datasette’s Settings now also supports boolean settings. A number of new configuration '
'options have been added:\n'
'\n'
'• num_sql_threads - the number of threads used to execute SQLite queries. Defaults to 3.\n'
'\n'
'• allow_facet - enable or disable custom Facets using the _facet= parameter. Defaults to '
'on.\n'
'\n'
'• suggest_facets - should Datasette suggest facets? Defaults to on.\n'
'\n'
'• allow_download - should users be allowed to download the entire SQLite database? Defaults '
'to on.\n'
'\n'
'• allow_sql - should users be allowed to execute custom SQL queries? Defaults to on.\n'
'\n'
'• default_cache_ttl - Default HTTP caching max-age header in seconds. Defaults to 365 days - '
'caching\n'
'can be disabled entirely by settings this to 0.\n'
'\n'
'• cache_size_kb - Set the amount of memory SQLite uses for its per-connection cache, in KB.\n'
'\n'
'• allow_csv_stream - allow users to stream entire result sets as a single CSV file. Defaults '
'to on.\n'
'\n'
'• max_csv_mb - maximum size of a returned CSV file in MB. Defaults to 100MB, set to 0 to '
'disable this limit.\n'
'\n'
'Control HTTP caching with ?_ttl=\n'
'\n'
'You can now customize the HTTP max-age header that is sent on a per-URL basis, using the new '
'?_ttl= query\n'
'string parameter.\n'
'\n'
'You can set this to any value in seconds, or you can set it to 0 to disable HTTP caching '
'entirely.\n'
'\n'
'Consider for example this query which returns a randomly selected member of the Avengers:\n'
'\n'
'select * from [avengers/avengers] order by random() limit 1\n'
'\n'
'If you hit the following page repeatedly you will get the same result, due to HTTP caching:\n'
'\n'
'/fivethirtyeight?sql=select+*+from+%5Bavengers%2Favengers%5D+order+by+random%28%29+limit+1\n'
'\n'
'By adding ?_ttl=0 to the zero you can ensure the page will not be cached and get back a '
'different super hero every\n'
'time:\n'
'\n'
'/fivethirtyeight?sql=select+*+from+%5Bavengers%2Favengers%5D+order+by+random%28%29+limit+1&_ttl=0\n'
'\n'
'Improved support for SpatiaLite\n'
'\n'
'The SpatiaLite module for SQLite adds robust geospatial features to the database.\n'
'\n'
'Getting SpatiaLite working can be tricky, especially if you want to use the most recent '
'alpha version (with support for\n'
'K-nearest neighbor).\n'
'\n'
'Datasette now includes extensive documentation on SpatiaLite, and thanks to Ravi Kotecha our '
'GitHub repo includes\n'
'a Dockerfile that can build the latest SpatiaLite and configure it for use with Datasette.\n'
'\n'
'1.26. Changelog 159',
'type': 'text'}],
'file_id': 'file-FeX0b3otKd4tjKQ9mZm98Gyv',
'file_name': 'docs-datasette-io-en-0.59.1.pdf',
'score': 0.48521595380373556}
{ 'content': [ { 'text': 'Datasette Documentation\n'
'\n'
'(continued from previous page)\n'
'\n'
'"spatialite": "4.3.0a"\n'
'}\n'
'\n'
'}\n'
'}\n'
'\n'
'• Renamed ?_sql_time_limit_ms= to ?_timelimit, closes #242\n'
'\n'
'• New ?_shape=array option + tweaks to _shape, closes #245\n'
'\n'
'– Default is now ?_shape=arrays (renamed from lists)\n'
'\n'
'– New ?_shape=array returns an array of objects as the root object\n'
'\n'
'– Changed ?_shape=object to return the object as the root\n'
'\n'
'– Updated docs\n'
'\n'
'• FTS tables now detected by inspect(), closes #240\n'
'\n'
'• New ?_size=XXX query string parameter for table view, closes #229\n'
'\n'
'Also added documentation for all of the _special arguments.\n'
'\n'
'Plus deleted some duplicate logic implementing _group_count.\n'
'\n'
'• If max_returned_rows==page_size, increment max_returned_rows - fixes #230\n'
'\n'
'• New hidden: True option for table metadata, closes #239\n'
'\n'
'• Hide idx_* tables if spatialite detected, closes #228\n'
'\n'
'• Added class=rows-and-columns to custom query results table\n'
'\n'
'• Added CSS class rows-and-columns to main table\n'
'\n'
'• label_column option in metadata.json - closes #234\n'
'\n'
'1.26.72 0.20 (2018-04-20)\n'
'\n'
'Mostly new work on the Plugins mechanism: plugins can now bundle static assets and custom '
'templates, and\n'
'datasette publish has a new --install=name-of-plugin option.\n'
'\n'
'• Add col-X classes to HTML table on custom query page\n'
'\n'
'• Fixed out-dated template in documentation\n'
'\n'
'• Plugins can now bundle custom templates, #224\n'
'\n'
'• Added /-/metadata /-/plugins /-/inspect, #225\n'
'\n'
'• Documentation for –install option, refs #223\n'
'\n'
'• Datasette publish/package –install option, #223\n'
'\n'
'• Fix for plugins in Python 3.5, #222\n'
'\n'
'• New plugin hooks: extra_css_urls() and extra_js_urls(), #214\n'
'\n'
'• /-/static-plugins/PLUGIN_NAME/ now serves static/ from plugins\n'
'\n'
'• <th> now gets class="col-X" - plus added col-X documentation\n'
'\n'
'• Use to_css_class for table cell column classes\n'
'\n'
'This ensures that columns with spaces in the name will still generate usable CSS class '
'names. Refs #209\n'
'\n'
'162 Chapter 1. Contents\n'
'\n'
'https://github.com/simonw/datasette/issues/242\n'
'https://github.com/simonw/datasette/issues/245\n'
'https://github.com/simonw/datasette/issues/240\n'
'https://github.com/simonw/datasette/issues/229\n'
'https://github.com/simonw/datasette/issues/230\n'
'https://github.com/simonw/datasette/issues/239\n'
'https://github.com/simonw/datasette/issues/228\n'
'https://github.com/simonw/datasette/issues/234\n'
'https://github.com/simonw/datasette/issues/224\n'
'https://github.com/simonw/datasette/issues/225\n'
'https://github.com/simonw/datasette/issues/223\n'
'https://github.com/simonw/datasette/issues/223\n'
'https://github.com/simonw/datasette/issues/222\n'
'https://github.com/simonw/datasette/issues/214\n'
'https://github.com/simonw/datasette/issues/209\n'
'\n'
'\n'
'Datasette Documentation\n'
'\n'
'• Add column name classes to <td>s, make PK bold [Russ Garrett]\n'
'\n'
'• Don’t duplicate simple primary keys in the link column [Russ Garrett]\n'
'\n'
'When there’s a simple (single-column) primary key, it looks weird to duplicate it in the '
'link column.\n'
'\n'
'This change removes the second PK column and treats the link column as if it were the PK '
'column from a\n'
'header/sorting perspective.\n'
'\n'
'• Correct escaping for HTML display of row links [Russ Garrett]',
'type': 'text'}],
'file_id': 'file-FeX0b3otKd4tjKQ9mZm98Gyv',
'file_name': 'docs-datasette-io-en-0.59.1.pdf',
'score': 0.4720704537426566}
{ 'content': [ { 'text': '• Refactored views into new views/ modules, refs #256\n'
'\n'
'• Documentation for SQLite full-text search support, closes #253\n'
'\n'
'• /-/versions now includes SQLite fts_versions, closes #252\n'
'\n'
'1.26.71 0.21 (2018-05-05)\n'
'\n'
'New JSON _shape= options, the ability to set table _size= and a mechanism for searching '
'within specific columns.\n'
'\n'
'• Default tests to using a longer timelimit\n'
'\n'
'Every now and then a test will fail in Travis CI on Python 3.5 because it hit the default '
'20ms SQL time limit.\n'
'\n'
'Test fixtures now default to a 200ms time limit, and we only use the 20ms time limit for the '
'specific test that\n'
'tests query interruption. This should make our tests on Python 3.5 in Travis much more '
'stable.\n'
'\n'
'• Support _search_COLUMN=text searches, closes #237\n'
'\n'
'• Show version on /-/plugins page, closes #248\n'
'\n'
'• ?_size=max option, closes #249\n'
'\n'
'• Added /-/versions and /-/versions.json, closes #244\n'
'\n'
'Sample output:\n'
'\n'
'{\n'
'"python": {\n'
'\n'
'"version": "3.6.3",\n'
'"full": "3.6.3 (default, Oct 4 2017, 06:09:38) \\n[GCC 4.2.1 Compatible Apple\n'
'\n'
'→˓LLVM 9.0.0 (clang-900.0.37)]"\n'
'},\n'
'"datasette": {\n'
'\n'
'"version": "0.20"\n'
'},\n'
'"sqlite": {\n'
'\n'
'"version": "3.23.1",\n'
'"extensions": {\n'
'"json1": null,\n'
'\n'
'(continues on next page)\n'
'\n'
'1.26. Changelog 161\n'
'\n'
'https://fivethirtyeight.datasettes.com/fivethirtyeight/nba-elo%2Fnbaallelo\n'
'https://fivethirtyeight.datasettes.com/fivethirtyeight/nba-elo%2Fnbaallelo\n'
'https://github.com/simonw/datasette/issues/256\n'
'https://docs.datasette.io/en/stable/full_text_search.html\n'
'https://github.com/simonw/datasette/issues/253\n'
'https://github.com/simonw/datasette/issues/252\n'
'https://github.com/simonw/datasette/issues/237\n'
'https://github.com/simonw/datasette/issues/248\n'
'https://github.com/simonw/datasette/issues/249\n'
'https://github.com/simonw/datasette/issues/244\n'
'\n'
'\n'
'Datasette Documentation\n'
'\n'
'(continued from previous page)\n'
'\n'
'"spatialite": "4.3.0a"\n'
'}\n'
'\n'
'}\n'
'}\n'
'\n'
'• Renamed ?_sql_time_limit_ms= to ?_timelimit, closes #242\n'
'\n'
'• New ?_shape=array option + tweaks to _shape, closes #245\n'
'\n'
'– Default is now ?_shape=arrays (renamed from lists)\n'
'\n'
'– New ?_shape=array returns an array of objects as the root object\n'
'\n'
'– Changed ?_shape=object to return the object as the root\n'
'\n'
'– Updated docs\n'
'\n'
'• FTS tables now detected by inspect(), closes #240\n'
'\n'
'• New ?_size=XXX query string parameter for table view, closes #229\n'
'\n'
'Also added documentation for all of the _special arguments.\n'
'\n'
'Plus deleted some duplicate logic implementing _group_count.\n'
'\n'
'• If max_returned_rows==page_size, increment max_returned_rows - fixes #230\n'
'\n'
'• New hidden: True option for table metadata, closes #239\n'
'\n'
'• Hide idx_* tables if spatialite detected, closes #228\n'
'\n'
'• Added class=rows-and-columns to custom query results table\n'
'\n'
'• Added CSS class rows-and-columns to main table\n'
'\n'
'• label_column option in metadata.json - closes #234',
'type': 'text'}],
'file_id': 'file-FeX0b3otKd4tjKQ9mZm98Gyv',
'file_name': 'docs-datasette-io-en-0.59.1.pdf',
'score': 0.4474298676134362}
{ 'content': [ { 'text': 'If you want to start making contributions to the Datasette project by installing a copy that '
'lets you directly modify the\n'
'code, take a look at our guide to Setting up a development environment.\n'
'\n'
'• Basic installation\n'
'\n'
'– Using Homebrew\n'
'\n'
'– Using pip\n'
'\n'
'• Advanced installation options\n'
'\n'
'– Using pipx\n'
'\n'
'* Installing plugins using pipx\n'
'\n'
'* Upgrading packages using pipx\n'
'\n'
'– Using Docker\n'
'\n'
'* Loading SpatiaLite\n'
'\n'
'* Installing plugins\n'
'\n'
'1.2.1 Basic installation\n'
'\n'
'Using Homebrew\n'
'\n'
'If you have a Mac and use Homebrew, you can install Datasette by running this command in '
'your terminal:\n'
'\n'
'brew install datasette\n'
'\n'
'This should install the latest version. You can confirm by running:\n'
'\n'
'1.2. Installation 7\n'
'\n'
'https://brew.sh/\n'
'\n'
'\n'
'Datasette Documentation\n'
'\n'
'datasette --version\n'
'\n'
'You can upgrade to the latest Homebrew packaged version using:\n'
'\n'
'brew upgrade datasette\n'
'\n'
'Once you have installed Datasette you can install plugins using the following:\n'
'\n'
'datasette install datasette-vega\n'
'\n'
'If the latest packaged release of Datasette has not yet been made available through '
'Homebrew, you can upgrade your\n'
'Homebrew installation in-place using:\n'
'\n'
'datasette install -U datasette\n'
'\n'
'Using pip\n'
'\n'
'Datasette requires Python 3.6 or higher. Visit InstallPython3.com for step-by-step '
'installation guides for your operating\n'
'system.\n'
'\n'
'You can install Datasette and its dependencies using pip:\n'
'\n'
'pip install datasette\n'
'\n'
'You can now run Datasette like so:\n'
'\n'
'datasette\n'
'\n'
'1.2.2 Advanced installation options\n'
'\n'
'Using pipx\n'
'\n'
'pipx is a tool for installing Python software with all of its dependencies in an isolated '
'environment, to ensure that they\n'
'will not conflict with any other installed Python software.\n'
'\n'
'If you use Homebrew on macOS you can install pipx like this:\n'
'\n'
'brew install pipx\n'
'pipx ensurepath\n'
'\n'
'Without Homebrew you can install it like so:\n'
'\n'
'python3 -m pip install --user pipx\n'
'python3 -m pipx ensurepath\n'
'\n'
'The pipx ensurepath command configures your shell to ensure it can find commands that have '
'been installed by\n'
'pipx - generally by making sure ~/.local/bin has been added to your PATH.\n'
'\n'
'Once pipx is installed you can use it to install Datasette like this:\n'
'\n'
'pipx install datasette\n'
'\n'
'Then run datasette --version to confirm that it has been successfully installed.\n'
'\n'
'8 Chapter 1. Contents\n'
'\n'
'https://installpython3.com/\n'
'https://pipxproject.github.io/pipx/\n'
'https://brew.sh/\n'
'\n'
'\n'
'Datasette Documentation\n'
'\n'
'Installing plugins using pipx\n'
'\n'
'You can install additional datasette plugins with pipx inject like so:\n'
'\n'
'$ pipx inject datasette datasette-json-html\n'
'injected package datasette-json-html into venv datasette\n'
'done!\n'
'\n'
'$ datasette plugins\n'
'[\n'
'\n'
'{\n'
'"name": "datasette-json-html",\n'
'"static": false,\n'
'"templates": false,\n'
'"version": "0.6"\n'
'\n'
'}\n'
']\n'
'\n'
'Upgrading packages using pipx\n'
'\n'
'You can upgrade your pipx installation to the latest release of Datasette using pipx upgrade '
'datasette:\n'
'\n'
'$ pipx upgrade datasette\n'
'upgraded package datasette from 0.39 to 0.40 (location: /Users/simon/.local/pipx/\n'
'→˓venvs/datasette)\n'
'\n'
'To upgrade a plugin within the pipx environment use pipx runpip datasette install -U\n'
'name-of-plugin - like this:\n'
'\n'
'% datasette plugins\n'
'[\n'
'\n'
'{\n'
'"name": "datasette-vega",\n'
'"static": true,\n'
'"templates": false,\n'
'"version": "0.6"\n'
'\n'
'}\n'
']',
'type': 'text'}],
'file_id': 'file-FeX0b3otKd4tjKQ9mZm98Gyv',
'file_name': 'docs-datasette-io-en-0.59.1.pdf',
'score': 0.4446240932775432}
{ 'content': [ { 'text': '(continues on next page)\n'
'\n'
'58 Chapter 1. Contents\n'
'\n'
'https://www.gaia-gis.it/gaia-sins/BLOB-Geometry.html\n'
'https://geojson.io/\n'
'https://pypi.org/project/Shapely/\n'
'https://whosonfirst.org/\n'
'https://spelunker.whosonfirst.org/id/404227475/\n'
'https://data.whosonfirst.org/404/227/475/404227475.geojson\n'
'\n'
'\n'
'Datasette Documentation\n'
'\n'
'(continued from previous page)\n'
'\n'
'conn.execute("SELECT CreateSpatialIndex(\'places\', \'geom\');")\n'
'# Now populate the table\n'
'from shapely.geometry.multipolygon import MultiPolygon\n'
'from shapely.geometry import shape\n'
'import requests\n'
"geojson = requests.get('https://data.whosonfirst.org/404/227/475/404227475.geojson').\n"
'→˓json()\n'
'# Convert to "Well Known Text" format\n'
"wkt = shape(geojson['geometry']).wkt\n"
'# Insert and commit the record\n'
'conn.execute("INSERT INTO places (id, name, geom) VALUES(null, ?, GeomFromText(?,\n'
'→˓4326))", (\n'
'\n'
'"Wales", wkt\n'
'))\n'
'conn.commit()\n'
'\n'
'1.15.6 Querying polygons using within()\n'
'\n'
'The within() SQL function can be used to check if a point is within a geometry:\n'
'\n'
'select\n'
'name\n'
'\n'
'from\n'
'places\n'
'\n'
'where\n'
"within(GeomFromText('POINT(-3.1724366 51.4704448)'), places.geom);\n"
'\n'
'The GeomFromText() function takes a string of well-known text. Note that the order used here '
'is longitude\n'
'then latitude.\n'
'\n'
'To run that same within() query in a way that benefits from the spatial index, use the '
'following:\n'
'\n'
'select\n'
'name\n'
'\n'
'from\n'
'places\n'
'\n'
'where\n'
"within(GeomFromText('POINT(-3.1724366 51.4704448)'), places.geom)\n"
'and rowid in (\n'
'\n'
'SELECT pkid FROM idx_places_geom\n'
'where xmin < -3.1724366\n'
'and xmax > -3.1724366\n'
'and ymin < 51.4704448\n'
'and ymax > 51.4704448\n'
'\n'
');\n'
'\n'
'1.16 Metadata\n'
'\n'
'Data loves metadata. Any time you run Datasette you can optionally include a JSON file with '
'metadata about your\n'
'databases and tables. Datasette will then display that information in the web UI.\n'
'\n'
'Run Datasette like this:\n'
'\n'
'1.16. Metadata 59\n'
'\n'
'\n'
'\n'
'Datasette Documentation\n'
'\n'
'datasette database1.db database2.db --metadata metadata.json\n'
'\n'
'Your metadata.json file can look something like this:\n'
'\n'
'{\n'
'"title": "Custom title for your index page",\n'
'"description": "Some description text can go here",\n'
'"license": "ODbL",\n'
'"license_url": "https://opendatacommons.org/licenses/odbl/",\n'
'"source": "Original Data Source",\n'
'"source_url": "http://example.com/"\n'
'\n'
'}\n'
'\n'
'You can optionally use YAML instead of JSON, see Using YAML for metadata.\n'
'\n'
'The above metadata will be displayed on the index page of your Datasette-powered site. The '
'source and license\n'
'information will also be included in the footer of every page served by Datasette.\n'
'\n'
'Any special HTML characters in description will be escaped. If you want to include HTML in '
'your description,\n'
'you can use a description_html property instead.\n'
'\n'
'1.16.1 Per-database and per-table metadata\n'
'\n'
'Metadata at the top level of the JSON will be shown on the index page and in the footer on '
'every page of the site. The\n'
'license and source is expected to apply to all of your data.\n'
'\n'
'You can also provide metadata at the per-database or per-table level, like this:\n'
'\n'
'{\n'
'"databases": {',
'type': 'text'}],
'file_id': 'file-FeX0b3otKd4tjKQ9mZm98Gyv',
'file_name': 'docs-datasette-io-en-0.59.1.pdf',
'score': 0.44160484246673626}
{ 'content': [ { 'text': 'Datasette Documentation\n'
'\n'
'1.26.8 0.56 (2021-03-28)\n'
'\n'
'Documentation improvements, bug fixes and support for SpatiaLite 5.\n'
'\n'
'• The SQL editor can now be resized by dragging a handle. (#1236)\n'
'\n'
'• Fixed a bug with JSON faceting and the __arraycontains filter caused by tables with spaces '
'in their names.\n'
'(#1239)\n'
'\n'
'• Upgraded httpx dependency. (#1005)\n'
'\n'
'• JSON faceting is now suggested even if a column contains blank strings. (#1246)\n'
'\n'
'• New datasette.add_memory_database() method. (#1247)\n'
'\n'
'• The Response.asgi_send() method is now documented. (#1266)\n'
'\n'
'• The official Datasette Docker image now bundles SpatiaLite version 5. (#1278)\n'
'\n'
'• Fixed a no such table: pragma_database_list bug when running Datasette against SQLite '
'ver-\n'
'sions prior to SQLite 3.16.0. (#1276)\n'
'\n'
'• HTML lists displayed in table cells are now styled correctly. Thanks, Bob Whitelock. '
'(#1141, #1252)\n'
'\n'
'• Configuration directory mode now correctly serves immutable databases that are listed in '
'inspect-data.\n'
'json. Thanks Campbell Allen and Frankie Robertson. (#1031, #1229)\n'
'\n'
'1.26.9 0.55 (2021-02-18)\n'
'\n'
'Support for cross-database SQL queries and built-in support for serving via HTTPS.\n'
'\n'
'• The new --crossdb command-line option causes Datasette to attach up to ten database files '
'to the same /\n'
'_memory database connection. This enables cross-database SQL queries, including the ability '
'to use joins and\n'
'unions to combine data from tables that exist in different database files. See '
'Cross-database queries for details.\n'
'(#283)\n'
'\n'
'• --ssl-keyfile and --ssl-certfile options can be used to specify a TLS certificate, '
'allowing\n'
'Datasette to serve traffic over https:// without needing to run it behind a separate proxy. '
'(#1221)\n'
'\n'
'• The /:memory: page has been renamed (and redirected) to /_memory for consistency with the '
'new /\n'
'_internal database introduced in Datasette 0.54. (#1205)\n'
'\n'
'• Added plugin testing documentation on Using pdb for errors thrown inside Datasette. '
'(#1207)\n'
'\n'
'• The official Datasette Docker image now uses Python 3.7.10, applying the latest security '
'fix for that Python\n'
'version. (#1235)\n'
'\n'
'1.26.10 0.54.1 (2021-02-02)\n'
'\n'
'• Fixed a bug where ?_search= and ?_sort= parameters were incorrectly duplicated when the '
'filter form on\n'
'the table page was re-submitted. (#1214)\n'
'\n'
'1.26.11 0.54 (2021-01-25)\n'
'\n'
'The two big new features in this release are the _internal SQLite in-memory database storing '
'details of all con-\n'
'nected databases and tables, and support for JavaScript modules in plugins and additional '
'scripts.\n'
'\n'
'For additional commentary on this release, see Datasette 0.54, the annotated release notes.\n'
'\n'
'1.26. Changelog 133',
'type': 'text'}],
'file_id': 'file-FeX0b3otKd4tjKQ9mZm98Gyv',
'file_name': 'docs-datasette-io-en-0.59.1.pdf',
'score': 0.4411633528921766}
{ 'content': [ { 'text': '• ?_facet_size=max sets that to the maximum, which defaults to 1,000 and is controlled by '
'the the\n'
'max_returned_rows setting. If facet results are truncated the . . . at the bottom of the '
'facet list now links to\n'
'this parameter. (#1337)\n'
'\n'
'• ?_nofacet=1 option to disable all facet calculations on a page, used as a performance '
'optimization for CSV\n'
'exports and ?_shape=array/object. (#1349, #263)\n'
'\n'
'• ?_nocount=1 option to disable full query result counts. (#1353)\n'
'\n'
'• ?_trace=1 debugging option is now controlled by the new trace_debug setting, which is '
'turned off by default.\n'
'(#1359)\n'
'\n'
'Bug fixes and other improvements\n'
'\n'
'• Custom pages now work correctly when combined with the base_url setting. (#1238)\n'
'\n'
'• Fixed intermittent error displaying the index page when the user did not have permission '
'to access one of the\n'
'tables. Thanks, Guy Freeman. (#1305)\n'
'\n'
'• Columns with the name "Link" are no longer incorrectly displayed in bold. (#1308)\n'
'\n'
'• Fixed error caused by tables with a single quote in their names. (#1257)\n'
'\n'
'• Updated dependencies: pytest-asyncio, Black, jinja2, aiofiles, click, and itsdangerous.\n'
'\n'
'• The official Datasette Docker image now supports apt-get install. (#1320)\n'
'\n'
'• The Heroku runtime used by datasette publish heroku is now python-3.8.10.\n'
'\n'
'1.26.7 0.56.1 (2021-06-05)\n'
'\n'
'Warning: This release fixes a reflected cross-site scripting security hole with the '
'?_trace=1 feature. You\n'
'should upgrade to this version, or to Datasette 0.57, as soon as possible. (#1360)\n'
'\n'
'132 Chapter 1. Contents\n'
'\n'
'https://owasp.org/www-community/attacks/xss/#reflected-xss-attacks\n'
'https://github.com/simonw/datasette/issues/1360\n'
'https://github.com/simonw/datasette/issues/619\n'
'https://github.com/simonw/datasette/issues/615\n'
'https://github.com/simonw/datasette/issues/1332\n'
'https://github.com/simonw/datasette/issues/1337\n'
'https://github.com/simonw/datasette/issues/1349\n'
'https://github.com/simonw/datasette/issues/263\n'
'https://github.com/simonw/datasette/issues/1353\n'
'https://github.com/simonw/datasette/issues/1359\n'
'https://github.com/simonw/datasette/issues/1238\n'
'https://github.com/simonw/datasette/issues/1305\n'
'https://github.com/simonw/datasette/issues/1308\n'
'https://github.com/simonw/datasette/issues/1257\n'
'https://github.com/simonw/datasette/issues/1320\n'
'https://owasp.org/www-community/attacks/xss/#reflected-xss-attacks\n'
'https://github.com/simonw/datasette/issues/1360\n'
'\n'
'\n'
'Datasette Documentation\n'
'\n'
'1.26.8 0.56 (2021-03-28)\n'
'\n'
'Documentation improvements, bug fixes and support for SpatiaLite 5.\n'
'\n'
'• The SQL editor can now be resized by dragging a handle. (#1236)\n'
'\n'
'• Fixed a bug with JSON faceting and the __arraycontains filter caused by tables with spaces '
'in their names.\n'
'(#1239)\n'
'\n'
'• Upgraded httpx dependency. (#1005)\n'
'\n'
'• JSON faceting is now suggested even if a column contains blank strings. (#1246)\n'
'\n'
'• New datasette.add_memory_database() method. (#1247)\n'
'\n'
'• The Response.asgi_send() method is now documented. (#1266)',
'type': 'text'}],
'file_id': 'file-FeX0b3otKd4tjKQ9mZm98Gyv',
'file_name': 'docs-datasette-io-en-0.59.1.pdf',
'score': 0.42098669734071165}
{ 'content': [ { 'text': 'Minor bugfix and documentation release.\n'
'\n'
'• CSV export now respects --cors, fixes #326\n'
'\n'
'• Installation instructions, including docker image - closes #328\n'
'\n'
'• Fix for row pages for tables with / in, closes #325\n'
'\n'
'1.26.67 0.23.1 (2018-06-21)\n'
'\n'
'Minor bugfix release.\n'
'\n'
'• Correctly display empty strings in HTML table, closes #314\n'
'\n'
'• Allow "." in database filenames, closes #302\n'
'\n'
'• 404s ending in slash redirect to remove that slash, closes #309\n'
'\n'
'• Fixed incorrect display of compound primary keys with foreign key references. Closes #319\n'
'\n'
'• Docs + example of canned SQL query using || concatenation. Closes #321\n'
'\n'
'• Correctly display facets with value of 0 - closes #318\n'
'\n'
'• Default ’expand labels’ to checked in CSV advanced export\n'
'\n'
'1.26.68 0.23 (2018-06-18)\n'
'\n'
'This release features CSV export, improved options for foreign key expansions, new '
'configuration settings and im-\n'
'proved support for SpatiaLite.\n'
'\n'
'See datasette/compare/0.22.1...0.23 for a full list of commits added since the last '
'release.\n'
'\n'
'CSV export\n'
'\n'
'Any Datasette table, view or custom SQL query can now be exported as CSV.\n'
'\n'
'Check out the CSV export documentation for more details, or try the feature out on '
'https://fivethirtyeight.datasettes.\n'
'com/fivethirtyeight/bechdel%2Fmovies\n'
'\n'
'If your table has more than max_returned_rows (default 1,000) Datasette provides the option '
'to stream all rows. This\n'
'option takes advantage of async Python and Datasette’s efficient pagination to iterate '
'through the entire matching\n'
'result set and stream it back as a downloadable CSV file.\n'
'\n'
'158 Chapter 1. Contents\n'
'\n'
'https://github.com/simonw/datasette/issues/326\n'
'https://github.com/simonw/datasette/issues/328\n'
'https://github.com/simonw/datasette/issues/325\n'
'https://github.com/simonw/datasette/issues/314\n'
'https://github.com/simonw/datasette/issues/302\n'
'https://github.com/simonw/datasette/issues/309\n'
'https://github.com/simonw/datasette/issues/319\n'
'https://github.com/simonw/datasette/issues/321\n'
'https://github.com/simonw/datasette/issues/318\n'
'https://github.com/simonw/datasette/compare/0.22.1...0.23\n'
'https://fivethirtyeight.datasettes.com/fivethirtyeight/bechdel%2Fmovies\n'
'https://fivethirtyeight.datasettes.com/fivethirtyeight/bechdel%2Fmovies\n'
'\n'
'\n'
'Datasette Documentation\n'
'\n'
'Foreign key expansions\n'
'\n'
'When Datasette detects a foreign key reference it attempts to resolve a label for that '
'reference (automatically or using\n'
'the Specifying the label column for a table metadata option) so it can display a link to the '
'associated row.\n'
'\n'
'This expansion is now also available for JSON and CSV representations of the table, using '
'the new _labels=on\n'
'query string option. See Expanding foreign key references for more details.\n'
'\n'
'New configuration settings\n'
'\n'
'Datasette’s Settings now also supports boolean settings. A number of new configuration '
'options have been added:\n'
'\n'
'• num_sql_threads - the number of threads used to execute SQLite queries. Defaults to 3.\n'
'\n'
'• allow_facet - enable or disable custom Facets using the _facet= parameter. Defaults to '
'on.\n'
'\n'
'• suggest_facets - should Datasette suggest facets? Defaults to on.\n'
'\n'
'• allow_download - should users be allowed to download the entire SQLite database? Defaults '
'to on.\n'
'\n'
'• allow_sql - should users be allowed to execute custom SQL queries? Defaults to on.',
'type': 'text'}],
'file_id': 'file-FeX0b3otKd4tjKQ9mZm98Gyv',
'file_name': 'docs-datasette-io-en-0.59.1.pdf',
'score': 0.40883845906210786}