1

I have a query running as below:

UPDATE datasets.riverside_houses
SET easting = addressbase.x_coordinate
FROM datasets.addressbase
WHERE addressbase.building_number = riverside_houses.house_number OR addressbase.building_name = riverside_houses.house_number AND addressbase.thoroughfare = riverside_houses.address_line_1 AND addressbase.postcode = riverside_houses.postcode;

UPDATE datasets.riverside_houses
SET northing = addressbase.y_coordinate
FROM datasets.addressbase
WHERE addressbase.building_number = riverside_houses.house_number OR addressbase.building_name = riverside_houses.house_number AND addressbase.thoroughfare = riverside_houses.address_line_1 AND addressbase.postcode = riverside_houses.postcode

The table 'addressbase' is huge (it estimates over 30 million rows).

Essentially I have a table of addresses (riverside_houses - split into separate fields, same as addressbase) that I need to match to addressbase and return the easting/northing. I require it to check multiple fields as the condition, otherwise it could pull through all kinds of irrelevant results.

The code above comes from some research on stack, adapted to meet the above requirement. My small scale testing on sample data worked perfectly. This is my first big test and it seems to be struggling.

Is there a more efficient way to perform this action or do I just need to accept it's a big table and will take a long time?

  • 3
    @JGH seems to have covered your solution, but I wanted to add two points: 1) Always include an EXPLAIN plan for SQL performance questions (just tack an "EXPLAIN " before your PG DML statement or use the Explain button in pgAdmin) 2) Always review your question for a GIS focus -- without geometry functions, this PostgreSQL question is better researched in Database Administrators. – Vince Oct 18 at 12:23
  • Thank you Vince, I have not seen this Explain function before but will be sure to take a look. Also apologies, I was originally introduced to GIS Stack and am not so used to using the others, I'll bear it in mind for next time. – JClarkson Oct 18 at 12:26
4

The query seems to contain a big mistake as it has an or clause without parentheses. Basically, you match the rows from the house and address tables based on the complete address match OR if addressbase.building_name = riverside_houses.house_number. So regardless of the street name, postal code etc, the update will be done if the house number match. The same row will be updated several times if there are multiple match!

Also you can combine the two updates.

UPDATE datasets.riverside_houses
SET easting = addressbase.x_coordinate,
    northing = addressbase.y_coordinate
FROM datasets.addressbase
WHERE (addressbase.building_number = riverside_houses.house_number 
         OR addressbase.building_name = riverside_houses.house_number) 
     AND addressbase.thoroughfare = riverside_houses.address_line_1 
    AND addressbase.postcode = riverside_houses.postcode;

At last, make sure to have an index on all those fields in the addressbase table.

  • Ah that's great thank you! I hadn't used the OR in a test before and didn't consider how it would interpret it, but I can see what you are saying here. Thank you for the rewrite and the advice, very much appreciated! – JClarkson Oct 18 at 12:19
  • to let you know, this worked perfectly. Finished within 21 minutes and did exactly what I needed achieving a 93% match rate! – JClarkson Oct 18 at 13:17

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.