A petl extension for spatial data in Oracle and Postgres
If installing manually as a pip module, run it like this:
pip install git+https://github.com/CityOfPhiladelphia/geopetl
Extract (read)
Provides access to data from any DB-API 2.0 connection via a given query. If query = None, then defaults to select * from table. Geopetl
extends access to spatial data in oracle and postgres SDE enabled databases as well as postgis databases. etl.frompostgis() method is
compatible with both postgres SDE and postgis.
petl.io.db.fromdb(dbo, query, *args, **kwargs)
fromoraclesde(dbo, query, *args, **kwargs)
frompostgis(dbo, query, *args, **kwargs)
import petl as etl
import geopetl
import psycopg2
import cx_Oracle
dsn = cx_Oracle.makedsn('host', 'port', service_name='service_name')
oracle_connection = cx_Oracle.connect('user', 'password', dsn, encoding="UTF-8")
oraclesde_data = etl.fromoraclesde(oracle_connection, 'oracle_table_name')
postgisconnection = psycopg2.connect(user="postgres",
password="password123",
host="127.0.0.1",
port="5432",
database="postgis_db")
postgis_data = etl.frompostgis(postgisconnection, 'postgis_table_name')
postgresde_connection = psycopg2.connect(user="postgres",
password="password123",
host="127.0.0.1",
port="5432",
database="postgressde_db")
postgressde_data = etl.frompostgis(postgresde_connection,'postgressde_table_name')
Load (write)
Load data into an existing database table via a DB-API 2.0 connection or cursor. Note that the database table will be truncated by default.
etl.topostgis() method is compatible with both postgres SDE and postgis.
petl.io.db.todb(table, dbo, tablename, schema=None, commit=True, create=False, drop=False, constraints=True, metadata=None, dialect=None, sample=1000)
tooraclesde(table, dbo, tablename,srid=None,truncate=True, increment=True)
topostgis(table, dbo, table_name, from_srid=None,)
import petl as etl
import geopetl
import psycopg2
import cx_Oracle
csv_data etl.fromcsv('mydata.csv')
dsn = cx_Oracle.makedsn('host', 'port', service_name='service_name')
oracleconnection = cx_Oracle.connect('user', 'password', dsn, encoding="UTF-8")
etl.tooraclesde(csv_data, oracleconnection, 'oracle_table_name')
postgisconnection = psycopg2.connect(user="postgres",
password="password123",
host="127.0.0.1",
port="5432",
database="postgres_db")
etl.topostgis(csv_data, postgisconnection, 'postgis_table_name')
postgresde_connection = psycopg2.connect(user="postgres",
password="password123",
host="127.0.0.1",
port="5432",
database="postgres_db")
etl.topostgis(csv_data, postgresde_connection, 'postgres_table_name')
To run the tests locally via docker compose, pull this version and place it in your path:
sudo wget https://github.com/docker/compose/releases/download/1.26.0-rc4/docker-compose-Linux-x86_64
sudo mv docker-compose-Linux-x86_64 /usr/bin/docker-compose && sudo chmod +x /usr/bin/docker-compose
The docker container requires oracle rpms which we have stored in S3. To get them, rename config.sh.example to config.sh, and populate the S3 creds into the appropriate environment variables.
Then run the config like so:
. .config.sh
Pull the oracle rpms:
. ./pull-oracle-rpm.sh
Then run docker-compose to run start the containers and run the pytest tests
`docker-compose -f oracle-docker-compose.yml up --build --abort-on-container-exit --exit-code-from geopetl`
`docker-compose -f postgis-docker-compose.yml up --build --abort-on-container-exit --exit-code-from geopetl`
`docker-compose -f postgressde-docker-compose.yml up --build --abort-on-container-exit --exit-code-from geopetl`
`docker-compose -f sde-rds-compose.yml up --build --abort-on-container-exit --exit-code-from geopetl`