Home » Server Options » Spatial » Address parsing using SDO_GCDR? (11g R2)
Address parsing using SDO_GCDR? [message #493502] |
Tue, 08 February 2011 08:34 ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
![](http://www.gravatar.com/avatar/01f450e3c09f67c53e7755244a6d965f?s=64&d=mm&r=g) |
mr2001
Messages: 2 Registered: February 2011
|
Junior Member |
|
|
Hello,
Is it possible to use functions from package SDO_GCDR to perform an address parsing?
For example if I have the following address:
1339 Concourse St. Ottawa Ontario
would be possible to extract Street address, City and Province? (I don't care about geospatial coordinates).
Thank you,
M.R.
[Updated on: Tue, 08 February 2011 08:35] Report message to a moderator
|
|
|
Re: Address parsing using SDO_GCDR? [message #494454 is a reply to message #493502] |
Mon, 14 February 2011 15:51 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
![](http://www.orafaq.com/forum/images/custom_avatars/43710.gif) |
Barbara Boehmer
Messages: 9097 Registered: November 2002 Location: California, USA
|
Senior Member |
|
|
If you don't care about geospatial coordinates and just want to parse the address, then you can use the ctx_entity package of Oracle Text. I have provided a brief simple demonstration below. There are many options for customization. You can add user dictionaries and rules using regular expressions and do the extraction in functions or procedures or triggers. You can also add your own parsing using substr and instr, as I have done below.
SCOTT@orcl_11gR2> -- create extract policy with default rules:
SCOTT@orcl_11gR2> EXEC CTX_ENTITY.CREATE_EXTRACT_POLICY ('pol1')
PL/SQL procedure successfully completed.
SCOTT@orcl_11gR2> -- add user-supplied rule to policy
SCOTT@orcl_11gR2> -- to add Canadian provinces and territoreis:
SCOTT@orcl_11gR2> BEGIN
2 CTX_ENTITY.ADD_EXTRACT_RULE
3 ('pol1',
4 1,
5 '<rule>
6 <expression>(Alberta|British Columbia|Manitoba|New Brunswick|Newfoundland|Labrador|Nova Scotia|Ontario|Prince Edward Island|Quebec|Saskatchewan|Northwest Territories|Nunavut|Yukon)</expression>
7 <type refid="1">state</type>
8 </rule>');
9 END;
10 /
PL/SQL procedure successfully completed.
SCOTT@orcl_11gR2> -- compile policy:
SCOTT@orcl_11gR2> EXEC ctx_entity.compile ('pol1')
PL/SQL procedure successfully completed.
SCOTT@orcl_11gR2> -- create address_typ object:
SCOTT@orcl_11gR2> CREATE OR REPLACE TYPE address_typ AS OBJECT
2 (street_address VARCHAR2 (23),
3 city VARCHAR2 (14),
4 province_or_state VARCHAR2 (19),
5 zip_code VARCHAR2 (20),
6 country VARCHAR2 (13));
7 /
Type created.
SCOTT@orcl_11gR2> -- create function to parse a single address and return address_typ:
SCOTT@orcl_11gR2> CREATE OR REPLACE FUNCTION parse_address
2 (p_full_address IN VARCHAR2)
3 RETURN address_typ
4 AS
5 v_entities CLOB;
6 v_address address_typ;
7 BEGIN
8 CTX_ENTITY.EXTRACT ('pol1', p_full_address, null, v_entities);
9 SELECT address_typ
10 (null,
11 MAX (DECODE (foo.type, 'city', foo.text)),
12 MAX (DECODE (foo.type, 'state', foo.text)),
13 MAX (DECODE (foo.type, 'zip_code', foo.text)),
14 MAX (DECODE (foo.type, 'country', foo.text)))
15 INTO v_address
16 FROM (SELECT XMLTYPE (v_entities) ents
17 FROM DUAL) e,
18 XMLTABLE
19 ('/entities/entity'
20 PASSING e.ENTS
21 COLUMNS
22 offset NUMBER PATH '@offset',
23 lngth NUMBER PATH '@length',
24 text VARCHAR2(50) PATH 'text/text()',
25 type VARCHAR2(50) PATH 'type/text()',
26 source VARCHAR2(50) PATH '@source') as foo;
27 v_address.province_or_state :=
28 NVL
29 (v_address.province_or_state,
30 SUBSTR (v_address.zip_code, 1, INSTR (v_address.zip_code, ' ') - 1));
31 v_address.zip_code := SUBSTR (v_address.zip_code, INSTR (v_address.zip_code, ' ') + 1);
32 v_address.street_address :=
33 RTRIM
34 (SUBSTR
35 (p_full_address,
36 1,
37 INSTR (p_full_address, v_address.city) - 1));
38 RETURN v_address;
39 END parse_address;
40 /
Function created.
SCOTT@orcl_11gR2> SHOW ERRORS
No errors.
SCOTT@orcl_11gR2> -- example usage of function:
SCOTT@orcl_11gR2> WITH
2 addresses (full_address) AS
3 (SELECT '1339 Concourse St. Ottawa Ontario Canada' FROM DUAL UNION ALL
4 SELECT '144 - 2600 - 4th Ave SW CALGARY, Alberta Canada' FROM DUAL UNION ALL
5 SELECT '500 Oracle Parkway Redwood Shores, California United States' FROM DUAL)
6 SELECT t.a.street_address,
7 t.a.city,
8 t.a.province_or_state,
9 t.a.country
10 FROM (SELECT parse_address (full_address) a
11 FROM addresses) t
12 /
A.STREET_ADDRESS A.CITY A.PROVINCE_OR_STATE A.COUNTRY
----------------------- -------------- ------------------- -------------
1339 Concourse St. Ottawa Ontario Canada
144 - 2600 - 4th Ave SW CALGARY Alberta Canada
500 Oracle Parkway Redwood Shores California United States
3 rows selected.
SCOTT@orcl_11gR2>
[Updated on: Mon, 14 February 2011 16:03] Report message to a moderator
|
|
|
|
Goto Forum:
Current Time: Sat Sep 07 18:22:52 CDT 2024
|