On Thu, 2012-12-13 at 02:03:42 -0800, Zwieberl wrote: > I have strings like 'POINT(-141.1643 32.42112)' and want to extract the > numbers between the brackets. > Note1: I can not use python to manipulate the string, since the string gets > created from a Postgresql-function-call within the select-statement, and > the substring has to be available in the same select. > Note2: the length of the numbers is not constant, therefore a simple > substring()-call is not possible, since I dont know the end-value.
How about this:
from sqlalchemy import *
from sqlalchemy.orm import *
from sqlalchemy.ext.declarative import declarative_base
db_engine = create_engine('postgresql://localhost')
Session = sessionmaker(bind=db_engine)
db_session = Session()
Base = declarative_base()
class Point(Base):
__tablename__ = 'points'
id = Column(Integer, primary_key=True)
data = Column(Text)
Base.metadata.create_all(bind=db_engine)
p1 = Point(data=u'POINT(-141.1643 32.42112)')
p2 = Point(data=u'POINT(-42.1337 99.99999)')
db_session.add_all([p1, p2])
db_session.commit()
q = db_session.query(func.regexp_split_to_array(
func.substring(Point.data, r'^POINT\((.*)\)$'), r'\s'))
for row in q:
print row
q = db_session.query(cast(func.regexp_split_to_table(
func.substring(Point.data, r'^POINT\((.*)\)$'), r'\s'), Float))
for row in q:
print row
> Any help would be appreciated! (Also if you have a different approach
> towards extracting the substring from the string)
Although I'm not familiar with this stuff, but perhaps this would be
better suited for PostGIS and GeoAlchemy?
--
Audrius Kažukauskas
http://neutrino.lt/
pgpsdWeHED66y.pgp
Description: PGP signature
