PostGIS query to get a list of points

Question

I have a PostGIS road network layer (all features are linestrings) which I am accessing in Python and would like to get the geometry for each feature as a list of points. Something like [(lat1,lon1),(lat2,lon2),(lat3,lon3)...] would be ideal. I am currently accessing the geometry by using the ST_AsText(geom) function, and then parsing the resulting string into points. But this seems clunky, and since this seems like a pretty common/basic operation I would expect there to be a standard way to do it, but I am not seeing anything in the documentation. Is there a function or good way to do it out there that I am just missing?

Answer 1

I use psycopg2 and shapely to read PostGIS geometries into Python, like this:

import psycopg2
from shapely import wkb
conn = psycopg2.connect(...)
curs = conn.cursor()
curs.execute('SELECT geom FROM line_table WHERE gid=%s', (23,))  # one geometry
geom = wkb.loads(curs.fetchone()[0], hex=True)
geom.coords[:]  # [(x1, y1), (x2, y2)]

If you have MultiLineString geometries instead, then each geometry has a .coords property, e.g. the first part is geom.geoms[0].coords[:].

Note that the coordinates are Cartesian (x y) which is the norm in GIS software, not (lat lon) the human readable norm. If you need the later, there are a few tricks that can be used on either the server or client side.