Skip to Main Content
  • Questions
  • Spatial queries involving lattitude and longitude

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question, Rajeshwaran.

Asked: October 13, 2018 - 4:24 am UTC

Last updated: November 18, 2019 - 12:24 pm UTC

Version: 11.2.0.3

Viewed 1000+ times

You Asked

Team,

We have a table in an application that has longitude and lattitue as NUMBER datatype.
also composite index exists on the columns (LATITUDE, LONGITUDE) in this order.

the sql from the application goes like this.

select *
from the_table
where Latitude BETWEEN 39.188920190130624 AND 42.091677809869374
AND longitude BETWEEN -75.78729387674332 AND -72.017976123256688 ;

this index doesn't help us here, and it goes for Full table scan. so planning to have a spatial index like this.

alter table the_table add x sdo_geometry;

update the_table
set x = sdo_geometry( 2001,null,sdo_point_type( LONGITUDE ,LATITUDE,null),null,null )
where latitude is not null;
commit;

insert into user_sdo_geom_metadata( table_name,column_name,diminfo,srid)
values( 'THE_TABLE' , 'X', mdsys.sdo_dim_array( mdsys.sdo_dim_element('X',-180,180,0.00005),
mdsys.sdo_dim_element('Y',-90,90,0.00005)),null);
commit;

create index test_idx
on the_table( x )
indextype is mdsys.spatial_index;

exec dbms_stats.gather_table_stats( user,'the_table');

so, could you help us how to rewrite the above sql statement where clause? do i need to go with LOCATOR_WITHIN_DISTANCE or SDO_COVERS operator?

with LiveSQL Test Case:

and we said...

I spoke to one of the Spatial Product Managers to get this response:

Using spatial queries is definitely the better option, especially for long/lat coordinates, and is the only way if you want to search for more than points in a rectangle. For that however, we need actual geometry objects (in SDO_GEOMETRY). I am starting with this table:

create table t1 as 
select 
  rownum id, 
  rpad(rownum,10) data,
  dbms_random.value(-90,90) lat,
  dbms_random.value(-180,180) lng
from   
 (select 1 from dual connect by level <= 1000),
 (select 1 from dual connect by level <= 1000);


Then I expand that table with a geometry column:

alter table t1 add location sdo_geometry;


And I populate the geometries:

update t1 set location = sdo_geometry(2001,4326,sdo_point_type(lng,lat,null),null,null);
commit;


Notice the number 4326. This number is a spatial reference system ID (or SRID). It says that the coordinates are not just any random numbers, but that they are longitude and latitude in GPS coordinates (also known as WGS84 coordinates). This is extremely important. If we do not tell the database what coordinate system we are using, then we will not be able to to meaningful comparisons or measurements. This is especially true for spherical coordinates (long/lat). If we do not say that they are spherical, then any measurement (area, length, distances, ...) will be totally wrong. Also we will not be able to correlate them with other geographic shapes, in other coordinate systems.

Coordinate systems for spatial data are like time zones for time. If I call for a tele conference at 2PM and send that to my people in Moscow, Berlin, Tokyo and New York, all of them will dial-in at 2PM - and find no-one else: they dialed at 2PM in their time zone. I need to specify 2PM CET for example. Same for spatial coordinates: if I do not specify in which coordinate system they are expressed, I will be unable to compare them.

Coordinate systems come in two flavors. Those that are represented on the spherical representation of the globe (called geodetic systems). And where you may think there is only one universal long/lat system, there are actually hundreds of different long/lats, each one using a slightly different representation of the earth, adapted to local conditions. But when nothing is specified, then in 99.9% of the case, it means the long/lat coordinates are in the WGS84 system, the one used by the GPS (and the other global positioning system). Incidentally: the earth is NOT a sphere. It is really slightly flattened at the poles (due to its rotation) … Also there used to be some systems using a different meridian for the origin (other than Greenwich): some old french systems used the Paris meridian (obviously).

The other kind of coordinate systems are projections: the coordinates are meters from some origin. There are literally thousands of those (the oracle database has some 5000 of them). Each country has several because projections lose accuracy on large areas. Each US state has several projections. There are also old and new projections - Oracle provides functions to convert from one coordinate system to the other.

To use the spatial types, you need to define metadata:

insert into user_sdo_geom_metadata (table_name,column_name,diminfo,srid)
values ( 
  'T1' , 
  'LOCATION', 
  sdo_dim_array(
    sdo_dim_element('Longitude',-180,180,0.05), 
    sdo_dim_element('Latgitude',-90,90,0.05)
  ),
  4326
);
commit;


Where:

- T1 is the table name and LOCATION the name of the SDO_GEOMETRY column.
- The DIMINFO structure describes the bounds of each dimension. For long/lat this is always -180 to 180 and -90 to 90.
- The value 0.05 in this structure (the same for each dimension) represents the tolerance of the spatial data in that table. It is expressed in meters and represents the minimum distance between two points for being considered distinct. It is not very important when dealing with customer locations (since those are very approximate), but it is important when it comes to determine if two adjacent land parcels are really adjacent. Here 0.05 means 5cm. It also means that if two parcels are separated

create index t1_sx on t1 (location) indextype is mdsys.spatial_index;


