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

Sorting street addresses

From: Robert Fitzpatrick <robert(at)webtent(dot)com>
To: PostgreSQL <pgsql-general(at)postgresql(dot)org>
Subject: Sorting street addresses
Date: 2004-10-28 19:36:00
Message-ID: 1098992160.30469.63.camel@columbus.webtent.org (view raw or flat)
Thread:
Lists: pgsql-general
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


Responses

pgsql-general by date

Next:From: George WoodringDate: 2004-10-28 20:14:29
Subject: Issue adding foreign key
Previous:From: Sally SallyDate: 2004-10-28 19:13:14
Subject: Re: primary key and existing unique fields

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