passing a multipolygon rather than the boundary box via a Crate DB table

Question

I have a geopandas dataframe called geodf. The geometry for each row in that dataframe is a multipolygon. For instance, if we consider the first row in the geometry column via the geodataframe:

bound = geodf['geometry'].iloc[0]
print(bound)

it looks something like this:

MULTIPOLYGON (((-86.46228799973933 34.31021100007911, -86.46447100007661 34.31018399970606, -86.46447800010341 34.31197299998977, -86.4623920000716 34.31198799958079

The multipolygon itself is massive so I tried to create a box for it instead

box = shapely.geometry.box(*geodf.bounds)

and then I passed box through the following query via our Crate DB server:

query = """
                 SELECT geohash, grid,shape 
                 FROM geo 
                 WHERE layer = 'pop' 
                AND MATCH(shape, '{}') USING within;
          """.format(box)
geoCursor.execute(query)
results = geoCursor.fetchall()
District = gpd.GeoDataFrame(results, columns = ['geohash', 'grid','shape'])

where I pass the polygonbox in the query above via .format(box)

What I would like to do instead is pass bound instead of box in the query above(note that the query above works for box). However, when I try to pass bound I get the following error:

ProgrammingError: SQLActionException[UnhandledServerException: java.lang.IllegalArgumentException: Cannot convert Map "{type=MultiPolygon, coordinates=[[[[D@2b59d486}" to shape]

I wasn't able to diagnose error above. I was wondering why bounds does not work and how I could might make it work? We don't want to use a boundary box because there's a lot of excess area that is not contained in our multipolygon boundary

score 0 · Answer 1 · answered Feb 12 '18 at 10:28

You could represent the multipolygon as dict that resembles the geoJSON format and then use parameter substitution. I'm not familiar with shapely, so I can't comment on how you get the dict representation, but with a manually crafted dictionary it would look as follows:

multipolygon = {
    "type": "MultiPolygon",
    "coordinates": [
        [
            [
                [ 40.0, 40.0 ],
                [ 20.0, 45.0 ],
                [ 45.0, 30.0 ],
                [ 40.0, 40.0 ]
            ]
        ],
        [
            [
                [ 20.0, 35.0 ],
                [ 10.0, 30.0 ],
                [ 10.0, 10.0 ],
                [ 30.0, 5.0 ],
                [ 45.0, 20.0 ],
                [ 20.0, 35.0 ]
            ],
            [
                [ 30.0, 20.0 ],
                [ 20.0, 15.0 ],
                [ 20.0, 25.0 ],
                [ 30.0, 20.0 ]
            ]
        ]
    ]
}

c.execute('select * from t where match(x, ?) using within', (multipolygon, ))
print(c.fetchall())

passing a multipolygon rather than the boundary box via a Crate DB table

1 Answers1