Changeset 807 for trunk


Ignore:
Timestamp:
Feb 1, 2016, 4:28:12 AM (4 years ago)
Author:
cito
Message:

Add another typecasting example to the docs

In the example, we import circles from PostgreSQL to SymPy?
to do calculations on them that are not possible within PostgreSQl.

Location:
trunk/docs/contents
Files:
2 edited

Legend:

Unmodified
Added
Removed
  • trunk/docs/contents/pg/adaptation.rst

    r799 r807  
    299299:meth:`set_typecast`, you may need to run ``db.dbtypes.reset_typecast()``
    300300to make these changes effective on connections that were already open.
     301
     302As one last example, let us try to typecast the geometric data type ``circle``
     303of PostgreSQL into a `SymPy <http://www.sympy.org>`_ ``Circle`` object.  Let's
     304assume we have created and populated a table with two circles, like so:
     305
     306.. code-block:: sql
     307
     308    CREATE TABLE circle (
     309        name varchar(8) primary key, circle circle);
     310    INSERT INTO circle VALUES ('C1', '<(2, 3), 3>');
     311    INSERT INTO circle VALUES ('C2', '<(1, -1), 4>');
     312
     313With PostgreSQL we can easily calculate that these two circles overlap::
     314
     315    >>> q = db.query("""SELECT c1.circle && c2.circle
     316    ...     FROM circle c1, circle c2
     317    ...     WHERE c1.name = 'C1' AND c2.name = 'C2'""")
     318    >>> q.getresult()[0][0]
     319    True
     320
     321However, calculating the intersection points between the two circles using the
     322``#`` operator does not work (at least not as of PostgreSQL version 9.5).
     323So let' resort to SymPy to find out.  To ease importing circles from
     324PostgreSQL to SymPy, we create and register the following typecast function::
     325
     326    >>> from sympy import Point, Circle
     327    >>>
     328    >>> def cast_circle(s):
     329    ...     p, r = s[1:-1].split(',')
     330    ...     p = p[1:-1].split(',')
     331    ...     return Circle(Point(float(p[0]), float(p[1])), float(r))
     332    ...
     333    >>> pg.set_typecast('circle', cast_circle)
     334
     335Now we can import the circles in the table into Python simply using::
     336
     337    >>> circle = db.get_as_dict('circle', scalar=True)
     338
     339The result is a dictionary mapping circle names to SymPy ``Circle`` objects.
     340We can verify that the circles have been imported correctly:
     341
     342    >>> circle['C1']
     343    Circle(Point(2, 3), 3.0)
     344    >>> circle['C2']
     345    Circle(Point(1, -1), 4.0)
     346
     347Finally we can find the exact intersection points with SymPy:
     348
     349    >>> circle['C1'].intersection(circle['C2'])
     350    [Point(29/17 + 64564173230121*sqrt(17)/100000000000000,
     351        -80705216537651*sqrt(17)/500000000000000 + 31/17),
     352     Point(-64564173230121*sqrt(17)/100000000000000 + 29/17,
     353        80705216537651*sqrt(17)/500000000000000 + 31/17)]
  • trunk/docs/contents/pgdb/adaptation.rst

    r799 r807  
    268268
    269269    >>> pgdb.set_typecast('jsonb', cast_json)
     270
     271As one last example, let us try to typecast the geometric data type ``circle``
     272of PostgreSQL into a `SymPy <http://www.sympy.org>`_ ``Circle`` object.  Let's
     273assume we have created and populated a table with two circles, like so:
     274
     275.. code-block:: sql
     276
     277    CREATE TABLE circle (
     278        name varchar(8) primary key, circle circle);
     279    INSERT INTO circle VALUES ('C1', '<(2, 3), 3>');
     280    INSERT INTO circle VALUES ('C2', '<(1, -1), 4>');
     281
     282With PostgreSQL we can easily calculate that these two circles overlap::
     283
     284    >>> con.cursor().execute("""SELECT c1.circle && c2.circle
     285    ...     FROM circle c1, circle c2
     286    ...     WHERE c1.name = 'C1' AND c2.name = 'C2'""").fetchone()[0]
     287    True
     288
     289However, calculating the intersection points between the two circles using the
     290``#`` operator does not work (at least not as of PostgreSQL version 9.5).
     291So let' resort to SymPy to find out.  To ease importing circles from
     292PostgreSQL to SymPy, we create and register the following typecast function::
     293
     294    >>> from sympy import Point, Circle
     295    >>>
     296    >>> def cast_circle(s):
     297    ...     p, r = s[1:-1].rsplit(',', 1)
     298    ...     p = p[1:-1].split(',')
     299    ...     return Circle(Point(float(p[0]), float(p[1])), float(r))
     300    ...
     301    >>> pgdb.set_typecast('circle', cast_circle)
     302
     303Now we can import the circles in the table into Python quite easily::
     304
     305    >>> circle = {c.name: c.circle for c in con.cursor().execute(
     306    ...     "SELECT * FROM circle").fetchall()}
     307
     308The result is a dictionary mapping circle names to SymPy ``Circle`` objects.
     309We can verify that the circles have been imported correctly:
     310
     311    >>> circle
     312    {'C1': Circle(Point(2, 3), 3.0),
     313     'C2': Circle(Point(1, -1), 4.0)}
     314
     315Finally we can find the exact intersection points with SymPy:
     316
     317    >>> circle['C1'].intersection(circle['C2'])
     318    [Point(29/17 + 64564173230121*sqrt(17)/100000000000000,
     319        -80705216537651*sqrt(17)/500000000000000 + 31/17),
     320     Point(-64564173230121*sqrt(17)/100000000000000 + 29/17,
     321        80705216537651*sqrt(17)/500000000000000 + 31/17)]
Note: See TracChangeset for help on using the changeset viewer.