I am trying to load a postgis linestring into python (using ST_DumpPoints) and extracting x,y,z coordinates, which is working but extremely slow..and if i mean slow, i really mean it (yes, it takes about 5 seconds per row, so imagine how long this is for around 5000 features). Funnily enough, if i read the same from a points layer (it is originally taken from a gpx-file, so it has track points and a track), the same amount of points is done in a jiffy. I have no idea where the performance loss is coming from and how to resolve it. I rely on this linestring though to calculate a comparing sample. Is it known that linestrings are not be meant for such kind of processing?

The code is as follows:

from osgeo import ogr
import numpy
source = ogr.Open("PG:host=localhost dbname=tracks user=anush")
if source is None:
    print "connection was unsuccessful"
    gFC = source.ExecuteSQL("SELECT st_numpoints(wkb_geometry) from w_130401_aster_comp.w_130401_aster_comp;")
    gFCi =(gFC.GetFeature(0)).GetField(0)
    tP = source.ExecuteSQL("SELECT (ST_DumpPoints(wkb_geometry)).path as path, ST_X(st_astext((ST_DumpPoints(wkb_geometry)).geom)) as X, ST_Y(st_astext((ST_DumpPoints(wkb_geometry)).geom)) as Y, ST_Z(st_astext((ST_DumpPoints(wkb_geometry)).geom)) as Z from w_130401_aster_comp.w_130401_aster_comp;")

    ele_comp = []
    for i in range(1,gFCi):
        f = tP.GetFeature(i)
        z = f.GetFieldAsDouble(3)

If i use the track_points alternative, the results are collectable pretty much right away, i.e.

from osgeo import ogr

source = ogr.Open("PG:host=localhost dbname=tracks user=postgres")
if source is None:
    print "connection was unsuccessful"
    data = source.ExecuteSQL("SELECT ogc_fid, the_geom, ele from w_130401.track_points")

    ele = {}
    for i in range(1,data.GetFeatureCount()):
        f = data.GetFeature(i)
        coords = f.GetGeometryRef()
        x = coords.GetX(); y = coords.GetY()

Another really strange thing is that for the linestring i cannot use an ogr.GetFeatureCount() which returns 0, so i have to pre-run a st_numpoints to initiate the for loop (which returns the number of vertices as integer).

Does anyone know what is going on here?

  • Is it possible that making fewer calls to ST_DumpPoints(wkb_geometry) in your SQL would help, or does the result of the first call get cached and reused? – sgillies Apr 8 '13 at 22:28
  • I once had sql code which run for 5days, after i changed for loop , it run in 5h. In that case problem was to for loop over line and move to next line. Faster versio finded line which had moust of points and then looped over all lines that many time and wrote answer to sql table. If line didnt have any point in place X, it just dosent return anything. It was build in sql. Also i think that you fetch one row at time. so you execute time table look like : fetch -> process line- fetch. Try read whole answer to memory and process all lines in loop – simplexio Apr 9 '13 at 7:14
  • the problem is definitely not the SQL query because the sql object is retrieved right away. I am really surprised that the for loop takes so long, though, because it is in memory and usable. 5000 should not really bring a python list or dict to its knees, or should it? – Arne Apr 13 '13 at 10:34

Your Answer

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

Browse other questions tagged or ask your own question.