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
Last activity: Mar 2, 2017, 04:11 AM