Пример #1
0
 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
Пример #2
0
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
Пример #3
0
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
Пример #4
0
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
Пример #5
0
 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
Пример #6
0
 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))
Пример #8
0
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
Пример #9
0
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
Пример #10
0
 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
Пример #11
0
 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
Пример #12
0
 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
Пример #13
0
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
Пример #14
0
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
Пример #15
0
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
Пример #16
0
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
Пример #17
0
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"
Пример #18
0
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
Пример #19
0
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"
Пример #20
0
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
Пример #21
0
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
Пример #22
0
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
Пример #23
0
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
Пример #24
0
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
Пример #25
0
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
Пример #26
0
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"
Пример #27
0
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
Пример #28
0
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"
Пример #29
0
def test_identify_band_names():
    sql = "SELECT ST_HISTOGRAM() FROM srtm90_v4"
    q = SQL2GEE(sql)
    assert q._band_names == ['elevation']
    return
Пример #30
0
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