repos: 237321267
This data as json
| id | node_id | name | full_name | private | owner | html_url | description | fork | created_at | updated_at | pushed_at | homepage | size | stargazers_count | watchers_count | language | has_issues | has_projects | has_downloads | has_wiki | has_pages | forks_count | archived | disabled | open_issues_count | license | topics | forks | open_issues | watchers | default_branch | permissions | temp_clone_token | organization | network_count | subscribers_count | readme | readme_html | allow_forking | visibility | is_template | template_repository | web_commit_signoff_required | has_discussions |
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| 237321267 | MDEwOlJlcG9zaXRvcnkyMzczMjEyNjc= | geojson-to-sqlite | simonw/geojson-to-sqlite | 0 | 9599 | https://github.com/simonw/geojson-to-sqlite | CLI tool for converting GeoJSON files to SQLite (with SpatiaLite) | 0 | 2020-01-30T22:51:05Z | 2022-03-05T00:40:56Z | 2022-04-13T23:39:25Z | 117 | 34 | 34 | Python | 1 | 1 | 1 | 1 | 0 | 3 | 0 | 0 | 4 | apache-2.0 | ["datasette-io", "datasette-tool", "geojson", "gis", "sqlite"] | 3 | 4 | 34 | main | {"admin": false, "maintain": false, "push": false, "triage": false, "pull": false} | 3 | 3 | # geojson-to-sqlite [](https://pypi.org/project/geojson-to-sqlite/) [](https://github.com/simonw/geojson-to-sqlite/releases) [](https://github.com/simonw/geojson-to-sqlite/actions?query=workflow%3ATest) [](https://github.com/simonw/geojson-to-sqlite/blob/main/LICENSE) CLI tool for converting GeoJSON to SQLite (optionally with SpatiaLite) [RFC 7946: The GeoJSON Format](https://tools.ietf.org/html/rfc7946) ## How to install $ pip install geojson-to-sqlite ## How to use You can run this tool against a GeoJSON file like so: $ geojson-to-sqlite my.db features features.geojson This will load all of the features from the `features.geojson` file into a table called `features`. Each row will have a `geometry` column containing the feature geometry, and columns for each of the keys found in any `properties` attached to those features. (To bundle all properties into a single JSON object, use the `--properties` flag.) The table will be created the first time you run the command. On subsequent runs you can use the `--alter` option to add any new columns that are missing from the table. You can pass more than one GeoJSON file, in which case the contents of all of the files will be inserted into the same table. If your features have an `"id"` property it will be used as the primary key for the table. You can also use `--pk=PROPERTY` with the name of a different property to use that as the primary key instead. If you don't want to use the `"id"` as the primary key (maybe it contains duplicate values) you can use `--pk ''` to specify no primary key. Specifying a primary key also will allow you to upsert data into the rows instead of insert data into new rows. If no primary key is specified, a SQLite `rowid` column will be used. You can use `-` as the filename to import from standard input. For example: $ curl https://eric.clst.org/assets/wiki/uploads/Stuff/gz_2010_us_040_00_20m.json \ | geojson-to-sqlite my.db states - --pk GEO_ID ## Using with SpatiaLite By default, the `geometry` column will contain JSON. If you have installed the [SpatiaLite](https://www.gaia-gis.it/fossil/libspatialite/index) module for SQLite you can instead import the geometry into a geospatially indexed column. You can do this using the `--spatialite` option, like so: $ geojson-to-sqlite my.db features features.geojson --spatialite The tool will search for the SpatiaLite module in the following locations: - `/usr/lib/x86_64-linux-gnu/mod_spatialite.so` - `/usr/local/lib/mod_spatialite.dylib` If you have installed the module in another location, you can use the `--spatialite_mod=xxx` option to specify where: $ geojson-to-sqlite my.db features features.geojson \ --spatialite_mod=/usr/lib/mod_spatialite.dylib You can create a SpatiaLite spatial index on the `geometry` column using the `--spatial-index` option: $ geojson-to-sqlite my.db features features.geojson --spatial-index Using this option implies `--spatialite` so you do not need to add that. ## Streaming large datasets For large datasets, consider using newline-delimited JSON to stream features into the database without loading the entire feature collection into memory. For example, to load a day of earthquake reports from USGS: $ geojson-to-sqlite quakes.db quakes tests/quakes.ndjson \ --nl --pk=id --spatialite When using newline-delimited JSON, tables will also be created from the first feature, instead of guessing types based on the first 100 features. If you want to use a larger subset of your data to guess column types (for example, if some fields are inconsistent) you can use [fiona](https://fiona.readthedocs.io/en/latest/cli.html) to collect features into a single collection. $ head tests/quakes.ndjson | fio collect | \ geojson-to-sqlite quakes.db quakes - --spatialite This will take the first 10 lines from `tests/quakes.ndjson`, pass them to `fio collect`, which turns them into a single feature collection, and pass that, in turn, to `geojson-to-sqlite`. ## Using this with Datasette Databases created using this tool can be explored and published using [Datasette](https://datasette.readthedocs.io/). The Datasette documentation includes a section on [how to use it to browse SpatiaLite databases](https://datasette.readthedocs.io/en/stable/spatialite.html). The [datasette-leaflet-geojson](https://datasette.io/plugins/datasette-leaflet-geojson) plugin can be used to visualize columns containing GeoJSON geometries on a [Leaflet](https://leafletjs.com/) map. If you are using SpatiaLite you will need to output the geometry as GeoJSON in order for that plugin to work. You can do that using the SpaitaLite `AsGeoJSON()` function - something like this: ```sql select rowid, AsGeoJSON(geometry) from mytable limit 10 ``` The [datasette-geojson-map](https://datasette.io/plugins/datasette-geojson-map) is an alternative plugin which will automatically render SpatiaLite geometries as a Leaflet map on the corresponding table page, without needing you to call `AsGeoJSON(geometry)`. | <div id="readme" class="md" data-path="README.md"><article class="markdown-body entry-content container-lg" itemprop="text"><h1 dir="auto"><a id="user-content-geojson-to-sqlite" class="anchor" aria-hidden="true" href="#user-content-geojson-to-sqlite"><svg class="octicon octicon-link" viewBox="0 0 16 16" version="1.1" width="16" height="16" aria-hidden="true"><path fill-rule="evenodd" d="M7.775 3.275a.75.75 0 001.06 1.06l1.25-1.25a2 2 0 112.83 2.83l-2.5 2.5a2 2 0 01-2.83 0 .75.75 0 00-1.06 1.06 3.5 3.5 0 004.95 0l2.5-2.5a3.5 3.5 0 00-4.95-4.95l-1.25 1.25zm-4.69 9.64a2 2 0 010-2.83l2.5-2.5a2 2 0 012.83 0 .75.75 0 001.06-1.06 3.5 3.5 0 00-4.95 0l-2.5 2.5a3.5 3.5 0 004.95 4.95l1.25-1.25a.75.75 0 00-1.06-1.06l-1.25 1.25a2 2 0 01-2.83 0z"></path></svg></a>geojson-to-sqlite</h1> <p dir="auto"><a href="https://pypi.org/project/geojson-to-sqlite/" rel="nofollow"><img src="https://camo.githubusercontent.com/b0c77834f0d6adf37e62573e121ccadd92711433ad2c5b4dd506610919762dab/68747470733a2f2f696d672e736869656c64732e696f2f707970692f762f67656f6a736f6e2d746f2d73716c6974652e737667" alt="PyPI" data-canonical-src="https://img.shields.io/pypi/v/geojson-to-sqlite.svg" style="max-width: 100%;"></a> <a href="https://github.com/simonw/geojson-to-sqlite/releases"><img src="https://camo.githubusercontent.com/e88a12c13acc77ac4d0338f065dfeb664076b3414a7ee2560e8d465e828c1320/68747470733a2f2f696d672e736869656c64732e696f2f6769746875622f762f72656c656173652f73696d6f6e772f67656f6a736f6e2d746f2d73716c6974653f696e636c7564655f70726572656c6561736573266c6162656c3d6368616e67656c6f67" alt="Changelog" data-canonical-src="https://img.shields.io/github/v/release/simonw/geojson-to-sqlite?include_prereleases&label=changelog" style="max-width: 100%;"></a> <a href="https://github.com/simonw/geojson-to-sqlite/actions?query=workflow%3ATest"><img src="https://github.com/simonw/geojson-to-sqlite/workflows/Test/badge.svg" alt="Tests" style="max-width: 100%;"></a> <a href="https://github.com/simonw/geojson-to-sqlite/blob/main/LICENSE"><img src="https://camo.githubusercontent.com/1698104e976c681143eb0841f9675c6f802bb7aa832afc0c7a4e719b1f3cf955/68747470733a2f2f696d672e736869656c64732e696f2f62616467652f6c6963656e73652d417061636865253230322e302d626c75652e737667" alt="License" data-canonical-src="https://img.shields.io/badge/license-Apache%202.0-blue.svg" style="max-width: 100%;"></a></p> <p dir="auto">CLI tool for converting GeoJSON to SQLite (optionally with SpatiaLite)</p> <p dir="auto"><a href="https://tools.ietf.org/html/rfc7946" rel="nofollow">RFC 7946: The GeoJSON Format</a></p> <h2 dir="auto"><a id="user-content-how-to-install" class="anchor" aria-hidden="true" href="#user-content-how-to-install"><svg class="octicon octicon-link" viewBox="0 0 16 16" version="1.1" width="16" height="16" aria-hidden="true"><path fill-rule="evenodd" d="M7.775 3.275a.75.75 0 001.06 1.06l1.25-1.25a2 2 0 112.83 2.83l-2.5 2.5a2 2 0 01-2.83 0 .75.75 0 00-1.06 1.06 3.5 3.5 0 004.95 0l2.5-2.5a3.5 3.5 0 00-4.95-4.95l-1.25 1.25zm-4.69 9.64a2 2 0 010-2.83l2.5-2.5a2 2 0 012.83 0 .75.75 0 001.06-1.06 3.5 3.5 0 00-4.95 0l-2.5 2.5a3.5 3.5 0 004.95 4.95l1.25-1.25a.75.75 0 00-1.06-1.06l-1.25 1.25a2 2 0 01-2.83 0z"></path></svg></a>How to install</h2> <div class="snippet-clipboard-content position-relative overflow-auto" data-snippet-clipboard-copy-content="$ pip install geojson-to-sqlite"><pre><code>$ pip install geojson-to-sqlite </code></pre></div> <h2 dir="auto"><a id="user-content-how-to-use" class="anchor" aria-hidden="true" href="#user-content-how-to-use"><svg class="octicon octicon-link" viewBox="0 0 16 16" version="1.1" width="16" height="16" aria-hidden="true"><path fill-rule="evenodd" d="M7.775 3.275a.75.75 0 001.06 1.06l1.25-1.25a2 2 0 112.83 2.83l-2.5 2.5a2 2 0 01-2.83 0 .75.75 0 00-1.06 1.06 3.5 3.5 0 004.95 0l2.5-2.5a3.5 3.5 0 00-4.95-4.95l-1.25 1.25zm-4.69 9.64a2 2 0 010-2.83l2.5-2.5a2 2 0 012.83 0 .75.75 0 001.06-1.06 3.5 3.5 0 00-4.95 0l-2.5 2.5a3.5 3.5 0 004.95 4.95l1.25-1.25a.75.75 0 00-1.06-1.06l-1.25 1.25a2 2 0 01-2.83 0z"></path></svg></a>How to use</h2> <p dir="auto">You can run this tool against a GeoJSON file like so:</p> <div class="snippet-clipboard-content position-relative overflow-auto" data-snippet-clipboard-copy-content="$ geojson-to-sqlite my.db features features.geojson"><pre><code>$ geojson-to-sqlite my.db features features.geojson </code></pre></div> <p dir="auto">This will load all of the features from the <code>features.geojson</code> file into a table called <code>features</code>.</p> <p dir="auto">Each row will have a <code>geometry</code> column containing the feature geometry, and columns for each of the keys found in any <code>properties</code> attached to those features. (To bundle all properties into a single JSON object, use the <code>--properties</code> flag.)</p> <p dir="auto">The table will be created the first time you run the command.</p> <p dir="auto">On subsequent runs you can use the <code>--alter</code> option to add any new columns that are missing from the table.</p> <p dir="auto">You can pass more than one GeoJSON file, in which case the contents of all of the files will be inserted into the same table.</p> <p dir="auto">If your features have an <code>"id"</code> property it will be used as the primary key for the table. You can also use <code>--pk=PROPERTY</code> with the name of a different property to use that as the primary key instead. If you don't want to use the <code>"id"</code> as the primary key (maybe it contains duplicate values) you can use <code>--pk ''</code> to specify no primary key.</p> <p dir="auto">Specifying a primary key also will allow you to upsert data into the rows instead of insert data into new rows.</p> <p dir="auto">If no primary key is specified, a SQLite <code>rowid</code> column will be used.</p> <p dir="auto">You can use <code>-</code> as the filename to import from standard input. For example:</p> <div class="snippet-clipboard-content position-relative overflow-auto" data-snippet-clipboard-copy-content="$ curl https://eric.clst.org/assets/wiki/uploads/Stuff/gz_2010_us_040_00_20m.json \ | geojson-to-sqlite my.db states - --pk GEO_ID"><pre><code>$ curl https://eric.clst.org/assets/wiki/uploads/Stuff/gz_2010_us_040_00_20m.json \ | geojson-to-sqlite my.db states - --pk GEO_ID </code></pre></div> <h2 dir="auto"><a id="user-content-using-with-spatialite" class="anchor" aria-hidden="true" href="#user-content-using-with-spatialite"><svg class="octicon octicon-link" viewBox="0 0 16 16" version="1.1" width="16" height="16" aria-hidden="true"><path fill-rule="evenodd" d="M7.775 3.275a.75.75 0 001.06 1.06l1.25-1.25a2 2 0 112.83 2.83l-2.5 2.5a2 2 0 01-2.83 0 .75.75 0 00-1.06 1.06 3.5 3.5 0 004.95 0l2.5-2.5a3.5 3.5 0 00-4.95-4.95l-1.25 1.25zm-4.69 9.64a2 2 0 010-2.83l2.5-2.5a2 2 0 012.83 0 .75.75 0 001.06-1.06 3.5 3.5 0 00-4.95 0l-2.5 2.5a3.5 3.5 0 004.95 4.95l1.25-1.25a.75.75 0 00-1.06-1.06l-1.25 1.25a2 2 0 01-2.83 0z"></path></svg></a>Using with SpatiaLite</h2> <p dir="auto">By default, the <code>geometry</code> column will contain JSON.</p> <p dir="auto">If you have installed the <a href="https://www.gaia-gis.it/fossil/libspatialite/index" rel="nofollow">SpatiaLite</a> module for SQLite you can instead import the geometry into a geospatially indexed column.</p> <p dir="auto">You can do this using the <code>--spatialite</code> option, like so:</p> <div class="snippet-clipboard-content position-relative overflow-auto" data-snippet-clipboard-copy-content="$ geojson-to-sqlite my.db features features.geojson --spatialite"><pre><code>$ geojson-to-sqlite my.db features features.geojson --spatialite </code></pre></div> <p dir="auto">The tool will search for the SpatiaLite module in the following locations:</p> <ul dir="auto"> <li><code>/usr/lib/x86_64-linux-gnu/mod_spatialite.so</code></li> <li><code>/usr/local/lib/mod_spatialite.dylib</code></li> </ul> <p dir="auto">If you have installed the module in another location, you can use the <code>--spatialite_mod=xxx</code> option to specify where:</p> <div class="snippet-clipboard-content position-relative overflow-auto" data-snippet-clipboard-copy-content="$ geojson-to-sqlite my.db features features.geojson \ --spatialite_mod=/usr/lib/mod_spatialite.dylib"><pre><code>$ geojson-to-sqlite my.db features features.geojson \ --spatialite_mod=/usr/lib/mod_spatialite.dylib </code></pre></div> <p dir="auto">You can create a SpatiaLite spatial index on the <code>geometry</code> column using the <code>--spatial-index</code> option:</p> <div class="snippet-clipboard-content position-relative overflow-auto" data-snippet-clipboard-copy-content="$ geojson-to-sqlite my.db features features.geojson --spatial-index"><pre><code>$ geojson-to-sqlite my.db features features.geojson --spatial-index </code></pre></div> <p dir="auto">Using this option implies <code>--spatialite</code> so you do not need to add that.</p> <h2 dir="auto"><a id="user-content-streaming-large-datasets" class="anchor" aria-hidden="true" href="#user-content-streaming-large-datasets"><svg class="octicon octicon-link" viewBox="0 0 16 16" version="1.1" width="16" height="16" aria-hidden="true"><path fill-rule="evenodd" d="M7.775 3.275a.75.75 0 001.06 1.06l1.25-1.25a2 2 0 112.83 2.83l-2.5 2.5a2 2 0 01-2.83 0 .75.75 0 00-1.06 1.06 3.5 3.5 0 004.95 0l2.5-2.5a3.5 3.5 0 00-4.95-4.95l-1.25 1.25zm-4.69 9.64a2 2 0 010-2.83l2.5-2.5a2 2 0 012.83 0 .75.75 0 001.06-1.06 3.5 3.5 0 00-4.95 0l-2.5 2.5a3.5 3.5 0 004.95 4.95l1.25-1.25a.75.75 0 00-1.06-1.06l-1.25 1.25a2 2 0 01-2.83 0z"></path></svg></a>Streaming large datasets</h2> <p dir="auto">For large datasets, consider using newline-delimited JSON to stream features into the database without loading the entire feature collection into memory.</p> <p dir="auto">For example, to load a day of earthquake reports from USGS:</p> <div class="snippet-clipboard-content position-relative overflow-auto" data-snippet-clipboard-copy-content="$ geojson-to-sqlite quakes.db quakes tests/quakes.ndjson \ --nl --pk=id --spatialite"><pre><code>$ geojson-to-sqlite quakes.db quakes tests/quakes.ndjson \ --nl --pk=id --spatialite </code></pre></div> <p dir="auto">When using newline-delimited JSON, tables will also be created from the first feature, instead of guessing types based on the first 100 features.</p> <p dir="auto">If you want to use a larger subset of your data to guess column types (for example, if some fields are inconsistent) you can use <a href="https://fiona.readthedocs.io/en/latest/cli.html" rel="nofollow">fiona</a> to collect features into a single collection.</p> <div class="snippet-clipboard-content position-relative overflow-auto" data-snippet-clipboard-copy-content="$ head tests/quakes.ndjson | fio collect | \ geojson-to-sqlite quakes.db quakes - --spatialite"><pre><code>$ head tests/quakes.ndjson | fio collect | \ geojson-to-sqlite quakes.db quakes - --spatialite </code></pre></div> <p dir="auto">This will take the first 10 lines from <code>tests/quakes.ndjson</code>, pass them to <code>fio collect</code>, which turns them into a single feature collection, and pass that, in turn, to <code>geojson-to-sqlite</code>.</p> <h2 dir="auto"><a id="user-content-using-this-with-datasette" class="anchor" aria-hidden="true" href="#user-content-using-this-with-datasette"><svg class="octicon octicon-link" viewBox="0 0 16 16" version="1.1" width="16" height="16" aria-hidden="true"><path fill-rule="evenodd" d="M7.775 3.275a.75.75 0 001.06 1.06l1.25-1.25a2 2 0 112.83 2.83l-2.5 2.5a2 2 0 01-2.83 0 .75.75 0 00-1.06 1.06 3.5 3.5 0 004.95 0l2.5-2.5a3.5 3.5 0 00-4.95-4.95l-1.25 1.25zm-4.69 9.64a2 2 0 010-2.83l2.5-2.5a2 2 0 012.83 0 .75.75 0 001.06-1.06 3.5 3.5 0 00-4.95 0l-2.5 2.5a3.5 3.5 0 004.95 4.95l1.25-1.25a.75.75 0 00-1.06-1.06l-1.25 1.25a2 2 0 01-2.83 0z"></path></svg></a>Using this with Datasette</h2> <p dir="auto">Databases created using this tool can be explored and published using <a href="https://datasette.readthedocs.io/" rel="nofollow">Datasette</a>.</p> <p dir="auto">The Datasette documentation includes a section on <a href="https://datasette.readthedocs.io/en/stable/spatialite.html" rel="nofollow">how to use it to browse SpatiaLite databases</a>.</p> <p dir="auto">The <a href="https://datasette.io/plugins/datasette-leaflet-geojson" rel="nofollow">datasette-leaflet-geojson</a> plugin can be used to visualize columns containing GeoJSON geometries on a <a href="https://leafletjs.com/" rel="nofollow">Leaflet</a> map.</p> <p dir="auto">If you are using SpatiaLite you will need to output the geometry as GeoJSON in order for that plugin to work. You can do that using the SpaitaLite <code>AsGeoJSON()</code> function - something like this:</p> <div class="highlight highlight-source-sql position-relative overflow-auto" data-snippet-clipboard-copy-content="select rowid, AsGeoJSON(geometry) from mytable limit 10"><pre><span class="pl-k">select</span> rowid, AsGeoJSON(geometry) <span class="pl-k">from</span> mytable <span class="pl-k">limit</span> <span class="pl-c1">10</span></pre></div> <p dir="auto">The <a href="https://datasette.io/plugins/datasette-geojson-map" rel="nofollow">datasette-geojson-map</a> is an alternative plugin which will automatically render SpatiaLite geometries as a Leaflet map on the corresponding table page, without needing you to call <code>AsGeoJSON(geometry)</code>.</p> </article></div> | 1 | public | 0 |
Links from other tables
- 12 rows from repo in releases