Inserts with shape.from_geom or WKB?

[youngpm]

Hi,

Thanks for the great extension to SQLAlchemy, just starting to get my feet wet!

It's unclear to me if its possible to insert a (Shapely) geometry into a table using shape.from_geom, or WKB for that matter using the Core. I've tried something like this,

from shapely import geometry
geom = geometry.box(0, 0, 1, 1)

# Do some stuff...

test_table = Table('test', metadata, Column('extent', Geometry('POLYGON')))
ins = test_table.insert()
connection.execute(ins, extent=shape.from_shape(geom))

and I get a ProgrammingError: (psycopg2.ProgrammingError) can't adapt type 'WKBElement' [SQL: 'INSERT INTO test_table (extent) VALUES (ST_GeomFromEWKT(%(extent)s))']. I'm guessing this is because from_shape returns a WKBElement rather than a WKTElement.

Using this instead works,

connection.execute(ins, extent=geom.wkt)

as well as constructing a WKTElement directly and using that.

Am I just doing it wrong or is this expected behavior?

Thanks!

[elemoine]

This is unexpected. What version of GeoAlchemy2 do you use?

This is what have with the current master of GeoAlchemy2:

>>> from shapely import geometry
>>> from sqlalchemy import Table, Column, MetaData
>>> from geolachemy2 import Geometry
>>>
>>> geom = geometry.box(0, 0, 1, 1)
>>> element = shape.from_shape(geom)
>>>
>>> test_table = Table('test', MetaData(), Column('extent', Geometry('POLYGON')))
>>> ins = test_table.insert().values(extent=element)
>>> str(ins)
'INSERT INTO test (extent) VALUES (ST_GeomFromWKB(:ST_GeomFromWKB_1, :ST_GeomFromWKB_2))'

[youngpm]

Thanks for the quick response!

I'm on SQLAlchemy 1.0.12 and GeoAlchemy2 0.2.16 (postres 9.5 and postgis 2.2).

In the snippet below, the first three inserts work (like in your example) and the second will issue the error. I guess in the last style of insert, the query string is already set and can't be changed to use ST_GeomFromWKB?

#!/usr/bin/env python
import os

from shapely import geometry
from sqlalchemy import (MetaData, create_engine, Table, Column)
from geoalchemy2 import Geometry, shape, elements

metadata = MetaData()

if __name__ == "__main__":

    db_conn_str = "postgresql+psycopg2://test:test@localhost:5432/test-db"

    test_table = Table('test', metadata, Column('extent', Geometry('POLYGON')))
    engine = create_engine(db_conn_str)
    metadata.create_all(engine)

    geom = geometry.box(0, 0, 1, 1)
    element = shape.from_shape(geom)

    connection = engine.connect()

    # This will work.
    ins = test_table.insert().values(extent=element)
    connection.execute(ins)

    # So will this.
    ins = test_table.insert()
    connection.execute(ins, extent=geom.wkt)

    # And this.
    ins = test_table.insert()
    connection.execute(ins, extent=elements.WKTElement(geom.wkt))

    # This will break.
    ins = test_table.insert()
    connection.execute(ins, extent=element)

[youngpm]

I was tinkering a bit this morning to see if I could work around the issue without just converting to WKT. I can get a single row to insert via the .values construct as above, but my use case is that I want to bulk insert a bunch of geometries.

According to the SQLAlchemy docs here,

"To emit an INSERT statement against multiple rows, the normal method is to pass a multiple values list to the Connection.execute() method, which is supported by all database backends and is generally more efficient for a very large number of parameters."

Basically I was hoping to be able to insert rows_wkb as defined below, but no luck!

from shapely import geometry
from sqlalchemy import (MetaData, create_engine, insert, Table, Column)
from geoalchemy2 import Geometry, shape

metadata = MetaData()

if __name__ == "__main__":

    db_conn_str = "postgresql+psycopg2://test:test@localhost:5432/test-db"

    test_table = Table('test', metadata, Column('extent', Geometry('POLYGON')))
    engine = create_engine(db_conn_str)
    metadata.create_all(engine)

    geom = geometry.box(0, 0, 1, 1)
    rows_wkt = [{'extent':geom.wkt}]*10
    rows_wkb = [{'extent':shape.from_shape(geom)}]*10

    connection = engine.connect()
    ins = insert(test_table)
    connection.execute(ins, rows_wkt) # This works
    connection.execute(ins, rows_wkb) # This doesn't

