I’ve got a ‘table’ in my database for each UK police force and within that table I’ve got a list of all the postcodes served by that police force. As a single field as a list, separated by a comma.
While this works in small scale for testing as we have used ‘real’ data I’m finding that the page load is impacted significantly as it loads all the data in before you can then filter it. for example the metropolitan police force have over 150k of postcodes to serve.
My data types are below
I don’t want go down the route of creating a record for each postcode as this will end up being over 2m rows and not scale when we do other services etc.
I’ve then on the page in the app got several search boxes (country, postcode, etc.)
I’ve then got a repeating group for the ‘cohort’ types to then produce a list of the items that meet the condition setup in the repeating groups
is there a way to make the filters so that you have to select them then hit a button for the search to take place and only pull back the relevant records that match the search condition - as at the moment I think we are pulling them all back to then filter in the browser - which is not ideal.
I’ll keep experimenting but any guidance would be appreciated.
my first experiment was to have the repeating groups populated via a button that has the workflow to ‘display data in a repeating group’ and that has improved the performance dramatically.
my second question is related to this in that my list of postcodes for something like the metropolitan police is some 179k of postcodes which means a character length of 1.4m characters in the field.
our aim is that anyone in the UK can type in a postcode and the system will return things like your olive force, health authority, local council, water provider, energy provider etc.
If you don’t want to display anything before the user hits the search button, you can simply uncheck the “Ignore empty constraints” in your search in the repeating group data source.
That way you’ll tell your repeating group to “Get what matches the search fields, and if the fields are empty then don’t get anything”.
And I do not understand the second question.
1 Like
Thanks for the initial response, that has helped…
so the second question…
the met police in the UK serve London and surrounding areas, this equates to approx 178k of postcodes in their patch.
I have created a Geo postcodes field in the database and added all the postcodes as a list separated by a comma.
sample below
BR1 3QN,BR1 3QP,BR1 3QQ,BR1 3QR,BR1 3QW,BR1 3QX,BR1 3QY,BR1 3QZ,BR1 3RA,BR1 3RB,BR1 3RD,BR1 3RE,BR1 3RF,BR1 3RG,BR1 3RH,BR1 3RJ,BR1 3RL,BR1 3RN,BR1 3RP,BR1 3RQ,BR1 3RR,
for a couple of regions the number of individual postcodes is significant.
so I guess my question is… is there a better way of sorting and storing this data without creating a record for each postcode as that could run into the millions of rows when we look at all the different options.
I want users to be able to goto the website, type in a postcode and be presented with all the groups associated to that postcode.
Could it be a solution to have a table that stores which police group operates in which postcode?
So, that would be the other way around than what you did:
You stored a list of zipcodes in each police group object. Maybe try having a list of police groups stored in each zipcode?
BTW, you can have both, especialy if the data is not updated super frequently which I guess is the case here:
A list of zipcodes stored in each police object AND a list of police stored in each zipcode.
Then you querry one or the other dataset depending on what you are doing and what is more efficient.
Do you not just need the first three characters of the postcode (because presumably police forces only serve within county lines that should have separate postcodes)?
It should. Fundamentally, if each postcode has different services, and there are 2 million postcodes, then there must be 2 million objects in the database. One other avenue might me using external APIs. User types a postcode → make API call to external service that works out a provider for that postcode.
It’s far from ideal but I’d have a Location data type with a Postcode text field and a List of Services. The Service data type has information about that service provider (fields like name, logo, type etc)
A list of postcodes on the police force is not scaleable. Lists are hard limited at 10,000 and start to get slow after 100+ depending on the size of the list content. Using my method, to find postcodes server by a police force you Do a search for post codes (service contains This Police Force).
my challenge is that different groups have different postcodes and I need a way to add them, I’m struggling to get bubble to either upload (via the backend) the force with the list of postcodes when it gets to a certain size or in the front end where I’ve got the ability to past in a comma separated list of postcodes.
I’ve gone with postcode sectors for the time being but it does produce a vert small number of postcodes that are on the fringe of 2 groups )eg. hounslow and Hammersmith & Fulham authority) while correctly being in the metropoloitan police area.
I need to make it simple for someone to create a group and ability to add postcode level granularity and sadly some of the services will have a large number of postcodes.
You need to create an endpoint in Bubble that takes a postcode data or list of postcode data and upload your data using an external script as the editor uploader just won’t be scaleable. Alternatively, use the Bulk create API with an external script.
while the outer postcode sector does limit it significantly, it’s not perfect…
my challenge is being able to create these groups (or rather a user creates groups) by pasting in a long list of postcodes separated by a comma via a front end.
it may be that in the case of someone like the met police we create ‘geographic’ sectors and then supply a smaller list of postcodes to that list and use them joined.
I was looking for the list limitation as you have said searching through it may take longer than I’d like.
thanks for your help so far.
yeah, that’s what I’m seeing is that it’s not easy to get the large list in either though the front end or in the back end with the upload button.
Hi, have you been successful with figuring out the solution to this problem @robbie.macintosh ?
not yet, I’m trying to find a way to use a single field to store over 10k of comma separated postcodes.
