2

I developed this code to query and update data in a postgres/postgis database that is stored on a local machine. The table being analyzed has only around 150K records. The code takes 6-7 hours to run, and I am wondering why? The data does have a spatial index. Is there a way the code could be written more efficiently, or could it be the way the database is set up, or my machine?

The code took about 6-7 hours to run on a Windows 2007 64-bit machine. I also tried to run on 2016 Macbook Pro and had to kill it; I set it to run overnight and after 9 hours or so I killed it.

How can I optimize this?

I am running PostgreSQL 9.5.

import psycopg2

FROM_ST_DICT = {}
TO_ST_DICT = {}

CONN = psycopg2.connect("dbname='test' user='postgres' host='localhost' password='pg*admin'")
print 'successfully connected to the database'

cur = CONN.cursor()
cur.execute('SELECT * FROM sf_citylots_unique')
for row in cur:
    GEOM = row[4]
    FROM_ADDRESS = row[0] + ' ' + row[2] + ' ' + row[3]
    TO_ADDRESS = row[1] + ' ' + row[2] + ' ' + row[3]

    if GEOM in FROM_ST_DICT:
        if FROM_ADDRESS not in FROM_ST_DICT[GEOM]:
            # append the new number to the existing array at this slot
            FROM_ST_DICT[GEOM].append(FROM_ADDRESS)
    else:
        # create a new array in this slot
        FROM_ST_DICT[GEOM] = [FROM_ADDRESS]
    if GEOM in TO_ST_DICT:
        if TO_ADDRESS not in TO_ST_DICT[GEOM]:
            TO_ST_DICT[GEOM].append(TO_ADDRESS)
    else:
        TO_ST_DICT[GEOM] = [TO_ADDRESS]
print 'adding FROM address data...'
for f in FROM_ST_DICT:
    cur.execute('UPDATE sf_citylots_unique set from_address = '+"'"+
                ','.join(FROM_ST_DICT[f])+ "'" + ' WHERE geom ='+"'" +f+"'")
print 'adding TO address data...'
for t in TO_ST_DICT:
    cur.execute('UPDATE sf_citylots_unique set to_address = ' + "'" +
                ','.join(TO_ST_DICT[t]) + "'" + ' WHERE geom =' + "'" + t + "'")

cur.execute("""select distinct a.from_address, a.to_address, a.geom into sf_citylots_unique_geom_with_addresses
               from sf_citylots_unique a""")

CONN.commit()
CONN.close()

Some background, the data represents city loth with associated address ranges. There are duplicate geometries where multiple addresses/ranges exist. I want a final layer that contains only unique geometry, and to retain all addresses associated with each lot. I set up 'to_address' and 'from_address' fields that should contain all full street addresses associated with each lot, like 123 Main St, 456 Park St for the from_address column, and 125 Main St, 458 Park St for the to_address column. In some cases there could be a large number or to/from addresses, and in some cases the to and from values are the same.

  • Do you know which of the three mayor parts (reading and building the dicts, updating "from", upüdating "to") takes the longest? – chrki Sep 1 '16 at 20:59
  • Yes I should have specified that, it completes all the code before the update statements very quickly; the update statements take forever. – kflaw Sep 1 '16 at 21:01
  • What are you trying to do here? If the goal is to update missing address data from citylots that don't have an address, based on matching geometries, it would be much more efficient to do this in a single-step PostGIS query using ST_Intersects. – amball Sep 2 '16 at 6:36
  • There are duplicate geometries in the table that have different addresses. I want to remove the duplicate geometries and consolidate all the unique from/to addresses in one record. – kflaw Sep 2 '16 at 12:19
5

The spatial index may not help, as you are matching on geometry equality, not spatial relations using the PostGIS functions. Add EXPLAIN to the start of your UPDATE query. You should get the query plan returned. Does the query plan use the spatial index, or does it do a sequential scan?

If the spatial index is not used, rather than:

WHERE geom ='+"'" +f+"'"

Try: WHERE ST_Equals(geom,'+"'" +f+"')" or WHERE ST_Intersects(geom,'+"'" +f+"')"

That should prompt Postgres to use the spatial index.

Having said that, you probably don't need to pull out intermediate results into Python. If you explain what you are trying to do, there's probably a much simpler way to update your table in a single Postgres call.

UPDATE. Based on what you are trying to do, wouldn't it make more sense to have 2 tables - one with unique geometries, and one with all addresses associated with that geometry? Something like: CREATE TABLE unique_lots AS SELECT DISTINCT geom FROM sf_citylots_unique; ALTER TABLE unique_lots ADD COLUMN lotid SERIAL PRIMARY KEY;

and then

CREATE TABLE lot_addresses AS SELECT DISTINCT lotid, address FROM unique_lots a, sf_citylots_unique b WHERE ST_Equals(a.geom, b.geom)

  • Thanks for the reply. I'm relatively new with postgis/postgres queries and am not sure how I would do the equivalent of building the python dictionary of the addresses associated with each geometry. – kflaw Sep 2 '16 at 14:14
  • I am trying st_equals but its still running so I don't know yet. Your query won't work in my case; I have mulitple address ranges with gaps in between in some cases for some lots. And yes, different street addresses for the same lots. – kflaw Sep 2 '16 at 17:58
  • Posting the EXPLAIN output for both your original UPDATE query and the one with ST_Equals would help. Also, can you give an example of what your data looks like and what you want the output to look like? E.g., if you have different street names for the same lot, how do you want to decide which one is retained? – amball Sep 2 '16 at 18:47
  • Ok, i ran the update query again using ST_equals, when the original update query finished. It took one minute. I didn't even realize the spatial index was not being used, or that it would take so long to compare geometry fields without using a geometry function. I really appreciate your help. In terms of a final ouput, I've posted more info above. – kflaw Sep 2 '16 at 22:04
  • Also, I tried to insert explain before the update query in the code and print it, but it just printed 'none'. I'm not sure how to get the explanation in python? – kflaw Sep 2 '16 at 22:08

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.