[elemoine]

Thanks for the detailed report. I don't know where the bug is right now.

How about this:

ins = test_table.insert().values([
    {"extent": shape.from_shape(shape1)},
    {"extent": shape.from_shape(shape2)}
])
connection.execute(ins)

?

Although that may not be as performant as passing the values to execute…

[elemoine]

http://docs.sqlalchemy.org/en/rel_1_0/core/tutorial.html#insert-expressions includes notes that are relevant to this issue. In particular:

since the data consists of literal values, SQLAlchemy automatically generates bind parameters for them

So when using

ins = test_table.insert().values(extent=shape.from_shape(s))

SQLAlchemy will not generate a bind parameter for extent, because the value specified for this column is not a literal (it's a WKBElement).

But when using

connection.execute(ins, extent=shape.from_shape(s))

the value passed for extent will be used as a bound parameter value, so the Geometry type's bind_expression function will be used, leading to the can't adapt type 'WKBElement' error.

Bottom line is: when using execute the values passed as bind parameters must be literals (so WKT or EWKT strings in the case of geometries).

So if you really want to pass values to execute you have no choice but using this (as you found out already):

connection.execute(ins, extent=shape.from_shape(s).wkt))

I wish I had a better answer to you, but I don't :)

Please tell us if you find more information on that topic!

[youngpm]

Thanks for the help! I'll close this issue out. I'll let you know if I figure out more from the sqlalchemy side of things!

[petrus-jvrensburg]

Thanks @youngpm & @elemoine, this was useful 👍

[richardsmd]

FWIW connection.execute(ins, extent=shape.from_shape(s).wkt)) yields an error for me Error: ProgrammingError("(psycopg2.ProgrammingError) can't adapt type '_FunctionGenerator'",)

However, connection.execute(ins, extent=shape.from_shape(s).desc)) works just fine.

Larger example:

# Process some telemetry data for a vehicle and save in the database
kw = []
for lat, lon, speed, heading in my_telemetry_data:
  point = geoalchemy2.shape.from_shape(shapely.geometry.Point(lon, lat), srid=WGS84).desc 
  d = dict(point=point,
           speed=speed,
           heading=heading)
  kw.append(d)

# standard sqlalchemy engine instance
conn = engine.connect()
transaction = conn.begin()
# I'm using inline inserts because the table uses inheritance, and in the insert trigger returns NULL.
# Without inline=True, sqlalchemy freaks out
insert = Telemetry.__table__.insert(inline=True)

conn.execute(insert, kw)
transaction.commit()
conn.close()

If anyone googles this issue (as I did) know that this is not safe for Raster elements.

[jshearer]

Question for everybody here: I encountered this same issue recently, and am not quite sure why it's happening, which does bother me. That being said... is there any reason not to simply teach psycopg2 about WKBElements?

Here's my working solution:

from psycopg2.extensions import adapt, register_adapter, AsIs
from geoalchemy2.elements import WKBElement

def WKBElementAdapter(element):
    return AsIs(adapt(element.desc).getquoted())

register_adapter(WKBElement, WKBElementAdapter)

The code to teach psycopg2 about a new type came from their docs

This feels like it's too good to be true, and nobody has even mentioned it yet, so is there some catch I'm missing?

Thanks! :)

[richardsmd]

@jshearer

I encountered this same issue recently, and am not quite sure why it's happening

connection.execute operates at a relatively low-level of sqlalchemy. sqlalchemy provides a variety of types and associated adapters that are all internal to sqlalchemy, and in normal usage will correctly marshal between native database data types and the ORM types we normally deal with in python code.

The error is generated because we've bypassed the layer of sqlalchemy that knows how to correctly adapt this, leaving pyscopg2 to figure it out, which it can't by default.

That being said... is there any reason not to simply teach psycopg2 about WKBElements?

I think it's a great solution!

It is slightly strange in that you're teaching psycopg2 to adapt WKBElement instead of sqlachemy, but I don't think there's any harm in that.