
Security News
MCP Community Begins Work on Official MCP Metaregistry
The MCP community is launching an official registry to standardize AI tool discovery and let agents dynamically find and install MCP servers.
Geocode rows from a SQLite table
Install this tool using pip
or pipx
:
# install inside a virtualenv
pip install geocode-sqlite
# install globally
pipx install geocode-sqlite
Let's say you have a spreadsheet with addresses in it, and you'd like to map those locations.
First, create a SQLite database and insert rows from that spreadsheet using sqlite-utils
.
sqlite-utils insert data.db data data.csv --csv
Now, geocode it using OpenStreetMap's Nominatim geocoder.
geocode-sqlite nominatim data.db data \
--location="{address}, {city}, {state} {zip}" \
--delay=1 \
--user-agent="this-is-me"
In the command above, you're using Nominatim, which is free and only asks for a unique user agent (--user-agent
).
This will connect to a database (data.db
) and read all rows from the table data
(skipping any that already
have both a latitude
and longitude
column filled).
You're also telling the geocoder how to extract a location query (--location
) from a row of data, using Python's
built-in string formatting, and setting a rate limit (--delay
) of one request per second.
For each row where geocoding succeeds, latitude
and longitude
will be populated. If you hit an error, or a rate limit,
run the same query and pick up where you left off.
The resulting table layout can be visualized with datasette-cluster-map.
Under the hood, this package uses the excellent geopy library, which is stable and thoroughly road-tested. If you need help understanding a particular geocoder's options, consult geopy's documentation.
The CLI currently supports these geocoders:
bing
googlev3
mapquest
(and open-mapquest
)mapbox
nominatim
opencage
Makefile
. Add tests if there's new shared functionality.Each geocoder needs to know where to find the data it's working with. These are the first two arguments:
database
: a path to a SQLite file, which must already existtable
: the name of a table, in that database, which exists and has data to geocodeFrom there, we have a set of options passed to every geocoder:
location
: a string format that will be expanded with each row to build a full query, to be geocodeddelay
: a delay between each call (some services require this)latitude
: latitude column namelongitude
: longitude column namegeojson
: store results as GeoJSON, instead of in latitude and longitude columnsspatialite
: store results in a SpatiaLite geometry column, instead of in latitude and longitude columnsraw
: store raw geocoding results in a JSON columnEach geocoder takes additional, specific arguments beyond these, such as API keys. Again, geopy's documentation is an excellent resource.
The --spatialite
flag will store results in a geometry column, instead of latitude
and longitude
columns. This is useful if you're doing other GIS operations, such as using a spatial index. See the SpatiaLite cookbook and functions list for more of what's possible.
Geocoding services typically return more data than just coordinates. This might include accuracy, normalized addresses or other context. This can be captured using the --raw
flag. By default, this will add a raw
column and store the full geocoding response as JSON. If you want to rename that column, pass a value, like --raw custom_raw
.
The shape of this response object will vary between services. You can query specific values using SQLite's built-in JSON functions. For example, this will work with Google's geocoder:
select
json_extract(raw, '$.formatted_address') as address,
json_extract(raw, '$.geometry.location_type') as location_type
from
innout_test
Check each geocoding service's documentation for what's included in the response.
The command line interface aims to support the most common options for each geocoder. For more fine-grained control, use the Python API.
As with the CLI, this assumes you already have a SQLite database and a table of location data.
from geocode_sqlite import geocode_table
from geopy.geocoders import Nominatim
# create a geocoder instance, with some extra options
nominatim = Nominatim(user_agent="this-is-me", domain="nominatim.local.dev", scheme="http")
# assuming our database is in the same directory
count = geocode_table("data.db", "data", query_template="{address}, {city}, {state} {zip}")
# when it's done
print(f"Geocoded {count} rows")
Any geopy geocoder can be used with the Python API.
To contribute to this tool, first checkout the code. Then create a new virtual environment:
cd geocode-sqlite
python -m venv .venv
source .venv/bin/activate
Or if you are using pipenv
:
pipenv shell
Now install the dependencies and tests:
pip install -e '.[test]'
To run the tests:
pytest
Please remember that this library is mainly glue code between other well-tested projects, specifically: click, geopy and sqlite-utils. Tests should focus on making sure those parts fit together correctly. We can assume the parts themselves already work.
To that end, there is a test geocoder included: geocode_sqlite.testing.DummyGeocoder
. That geocoder works with an included dataset of In-N-Out Burger locations provided by AllThePlaces. It works like a normal GeoPy geocoder, except it will only return results for In-N-Out locations using the included database.
FAQs
Geocode rows from a SQLite table
We found that geocode-sqlite demonstrated a healthy version release cadence and project activity because the last version was released less than a year ago. It has 1 open source maintainer collaborating on the project.
Did you know?
Socket for GitHub automatically highlights issues in each pull request and monitors the health of all your open source dependencies. Discover the contents of your packages and block harmful activity before you install or update your dependencies.
Security News
The MCP community is launching an official registry to standardize AI tool discovery and let agents dynamically find and install MCP servers.
Research
Security News
Socket uncovers an npm Trojan stealing crypto wallets and BullX credentials via obfuscated code and Telegram exfiltration.
Research
Security News
Malicious npm packages posing as developer tools target macOS Cursor IDE users, stealing credentials and modifying files to gain persistent backdoor access.