Go package for indexing GeoJSON features in a Spatialite (SQLite) database.
You will need to have both Go
(specifically a version of Go more recent than 1.6 so let's just assume you need Go 1.8 or higher) and the make
programs installed on your computer. Assuming you do just type:
make bin
All of this package's dependencies are bundled with the code in the vendor
directory.
CREATE TABLE geojson (
id INTEGER NOT NULL PRIMARY KEY,
properties JSON
);
SELECT InitSpatialMetaData();
SELECT AddGeometryColumn('geojson', 'geometry', 4326, 'GEOMETRY', 'XY');
SELECT CreateSpatialIndex('geojson', 'geometry');
In order to index geometries you will need to have the Spatialite extension already installed. Installation details are outside the scope of this document.
This package also assumes you if you are accessing the resulting database using
a sqlite3
binary or language-specific SQLite3 library (other than this
package) that the JSON1 extension is
available and loaded. If you are creating an application in Go that uses this
library make sure to go build
it (your application) with the --tags json1
flag to tell the underlying go-sqlite3 package to enable support for the
JSON1 extension. For example:
$> go build --tags json1 -o bin/wof-sqlite-index-geojson cmd/wof-sqlite-index-geojson.go
Sure. You just need to write a per-table package that implements the Table
interface as described in go-whosonfirst-sqlite.
./bin/wof-sqlite-index-geojson -h
Usage of ./bin/wof-sqlite-index-geojson:
-all
Index all tables (except the 'search' and 'geometries' tables which you need to specify explicitly)
-dsn string
(default ":memory:")
-geojson
Index the 'geojson' table (default true)
-live-hard-die-fast
Enable various performance-related pragmas at the expense of possible (unlikely) database corruption (default true)
-mode string
The mode to use importing data. Valid modes are: directory,feature,feature-collection,files,geojson-ls,meta,path,repo,sqlite. (default "files")
-processes int
The number of concurrent processes to index data with (default 8)
-timings
Display timings during and after indexing
Please finish writing me...
There isn't a general purpose query tool as of this writing. Every GeoJSON
record's properties
dictionary is stored as a SQLite JSON
database column
and it is assumed you will query them using the json_extract
function.
Let's say that you've indexed the whosonfirst-data-constituency-ca repo like this:
./bin/wof-sqlite-index-geojson -dsn test.db -mode repo /usr/local/data/whosonfirst-data-constituency-ca/
You might then query the data (in a Go program or anything else that can talk to SQLite) like this:
package main
import (
"github.com/whosonfirst/go-whosonfirst-sqlite/database"
"log"
)
func main (){
db, _ := database.NewDBWithDriver("spatialite", "test.db")
defer db.Close()
conn, _ := db.Conn()
sql := "SELECT json_extract(properties, '$.ebc:ed_abbrev') AS ed FROM geojson WHERE json_extract(properties, '$.ebc:ed_abbrev') = 'ABM'"
row := conn.QueryRow(sql)
var eb string
row.Scan(&eb)
log.Println(eb)
}
Error handling has been removed for the sake of brevity.
This package can index any input source supported by the go-whosonfirst-index package.
Unknown. On the face of it, it seems certain that having to call json_extract
all the time wouldn't be terribly performant. Think of it as a cheap-and-easy
tool for spelunking data until proven otherwise and/or working with small
datasets where the extra cost doesn't matter.