oracle spatial

-- 创建测试用表
CREATE TABLE "SPATIALTEST" (
	"ID" VARCHAR2(20) NOT NULL,
	"NAME" VARCHAR2(100),
	"ADDRESS" VARCHAR2(200),
	"TELEPHONE" VARCHAR2(50),
	"LOCATION" "MDSYS"."SDO_GEOMETRY"
)LOGGING;

-- 创建主键约束
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),
			MDSYS.SDO_DIM_ELEMENT('Latitude',-90,90,10)
		),
		8307
	);

-- 建立空间索引
CREATE INDEX SPATIAL_IDX
	ON SPATIALTEST(location)
	INDEXTYPE IS MDSYS.SPATIAL_INDEX;

-- 导入数据测试
INSERT INTO "SPATIALTEST"
	VALUES(
		'dbeb7ea11eaf2b53a9b7',
		'小肥羊(天河店)',
		'广州市天河区天寿路25号',
		'020-38217746',
		MDSYS.SDO_GEOMETRY(
			2001,
			8307,
			MDSYS.SDO_POINT_TYPE(113.3293658, 23.14338586, 0),
			NULL,
			NULL
		)
	);
INSERT INTO "SPATIALTEST"
	VALUES(
		'ef8393ef6273a72b2f70',
		'山东老家',
		'广州市越秀区合群一马路43号',
		'020-87778983',
		MDSYS.SDO_GEOMETRY(
			2001,
			8307,
			MDSYS.SDO_POINT_TYPE(113.2932474, 23.11883515, 0),
			NULL,
			NULL
		)
	);

/*
--Insert a topological element
INSERT INTO cola_markets VALUES(
	2,
	'cola_b',
	MDSYS.SDO_GEOMETRY(
		2003,  -- 2-dimensional polygon
		NULL,
		NULL,
		MDSYS.SDO_ELEM_INFO_ARRAY(1,1003,1), -- one polygon (exterior polygon ring)
		MDSYS.SDO_ORDINATE_ARRAY(5,1, 8,1, 8,6, 5,7, 5,1)
	)
);
*/

-- 属性信息查询
SQL> select location from spatialtest;

LOCATION(SDO_GTYPE, SDO_SRID, SDO_POINT(X, Y, Z), SDO_ELEM_INFO, SDO_ORDINATES)
--------------------------------------------------------------------------------
SDO_GEOMETRY(2001, 8307, SDO_POINT_TYPE(113.329366, 23.1433859, 0), NULL, NULL)

SQL> select s.location.SDO_POINT.x langtitude from spatialtest s;

LANGTITUDE
----------
113.329366

-- 空间分析查询(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,8307,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,8307,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,8307,MDSYS.SDO_POINT_TYPE(113.2359818,23.16937253,0),null,null),'SDO_NUM_RES=5',1) = 'TRUE'
;

/*
-- Return the topological difference of two geometries.
SELECT SDO_GEOM.SDO_DIFFERENCE(c_a.shape, m.diminfo, c_c.shape, m.diminfo)
  FROM cola_markets c_a, cola_markets c_c, user_sdo_geom_metadata m
  WHERE m.table_name = 'COLA_MARKETS' AND m.column_name = 'SHAPE'
  AND c_a.name = 'cola_a' AND c_c.name = 'cola_c';
*/

转载自：https://blog.csdn.net/fatway/article/details/2493763