The Wayback Machine - https://web.archive.org/web/20160325035630/http://gis.stackexchange.com/questions/174441/create-a-line-from-an-array-and-measure-its-length-with-postgis-st-makeline-st

current community

your communities

more stack exchange communities

company blog
Stack ExchangeInboxReputation and Badges
tour help
Geographic Information Systems Stack Exchange is a question and answer site for cartographers, geographers and GIS professionals. It's 100% free, no registration required.

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top
up vote1down votefavorite

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)

share|improve this question
    
elements in the list seems to be WKB – nickvesDec 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'));""") – studentDec 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 ? – nickvesDec 18 '15 at 17:33
up vote2down voteaccepted

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',)]
share|improve this answer

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

close