I currently have a query that pulls a latitude field and a longitude field (separate fields). This is sufficient for one portion of customer product, but for a second table I need to add a second component that relates the coordinates to a polygon. Is there a query to perform a spatial query or join using SQL (Oracle) without having the vector component, but instead the lat/long values in separate fields?
[GIS] Performing a spatial relationship in Oracle SQL with only lat/long.
oracle-dbmssdo-geometrysql
Related Solutions
Let's assume that your table is like this:
create table central_offices (
id number primary key,
lat varchar2(20),
lng varchar2(20)
);
insert into central_offices (id, lat, lng) values (1, '39.018483', '22.9983436');
commit;
Your strings are stored with a point as decimal separator. When you use this string in a statement that expects a number, Oracle will automatically do the proper type casting and conversion, i.e. parse the string into a number.
BUT: that parsing happens in the context of the locale you use in your session. Since 2100 is a Greek system, I assume you are in Greece, and so use a Greek locale where the decimal separator is a comma. If you try converting the above string to a number, you will get the following error:
SQL> select to_number('39.018483') from dual;
select to_number('39.018483') from dual
*
ERROR at line 1:
ORA-01722: μη αποδεκτός αριθμός
Replacing ',' with '.' on the fly is a valid solution. A simpler approach is just to override the locale for your session:
alter session set nls_numeric_characters = '.,';
select id,
sdo_cs.transform (
sdo_geometry (2001, 4326, sdo_point_type(lng, lat, null), null,null),
2100
).get_wkt()
from central_offices;
ID SDO_CS.TRANSFORM(SDO_GEOMETRY(2001,4326,SDO_POINT_TYPE(LNG,LAT,NULL),NULL,NULL)
---- -------------------------------------------------------------------------------
1 POINT (413136.397473566 4319017.56676377)
Well, you can still just store the location information (I am assuming you mean long/lat GPS coordinates) as two X and Y (or longitude and latitude) number columns. Or you could also use a text string encoded in GeoJSON. Or an XMLTYPE holding a GML encoding.
Those are all possible and legal, and fine if all you want is to store the coordinates, without doing any sort of processing in the database. If you want to perform any kind of spatial querying or processing (like: searching for entities that are within some distance of another, measure the distance between entities etc) then, as Vince indicated, you need to use some specialized data type. The simplest (and cheapest: it's free) is to use Oracle's native spatial type called SDO_GEOMETRY.
That type comes with Oracle Locator, the free part of Oracle Spatial. It is installed by default in all variants of the Oracle Database (including the free Express Edition). If it does not exist in your database, that means that the DBA (or whoever created it) did an explicit choice of NOT including it in the database. To add it, just ask him/her to use the DBCA (Database Configuration Assistant) to add it.
Best Answer
What you need is to use a function-based spatial index, i.e. define a spatial index that constructs a geometry object dynamically. Here is an example on a table called US_CITIES_SX that looks like this:
1) Setup the spatial metadata (needed to build the spatial index)
2) create the function-based spatial index
3) Perform queries
4) Create a view to hide the function call
5) Perform queries on view