types need process_literal_param() to compile sql

[DeoLeung]

Will be best to have process_literal_param defined in types to support compile sql with param

http://docs.sqlalchemy.org/en/latest/faq/sqlexpressions.html
http://docs.sqlalchemy.org/en/latest/core/custom_types.html#sqlalchemy.types.TypeDecorator.process_literal_param

at the moment if I compile the target sql expression.compile(pool, compile_kwargs={"literal_binds": True}), I get

SELECT poi.lng, poi.lat
FROM poi
WHERE ST_Intersects(poi.geometry, ST_GeomFromEWKB(NULL))

will be best if the NULL part could be wkt or wkb

[elemoine]

Could you please provide a full example demonstrating this?

I tried the following, but I get a different output:

from sqlalchemy import Column, Integer, create_engine, MetaData
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import sessionmaker
from geoalchemy2 import Geometry
from geoalchemy2 import WKBElement

engine = create_engine('postgresql://gis:gis@localhost/gis', echo=True)
metadata = MetaData(engine)
Base = declarative_base(metadata=metadata)


class Poi(Base):
    __tablename__ = 'poi'
    id = Column(Integer, primary_key=True)
    geom = Column(Geometry('POLYGON'))


Session = sessionmaker()
session = Session()

e = WKBElement(b'\x01\x02', extended=True)
q = session.query(Poi).filter(Poi.geom.ST_Intersects(e))
print(q.statement.compile(engine, compile_kwargs={'literal_binds': True}))

I tried to add a literal_processor function to the _GISType class (our UserDefinedType class), but that did not make a difference. The function is not even called.

This is what I've tried:

    def literal_processor(self, dialect):
        def process(value):
            return str(value)
        return process

Maybe @zzzeek can help with that :)

[zzzeek]

your _GISType isn't involved in the above test script. The WKBElement is a Function and it just passes that bytestring in as one of the arguments to the function, so it's of type "string":

-> q = session.query(Poi).filter(Poi.geom.ST_Intersects(e))
(Pdb) e.clauses.clauses
[BindParameter('%(140651988091088 ST_GeomFromEWKB)s', '\x01\x02', type_=String())]

I tried shoving Geometry in there but that type seems to want to wrap with its own function and stuff, not sure what effect you're looking for.

literal_binds works "fine" in a test here:

with:

SELECT poi.id, ST_AsEWKB(poi.geom) AS geom 
FROM poi 
WHERE ST_Intersects(poi.geom, ST_GeomFromEWKB('��'))

without:

SELECT poi.id, ST_AsEWKB(poi.geom) AS geom 
FROM poi 
WHERE ST_Intersects(poi.geom, ST_GeomFromEWKB(%(ST_GeomFromEWKB_1)s))

if you want NULL as the keyword and not a bound param, use sqlalchemy.null():

e = WKBElement(b'\x01\x02', extended=True)
q = session.query(Poi).filter(Poi.geom.ST_Intersects(null()))
print(q.statement.compile(engine, compile_kwargs={'literal_binds': True}))

output:

SELECT poi.id, ST_AsEWKB(poi.geom) AS geom 
FROM poi 
WHERE ST_Intersects(poi.geom, NULL)

[elemoine]

Thanks @zzzeek.

@DeoLeung, feel free to provide more detail on your use-case and what you expect from geoalchemy.

[DeoLeung]

here's my sample code

import asyncio
loop = asyncio.get_event_loop()


from geoalchemy2 import Geometry
from geoalchemy2 import func
from sqlalchemy import MetaData, Table, Column, BigInteger
from sqlalchemy.sql import select
from aiopg.sa import create_engine

meta = MetaData()

table = Table('poi',
              meta,
              Column('id', BigInteger, primary_key=True),
              Column('geometry', Geometry))
pool = loop.run_until_complete(
        create_engine(
            "you_db_str",
            minsize=1,
            maxsize=100
        )
    )


async def extract_sa(sql):
  """Execute the SQLAlchemy expression and get return value"""
  async with pool.acquire() as conn:
    print(sql.compile(pool, compile_kwargs={"literal_binds": True}))
    proxy = await conn.execute(sql)
    return await proxy.fetchone()

sql = select([table.c.geometry]).where(table.c.id == 26420100099)
geometry = loop.run_until_complete(extract_sa(sql)).geometry
print(geometry)
print(type(geometry))
sql = select([table.c.id]).where(func.ST_Intersects(table.c.geometry, geometry))
print(loop.run_until_complete(extract_sa(sql)))

output

SELECT ST_AsEWKB(poi.geometry) AS geometry
FROM poi
WHERE poi.id = 26420100099
01010000008330b77bb9935c40edef6c8fde8c3e40
<class 'geoalchemy2.elements.WKBElement'>
SELECT poi.id
FROM poi
WHERE ST_Intersects(poi.geometry, ST_GeomFromEWKB(NULL))
(26420100099,)

I would expect the NULL to be something more verbose, wkt or wkb. As NULL makes my debug a bit confusing, though I actually get the expected result.

[elemoine]

@DeoLeung I don't understand why you get NULL in the SQL string in your case. I'll try to do more tests and see if I can get the same.

[adrien-berchet]

Closed due to a lack of activity.
Feel free to reopen.