In my situation, I have a table of postcodes. I want each row to have a list of surrounding postcodes.
With the app I’m developing, I want to be able to search by a postcode/suburb and include postcodes that are nearby. Say within 2 miles. I know how to identify postcodes surrounding suburbs, but don’t know how to associate these so that I could retrieve the surrounding suburbs for a postcode in my model.
For example in the below, postcode 0001 might be surrounded by 0003, and 0004
id | postcodes 1 | 0001 2 | 0002 3 | 0003 4 | 0004
I’m very unsure about how to build this model. Would I need a separate table that associates postcodes with their neighbors? Could this be done within the one table?
If it assists, I’m running PostgreSQL with Python SQLAlchemy ORM.
Thanks for any help. Happy to answer any questions that will assist.
Advertisement
Answer
This is a tricky thing as this is essentially ‘graph’ data and this can be hard to work with. A few questions:
Are those “surrounding” codes calculable based on the data in the table? Or is a magic knowledge that needs to be set in the table?
Do you have this when adding a row or is this being updated later?
Does this change (rearly?, often? never?) or is this a constant table?
Two things you can do is: 1) create a new table with this data 2) create a new column holding an array with those surrounding codes. This can even be efficiently indexed and searched through in Postgress.
The con of new table is that it is a new table. The pro is that you can define a column as foreign key and if you delete you can take advantage of this.
The pro of a column with an array is you do not need a new table but the con is you cannot define it as a foreign key so if any codes get deleted you will be responsible for writing some kind of procedure to clean those codes from the arrays.
This would look like this:
id | postcodes | adjecent
1 | 0001 | {0003, 0004, ...}
2 | 0002 | {...}
3 | 0003 | {0001, ...}
4 | 0004 | {0001, ...}
The new table could be a simple pivot table:
id | adjecent 1 | 3 1 | 4 3 | 1 4 | 1
or a more complicated array table:
id | adjecent
1 | {3, 4}
3 | {1}
4 | {1}
Just remember that if you use an array you are using that foreign key info.