1

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)

  • elements in the list seems to be WKB – nickves Dec 18 '15 at 17:15
  • yes, but like this it works, so it shouldn't be a problem, right? cur.execute("""SELECT ST_Length(ST_MakeLine('0101000020E61000002073602DC48B02C08A97E9353F434B40', '0101000020E610000052F1834C1F0706C0E5F623C767554B40'));""") – student Dec 18 '15 at 17:28
  • The query expects a datatype of geometry or geography. You need to cast that value as such. Did you try postgis.net/docs/ST_GeomFromWKB.html ? – nickves Dec 18 '15 at 17:33
2

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',)]

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.