oracle spatial空間データ学習

最近のプロジェクト使用空間データベースoracle spatial
 

--      
 CREATE TABLE SPATIALTEST (
 "ID" VARCHAR2(20) NOT NULL,
 "NAME" VARCHAR2(100),
 "ADDRESS" VARCHAR2(200),
 "TELEPHONE" VARCHAR2(50),
 "LOCATION" "MDSYS"."SDO_GEOMETRY"
 )
 --      
 ALTER TABLE "SPATIALTEST"
 ADD CONSTRAINT "PK_SPATIAL" PRIMARY KEY("ID");
--            ,      ，          ，    ，     
 INSERT INTO USER_SDO_GEOM_METADATA
 VALUES(
 'SPATIALTEST',
 'location',
 MDSYS.SDO_DIM_ARRAY(
 MDSYS.SDO_DIM_ELEMENT('Longitude',-180,180,10),---Longitude   ，       。
 MDSYS.SDO_DIM_ELEMENT('Latitude',-90,90,10)-----Latitude   ，       。
 ),
 4326
 );
--                   ，    
CREATE INDEX SPATIAL_IDX ON SPATIALTEST(location) INDEXTYPE IS MDSYS.SPATIAL_INDEX
 
--      
--1
INSERT INTO SPATIALTEST
VALUES
  ('1',
   '    ',
   '       ',
   '010-123456',
   MDSYS.SDO_GEOMETRY(2001,--       
                      4326,--         
                      MDSYS.SDO_POINT_TYPE(113.3293658, 23.14338586, 0),--        
                      NULL,
                      NULL));
--      
--2
INSERT INTO SPATIALTEST
VALUES
  ('2',
   '  ',
   '         ',
   '010-123456',
   MDSYS.SDO_GEOMETRY(2001,--       
                      4326,
                      MDSYS.SDO_POINT_TYPE(113.2932474, 23.11883515, 0),
                      NULL,
                      NULL));
--     
select location from spatialtest;
select s.location.SDO_POINT.x langtitude from spatialtest s;

--      (113.2359818,23.16937253)       5 
 SELECT B.id id, B.name name, B.dist dist
   FROM (SELECT A.id id,
                A.name name,
                SDO_GEOM.SDO_DISTANCE(A.location,
                                      MDSYS.SDO_GEOMETRY(2001,
                                                         4326,
                                                         MDSYS.SDO_POINT_TYPE(113.2359818,
                                                                              23.16937253,
                                                                              0),
                                                         NULL,
                                                         NULL),
                                      1) dist
           FROM spatialtest A
          WHERE SDO_WITHIN_DISTANCE(A.LOCATION,
                                    MDSYS.SDO_GEOMETRY(2001,
                                                       4326,
                                                       MDSYS.SDO_POINT_TYPE(113.2359818,
                                                                            23.16937253,
                                                                            0),
                                                       NULL,
                                                       NULL),
                                    'distance=10000') = 'TRUE'
          ORDER BY A.name) B
  WHERE ROWNUM <= 5;
 --       (113.2359818,23.16937253)   5   
 SELECT A.id id,
        A.name name,
        A.location.SDO_POINT.x langtitude,
        A.location.SDO_POINT.y latitude,
        MDSYS.SDO_NN_DISTANCE(1) distance
   FROM spatialtest A
  WHERE SDO_NN(A.LOCATION,
               MDSYS.SDO_GEOMETRY(2001,
                                  4326,
                                  MDSYS.SDO_POINT_TYPE(113.2359818,
                                                       23.16937253,
                                                       0),
                                  null,
                                  null),
               'SDO_NUM_RES=5',
               1) = 'TRUE'