Issue: Creating a custom field that automatically converts between db types and python types
Author: larsks
State: closed
Created: 2015-11-18 15:48:02

I want to use Peewee to manage a collection of GIS data.  I've defined some custom fields to handle the PostGIS `geometry`  type:

```
class GeomField(Field):
    db_field = 'geometry'

    def __init__(self, geom_type=None, geom_srid=None, **kwargs):
        self.geom_srid = geom_srid
        self.geom_type = geom_type

        super(GeomField, self).__init__(**kwargs)
        if geom_type:
            if geom_srid:
                self.db_field = 'geometry(%s, %s)' % (geom_type, geom_srid)
            else:
                self.db_field = 'geometry(%s)' % (geom_type, )


class PointField(GeomField):
    def __init__(self, geom_srid=None, **kwargs):
        super(PointField, self).__init__(
            geom_type='Point',
            geom_srid=geom_srid,
            **kwargs)
```

This works fine so far.  I would like to be able to set a `PointField`
to a Python tuple and have it converted automatically to a `geometry`
value (and I want the inverse as well).  I started with this:

```
def db_value(self, value):
    return fn.st_setsrid(
      fn.st_makepoint(value[0],value[1],self.geom_srid))
```

But I guess that Peewee doesn't interpolate these functions at the
right point, because that gets me:

```
ValueError: only bytes values expected, got Func
```

So instead I've done this:

```
def python_value(self, value):
    res = database.execute_sql('select st_x(%s), st_y(%s)',
                               (value, value))
    return res.fetchone()

def db_value(self, value):
    res = database.execute_sql(
        'select st_setsrid(st_makepoint(%s,%s), %s)',
        (value[0], value[1], self.geom_srid))
    return res.fetchone()
```

This works, but it seems less than elegant and I am curious if I am
missing a simpler solution.


I would just create wrapper methods that do what you want, since by the time you're calling `python_value()` it's technically too late as the value has already been pulled from the database. As to `db_value()` doing the inverse, that's not really the intended purpose of the method.

Since you're using Postgres, you can also register custom adapters: https://github.com/coleifer/peewee/blob/master/playhouse/postgres_ext.py#L106-L120


@larsks FYI, I have the same need, and started working on https://github.com/yohanboniface/psycopg-postgis
Example usage: https://github.com/etalab/ban/blob/master/ban/db/fields.py#L16
I may wrap all this in a `peewee-postgis` module at some point, but for now it's experimental and I just need Point in my app. So work in progress… ;)


Thanks for the pointer!  I ultimately followed @coleifer suggestion and set up an adapter, which is pretty much straight out of the psycopg docs:

```
Point = namedtuple('Point', ['x', 'y', 'srid'])

def adapt_point(point):
    x = adapt(point.x).getquoted()
    y = adapt(point.y).getquoted()
    srid = adapt(point.srid).getquoted()
    return AsIs("st_setsrid(st_makepoint(%s, %s), %s)" % (x,y,srid))

register_adapter(Point, adapt_point)
```

And then in my PointField class:

```
def db_value(self, value):                                                  
    if isinstance(value, tuple):                                            
        return Point(value[0], value[1], self.geom_srid)                    
    else:                                                                   
        return value                                                        
```

And this Just Works as far as storing data goes.  It doesn't handle
converting the stored data back into Python tapes on load, because
this seemed a bit trickier, since it involves querying the database
for oid information.