That is actually a resource-consuming operation, quite CPU-intensive, more so for long/lat coordinates.

Now we can start doing some queries. To do some of them, we use a table that contains the shape of all countries worldwide. You can find it along with other tables via SPATIAL_COUNTRIES and importing via traditional import. The imported tables are:

TABLE_NAME                       NUM_ROWS
------------------------------ ----------
US_CITIES                             195
US_COUNTIES                          3230
US_INTERSTATES                        239
US_PARKS                             6371
US_RASTERS                              1
US_RIVERS                              56
US_STATES                              56
WORLD_CONTINENTS                      111
WORLD_COUNTRIES                       251

31 rows selected.


And then we can do some sample queries:

1) The original query:

select t1.id
from t1
where sdo_inside (
        location,
        sdo_geometry(
          2003,4326,null,
          sdo_elem_info_array(1,1003,3),
          sdo_ordinate_array (
            -75.78729387674332,  39.188920190130624, 
            -72.017976123256688, 42.091677809869374 
          )
        )
      ) = 'TRUE';



2) Find all points within 20km from a given point:

select b.id
from t1 a, t1 b
where a.id = 42
and sdo_within_distance (b.location, a.location, 'distance=20 unit=km') = 'TRUE'; 

        ID
----------
     29373
        42
    954025
    922187

4 rows selected.


3) How far are they ?

select b.id, sdo_geom.sdo_distance(a.location, b.location, 0.05, 'unit=m') distance
from t1 a, t1 b
where a.id = 42
and sdo_within_distance (b.location, a.location, 'distance=20 unit=km') = 'TRUE'
order by distance;

        ID   DISTANCE
---------- ----------
        42          0
     29373  10881.377
    954025 12654.2977
    922187 17152.4322

4 rows selected.

Note that point 42 is the closest - at a distance of 0 meters.

4) How many points are in Germany ?

select count(*)
from t1 p, world_countries w
where sdo_inside (p.location, w.geometry) = 'TRUE'
and w.cntry_name = 'Germany';

  COUNT(*)
----------
       716

1 row selected.


5) How many points in each country ?

select w.cntry_name, count(*)
from t1 p, world_countries w
where sdo_inside (p.location, w.geometry) = 'TRUE'
group by w.cntry_name
order by w.cntry_name;

CNTRY_NAME                                 COUNT(*)
---------------------------------------- ----------
Afghanistan                                     970
Albania                                          51
Algeria                                        3315
Andorra                                           1
Angola                                         1572
Antarctica                                    81817
Antigua and Barbuda                               1
...
Western Samoa                                     1
Yemen                                           533
Zaire                                          2901
Zambia                                         1023
Zimbabwe                                        499

203 rows selected.

6) Countries with the largest number of points

select w.cntry_name, count(*) npoints
from t1 p, world_countries w
where sdo_inside (p.location, w.geometry) = 'TRUE'
group by w.cntry_name
order by npoints desc
fetch first 5 rows only;

CNTRY_NAME                                  NPOINTS
---------------------------------------- ----------
Antarctica                                    81817
Russia                                        45255
Canada                                        26235
United States                                 17071
China                                         14642

5 rows selected.


7) Points not in any country

select count(*)
from t1
where id not in (
  select p.id
  from t1 p, world_countries w
  where sdo_inside (p.location, w.geometry) = 'TRUE'
);

  COUNT(*)
----------
    680527

1 row selected.

8) Points within a radius of 100km around Denver

select count(*)
from t1, us_cities c
where c.city = 'Denver'
and sdo_within_distance (t1.location, c.location, 'distance=100 unit=km') = 'TRUE'; 

  COUNT(*)
----------
        51

1 row selected.


Rating

  (1 rating)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Can you adapt this techique for BETWEEN queries

oj, November 17, 2019 - 8:20 pm UTC

Connor, could we adapt your Spatial technique to a BETWEEN query which involves 2 columns and just simple numbers ?

e.g.

select *
from   some_table
where  start_key <= :1 
and    end_key   >= :1
and    rownum < 2


I've followed a technique in this link;

https://www.orafaq.com/node/1798

...which works well for my SINGLE ROW query and indexed on (end_key, start_key).

However, performance ain't so great when there's is no rows found for the BIND value.

Looking at your Spatial example, something like this perhaps;

select *
from   some_table st
where  sdo_within_distance (st.start_key, st.end_key, '??? not sure what goes here ???') = 'TRUE'; 


I just wasn't sure how I can get Spatial (the SDO_GEOMETRY column and it's index) to work for "normal" numbers that aren't latitude/longitude.

I would like to try Spatial out to see if it can handle BETWEEN queries better than the method I'm currently using which works well ONLY when there is data found.


Connor McDonald
November 18, 2019 - 12:24 pm UTC

Yeah, I'm not sure that is going to fly.

You would need to map max/min keys to the allowable lat/long ranges...and explaining *that* the next person that needs to maintain your code is going to be ..... fun :-)

I'd perhaps look at some granular 2-level partitioning on start and end keys. The indexing then gives you fast single row lookups, and the partitioning give you fast range lookups.

More to Explore

PL/SQL demos

Check out more PL/SQL tutorials on our LiveSQL tool.

PL/SQL docs

PL/SQL reference manual from the Oracle documentation library