Note
This page was generated from examples/notebooks/to_database.ipynb.
[1]:
import starepandas
import geopandas
import sqlalchemy
import pandas
import numpy
Data Prep#
[2]:
cities = ['Buenos Aires', 'Brasilia', 'Santiago',
'Bogota', 'Caracas', 'Sao Paulo', 'Bridgetown']
latitudes = [-34.58, -15.78, -33.45, 4.60, 10.48, -23.55, 13.1]
longitudes = [-58.66, -47.91, -70.66, -74.08, -66.86, -46.63, -59.62]
data = {'City': cities,
'Latitude': latitudes, 'Longitude': longitudes}
cities = starepandas.STAREDataFrame(data)
sids = starepandas.sids_from_xy(cities.Longitude, cities.Latitude, level=27)
cities.set_sids(sids, inplace=True)
geom = geopandas.points_from_xy(cities.Longitude, cities.Latitude, crs='EPSG:4326')
cities.set_geometry(geom, inplace=True)
To SQLite#
[3]:
db_path = '' # Empty string for inmemory
uri = 'sqlite:///{db_path}'.format(db_path=db_path)
engine = sqlalchemy.create_engine(uri)
cities = geopandas.io.sql._convert_to_ewkb(cities, 'geometry', 4326)
cities.to_sql(name='cities', con=engine, if_exists='replace')
[3]:
7
From SQLite#
[4]:
df = pandas.read_sql_table('cities', con=engine)
df
[4]:
| index | City | Latitude | Longitude | sids | geometry | |
|---|---|---|---|---|---|---|
| 0 | 0 | Buenos Aires | -34.58 | -58.66 | 2663379193440875387 | 0101000020E610000014AE47E17A544DC00AD7A3703D4A... |
| 1 | 1 | Brasilia | -15.78 | -47.91 | 2867415364672350651 | 0101000020E610000014AE47E17AF447C08FC2F5285C8F... |
| 2 | 2 | Santiago | -33.45 | -70.66 | 2723774768829278555 | 0101000020E61000000AD7A3703DAA51C09A99999999B9... |
| 3 | 3 | Bogota | 4.60 | -74.08 | 2667981979956219515 | 0101000020E610000085EB51B81E8552C0666666666666... |
| 4 | 4 | Caracas | 10.48 | -66.86 | 2494081632617553403 | 0101000020E6100000D7A3703D0AB750C0F6285C8FC2F5... |
| 5 | 5 | Sao Paulo | -23.55 | -46.63 | 2803225788975740475 | 0101000020E6100000713D0AD7A35047C0CDCCCCCCCC8C... |
| 6 | 6 | Bridgetown | 13.10 | -59.62 | 2518254660685127707 | 0101000020E61000008FC2F5285CCF4DC0333333333333... |