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
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 messageGo to next message
Barbara Boehmer
Messages: 9077
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

Re: Address parsing using SDO_GCDR? [message #495370 is a reply to message #494454] Fri, 18 February 2011 09:36 Go to previous message
mr2001
Messages: 2
Registered: February 2011
Junior Member
Thank you very much Barbara for taking the time to provide such a detailed response!
Using ctx_entity package is a very interesting solution for parsing addresses.

Best regards,
M.R.
Previous Topic: Spatial Question
Next Topic: Update query
Goto Forum:
  


Current Time: Thu Mar 28 04:40:25 CDT 2024