SQL Server更新shape空间字段语句

### **一、 更新objectId**

update BGY_YSGDB set OBJECTID = convert(int,SUBSTRING([管线点号], 3,DATALENGTH([管线点号])-1))

### **二、更新点**

update 
    BM_POINT set SHAPE = (geometry::STGeomFromText('POINT ('+ CONVERT(VARCHAR(20),CONVERT(DECIMAL(20,12),
    BM_POINT.Y))+' '+CONVERT(VARCHAR(20),CONVERT(DECIMAL(20,12),
    BM_POINT.X))+')', 0));

### **三、更新线**

UPDATE js 
    SET SHAPE = (
    	geometry :: STGeomFromText (
    		'LINESTRING (' + 
    			CONVERT (VARCHAR ( 20 ),CONVERT ( DECIMAL ( 20, 12 ), js.QDX )) + ' ' +
    			CONVERT (VARCHAR ( 20 ),CONVERT ( DECIMAL ( 20, 12 ), js.QDY )) + ', ' + 
    			CONVERT (VARCHAR ( 20 ),CONVERT ( DECIMAL ( 20, 12 ), js.ZDX )) + ' ' + 
    			CONVERT (VARCHAR ( 20 ),CONVERT ( DECIMAL ( 20, 12 ), js.ZDY )) + ')',
    		0 
    	)) 
    FROM
    	(
    	SELECT
    		BM_LINE.*,
    		( SELECT Y FROM BM_POINT WHERE BM_POINT.[物探点号] = BM_LINE.[起点点号] ) AS QDX,
    		( SELECT X FROM BM_POINT WHERE BM_POINT.[物探点号] = BM_LINE.[起点点号] ) AS QDY,
    		( SELECT Y FROM BM_POINT WHERE BM_POINT.[物探点号] = BM_LINE.[连接方向] ) AS ZDX,
    		( SELECT X FROM BM_POINT WHERE BM_POINT.[物探点号] = BM_LINE.[连接方向] ) AS ZDY 
    	FROM
    		BM_LINE 
    	) js where js.QDX is not null and js.QDY is not null and js.ZDX is not null and js.ZDY is not null AND js.QDX <> js.ZDX AND js.QDY <> js.ZDY;

### **四、更新管径大小**

UPDATE line set line.[模型宽度] = ROUND(convert(FLOAT,SUBSTRING(line.[管径],0,CHARINDEX('X', line.[管径]))) / 100, 2) FROM YX_LINE line WHERE line.[管径] LIKE '%X%';
    
    UPDATE line set line.[模型宽度] = 1 FROM YX_LINE line WHERE line.[管径] is NULL;
    
    UPDATE line set line.[模型宽度] = ROUND(convert(FLOAT,line.[管径]) / 100, 2) FROM YX_LINE line WHERE CHARINDEX('X', line.[管径]) <= 0;

### **五、JS_LINE 数据出现终点和起点坐标一样**

update BGY_YSGDB set OBJECTID = convert(int,SUBSTRING([管线点号], 3,DATALENGTH([管线点号])-1))

[更新shape的sql语句.txt](/api/file/getAttach?fileId=63a3c0179a54ae022200003b)

An's Blog

Navigation

Recent Posts

Friend Links