In Oracle: I have a tablespace that records the positions of many cars. Each record has a mileage, time, LONGITUDE and LATITUDE. The tablespace has five columns: 'PLATENO' ‘SENDTIME’, ‘MILEAGE’, and 'LONGITUDE' 'LATITUDE'. They represent the plate number of cars, positon send time, mileage and two coordinates of cars respectively. The values in the ‘SENDTIME’ column are in the datetime format. ALSO, i have some coordinates of buildings. I want to find out the vehicles whose trajectory passes 200 meters near the buildings. So i create a table named buildings to record the buildings, something like this:
CREATE TABLE BUILDINGS (
NAME VARCHAR2(50) NOT NULL,
ADDR VARCHAR2(50) NOT NULL,
LONGITUDE NUMBER NOT NULL,
LATITUDE NUMBER NOT NULL
);
INSERT INTO BUILDINGS (NAME, ADDR, LONGITUDE, LATITUDE) VALUES (
'北京市大兴区永兴路7号院1号楼1F2F局部内装修工程', '北京市大兴区永兴路7号院1号楼',
116.314497, 39.685536);
INSERT INTO BUILDINGS (NAME, ADDR, LONGITUDE, LATITUDE) VALUES (
'北京金沃夫生物工程科技有限公司23号楼诊断试剂GMP生产车间及配套实验室、研发实验室建设项目', '北京市大兴区华佗路50号院',
116.282965, 39.668402);
Then I use sdo_geom.sdo_distance function to calculate distance to find the reords to meet my requirements.
select g.PLATENO, g.SENDTIME, g.MILEAGE, g.LONGITUDE, g.LATITUDE,
b.NAME as BUILDING_NAME, b.ADDR, b. LONGITUDE, b.LATITUDE
from GPSINFO g, BUILDINGS b
where sdo_geom.sdo_distance (
sdo_geometry (2001, 4326, sdo_point_type (g.LONGITUDE, g.LATITUDE, null), null, null),
sdo_geometry (2001, 4326, sdo_point_type (b.LONGITUDE, b.LATITUDE, null), null, null),
0.01,
'unit=M'
) <= 200;
But the query runs rather slowly. I only need three records that match my criteria. How to optimize my code? i tried FETCH FIRST, but it doesn't work：
select v.PLATENO, v.SENDTIME, v.MILEAGE, v.LONGITUDE, v.LATITUDE, b.NAME as BUILDING_NAME
from VEHICLES v, BUILDINGS b
where sdo_geom.sdo_distance (
sdo_geometry (2001, 4326, sdo_point_type (v.LONGITUDE, v.LATITUDE, null), null, null),
sdo_geometry (2001, 4326, sdo_point_type (b.LONGITUDE, b.LATITUDE, null), null, null),
0.01,
'unit=M'
) <= 50
and HAVERSINE(v.LONGITUDE, v.LATITUDE, b.LONGITUDE, b.LATITUDE) <= 50
ORDER BY v.SENDTIME DESC
FETCH FIRST 3 ROWS ONLY;
Rather than storing the longitude and latitude as NUMBER data types and then converting to SDO_GEOMETRY in the query, you can store them as SDO_GEOMETRY types and add a spatial index:
CREATE TABLE BUILDINGS (
NAME VARCHAR2(200) NOT NULL,
ADDR VARCHAR2(200) NOT NULL,
LOCATION SDO_GEOMETRY NOT NULL
);
INSERT INTO USER_SDO_GEOM_METADATA (
TABLE_NAME, COLUMN_NAME, DIMINFO, SRID
) VALUES (
'BUILDINGS',
'LOCATION',
SDO_DIM_ARRAY(
SDO_DIM_ELEMENT('LONG', -180.0, 180.0, 0.0001),
SDO_DIM_ELEMENT('LAT', -90.0, 90.0, 0.0001)
),
4326
);
CREATE INDEX Buildings_SIDX ON Buildings( location ) INDEXTYPE IS MDSYS.SPATIAL_INDEX;
Then you can insert the data:
INSERT INTO BUILDINGS (NAME, ADDR, LOCATION)
VALUES (
'北京市大兴区永兴路7号院1号楼1F2F局部内装修工程',
'北京市大兴区永兴路7号院1号楼',
sdo_geometry(2001, 4326, sdo_point_type (116.314497, 39.685536, null), null, null)
);
INSERT INTO BUILDINGS (NAME, ADDR, LOCATION)
VALUES (
'北京金沃夫生物工程科技有限公司23号楼诊断试剂GMP生产车间及配套实验室、研发实验室建设项目',
'北京市大兴区华佗路50号院',
sdo_geometry(2001, 4326, sdo_point_type (116.282965, 39.668402, null), null, null)
);
If you want to get the components of the location then you can use the nested objects (note: you need to ensure you start with the table alias):
SELECT b.name,
b.addr,
b.location.sdo_point.x AS longitude,
b.location.sdo_point.y AS latitude
FROM buildings b
Which outputs:
| NAME | ADDR | LONGITUDE | LATITUDE |
|---|---|---|---|
| 北京市大兴区永兴路7号院1号楼1F2F局部内装修工程 | 北京市大兴区永兴路7号院1号楼 | 116.314497 | 39.685536 |
| 北京金沃夫生物工程科技有限公司23号楼诊断试剂GMP生产车间及配套实验室、研发实验室建设项目 | 北京市大兴区华佗路50号院 | 116.282965 | 39.668402 |
You can make the same change to the GPSINFO table and then your queries will not need to generate lots of objects at run-time and you can take advantage of the indexes.
You also do not need to use both spatial comparisons and your HAVERSINE function.