[GIS] Create a line from an array and measure its length with PostGIS (ST_MakeLine, ST_Length)

distancelengthpostgis

do I understand it correctly that it is possible to create a line out of an array with the function ST_MakeLine()? I would like to create a line from several points (more than two) and then calculate the length of this line. So the idea is to use ST_Length(ST_MakeLine()). But I wouldn't mind to use something else if it works better. And I don't need a line if it is possible to calculate the distance without creating the line. So I need actually the distance between several points.
I will have many different arrays calculated for 3-15 points from the database, so the 'geom' array further down is just an example.

I am trying the following code: 

   try:
        conn = psycopg2.connect("dbname='postgis_22_sample' user='postgres'                         password='db_postgres'")
        print "connected"
    except:
        print "I am unable to connect to the database"

    cur = conn.cursor()

    geom = ['0101000020E61000002073602DC48B02C08A97E9353F434B40', '0101000020E610000052F1834C1F0706C0E5F623C767554B40', '0101000020E6100000786C0C11DBEA02C002893F6561444B40', '0101000020E6100000CB67791EDCDD03C04963B48EAA4A4B40']

    cur.execute("""SELECT ST_Length(ST_MakeLine(geom));""")

Error: the column 'geom' does not exist

I thought that theoretically I can insert any possible array in there. Or is there something wrong with the syntax?

If I read the values from the table it gives the error that the function is not compatible with these arguments.

    cur.execute("""SELECT ST_Length(ST_MakeLine(geom)) FROM table;""")

(I have the version 2.2 of PostGIS and here (http://postgis.net/docs/ST_MakeLine.html) it is written that since 1.4 the arrays are supported)

Best Answer

First, in this statement:

cur.execute("""SELECT ST_Length(ST_MakeLine(geom));""")

geom doesn't refer to your Python variable named geom; it refers to a postgres column named geom, which does not exist and is why you are seeing this error:

ERROR:  column "geom" does not exist

To substitute in the Python variable, you need something like this:

cur.execute("""SELECT ST_Length(ST_MakeLine(%s));""", (geom,))

Which would result in the following SQL:

select 
st_makeline(ARRAY['0101000020E61000002073602DC48B02C08A97E9353F434B40', 
'0101000020E610000052F1834C1F0706C0E5F623C767554B40', 
'0101000020E6100000786C0C11DBEA02C002893F6561444B40', 
'0101000020E6100000CB67791EDCDD03C04963B48EAA4A4B40'])

And which would return the following result:

>>> cur.execute("""select st_makeline(%s)""", (geom,))
>>> cur.fetchall()
[('0102000020E6100000040000002073602DC48B02C08A97E9353F434B4052F1834C1F0706C0E5F623C767554B40786C0C11DBEA02C002893F6561444B40CB67791EDCDD03C04963B48EAA4A4B40',)]
Related Question