Using Python to Create a GEOJSON file from a database containing lats and longs
Ofttimes I find myself having some sort of location data stored in a MySQL database and wanting to create a GEOJSON file in order to represent those locations on a map. There are likely utilities to perform this type of task, but I find myself turning to some trusty Python I wrote to do the job.
Consider the following MySQL table containing some of the top public golf courses in the country.
The structure of a GEOJSON file dictates we have a dictionary where the first key/value is “type”:”FeatureCollection”. The second key is “Features, and its value is a list of dictionaries, where each element of the list represents one golf course. But each member of this list is itself a dictionary containing information about a given course.
So overall we have a dictionary of the form
{“type”:'”FeatureCollection”,”Features”:COURSELIST}
where COURSELIST is a list that looks looks like[COURSE1,COURSE2,COURSE3,…].
Each of the COURSE entries in this list is itself a dictionary containing information about one course. As an example, here is the list entry for the SHADOW CREEK course from the database listing above.
{
“type”: “Feature”,
“properties”: {
“city”: “North Las Vegas”,
“course”: “SHADOW CREEK”,
“state”: “NV”
},
“geometry”: {
“coordinates”: [
-115.110224,
36.2570487
],
“type”: “Point”
},
}
Here is the Python code that will create a GEOJSON file from the database listing above
import MySQLdb
import json
from shapely.geometry import Point,mapping
#replace id,pwd and dbname in the folllowing connect
db = MySQLdb.connect("127.0.0.1","id","pwd","dbname")
sel_cursor = db.cursor()
crs_sql = "SELECT name,city,state,lat,lng FROM courses_stage where lat is not null;"
sel_cursor.execute(crs_sql)
crs_list= sel_cursor.fetchall()
# gjson is th emain dictionary
gjson_dict={}
gjson_dict["type"]= "FeatureCollection"
feat_list = []
# Loop through all the courses, building a list entry which is itself a dictionary
# Each of these dictionaries has nested within it a type dictionary, which contains a point dictionary and a properties dictionary
for crs in crs_list:
type_dict = {}
pt_dict = {}
prop_dict = {}
type_dict["type"]= "Feature"
pt_dict["type"]="Point"
# GEOJSON looks for long,lat so reverse order
type_dict["geometry"]=mapping(Point(crs[4],crs[3]))
prop_dict["course"]= crs[0]
prop_dict["city"]= crs[1]
prop_dict["state"]= crs[2]
type_dict["properties"]=prop_dict
feat_list.append(type_dict)
gjson_dict["features"] = feat_list
# Serialize JSON for writing to file
with open('c:\GolfRT\stage_geojson_blg.json', 'w') as outfile:
json.dump(gjson_dict, outfile, sort_keys = True, indent = 4,
ensure_ascii=False)
Finally, after generating the GEOJSON file, we can look at the courses on a map using the site geojsonlint.com , which is very useful for checking the structure of GEOJSON files.