Sample Header Ad - 728x90

Field/datatype for searchable address components?

2 votes
1 answer
237 views
I'm using Django with Postgres, and I'm looking for a field/datatype to store address components that come from Google Places API in this format: "address_components": [ {"long_name": "27", "short_name": "27", "types": ["street_number"]}, {"long_name": "Dromore Crescent", "short_name": "Dromore Crescent", "types": ["route"]}, {"long_name": "Westdale South", "short_name": "Westdale South", "types": ["neighborhood", "political"]}, {"long_name": "Hamilton", "short_name": "Hamilton", "types": ["locality", "political"]}, {"long_name": "Hamilton Division", "short_name": "Hamilton Division", "types": ["administrative_area_level_2", "political"]}, {"long_name": "Ontario", "short_name": "ON", "types": ["administrative_area_level_1", "political"]}, {"long_name": "Canada", "short_name": "CA", "types": ["country", "political"]}, {"long_name": "L8S 4A8", "short_name": "L8S 4A8", "types": ["postal_code"]} ], "formatted_address": "27 Dromore Crescent, Hamilton, ON L8S 4A8, Canada", "place_id": "ChIJtZiMplGbLIgRcGQ4Anc337s", The goal is to allow users to see this row in their results if they search for westdale, L8S 4A8, l8s4a8, 27 dromore, hamilton, etc. How should this data be saved? 1. Save it as-is in jsonb? There's a lot of duplicate text, for instance 'long_name' and 'short_name' keep appearing over and over, wasting space. 2. Strip all the keys and only keep the actual meat of the components in a varchar[], ex. ['27', 'Dromore Crescent', 'Westdale South', 'Hamilton', 'Hamilton Division', 'Ontario', 'Canada', 'L8S 4A8']? 3. Normalize this data in layers of foreign-key related tables? Four tables are required to normalize this: address_to_component, component, component_type, component_to_component_type. (I actually started with this, but couldn't wrap my head around how to go about querying it.) 4. Something else?
Asked by davidtgq (759 rep)
Dec 8, 2016, 12:03 AM
Last activity: Mar 2, 2017, 04:11 AM