6

I am using PostgreSQL, SQLAlchemy and GeoAlchemy2 libraries to store some geospatial coordinates in the database.

I define the database column in my database as follows using Python:

import sqlalchemy as sa
import geoalchemy2 as ga
geo = sa.Column(ga.Geography('POINT', srid=4326))  # 4326 = WGS84 Lat Long

And I can add the geo coordinates by converting as follows:

self.geo = 'POINT({} {})'.format(latitude, longitude)

This encodes it as a string like: 0100002076ED....

My question is: How can I convert this string back to latitude, and longitude from within python?

Ilja Everilä
  • 50,538
  • 7
  • 126
  • 127
Luca
  • 10,458
  • 24
  • 107
  • 234
  • You can use `re` module to extract values from the string, look at answers to this question: [sscanf in Python](https://stackoverflow.com/q/2175080/2913477) – MiniMax Aug 22 '19 at 00:32
  • 1
    This question is unclear to me. First, your `format` should produce a string starting with `'POINT(`, not `0100002076ED`. Second, since you already *have* `latitude` and `longitude`, why not just store them in a tuple instead of formatting them and then converting it back? – Roland Smith Aug 22 '19 at 09:06
  • Please provide a [mcve], without truncating the model definition, inputs, or outputs. Could you also clarify the "within Python", are you unwilling to use the PostGIS functions while querying, and instead want to parse (what looks like) [WKB](https://en.wikipedia.org/wiki/Well-known_text_representation_of_geometry#Well-known_binary)? – Ilja Everilä Aug 22 '19 at 09:36
  • Does this answer your question? [How to get lng lat value from query results of geoalchemy2](https://stackoverflow.com/questions/24092817/how-to-get-lng-lat-value-from-query-results-of-geoalchemy2) – IamMashed May 25 '20 at 15:10
  • With [Postgis it's `(longitude, latitude)`](https://postgis.net/2013/08/18/tip_lon_lat/). – maxschlepzig Dec 11 '21 at 18:34

1 Answers1

2

When selecting, you convert it as part of the select expression, e.g. like this:

select ST_X(some_loc_col::geometry), ST_Y(some_loc_col::geometry) from mytable

With SQLAlchemy, you can either directly execute such a statement, prepare it from text (cf. sqlalchemy.sql.text()), or build an expression using the functions from SQLAlchemy and GeoAlchemy2.

Expression example:

import sqlalchemy
from sqlalchemy.sql.expression import cast
import geoalchemy2
import geoalchemy2.functions as fns

db_url = '...'
echo = True
sqlalchemy.create_engine(db_url, echo=echo)
meta = sqlalchemy.MetaData()
my_table = sqlalchemy.Table('mytable', meta, autoload=True, autoload_with=engine)
stmt = my_table.select().with_only_columns(
        [ fns.ST_X(cast(my_table.c.some_loc_col, geoalchemy2.types.Geometry)),
          fns.ST_Y(cast(my_table.c.some_loc_col, geoalchemy2.types.Geometry)) ])

See also: https://geoalchemy-2.readthedocs.io/en/0.11.1/spatial_functions.html

maxschlepzig
  • 35,645
  • 14
  • 145
  • 182