-
-
Notifications
You must be signed in to change notification settings - Fork 0
/
load_campsites_geojson.py
130 lines (120 loc) · 4.44 KB
/
load_campsites_geojson.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
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
#!/Users/finnlesueur/.pyenv/shims/python3
"""Author: Finn LeSueur
This script parses the geojson file
containing all huts in New Zealand.
It can be exported here:
https://catalogue.data.govt.nz/dataset/doc-campsites
"""
import pymysql.cursors
from dotenv import load_dotenv
from pathlib import Path
import geojson
import time
import os
import subprocess
def main():
"""The brains."""
env_path = Path('.') / '.env'
load_dotenv(dotenv_path=env_path)
# Load the DOC Huts geojson
with open("DOC_Campsites.geojson") as file:
campsites = geojson.load(file)
# Connect to MySQL
connection = pymysql.connect(host=os.getenv("DB_HOST"),
user=os.getenv("DB_USERNAME"),
password=os.getenv("DB_PASSWORD"),
db=os.getenv("DB_DATABASE"),
charset='utf8mb4',
cursorclass=pymysql.cursors.DictCursor)
for feature in campsites["features"]:
with connection.cursor() as cursor:
# Create a new record
sql = """
INSERT INTO
`adventure_log_places_places` (
`created_at`,
`created_by_id`,
`name`,
`name_slug`,
`place_slug`,
`fid`,
`place`,
`region`,
`facilities`,
`static_link`,
`asset_id`,
`date_loaded_to_gis`,
`latitude`,
`longitude`,
`thumbnail`,
`place_type`
) VALUES (
%s,
%s,
%s,
%s,
%s,
%s,
%s,
%s,
%s,
%s,
%s,
%s,
%s,
%s,
%s,
%s
)"""
cursor.execute(sql, (
time.strftime('%Y-%m-%d %H:%M:%S'),
1,
feature["properties"]["name"],
slugify(feature["properties"]["name"]),
slugify(feature["properties"]["place"]),
int(feature["properties"]["FID"]),
feature["properties"]["place"],
feature["properties"]["region"],
feature["properties"]["facilities"],
feature["properties"]["staticLink"],
int(feature["properties"]["assetId"]),
feature["properties"]["dateLoadedToGIS"],
float(feature["geometry"]["coordinates"][1]),
float(feature["geometry"]["coordinates"][0]),
feature["properties"]["introductionThumbnail"],
"campsite"
))
connection.commit()
# Fix some incorrect values
with connection.cursor() as cursor:
sql = 'UPDATE adventure_log_places_places SET place_slug="unknown" WHERE place_slug="misc";'
cursor.execute(sql)
sql = 'UPDATE adventure_log_places_places SET place_slug="unknown" WHERE place_slug IS NULL;'
cursor.execute(sql)
sql = 'UPDATE adventure_log_places_places SET place_slug="unknown" WHERE place_slug="";'
cursor.execute(sql)
sql = 'UPDATE adventure_log_places_places SET place="Unknown" WHERE place="Misc";'
cursor.execute(sql)
sql = 'UPDATE adventure_log_places_places SET place="Unknown" WHERE place IS NULL;'
cursor.execute(sql)
sql = 'UPDATE adventure_log_places_places SET region="Unknown" WHERE region IS NULL;'
cursor.execute(sql)
connection.commit()
subprocess.call(['php', 'artisan', 'streams:index'])
def slugify(string):
"""Slugifies a string."""
if string is None:
string = ""
non_url_safe = ['"', '#', '$', '%', '&', '+',
',', '/', ':', ';', '=', '?',
'@', '[', '\\', ']', '^', '`',
'{', '|', '}', '~', "'"]
translate_table = {ord(char): u'' for char in non_url_safe}
slug = string.translate(translate_table)
slug = slug.split()
for count, value in enumerate(slug):
slug[count] = value.lower()
slug = '-'.join(slug)
return slug
if __name__ == "__main__":
main()