python - 使用sqlalchemy从postgis数据库获取经度和纬度

Question

我正在使用 sqlalchemy 包对我的 postgis 数据库进行查询，该数据库填充了城市的 .osm 数据。我想从 Planet_osm_point 表中检索经度和纬度值。我声明的 sql 查询如下所示:

      SELECT st_y(st_asewkt(st_transform(way, 4326))) as lat,
             st_x(st_asewkt(st_transform(way, 4326))) as lon
             "addr:housenumber" AS husenumber,
             "addr:street" AS street,
             "addr:postcode" AS postcode
      FROM planet_osm_point

Sqlalchemy 向我抛出此错误:

sqlalchemy.exc.InternalError: (psycopg2.InternalError) FEHLER:  Argument to ST_Y() must be a point

唯一的问题是 ST_Y() 和 ST_X() 函数。

Answer 1

ST_X/ST_Y 返回 float 。您可以使用 float 或将它们转换为文本。

使用 ST_AsEWKT 在这里是有问题的，因为 ST_Y/ST_X 都返回 floats 并且 ST_AsEWKT 需要 几何。

使用你得到的 float :

SELECT st_y(st_transform(way, 4326)) AS lat,
             st_x(st_transform(way, 4326)) AS lon
             "addr:housenumber" AS husenumber,
             "addr:street" AS street,
             "addr:postcode" AS postcode
      FROM planet_osm_point

或转换为文本:

SELECT cast(st_y(st_transform(way, 4326)) as text) AS lat,
             cast(st_x(st_transform(way, 4326)) as text) AS lon
             "addr:housenumber" AS husenumber,
             "addr:street" AS street,
             "addr:postcode" AS postcode
      FROM planet_osm_point