Sorting street addresses

[Robert Fitzpatrick]

Thanks to some help here on the list, I've been able to get addresses
sorting pretty well, but now I have a issue with same addresses on
different streets not grouping the streets. This is what I'm using a
substring search in the ORDER BY statement now like in this view:

SELECT tblhudsimilargroups.rems_id, tblhudsimilargroups.group_id,
tblhudsimilargroups.similar_group_id, tblhudbuildings.address,
tblhudbuildings.hud_building_id,
is_bldg_lbp(tblhudbuildings.hud_building_id) AS is_lbp,
is_bldg_lbp_hazard(tblhudbuildings.hud_building_id ) AS is_lbp_hazard
FROM (tblhudsimilargroups LEFT JOIN tblhudbuildings ON
((tblhudsimilargroups.similar_group_id =
tblhudbuildings.similar_group_id)))
ORDER BY tblhudsimilargroups.rems_id, tblhudsimilargroups.group_id,
("substring"((tblhudbuildings.address)::text,
'[^0-9]+'::text))::character
varying, ("substring"((tblhudbuildings.address)::text,
'^[0-9]+'::text))::integer;

And getting this result:

ohc=> SELECT public.viewbldginfo.group_id, public.viewbldginfo.address
FROM public.viewbldginfo WHERE (public.viewbldginfo.rems_id
='800004136');
group_id | address
----------+--------------------------
A | 3606 ROYALTY COURT
A | 3601/3603 ROYALTY COURT
A | 3602/3604 ROYALTY COURT
A | 3605/3607 ROYALTY COURT
A | 3701/3703 MCKINLEY COURT
A | 3702/3704 MCKINLEY COURT
A | 3705/3707 MCKINLEY COURT
A | 3709/3711 MCKINLEY COURT
A | 7801/7803 SOCIAL CIRCLE
A | 7801/7803 ANDALUSIA
A | 7801/7803 HAVERSHAM
A | 7802/7804 ANDALUSIA
A | 7802/7804 HAVERSHAM
A | 7805/7807 SOCIAL CIRCLE
A | 7806/7808 HAVERSHAM
A | 7811/7813 SOCIAL CIRCLE
A | 7815/7817 SOCIAL CIRCLE
A | 7825/7827 SOCIAL CIRCLE
A | 7833/7835 SOCIAL CIRCLE

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?

--
Robert
---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to ma*******@postgresql.org

[Richard Poole]

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 ma*******@postgresql.org)

[Joshua D. Drake]

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.

You could also add a function index that would help speed things along.

Sincerely,

Joshua D. Drake

Richard

---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to ma*******@postgresql.org)

--
Command Prompt, Inc., home of PostgreSQL Replication, and plPHP.
Postgresql support, programming shared hosting and dedicated hosting.
[phone] - [e-mail] - http://www.commandprompt.com
Mammoth PostgreSQL Replicator. Integrated Replication for PostgreSQL
---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faqs/FAQ.html

[Jean-Luc Lachance]

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 ma*******@postgresql.org)

---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend

[Greg Sabino Mullane]

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

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?

As some have demonstrated, this can be a difficult problem. Your best bet
is not to reinvent the wheel, but find someone who has already solved the
problem. For example:

http://search.cpan.org/dist/Lingua-E...ddressParse.pm

You could easily use the above in a Pl/perl function, or just in Perl
if your application happens to be written in that. Worse case, check out the
source code of the module and see how they have done it and create your
own version.

- --
Greg Sabino Mullane gr**@turnstep.com
PGP Key: 0x14964AC8 200410290722

-----BEGIN PGP SIGNATURE-----

iD8DBQFBgihdvJuQZxSWSsgRAvFBAKC1EcSW5Ru5+nZwNLenbR VqQlQP/wCgpvd6
TOiKff34E+DJVM7rcPkMACU=
=55zK
-----END PGP SIGNATURE-----

---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to ma*******@postgresql.org)