Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Are ST_AsGeoJSON versions supported? #217

Closed
erictheise opened this Issue Mar 25, 2019 · 9 comments

Comments

Projects
None yet
2 participants
@erictheise
Copy link

commented Mar 25, 2019

I'm revisiting an old application and realizing that I'm outputting an absurd number of decimal places for my randomly generated lat/lngs. I thought I'd be able to specify precision as

db.session.scalar(func.ST_AsGeoJSON(feature_geom, 6))

which corresponds to Version 2 but I get

sqlalchemy.exc.ProgrammingError: (psycopg2.ProgrammingError) function st_asgeojson(unknown, integer) is not unique
LINE 1: SELECT ST_AsGeoJSON('0101000020AD10000067732A769B7452C057DED...
^
HINT:  Could not choose a best candidate function. You might need to add explicit type casts.

[SQL: SELECT ST_AsGeoJSON(%(ST_AsGeoJSON_2)s, %(ST_AsGeoJSON_3)s) AS "ST_AsGeoJSON_1"] 
[parameters: {'ST_AsGeoJSON_2': '0101000020AD10000067732A769B7452C057DED192AF624440', 'ST_AsGeoJSON_3': 6}]
(Background on this error at: http://sqlalche.me/e/f405)

Thought I'd ask if this should work before digging further. This is a Flask application using PostgreSQL/PostGIS and ... well, here's my requirements.txt:

aniso8601==6.0.0
click==6.7
Flask==1.0.2
Flask-RESTful==0.3.6
Flask-SQLAlchemy==2.3.2
GeoAlchemy2==0.6.1
gunicorn==19.9.0
honcho==1.0.1
infinity==1.4
intervals==0.8.1
itsdangerous==1.1.0
Jinja2==2.10
MarkupSafe==1.1.1
numpy==1.16.2
psycopg2==2.7.7 --no-binary psycopg2
python-dateutil==2.8.0
python-dotenv==0.10.1
pytz==2018.9
six==1.12.0
SQLAlchemy==1.3.1
SQLAlchemy-Utils==0.33.11
Werkzeug==0.15.1

Thanks in advance for any insights.

@elemoine

This comment has been minimized.

Copy link
Member

commented Mar 25, 2019

What is feature_geom in the above code snippet? Can you please come up with a complete test case?

@elemoine

This comment has been minimized.

Copy link
Member

commented Mar 25, 2019

For example, the following seems to work in my case.

Python code:

from sqlalchemy import create_engine, select, func
from geoalchemy2 import functions  # NOQA


engine = create_engine('postgresql://elemoine@localhost/gis', echo=True)

results = engine.execute(select([func.ST_AsGeoJSON(func.ST_MakePoint(5.55555, 45.66666), 2)]))

print(results.fetchone()[0])

Execution:

$ python geojson.py 
2019-03-25 18:34:59,646 INFO sqlalchemy.engine.base.Engine select version()
2019-03-25 18:34:59,646 INFO sqlalchemy.engine.base.Engine {}
2019-03-25 18:34:59,647 INFO sqlalchemy.engine.base.Engine select current_schema()
2019-03-25 18:34:59,647 INFO sqlalchemy.engine.base.Engine {}
2019-03-25 18:34:59,648 INFO sqlalchemy.engine.base.Engine SELECT CAST('test plain returns' AS VARCHAR(60)) AS anon_1
2019-03-25 18:34:59,648 INFO sqlalchemy.engine.base.Engine {}
2019-03-25 18:34:59,649 INFO sqlalchemy.engine.base.Engine SELECT CAST('test unicode returns' AS VARCHAR(60)) AS anon_1
2019-03-25 18:34:59,649 INFO sqlalchemy.engine.base.Engine {}
2019-03-25 18:34:59,649 INFO sqlalchemy.engine.base.Engine show standard_conforming_strings
2019-03-25 18:34:59,649 INFO sqlalchemy.engine.base.Engine {}
2019-03-25 18:34:59,650 INFO sqlalchemy.engine.base.Engine SELECT ST_AsGeoJSON(ST_MakePoint(%(ST_MakePoint_1)s, %(ST_MakePoint_2)s), %(ST_AsGeoJSON_2)s) AS "ST_AsGeoJSON_1"
2019-03-25 18:34:59,650 INFO sqlalchemy.engine.base.Engine {'ST_MakePoint_1': 5.55555, 'ST_MakePoint_2': 45.66666, 'ST_AsGeoJSON_2': 2}
{"type":"Point","coordinates":[5.56,45.67]}
@erictheise

This comment has been minimized.

Copy link
Author

commented Mar 25, 2019

I'm generating random locations by sampling from population-weighted US Census Tracts. There's a hosted version at http://trctr-pllr.herokuapp.com/

feature_geom is assigned here

while True: 
    try:        
        sample = random.randint(0, max)
        tract = db.session.query(TractDistribution).filter(TractDistribution.weight.contains(sample)).one()
        feature_geom = db.session.execute(func.RandomPoint(tract.wkb_geometry)).scalar()
        break       
    except Exception:
        print(Exception)
        db.session.rollback()

RandomPoint is the stored procedure found at https://trac.osgeo.org/postgis/wiki/UserWikiRandomPoint.

@elemoine

This comment has been minimized.

Copy link
Member

commented Mar 25, 2019

Are you sure func.ST_AsGeoJSON(feature_geom) works at all in your case? (With no precision passed to ST_AsGeoJSON.)

@erictheise

This comment has been minimized.

Copy link
Author

commented Mar 25, 2019

Yes, I've used it locally and run it at heroku since early 2018.

@elemoine

This comment has been minimized.

Copy link
Member

commented Mar 25, 2019

Ok. What PostGIS version do you have? Does it work in PostGIS (w/o GeoAlchemy)?

Are you able to create a small example reproducing the problem that I can easily execute on my environment? Thanks.

@erictheise

This comment has been minimized.

Copy link
Author

commented Mar 26, 2019

Running PostgreSQL 10.4 with PostGIS 2.4.4. Yes, specifying precision works directly using the psql client. Ran that before opening this issue to make sure I wasn't misunderstanding the format.

I'll look into this again later tonight, thanks for your help.

@elemoine

This comment has been minimized.

Copy link
Member

commented Mar 26, 2019

I understand the problem.

The following works:

 select st_asgeojson('0101000000D42B6519E238164093C6681D55D54640');

While this produces an error:

select st_asgeojson('0101000000D42B6519E238164093C6681D55D54640', 2);
ERROR:  function st_asgeojson(unknown, integer) is not unique

The first form of st_asgeojson works with a WKB string as the first argument, while the second does not.

To fix the problem in your Python code you need to change the return type of the RandomPoint function. Try this at the begining of your program:

from geoalchemy2 import Geometry
from geoalchemy2.functions import GenericFunction


class RandomPoint(GenericFunction):
    name = 'RandomPoint'
    type = Geometry

With this every form of ST_GeoJSON should work as expected.

@elemoine elemoine closed this Mar 26, 2019

@erictheise

This comment has been minimized.

Copy link
Author

commented Mar 26, 2019

Works as advertised, thanks again @elemoine.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
You can’t perform that action at this time.