Storing and querying postal addresses

Question

I have a system with the concept of addresses (as in postal addresses). We have an Address table with fields Address1-4, City, PostCode.

I am trying to figure out the best way to store this Address data so that it can be queried easily in the UI. Users don't generally appreciate that there are separate fields (except for perhaps Post Code) so asking them to put the right string in 5 search fields doesn't work. I feel it needs to be a single search field which will try and match against part of the address.

For example

Address1: Flat 23
Address2: Big Towers
Address3: 1 Baker Street
Address4: (Blank)
City: London
PostCode: W1U 6AA

So for example if wanted to find "Flat 23 in Big Towers" I might like to type "23 Big Towers" or "Big Towers 23" into a query box and expect it to find the above. But because the fields are separate this is difficult to achieve.

The app is completely proprietary so making changes to how the address is stored or storing it in multiple ways is perfectly feasible. The app is C# UI and Java middle-tier.

Does anyone have any suggestions about how to store the Address and structure a search UI + query to make this straightforward from a user's points of view?

EDIT: Added an example

what about using db tools? ie. full text? Would this be an option? — wmz, Feb 01 '12 at 23:20

Stafford Williams · Answer 1 · 2012-07-10T01:51:20.437

2

Based off your example you could have a generated field that concatenates all other fields (with space delimiters). Then you could just query this field instead of the others, such that (with your example):

Generated field:

Flat 23 Big Towers 1 Baker Street London W1U 6AA

Query:

SELECT
  * 
FROM
  Address
WHERE
  GeneratedField LIKE '%searchTerm1%' 
AND
  GeneratedField LIKE '%searchTerm2%'
AND
  GeneratedField LIKE '%searchTerm3%'

edited Jul 10 '12 at 01:51

answered Feb 01 '12 at 22:25

Stafford Williams

9,434
7
44
94

That wouldn't work for the example given where the search string spans 2 or more fields. – Mike Q Feb 01 '12 at 22:45

score 1 · Answer 2 · edited May 23 '17 at 10:34

1

Do you have the option of hooking up to a 3rd party API? You could send a request over to Google or Yahoo maps with the original string, then get back a nicely delimited set of data that you could use to compare. That may be easiest - let Google's servers do it for you! As a direct answer, I'd say store each address component separately, then query against the API lookup results.

I think there's some interesting additional discussion here:

Parse usable Street Address, City, State, Zip from a string

edited May 23 '17 at 10:34

Community

1
1

answered Feb 01 '12 at 22:15

eouw0o83hf

8,640
3
51
66

I already have the data delimited cleanly, it's more of a problem to figure out how to make searching for this data as easy as possible. – Mike Q Feb 01 '12 at 22:18
I was envisioning giving the user a single text input box, letting Google parse it into meaningful address components, then compare those component results against the database and find the closest match - are you looking for something more specific? – eouw0o83hf Feb 01 '12 at 22:21
I've added an example, maybe that makes it clearer. While I can change the internals of the app we deploy this inside other companies so I would prefer to avoid going out to something like google as it raises security issues. Plus I only want to search the addresses stored in the system rather than generally. For example an install may only have a few thousand relevant addresses. – Mike Q Feb 01 '12 at 22:26

score 0 · Answer 3 · answered Feb 01 '12 at 22:28

0

I like storing addresses as an xml snippet myself. with scope for extra attributes like town, country, country etc.

Gives you a lot more options and elbow room.

answered Feb 01 '12 at 22:28

Tony Hopkinson

19,528
3
29
38

score 0 · Answer 4 · answered Feb 01 '12 at 22:29

0

You can do it simply by using a parameterized query. Just surround each text value with % for MSSQL, and add them as parameters for this query:

SELECT * FROM Addresses WHERE Address1 LIKE @Address1 OR Address2 LIKE @Address2 OR Address3 LIKE @Address3 OR Address4 LIKE @Address4

parameters.Add("@Address1", String.Format("%{0}%", address1Text.Text)

answered Feb 01 '12 at 22:29

scottm

26,493
22
102
155

I only want to have a single text box for users to enter search queries as it will be non-intuitive for a user to figure out which field contains what, and this probably won't be consistent across all addresses in the system. – Mike Q Feb 01 '12 at 22:46

Storing and querying postal addresses

4 Answers4