More fun with a integrarelational DBMS: SoilGrids

While the SoilGrids FDW in my first post on this subject works fine, I now think there is a better, more flexible, and more elegant way to do it.

Since PostgreSQL has JSON built in, why not return the whole response and parse with SQL? This way you can get all the data from SoilGrids without having to return a bazillion columns, the JSON response can be stored for future use, and indexed as well.

And this is how it looks like:

	from multicorn import ForeignDataWrapper
	import urllib2
	class SoilGridsForeignDataWrapper(ForeignDataWrapper):
	def __init__(self, options, columns):
	super(SoilGridsForeignDataWrapper, self).__init__(options, columns)
	def execute(self, quals, columns):
	row = {}
	lat_ok = False
	long_ok = False
	if quals:
	for qual in quals:
	if qual.field_name.lower() == 'latitude' and qual.operator == '=':
	latitude = qual.value
	lat_ok = True
	if qual.field_name.lower() == 'longitude' and qual.operator == '=':
	longitude = qual.value
	long_ok = True
	if lat_ok and long_ok:
	req = urllib2.Request(
	'http://rest.soilgrids.org/query?lon={0}&lat={1}'.format(longitude, latitude))
	req.add_header('Content-Type', 'application/json;charset=UTF-8')
	resp = urllib2.urlopen(req)
	results = resp.read()
	row['longitude'] = longitude
	row['latitude'] = latitude
	row['response'] = results
	yield row

view raw __init__.py hosted with ❤ by GitHub

CREATE FOREIGN TABLE public.soilgrids1km
   (response jsonb , -- json also works, PostgreSQL does the correct cast for us
    latitude real ,
    longitude real )
   SERVER soilgrids_srv;

select (response->'properties'->'PHIHOX'->'M'->>'sd1')::REAL / 10.0::real from soilgrids1km where latitude = '51.57' and longitude = '5.39'

5.8

And again, that level of extensibility is one of the reasons why I think that PostgreSQL is one awesome DBMS. Integrarelational even...