Support for native PostgreSQL "polygon" type?

[Demitri Muna]

Hi,

Is there a way to use the native PostgreSQL (i.e. not postgis) "polygon" data type through SQLAlchemy? I'm guessing one might be able to define the model class via autoload as normal, then add some kind of custom column definition? Surely someone has done this before, but I haven't been able to find an example.

I've taken a look at geoalchemy and tried to implement that, but was getting this error:

ProgrammingError: (psycopg2.ProgrammingError) function st_asbinary(polygon) does not exist

LINE 1: ....sdss_frame.filename AS muna_sdss_frame_filename, ST_AsBinar...

                                                             ^

HINT:  No function matches the given name and argument types. You might need to add explicit type casts.

If at all possible, I'd prefer to use the native data type, even if it requires a custom adaptor. (Again, surely someone has written this?)

Cheers,

Demitri

[Ian McCullough]

Is there some compelling reason you wouldn't just install the PostGIS extensions? Assuming there is...

You could use a TEXT column to store WKT values or a BLOB/bytea column to store WKB representations, and perhaps use hybrid_attributes to manage the conversion, but realize that because (without PostGIS) the DB doesn't semantically understand these types, so you won't be able to filter against them (other than IS [NOT] NULL) or use them in any meaningful SQL expressions. You'll likely be limited to CRUD capabilities. 

Actually, now that I think about it, since you won't be able to use them in query expressions anyway, there's probably no reason to go to the extra effort of hybrid properties; just make a pure python property to do the conversion between the mapped TEXT|BLOB column and the Geometry types.

But again, trying to use geo types in PostgreSQL without PostGIS just kinda sounds like self-flagellation. :)

Ian

[Demitri Muna]

Hi Ian,

Thanks for the reply.

On Saturday, September 5, 2015 at 11:53:39 AM UTC-4, Ian McCullough wrote:

Is there some compelling reason you wouldn't just install the PostGIS extensions? Assuming there is...

There is; I can't use any of the functionality. My use case is astronomical data where all values are points on a sphere. I can't use the various projections that GIS provides. I investigated the possibility of using GIS tools for astronomical data a few years back, but found it wasn't really appropriate. I actually came across this which I found amusing:

http://lists.osgeo.org/pipermail/postgis-devel/2009-March/005184.html

 

Actually, now that I think about it, since you won't be able to use them in query expressions anyway, there's probably no reason to go to the extra effort of hybrid properties; just make a pure python property to do the conversion between the mapped TEXT|BLOB column and the Geometry types.

PostrgreSQL has a native "polygon" data type that I'd like to use:

http://www.postgresql.org/docs/current/static/datatype-geometric.html

There is a library that is specifically designed for astronomical data (which indexes points on a sphere):

https://github.com/segasai/q3c

and uses the polygon data type in this function:

q3c_poly_query(ra, dec, poly) -- returns true if ra, dec is within
  the postgresql polygon poly.

This is all I really need. I imagine my class will look something like this:

class Field(Base):

    __tablename__ = 'field'

    __table_args__ = {'autoload' : True}

    polygon_column = ???

Where I can do:

f = Field()

f.polygon = ???

I just don't know what to put into the '???'s.

Thanks,

Demitri

[Mike Bayer]

SQLAlchemy doesn't do much else with types at the most basic level other than pass the data through to the DBAPI, in this case hopefully psycopg2.  Feel free to set the column type to NullType and just pass through strings, assuming that's what psycopg2 does here by default, or if you'd like to define your own type that translates some Python value to what psycopg2 expects here, there is UserDefinedType as well as TypeDecorator: http://docs.sqlalchemy.org/en/rel_1_0/core/custom_types.html#types-custom

qc3_poly_query would be a SQL function; any function name is available from func: http://docs.sqlalchemy.org/en/rel_1_0/core/sqlelement.html?highlight=func#sqlalchemy.sql.expression.func

Thanks,

Demitri

--
You received this message because you are subscribed to the Google Groups "sqlalchemy" group.
To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+...@googlegroups.com.
To post to this group, send email to sqlal...@googlegroups.com.
Visit this group at http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.

[Demitri Muna]

Hi Michael,

On Monday, September 7, 2015 at 5:40:02 PM UTC-4, Michael Bayer wrote:

SQLAlchemy doesn't do much else with types at the most basic level other than pass the data through to the DBAPI, in this case hopefully psycopg2.  Feel free to set the column type to NullType and just pass through strings, assuming that's what psycopg2 does here by default, or if you'd like to define your own type that translates some Python value to what psycopg2 expects here, there is UserDefinedType as well as TypeDecorator: http://docs.sqlalchemy.org/en/rel_1_0/core/custom_types.html#types-custom

 

That's perfect, thanks. I decided to try a TypeDecorator, and it's working for me. For anyone else who comes across this, I did this:

class Field(Base):

    __tablename__ = 'field'

    __table_args__ = {'autoload' : True}

    polygon_  = Column('polygon', PGPolygon)

The column name in the database is "polygon"; I couldn't figure out how to override it after autoload, so I created a new column property mapped to the same column. (I'd be happy to hear a better way to do this.) I then created a new file that handles the translation (pasted below). One should be able to easily expand this to add the other native PostgreSQL geometric data types.

Cheers,

Demitri

---

'''

These classes define native PostgreSQL geometry types to be used with SQLAlchemy.

Ref: http://www.postgresql.org/docs/9.4/static/datatype-geometric.html

'''

from sqlalchemy.types import TypeDecorator, VARCHAR

class PGPolygon(TypeDecorator):

    """Represents the native polygon data type in PostgreSQL (i.e. *not* PostGIS).

    Usage::

        polygonColumnName = Column('polygon_column_name', PGPolygon)

    """

    impl = VARCHAR

    def process_bind_param(self, value, dialect):

        '''

        Take the object and convert it into a string to be entered into the database.

        The value should be in the form of a Python list of tuples, e.g.

        [ (x1,y1), (x2,y2), (x3,y3), ... ]

        '''

        if value is not None:

            value = "({0})".format(",".join([str(x) for x in value]))

        return value

    def process_result_value(self, value, dialect):

        '''

        Take the polygon value from the database and convert it into a list of point tuples.

        

        The incoming format looks like this: '((12,34),(56,78),(90,12))'

        '''

        if value is not None:

            polygon = list()

            for point in value[1:-1].split("),("): # also strip outer single quotes

                point = point.lstrip("(") # remove extra "(" ")" (first and last elements only)

                point = point.rstrip(")")

                x, y = point.split(",")

                polygon.append((float(x), float(y)))

            value = polygon

        return value

[Alec Benzer]

Demitri, any plans (or previous attempts) to integrate this into SQLAlchemy proper?