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.
6 courses snapshot

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.

Pic of Shadow Creek on Map