def test_table_name_property(self): sql2gee = SQL2GEE('select pepe from mytable') table_name = sql2gee.target_data self.assertEqual(table_name, 'mytable') sql2gee = SQL2GEE('SELECT TOP 1 * FROM mytable ORDER BY unique_column DESC') table_name = sql2gee.target_data self.assertEqual(table_name, 'mytable') return
def test_limit_on_tables(): """Test ability to limit the size of SQL table requests""" sql = 'select width from "ft:1qpKIcYQMBsXLA9RLWCaV9D0Hus2cMQHhI-ViKHo" LIMIT ' err = 'Response was not equal to size of LIMIT' limit = 1 q = SQL2GEE(sql + str(limit)) assert len(q.response['features']) == limit, err limit = 2 q = SQL2GEE(sql + str(limit)) assert len(q.response['features']) == limit, err limit = 5 q = SQL2GEE(sql + str(limit)) assert len(q.response['features']) == limit, err return
def test_STSUMMARYSTATS_with_area_restriction_from_geojson_polygon(): """First, I need to construct a simple polygon out of multipolygon data, and pass that to SQL2GEE""" expected = { u'elevation': { 'count': 99626, 'max': 489, 'mean': 345.39571999277297, 'min': 194, 'stdev': 64.2380798454734, 'sum': 34410394.0 } } gstore = "http://staging-api.globalforestwatch.org/geostore/4531cca6a8ddcf01bccf302b3dd7ae3f" r = requests.get(gstore) j = r.json() j = j.get('data').get('attributes') j['geojson']['features'][0]['geometry']['type'] = "Polygon" j['geojson']['features'][0]['geometry']['coordinates'] = j['geojson'][ 'features'][0]['geometry']['coordinates'][0][0] # Initilise an SQL2GEE query object with geojson q = SQL2GEE("SELECT ST_SUMMARYSTATS() FROM srtm90_v4", geojson=j) assert isinstance( q.geojson, ee.FeatureCollection ), "FeatureCollection wasn't created from passed Geojson data" assert q.response == expected, "Response was not equal to expected values" return
def test_STSUMMARYSTATS_with_area_restriction_via_passing_geojson_multipolygon( ): """If a geojson argument is passed to SQL2GEE it should be converted into an Earth Engine Feature Collection. This should then be used to subset the area considered for results.""" # Get a test geojson object by accessing Vizzuality's geostore expected = { u'elevation': { 'count': 118037, 'max': 489, 'mean': 326.5521573743826, 'min': 126, 'stdev': 75.69057079693977, 'sum': 38545237.0 } } gstore = "http://staging-api.globalforestwatch.org/geostore/4531cca6a8ddcf01bccf302b3dd7ae3f" r = requests.get(gstore) j = r.json() j = j.get('data').get('attributes') # Initilise an SQL2GEE query object with geojson q = SQL2GEE("SELECT ST_SUMMARYSTATS() FROM srtm90_v4", geojson=j) assert isinstance( q.geojson, ee.FeatureCollection ), "geojson data not converted to ee.FeatureCollection type" assert q.response == expected, "Area restricted response did not match expected result" return
def test_obtain_class_docstring(self): sql = 'select * from "ft:1qpKIcYQMBsXLA9RLWCaV9D0Hus2cMQHhI-ViKHo"' q = SQL2GEE(sql) dstring = q.__doc__ assert isinstance(dstring, str), "Docstring not returned" assert len(dstring) > 0, "Class docstring was empty" return
def test_fail_too_many_image_keywords(self): """An Error should be returned if multiple image keywords given. This may not be the right behaviour, will need to check this after the library is more developed.""" q = SQL2GEE('select ST_SUMMARYSTATS(*), ST_HISTOGRAM(*) from myimage') with pytest.raises(ValueError): _ = q._is_image_request return
def execute_query(json_sql, geojson=None, flags=None): logging.info('Executing Query') logging.info(json_sql) try: return SQL2GEE(json_sql, geojson=geojson, flags=None) except Exception as error: raise GEEQueryError(str(error))
def test_ST_HISTOGRAM(): """Test that a dictionary containing a list of expected length and values is returned""" sql = "SELECT ST_HISTOGRAM() FROM srtm90_v4" q = SQL2GEE(sql) num_bins = len(q.histogram['elevation']) assert num_bins == 753, "Should be 753 bins by default (set by Freedman-Diaconis method)" assert q.response['elevation'][0] == [-415.0, 14.0], 'First bin incorrect' assert q.response['elevation'][-1] == [7148.941567065073, 0.0], 'Last bin incorrect' return
def execute_query(query, geojson=None): logging.info('Executing Query: ' + query) try: q = SQL2GEE(sql=query, geojson=geojson, flags=None) response = q.response except Exception as error: raise GEEQueryError(error) return response
def test_long_group_select(self): sql = 'select count(pepe), sum(pepe), avg(pepe), first(pepe), last(pepe), max(pepe), min(pepe) from mytable' q = SQL2GEE(sql) self.assertEqual(q.group_functions, [{'function': 'COUNT', 'value': 'pepe'}, {'function': 'SUM', 'value': 'pepe'}, {'function': 'AVG', 'value': 'pepe'}, {'function': 'FIRST', 'value': 'pepe'}, {'function': 'LAST', 'value': 'pepe'}, {'function': 'MAX', 'value': 'pepe'}, {'function': 'MIN', 'value': 'pepe'}]) return
def test_identify_feature_queries(self): err = 'Unable to determine type of request for sql:' feature_qlist = [ 'select pepe from mytable', 'SELECT TOP 1 * FROM mytable ORDER BY unique_column DESC', 'select * from mytable where a > 2 and c = 2 or x <= 2'] for query in feature_qlist: q = SQL2GEE(query) assert q._is_image_request is False, ' '.join([err, q._raw]) del q return
def test_identify_image_queries(self): err = 'Unable to determine type of request for sql:' image_qlist = [ 'select ST_METADATA(*) from myimage', 'select ST_METADATA(band1) from myimage', 'select ST_METADATA(band1) from myimage', 'select ST_HISTOGRAM(*) from myimage', 'select ST_SUMMARYSTATS(*) from myimage'] for query in image_qlist: q = SQL2GEE(query) assert q._is_image_request is True, ' '.join([err, q._raw]) del q return
def test_STSUMMARYSTATS(): """Check an expected dictionary is returned via the ST_SUMMARYSTATS() keyword""" expected = { u'elevation': { 'count': 2747198, 'max': 7159, 'mean': 689.8474833769903, 'min': -415, 'stdev': 865.9582784994756, 'sum': 1859471136.0274282 } } sql = "SELECT ST_SUMMARYSTATS() FROM srtm90_v4" q = SQL2GEE(sql) q.response == expected, "Summary stats did not match expected result" return
def test_ST_HISTORGRAM_multiband_image(): expected_keys = [ u'B10', u'BQA', u'B11', u'B4', u'B5', u'B6', u'B7', u'B1', u'B2', u'B3', u'B8', u'B9' ] q = SQL2GEE("SELECT ST_HISTOGRAM() FROM LC81412332013146LGN00") assert isinstance(q.response, dict), "Dictionary was not returned as a response" assert len( q.response ) == 12, "Size of the dictionary was diffrent from expected response" assert q.response.keys( ) == expected_keys, "Expected keys in response dictionary were not returned" for key in q.response.keys(): if q.response[key] != None: assert len( q.response[key]) == 210, "Expected 210 bins in histogram" return
def test_retrieve_image_metadata(): """Test that basic raster metadata (in dictionary format) is returned when the postgis ST_METADATA() command is given. Notes: srtm90_v4 is a 90m Elevation image. """ q = SQL2GEE("SELECT ST_METADATA(*) FROM srtm90_v4") ee_meta = { u'bands': [{ u'crs': u'EPSG:4326', u'crs_transform': [0.000833333333333, 0.0, -180.0, 0.0, -0.000833333333333, 60.0], u'data_type': { u'max': 32767, u'min': -32768, u'precision': u'int', u'type': u'PixelType' }, u'dimensions': [432000, 144000], u'id': u'elevation' }], u'id': u'srtm90_v4', u'properties': { u'system:asset_size': 18827626666, u'system:time_end': 951177600000, u'system:time_start': 950227200000 }, u'type': u'Image', u'version': 1463778555689000 } assert q.response == ee_meta, 'Metadata response was not equal to expected metadata' return
def test_ST_HISTOGRAM_with_area_restriction(): """If a geojson argument is passed to SQL2GEE it should be converted into an Earth Engine Feature Collection. This should then be used to subset the area considered for results.""" # Get a test geojson object by accessing Vizzuality's geostore gstore = "http://staging-api.globalforestwatch.org/geostore/4531cca6a8ddcf01bccf302b3dd7ae3f" r = requests.get(gstore) j = r.json() j = j.get('data').get('attributes') # Initilise an SQL2GEE query object with geojson q = SQL2GEE("SELECT ST_HISTOGRAM() FROM srtm90_v4", geojson=j) assert isinstance( q.geojson, ee.FeatureCollection ), "Geojson data not converted to ee.FeatureCollection type" assert len( q.response['elevation'] ) == 101, "Returned area-restricted histogram not equal to len of expected result" flist = [freq for x, freq in q.response['elevation']] assert np.mean( flist ) == 1168.6435643564357, "Values returned from histogram dont match expected" assert q.response['elevation'][0] == [ 126.0, 8.0 ], "Returned bins don't match expected values" return
def test_var_table_where_all_equal(): sql = 'select VAR(width) from "ft:1qpKIcYQMBsXLA9RLWCaV9D0Hus2cMQHhI-ViKHo" WHERE width = 500' q = SQL2GEE(sql) assert q.response == 0.0, "Simple VAR query incorrect"
def test_first_with_where_table_query(): sql = 'select FIRST(width) from "ft:1qpKIcYQMBsXLA9RLWCaV9D0Hus2cMQHhI-ViKHo" WHERE width < 500' q = SQL2GEE(sql) assert q.response == 125.0, "FIRST with WHERE query incorrect" return
def test_var_table_query(): sql = 'select VAR(width) from "ft:1qpKIcYQMBsXLA9RLWCaV9D0Hus2cMQHhI-ViKHo"' q = SQL2GEE(sql) assert q.response == 2423.5074511457533, "Simple VAR query incorrect"
def test_avg_with_doublewhere_table_query(): sql = 'select AVG(width) from "ft:1qpKIcYQMBsXLA9RLWCaV9D0Hus2cMQHhI-ViKHo" where width > 100 and where width < 280' q = SQL2GEE(sql) assert q.response == 212.25, "Complex AVG (with compound WHERE) query incorrect" return
def test_first_table_query(): sql = 'select FIRST(width) from "ft:1qpKIcYQMBsXLA9RLWCaV9D0Hus2cMQHhI-ViKHo"' q = SQL2GEE(sql) assert q.response == 500.0, "Simple FIRST query incorrect" return
def test_sum_with_where_table_query(): sql = 'select SUM(width) from "ft:1qpKIcYQMBsXLA9RLWCaV9D0Hus2cMQHhI-ViKHo" WHERE width < 400' q = SQL2GEE(sql) assert q.response == 83306.0, "SUM with WHERE query incorrect" return
def test_avg_with_where_table_query(): sql = 'select AVG(width) from "ft:1qpKIcYQMBsXLA9RLWCaV9D0Hus2cMQHhI-ViKHo" WHERE width < 400' q = SQL2GEE(sql) assert q.response == 354.4936170212766, "AVG with WHERE query incorrect" return
def test_min_table_query(): sql = 'select MIN(width) from "ft:1qpKIcYQMBsXLA9RLWCaV9D0Hus2cMQHhI-ViKHo"' q = SQL2GEE(sql) assert q.response == 125.0, "Basic MIN query incorrect" return
def test_sum_table_query(): sql = 'select SUM(width) from "ft:1qpKIcYQMBsXLA9RLWCaV9D0Hus2cMQHhI-ViKHo"' q = SQL2GEE(sql) assert q.response == 924091.0, "Basic SUM query incorrect" return
def test_stdev_width_lt_table(): sql = 'select STDEV(width) from "ft:1qpKIcYQMBsXLA9RLWCaV9D0Hus2cMQHhI-ViKHo" where width < 500' q = SQL2GEE(sql) assert q.response == 35.01078172011275, "STDEV with WHERE LT 500 query incorrect"
def test_count_table_query_with_where_statement(): sql = 'select count(width) from "ft:1qpKIcYQMBsXLA9RLWCaV9D0Hus2cMQHhI-ViKHo" where width > 400' q = SQL2GEE(sql) assert q.response == 1677, "COUNT with WHERE query incorrect" return
def test_stdev_width_eq_table(): sql = 'select STDEV(width) from "ft:1qpKIcYQMBsXLA9RLWCaV9D0Hus2cMQHhI-ViKHo" where width = 500' q = SQL2GEE(sql) assert q.response == 0.0, "STDEV with WHERE EQ 500 query incorrect"
def test_identify_band_names(): sql = "SELECT ST_HISTOGRAM() FROM srtm90_v4" q = SQL2GEE(sql) assert q._band_names == ['elevation'] return
def test_count_table_query(): sql = 'select count(width) from "ft:1qpKIcYQMBsXLA9RLWCaV9D0Hus2cMQHhI-ViKHo"' q = SQL2GEE(sql) assert q.response == 1919, "BASIC COUNT query incorrect" return