Skip site navigation (1) Skip section navigation (2)

Re: Sorting street addresses

From: Jean-Luc Lachance <jllachan(at)sympatico(dot)ca>
To: Richard Poole <rp(at)guests(dot)deus(dot)net>
Cc: PostgreSQL <pgsql-general(at)postgresql(dot)org>
Subject: Re: Sorting street addresses
Date: 2004-10-29 01:18:33
Message-ID: 41819A69.30800@sympatico.ca (view raw or flat)
Thread:
Lists: pgsql-general
How will that work when people reside at
  123A Some St.

Address that need to be sorted and/or grouped in any way should be 
stored as multiple fields.

door number
door number suffix          Most often a letter
street name prefix          Section
street name
street name suffix          Direcetion
street type                 St, Cr, Rd etc
subdivision type            Unit, Apt, Office etc
subdivision                 Alphanumeric
City
State
Postal Code




Reformating street address for address correction and for the purpose of 
distribution and/or statistics is a pain.

Try these:

105-1234 N 13th St E NY
1234 N 13th E St apt 105
1234 North 13th St East apt 105 New-York
#105 1234 N Thirteenth St E NY

You get my drift... and I did not try appartment letter.

JLL







Richard Poole wrote:

> On Thu, Oct 28, 2004 at 03:36:00PM -0400, Robert Fitzpatrick wrote:
> 
> 
>>I would like all those on the same street grouped together. Is there any
>>tricks to getting the street names sorted first, possibly where numbers
>>and strings separate?
> 
> 
> You could do something like
> 
> CREATE FUNCTION streetname(text) RETURNS text AS '
> 	SELECT substring($1 FROM ''[a-zA-Z ]+$'')
> ' LANGUAGE 'SQL';
> 
> and then add an ORDER BY streetname(address) to your select.
> 
> Richard
> 
> ---------------------------(end of broadcast)---------------------------
> TIP 2: you can get off all lists at once with the unregister command
>     (send "unregister YourEmailAddressHere" to majordomo(at)postgresql(dot)org)
> 

In response to

pgsql-general by date

Next:From: JoelDate: 2004-10-29 03:36:54
Subject: {OT?] Auth_PG_grp_group_field directive gives parameter error
Previous:From: Alvaro HerreraDate: 2004-10-29 00:40:47
Subject: Re: determine sequence name for a serial

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group