Home » Server Options » Spatial » Assigning geometry to variable
Assigning geometry to variable [message #505497] Tue, 03 May 2011 06:41 Go to next message
Messages: 1
Registered: May 2011
Junior Member

Can someone explain how variables are assigned that contain a geometry type, that can be used in e.g. sdo_nn or sdo_filter.

For example:

I have a table Municipalities with various columns, including:
mun_name varchar
state varchar
shape sdo_geometry

Let's say I want to find the 5 closest municipalities to Colorado, that are located in Kansas. I'm trying with:

select m.mun_name
from Municipalities m
where m.state = 'Kansas'
sdo_nn(m.shape, :Colorado,'sdo_num_res=5') = 'True'

How do I assign the Colorado geometry to the variable?
Would the above code work, or should I use sdo_filter instead/also?

Thanks for any help

Re: Assigning geometry to variable [message #505547 is a reply to message #505497] Tue, 03 May 2011 11:46 Go to previous message
Messages: 573
Registered: February 2008
Senior Member
You have to use the municipality table twice:
SELECT m2.mun_name
  FROM municipalities m1,  municipalities m2
 WHERE m1.state= 'Colorado' 
   AND m2.state = 'Kansas'
   AND sdo_nn (m1.shape, m2.shape, 'sdo_num_res=5') = 'True';
Previous Topic: query to find first value of array (3 threads merged by bb)
Next Topic: how to use collections and forall insert
Goto Forum:

Current Time: Wed May 05 20:47:48 CDT 2021