I also discovered the [shapely](https://pypi.python.org/pypi/Shapely) module, which has functions for
handling WKT/WKB data in Python, which would probably make this all
much simpler (and less tied to psycopg-specific features).


>  It doesn't handle
> converting the stored data back into Python tapes on load, because
> this seemed a bit trickier, since it involves querying the database
> for oid information.

Yep, that's what psycopg-postgis does. I need the conversion also on data fetching :)

> I also discovered the shapely module, which has functions for
> handling WKT/WKB data in Python

AFAIK, it does not handle srid. :/


@coleifer is there a way to override the fn called when performing a read?

IE, instead of 

```sql
select foo, bar, baz from table
```

You could specify a wrapper fn for a field, so you could so something like

```sql
select foo, bar, ST_AsText(baz) from table
```

which would alleviate a lot of the trouble here (I too am trying to figure out how to read/write a simple `geometry(Point)` field with postgis.

Have you tried implementing the `db_value` method such that it calls the function when passing the value into the db?

```python
class GeometryField(WhateverField):
    def db_value(self, value):
        return fn.ST_AsText(value)
```

FYI, for those curious, the adapt code linked above was lost in recent code changes but I found it here in a 2.x release: https://github.com/coleifer/peewee/blob/761f9144a0e17381147a81658019cffe14c118ca/playhouse/postgres_ext.py#L106-L120

As of commit 090dc61d08929bec863fc08da342284116807a42, the OP's original attempt should be working:

```python
def db_value(self, value):
    return fn.st_setsrid(
      fn.st_makepoint(value[0],value[1],self.geom_srid))
```

Although if the value is a list, it may be necessary to wrap it in `AsIs()` additionally.

First off, I want to say thanks to @coleifer for writing and maintaining such a great library.

I think what @whalesalad is getting at is the same problem / hassle I'm running into: the extension (e.g. PostGIS) stores a different native type in the database, so when you want to use it outside the DB you need to serialize it to something like WKT. This has to be done at the time of query generation, as in their example:

`select foo, bar, ST_AsText(baz) from table`

I initially thought I could use fn.AsText (I'm trying this with Spatialite) in a `python_value` method, but as you pointed out this doesn't run until after the query and just ends up returning a useless function object.

If I understand right, the example given by @coleifer would wrap geometry fields with ST_AsText in INSERT and UPDATE statements in addition to SELECTS:

    class GeometryField(WhateverField):
        def db_value(self, value):
            return fn.ST_AsText(value)

I think this would break INSERT and UPDATE statements attempting to store geometries in db-native format - it would write strings instead.

At the moment, I have a utility function that checks if a field name is 'geometry' and if so, wraps it in fn.AsText:

    def fix_geom_field(table, query_fields):
        fields = []
        for field_name in table._meta.sorted_field_names:
            if field_name in query_fields:
                field = getattr(table, field_name)
                if field_name == 'geometry':
                    field = fn.AsText(field)
                fields.append(field)

This gets run on a list of field names that get converted into field instances associated with the table of interest, and wraps a column names geometry (this could also work with introspection into field types). The results of this query can then be used in ORM selects:

    Table.select(*fix_geom_field(Table, query_fields))

Experienced developer learning PeeWee for the first time. Thank you so much, @coleifer.

Has this updated in the last year? In @nbolten 's implementation, it does seem as though the map from field -> fn.AsText(field) ought to be able to be inserted into the GeometryField declaration class itself. 

This appears to create the same outcome as @nbolten , and allows the containment to be on the field itself. It is unclear to me whether _returning is only those fields selected by the select() method, and how this will affect performance in more complex queries. Posting here, though.

```
class PointField(pw.Field):
    """Geometry field type Point, conforming to MySQL 8.0+, format is WKT: 'POINT(18 23)'"""

    # Field type in MySQL is Point with a capital P.
    field_type = 'Point'

    def python_value(self, value):
        return value

    def db_value(self, ewkt):
        """Converts EWKT format to MySQL database requirements, e.g., SRID=3857;POINT(10,10)"""

        # Does the ewkt start with "SRID=XXXX;...".
        # If so, remove the value from the geometry and parse/extract the srid
        s = ewkt.split(";")
        if s[0].startswith("SRID="):
            geo = ";".join(s[1:])
            srid = s[0].split("=")[1]
            return pw.fn.ST_GeomFromText(geo, srid)
        else:
            return pw.fn.ST_GeomFromText(ewkt)

    def _db_wrangler(self, field):
        return pw.fn.ST_AsText(field).alias(field.name)
```

```
query = PadsLocations.select(PadsLocations)
for r_i, r in enumerate(query._returning):
    if hasattr(r, '_db_wrangler'):
        query._returning[r_i] = r._db_wrangler(r)
```

@davidbernat Thanks for your post - I finally got lat/lon in and out of a PeeWee PostGIS database (which I barely understand) with your code. I clearly still don't understand something, though: `ST_GeomFromText` returns `Geometry`, not `Point`, and I only get your code to work by specifying `field_type = 'Geometry'`.