-
Notifications
You must be signed in to change notification settings - Fork 0
/
sensum_db_publish.py
71 lines (60 loc) · 2.69 KB
/
sensum_db_publish.py
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
'''
-----------------------------------------------------------------------------
sensum_db_publish.py
-----------------------------------------------------------------------------
Created on 26.11.2014
Last modified on 01.12.2014
Author: Marc Wieland
Description:
- this script publishes the data and metadata tables of the sensum_db model
to github for versioning
- dump database views of sensum_db model to geojson and csv
- add files and commit changes to local git repository
- push changes to remote git repository
Input:
- a database that follows the sensum_db model with data (object_res1.v_resolution1_data)
and metadata (object_res1.v_resolution1_metadata) views
- an initiated local git repository with added remote repository
Dependencies:
- psycopg2
- sensum_db model
----
'''
print(__doc__)
import os
# Parameters to set ########################################################################################################
host = 'localhost'
port = '5432'
dbname = 'sensum_db_scenario'
user = 'postgres'
pw = '****'
s_srs = '4326' # source spatial reference system
git_local = 'PATH_TO_LOCAL_GIT_REPO/' # path to local git repository (should have a remote repo assigned)
############################################################################################################################
# delete existing files
if os.path.exists(git_local + 'v_resolution1_data.geojson'):
com = 'sudo rm ' + git_local + 'v_resolution1_data.geojson'
os.system(com)
if os.path.exists(git_local + 'v_resolution1_metadata.csv'):
com = 'sudo rm ' + git_local + 'v_resolution1_metadata.csv'
os.system(com)
# get source srs of table
# dump data to geojson with specified source and target spatial reference system (4326 - WGS84)
com = 'ogr2ogr -f "GeoJSON" ' + git_local + 'v_resolution1_data.geojson -s_srs EPSG:' + s_srs + ' -t_srs EPSG:4326 PG:"host=' + host + ' port=' + port + ' dbname=' + dbname + ' user=' + user + ' password=' + pw + '" "object_res1.v_resolution1_data"'
os.system(com)
# dump metadata to csv (note: use psql and \copy to avoid permission error)
com = 'psql -U ' + user + ' -p ' + port + ' -d ' + dbname + ' -c "\COPY (SELECT * FROM object_res1.v_resolution1_metadata) TO ' + git_local + 'v_resolution1_metadata.csv DELIMITER \',\' CSV HEADER;"'
os.system(com)
# add file to local git repo
os.chdir(git_local)
com = 'git add *'
os.system(com)
# commit changes
com = 'git commit -m "new ' + dbname + ' release"'
os.system(com)
# pull from remote repo
#com = 'git pull -u origin master'
#os.system(com)
# push to remote repo
com = 'git push -u origin master'
os.system(com)