We’re rewarding the question askers & reputations are being recalculated! Read more.

Hot answers tagged

17

I spent 7 years developing software for a publishing company and one of the hardest problems we ever tackled was parsing street addresses in subscription lists. It is useful to split up addresses into distinct fields, but you can never, EVER design for every possible pathological aberration of address formats and components the human brain can devise. ...


10

Problems that can be solved by splitting include Validation Any one part of the name can be compared to a master list. Those which do not match can be rejected. Postcode / zipcode is an obvious example. These are issued and maintained by an independent authority. The only valid ones are those issued by that authority. Sorting and Selection I have seen ...


8

Like all design questions, there's a hugely qualified "it depends". It depends on your data story - how the data is collected, how it is used, how it gets updated, etc. All my comments should be taken as discussion points, not how-to answers. It sounds like* you could benefit more from using an address validation service than trying to build one for ...


8

This is a question address canonicalization and parsing. Essentially what you're talking about is handled through a gazetteer (geographical rule set). There are two ways to do this right, address_standardizer from the PostGIS project and certainly better if you're only using United States addresses. pgsql-postal may be a better method for international ...


5

Totally leaving aside the enormous challenge of correctly parsing the unpredictable gibberish that people supply, the benefit of parsing is it gives you dimensions for grouping and sorting. Postcode, for example. However, there is no payoff from parsing out a specific dimension until you need to group or sort on that dimension. What is an address, anyway? ...


5

While McNets's solutions would also work, it's possibly the easiest to use ANY with an operator, in this case equality: SELECT '10.10.10.10' = ANY ('{10.10.10.10, 10.10.10.20}'::inet[]); The background is that an inet array is no different from any other array type in this regard - ANY will work with any boolean-returning operator that is defined on the ...


3

I have implemented a system like this before, albeit in The Netherlands. The thing is, this kind of information can change in more ways than you think. Streets are renamed, cities are merged and so on. It's nice to be able to update that kind of information without parsing the addresses as a single string.


3

Separating out postcode/zip code, building name, road name can make sense. But then when you start adding “town”, “area” etc it gets questionable, compared to just line1, line2 etc. The issue is that even I and my wife can’t agree on the name of the town we live in! Is the “village” name to be put in the town field, or does it go in the line below the ...


3

Here's what we did in LedgerSMB: Entity table storing basic container info on individuals and businesses location table storing address information. Right now we have a join table between address and entity, but in future versions that will probably be merged with the address table. Tables 'person' and 'company' which extend entity for that purpose. That ...


3

Separate fields. Every time. String operations are costly, especially cutting them up into bits.


2

In addition to the problems already mentioned in other answers, in some languages -- Germanic in particular -- street names tend to be compound. For example, it's common in many German towns / cities to have a "Bahnhofstrasse", the street that goes to the railway station ("Bahnhof" meaning railway / train station, "Strasse" meaning street). Certainly you ...


2

Build up a generic address table. Using a one to many translation table relate users/business/etc -> translation table -> address table. This way you can expand your user 'types' and be flexible yet still be very strict and sanitary about the addresses used in relation to those flexible user types.


2

Let me know if this answer can helps you, I've set up next sample on dbfiddle.uk. create table myt ( id uuid NOT NULL, ipAddresses inet[], createdAt timestamp with time zone, updatedAt timestamp with time zone ); insert into myt values ('0e37df36-f698-11e6-8dd4-cb9ced3df976'::uuid, array['10.0.0/24','20.0.0.1']::inet[], now(), now()...


2

The default on Linux is to connect over unix domain sockets, rather than TCP, so there is no hostname. If you want to reproduce the default with an explicit option, it would be something like -h /var/run/postgresql (if you installed from yum) or -h /tmp (if you installed from sources using the default settings).


2

If you're connecting from the same machine, use localhost A given computer can run multiple instances of PostgreSQL on different sockets (address:port pairs). The default port is 5432 A given PostgreSQL server can run multiple databases on the same instance. The postgres database is always there. There's no "default database" though really. Are you sure ...


2

UPDATE: see this answer for a working example of the below How would I store it? What you've got is User data (formatted_address) Normalized address breakdown (address_components) I would store only the formatted_address or whatever the user provided. Why? I don't trust Django or whatever you're integrating with I want the ability to re-normalize ...


1

This is not a clean problem. The following should work assuming that there will not be multiple street addresses with different suite numbers. Also the data will have to be formatted with the comma and space before the Suite. I added a duplicate record (1 Burton Hills...) for testing the group by logic. Create Table Addresses (Address Varchar(8000)) ...


1

A single address table will be a clean and efficient solution to maintain and execute queries against. From the information you've given the main reason you might not want this is you have two well defined use cases, one of which expects additional fields to be completed while the other accepts a significant number of blank fields. Handle this in your ...


1

You were more or less on the right track with (b), but you want to make sure that latitude and longitude are handled independently. What you need are indexes that cover your query parameters. This is what you are looking for: Segregate your retailer addresses (including lat/lon). This could be in a RetailerAddress table or it could be columns on the ...


1

The right answer is choice #5: use PostGIS. An example from OpenGeo: "Note the magic <-> operator in the ORDER BY clause. This is where the magic occurs. The <-> is a “distance” operator, but it only makes use of the index when it appears in the ORDER BY clause. Between putting the operator in the ORDER BY and using a LIMIT to truncate the ...


Only top voted, non community-wiki answers of a minimum length are eligible