X

The leading edge of scripting languages and Oracle Database brought to you by the Data Access Development team

  • php
    March 27, 2008

Location Awareness With Oracle Spatial in PHP

Christopher Jones
Senior Principal Product Manager

<?php

// I often see questions about using Oracle's Spatial functionality in
// PHP. Oracle Spatial is a powerful library for adding location
// awareness to applications. This script uses the core subset of
// Spatial, called Oracle Locator, which is included in all Oracle
// Database editions.
//
// A great introduction to Oracle Locator is in the Database Express
// Edition "2 Day Plus Locator Developer Guide". Other Oracle XE guides
// are on the Oracle XE homepage.
//
// The first thing to determine is what functionality you need to do
// in PHP. Is there really a need to fetch the data to PHP? Or can
// you use pre-supplied (or your own) PL/SQL routines? It's often
// faster to keep the processing close to the data and return only the
// minimum amount of data to PHP. This lets Oracle do the data
// processing efficiently, and reduces network traffic between PHP and
// Oracle.
//
// Inserting values is simply a matter of executing the appropriate
// INSERT statement in PHP. This is left as an exercise for the reader!
//
// If you want to fetch values, here are some methods you can use.
//
// This example uses the tables created in the Oracle Database Express
// Edition "2 Day Plus Locator Developer Guide" sample scenario.
// Create these in SQL Developer or SQL*Plus before running this script.

$c = @oci_connect('hr', 'hrpwd', '//localhost/XE'); ;
if (!$c) {
$m = oci_error();
echo $m['message'], "\n";
exit;
}

echo "Simple Column Types\n\n";

// Queries using basic types are no different to other PHP queries.
// The first query finds the three closest customers to the store
// with ID of 101. The query is only of NUMBER and VARCHAR2 columns,
// using an in-built Spatial function SOD_NN() to determine the
// relationship of the customers.

$sql = "SELECT /*+ ordered */
c.customer_id,
c.first_name,
c.last_name
FROM stores s, customers c
WHERE s.store_id = :sid
AND sdo_nn(c.cust_geo_location, s.store_geo_location, :nres) = 'TRUE'";
$sid = 101;
$nres = 'sdo_num_res=3';

$s = oci_parse($c, $sql);
oci_bind_by_name($s, ":sid", $sid);
oci_bind_by_name($s, ":nres", $nres);
oci_execute($s);
oci_fetch_all($s, $res); // or your favorite fetch function
var_dump($res);

// The output is:
//
// array(3) {
// ["CUSTOMER_ID"]=>
// array(3) {
// [0]=>
// string(4) "1001"
// [1]=>
// string(4) "1003"
// [2]=>
// string(4) "1004"
// }
// ["FIRST_NAME"]=>
// array(3) {
// [0]=>
// string(9) "Alexandra"
// [1]=>
// string(6) "Marian"
// [2]=>
// string(6) "Thomas"
// }
// ["LAST_NAME"]=>
// array(3) {
// [0]=>
// string(7) "Nichols"
// [1]=>
// string(5) "Chang"
// [2]=>
// string(8) "Williams"
// }
// }

echo "\nSDO_UTIL.GETVERTICES\n\n";

// If you want to return data to PHP, there are some inbuilt functions
// like SDO_UTIL.GETVERTICES, which return the coordinates from a
// geometry.

$sql = "select t.x, t.y
from customers,
table(sdo_util.getvertices(customers.cust_geo_location)) t
where customer_id = :cid";
$cid = 1001;

$s = oci_parse($c, $sql);
oci_bind_by_name($s, ":cid", $cid);
oci_execute($s);
oci_fetch_all($s, $res); // or your favorite fetch function
var_dump($res);

// The output is:
//
// array(2) {
// ["X"]=>
// array(1) {
// [0]=>
// string(9) "-71.48923"
// }
// ["Y"]=>
// array(1) {
// [0]=>
// string(8) "42.72347"
// }
// }

echo "\nCustom function\n\n";

// Since PHP can't fetch complex Oracle types as PHP objects or
// arrays, sometimes you may want to create a PL/SQL function to
// de-compose the data into simple types. This example uses the
// COLA_MARKETS table from example 1-8. Create this table and
// insert the sample data using SQL Developer or SQL*Plus. Then
// continue with this PHP script.
//
// The example row has a polygon of (x,y) ordinates in the
// SDO_ORDINATE_ARRAY array:
//
// INSERT INTO cola_markets VALUES(
// 301,
// 'polygon',
// SDO_GEOMETRY(
// 2003, -- two-dimensional polygon
// NULL,
// NULL,
// SDO_ELEM_INFO_ARRAY(1,1003,1), -- one polygon (exterior polygon ring)
// SDO_ORDINATE_ARRAY(5,1, 8,1, 8,6, 5,7, 5,1)
// )
// );
//
// By looking at the SDO_GEOMETRY object structure you can query just
// the ordinate collection and create a decomposition function. Use
// an alias (also known as a "correlation name") for the table in the
// query. Thanks to Bryn LLewellyn for pointing this trick out.

$s = oci_parse($c,
"create or replace procedure myproc(
p_id in number, p_o out sdo_ordinate_array) as
begin
select v.shape.sdo_ordinates
into p_o
from cola_markets v
where mkt_id = p_id;
end;");
oci_execute($s);

// Now PHP can fetch the geometry data as a collection

$s = oci_parse($c, "begin myproc(:id, :ords); end;");
$id = 301;
oci_bind_by_name($s, ":id", $id);
$ords = oci_new_collection($c, "SDO_ORDINATE_ARRAY");
oci_bind_by_name($s, ":ords", $ords, -1, OCI_B_NTY);
oci_execute($s);

for ($i = 0; $i < $ords->size(); $i++) {
$v = $ords->getElem($i);
echo "Value: $v\n";
}

// The output is the list of coordinates matching the inserted data:
//
// Value: 5
// Value: 1
// Value: 8
// Value: 1
// Value: 8
// Value: 6
// Value: 5
// Value: 7
// Value: 5
// Value: 1

?>

Be the first to comment

Comments ( 0 )
Please enter your name.Please provide a valid email address.Please enter a comment.CAPTCHA challenge response provided was incorrect. Please try again.