2

I have a data table like this:

CREATE TABLE devices (id INTEGER PRIMARY KEY NOT NULL, shape SDE.ST_GEOMETRY)

For a given GPS location (latitude, longitude), I'd like to query all objects within 50 meters. How to achieve that?

I'm so frustrated in searching many many docs, and no result till now.

One idea is to use the function on Eris (company who developed ArcGis) web site:

sde.st_distance (g1 sde.st_geometry, g2 sde.st_geometry)

However the return value is described as "double precision", how to use that with GPS? And many other functions (e.g. Oracle built-in functions) work with different type I think (SDO_GEOMETRY). http://webhelp.esri.com/arcgisserver/9.3/java/index.htm#geodatabases/st_distance.htm

Another idea is to create an ArcGis shape object (sde.st_geometry), a circle object with 50 meters radius and use sde.st_contains function (http://webhelp.esri.com/arcgisserver/9.3/java/index.htm#geodatabases/st_contains.htm). However, how can I create such a geometry object?

And all the geo object creation SQL (INSERT...) in the example fails on my Oracle DB, with error liek "wrong number or types of arguments in call to 'ST_POINT'.

I think this should be a fundamental requirement in most GPS based applications. Sorry for the mess...

Edit: I found there's oracle functions which deals with geo data, but not sure whether this works with SDE.ST_GEOMETRY object? http://docs.oracle.com/cd/B12037_01/appdev.101/b10826/sdo_complex_queries.htm#sthref2131

Verilocos
  • 115
  • 9
  • 1
    The ST_GEOMETRY functions require some [binaries to be installed](http://resources.arcgis.com/en/help/main/10.1/index.html#//019v0000000s000000) and available to run on your oracle database server, are these available in your instance ? If there is no reason use the ST_GEOMETRY datatype you may have more luck with the native SDO_GEOMETRY, and the [SDO_WITHIN_DISTANCE](http://docs.oracle.com/cd/B19306_01/appdev.102/b14255/sdo_operat.htm#i77653) – kes Feb 12 '15 at 17:02

1 Answers1

0

Assuming you can get your device.shape data into the table using the SDO_GEOMETRY data type, then using the spatial operator SDO_WITHIN_DISTANCE should do the trick. The following query selects all devices within 50 meters of the longitude and latitude coordinates -71.3033, 44.2706.

select * from devices 
where sdo_within_distance (devices.shape, sdo_geometry(2001,4326, 
sdo_point_type(-71.3033,44.2706,null),null,null),
'distance=50 unit=meter') = 'TRUE'
James Lawruk
  • 30,112
  • 19
  • 130
  • 137