hi Chris Amico. I Using GNU/Linux os And in GNU/Linux, I can not run the cli.py file. But with a small change I made, this problem was solved. If you like, please merging.

I'm a contractor for OpenCage geocoder. We'd like to add an opencage option to the CLI. OpenCage is already available in geopy so I have just copied the existing geocoder code with the relevant options.

• Added OpenCage as an option to CLI
• Added OpenCage to README
• Added OpenCage test to Makefile

Looking at this with fresh eyes, this whole bit is dumb:

Note the order of options: There are two sets of options we need to pass.

The first concerns the data we're geocoding. We need to say where our database is and what table we're using, and optionally, how to extract a location query.

Then, we need to say what geocoder we're using, and pass in any options needed to initalize it. This will be different for each geocoder we want to use.

Ideally, it should be something like this:

geocode-sqlite nominatum  data.db data \
 --location="{address}, {city}, {state} {zip}" \
 --delay=1 \
 --user-agent="this-is-me"

Maybe this would be ok:

geocode-sqlite
 --location="{address}, {city}, {state} {zip}" \
 --delay=1 \
 --user-agent="this-is-me" \
nominatum data.db data

But the first way is definitely better.

Would it be worth helping convert lat/lng fields to a spatial index? https://docs.datasette.io/en/stable/spatialite.html#spatial-indexing-latitude-longitude-columns

Here's the whole code block:

import sqlite3
conn = sqlite3.connect('museums.db')
# Lead the spatialite extension:
conn.enable_load_extension(True)
conn.load_extension('/usr/local/lib/mod_spatialite.dylib')
# Initialize spatial metadata for this database:
conn.execute('select InitSpatialMetadata(1)')
# Add a geometry column called point_geom to our museums table:
conn.execute("SELECT AddGeometryColumn('museums', 'point_geom', 4326, 'POINT', 2);")
# Now update that geometry column with the lat/lon points
conn.execute('''
    UPDATE events SET
    point_geom = GeomFromText('POINT('||"longitude"||' '||"latitude"||')',4326);
''')
# Now add a spatial index to that column
conn.execute('select CreateSpatialIndex("museums", "point_geom");')
# If you don't commit your changes will not be persisted:
conn.commit()
conn.close()

I don't know if it belongs in this package or not.

It's generally bad practice to throw a few hundred geocoding requests at a free service. Given that, is it worth setting a default delay of one second, and let people explicitly turn it off if needed?

Here are the geocoders currently supported by the CLI. I want to add the most common ones (which I use) first:

• [x] Google
• [x] MapBox
• [x] OpenStreetMap
• [x] MapQuest
• [x] Bing
• [ ] Geocode.Earth
• [ ] Geocodio
• [ ] Pelias
• [ ] MapTiler

What else?

if MAPBOX_API_KEY is not set in the environment, test_pass_kwargs fails.

noticed this when trying to run tests locally. the D prefix defers to values already set in the environment.

you may not want to introduce a new dependency to your project (namely pytest-env), and I would understand that, but just thought I'd bring this to your attention with a suggested fix. take it or leave it! this is a very useful library. thanks!

Closes #22 Closes #24 Closes #26

Passing a --geojson flag will store results as a GeoJSON geometry, instead of in latitude and longitude columns.

Using --spatialite will add a geometry column and store results as a SpatiaLite binary.

This should make it easier to work with datasette-geojson and datasette-geojson-map.

Right now, results are saved in two columns: latitude and longitude.

Now that datasette-geojson and datasette-geojson-map exist, it would be nice to have this plugin feed into those.

Maybe it's an option: sqlite-geocode data.db table ... --geojson

That would be backwards compatible at least.

Is there anything more in https://geopy.readthedocs.io/en/stable/#geopy.location.Location that's worth capturing?

Could a geocoder specify parts of the raw response to record?

This would probably have to automatically set alter=True.

Especially when rate limiting, which is now the default, geocoding a table is slow. With a one-second delay, a 300 row table takes five minutes to finish, at best.

I do this in three places:

• In geocode_table, which I wrote first
• In the CLI
• In geocode_list, which I needed for the progress bar

This whole block is basically repeated: https://github.com/eyeseast/geocode-sqlite/blob/main/geocode_sqlite/utils.py#L50-L68. Part of the issue is that in the Python API, I'm using log.info and in the CLI I'm using click.echo. Maybe I can abstract that and pass in a print function.

Geopy has a set of tools to help with async here: https://geopy.readthedocs.io/en/latest/#async-mode

Should definitely use those, especially when thinking about Datasette integration.