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
Views: Raw Message | Whole Thread | Download mbox | Resend email
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

Browse pgsql-general by date

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