GNAFtoPostgresql/GNAF_SQL.py at master · jubertroldan/GNAFtoPostgresql · GitHub

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
72
73
74
75
76
77
78
# GNAF SQL COMBINE

# creates GNAF table with Meshblock mapping


# libraries installed
# pip 9.0.1
# psycopg2-2.7.3

import psycopg2
import csv

# Connect to an existing database
conn = psycopg2.connect("dbname=GNAF_DB user=postgres password=postgres")

# Open a cursor to perform database operations
cur = conn.cursor()

# drop table
cur.execute("DROP TABLE IF EXISTS public.GNAF_address;")

cur.execute("SELECT address_detail.address_detail_pid, longitude, latitude \
	into public.GNAF_address \
	FROM public.address_detail left outer join public.address_default_geocode ON public.address_detail.address_detail_pid = public.address_default_geocode.address_detail_pid\
	where (public.address_detail.confidence >= 0) AND (public.address_detail.alias_principal = 'P');")

conn.commit()

cur.execute("DROP TABLE IF EXISTS public.GNAF_address_mb_2016_pid;")

cur.execute("SELECT GNAF_address.address_detail_pid, longitude, latitude, mb_2016_pid \
	into public.GNAF_address_mb_2016_pid \
	FROM public.GNAF_address left outer join public.address_mesh_block_2016 ON public.GNAF_address.address_detail_pid = public.address_mesh_block_2016.address_detail_pid\
	;")

conn.commit()


cur.execute("DROP TABLE IF EXISTS public.GNAF_address_mb_2016;")

cur.execute("SELECT address_detail_pid, longitude, latitude, mb_2016_code  \
	into public.GNAF_address_mb_2016 \
	FROM public.GNAF_address_mb_2016_pid left outer join public.mb_2016 ON public.GNAF_address_mb_2016_pid.mb_2016_pid = public.mb_2016.mb_2016_pid\
	;")

conn.commit()


cur.execute("DROP TABLE IF EXISTS public.GNAF_address_mb_2011_pid;")

cur.execute("SELECT GNAF_address.address_detail_pid, longitude, latitude, mb_2011_pid \
	into public.GNAF_address_mb_2011_pid \
	FROM public.GNAF_address left outer join public.address_mesh_block_2011 ON public.GNAF_address.address_detail_pid = public.address_mesh_block_2011.address_detail_pid\
	;")

conn.commit()


cur.execute("DROP TABLE IF EXISTS public.GNAF_address_mb_2011;")

cur.execute("SELECT address_detail_pid, longitude, latitude, mb_2011_code  \
	into public.GNAF_address_mb_2011 \
	FROM public.GNAF_address_mb_2011_pid left outer join public.mb_2011 ON public.GNAF_address_mb_2011_pid.mb_2011_pid = public.mb_2011.mb_2011_pid\
	;")

conn.commit()


# delete uneeded table
cur.execute("DROP TABLE IF EXISTS public.gnaf_address;")
cur.execute("DROP TABLE IF EXISTS public.gnaf_address_mb_2011_pid;")
cur.execute("DROP TABLE IF EXISTS public.gnaf_address_mb_2016_pid;")

conn.commit()


conn.close()