postgres 的sde库的空间函数

wkt数据 POINT(-122.34900 47.65100)

LINESTRING ( 32.75550000 24.20710000, 47.68750000 28.61260000 51.01660000 14.98210000, 35.93630000 69.31130000, 35.68600000 17.02240000)

POLYGON (( 133.41490000 40.38040000, 133.64250000 40.76260000, 133.19420000 40.64110000, 133.95230000 40.93330000, 133.41490000 40.38040000))

如果wkt和sde表的坐标系一致，可以直接用下面这个方法将wkt转shape，这个方法的好处就是不用srid了

SELECT st_geometryfromtext('POINT(112.545539 28.4678)')  说明，在postgis数据库里面创建的sde库才有这个方法

 

wkt转geometry

 

 

查询某个点在范围内的数据

select * FROM feature_field WHERE st_contains(shape,st_point('POINT(-122.34900 47.65100)',4490))

 

查询时报错 > 错误:  ESRI: error coordinate references are not compatible [-2023]: 

SELECT name FROM xzqh_rkfr where ST_Contains(shape, st_geometry('POLYGON (( 112.98333333 28.28333333, 113.00000000 28.28333333, 113.00000000 28.30000000, 112.98333333 28.30000000, 112.98333333 28.28333333))',4490))

　　

select objectid, st_astext(t.shape) as shape from sde.feature_project_trucks t where
st_contains(
(SELECT shape FROM sde.feature_project_fences where status=1 and objectid = #{fence,jdbcType=INTEGER}),t.shape)

两个数据的坐标系不同造成的问题，这个时候查询表xzqh_rkfr的rsid值

 

SELECT * FROM sde.sde_geometry_columns WHERE f_table_name='xzqh_rkfr'

查询出来是2

 

所以将上面语句中的4490改成2

 

如果在做查询某个空间范围类的数据时，报如下错误

可以尝试如下语句

select  * from sde.tdcrk as a INNER JOIN  sde.xfxq as b on st_intersects(a.shape,b.shape) where b.objectid='4';

select  * from sde.xftd as a INNER JOIN  sde.xzqhcs as b on st_intersects(a.shape,b.shape) where b.objectid='5';

SELECT x.objectid,x.crkmc,x.zt,x.bjrbh, u.yhxm,x.xgsj FROM sde.tdcrk x INNER JOIN (SELECT shape FROM sde.xfxq where id = '367a73850a7740d3a2e77c9eea5404cc' limit 1) as a on st_intersects(x.shape,a.shape) LEFT JOIN rds.yhxx u on x.bjrbh=u.yhbm

　　

 如果srid不相同，可以先通过方法 st_geometry('wkt',4490) 转换成相同的srid数据，比如这里将数据转成了4490的数据

比如sde.xfctd是线数据，坐标系是4490，srid是4490；sde.xfxq是面数据，坐标系是4490，但是srid是8；要查询某个面里面的线数据。这个地方需要将shape数据的srid转换成一样的。

下面这个查询数据线根据rds.yhxx表找到sde.xfxq数据

select d.objectid,d.dlbm from sde.xfctd d left join sde.wgdy_5 w on d.wgdm=w.grid_code 
WHERE st_intersects(d.shape,st_geometry((SELECT CAST(st_astext(shape) as text) as shape FROM sde.xfxq where id = (select xzqhdm from rds.yhxx where yhbm='367a73850a7740d3a2e77c9eea5404aa') limit 1),4490))

select d.objectid,d.dlbm from sde.xfctd d left join sde.wgdy_5 w on d.wgdm=w.grid_code 
WHERE st_intersects(d.shape,st_geometry((SELECT CAST(st_astext(shape) as text) as shape FROM sde.xfxq where id = '367a73850a7740d3a2e77c9eea5404cc'),4490))

　

查询某个点10米范围内的数据

SELECT OBJECTid,dzqc FROM dmdz WHERE ST_DWithin(st_point('POINT(123.48603435 30.21406115)',4490),shape, 1.5696123057604772E-6)

ST_DWithin 第三个参数的单位是度。米转度的方法

// 地球半径，单位：米
private static final double EARTH_RADIUS = 6371000;

// 将距离（以米为单位）转换为经度的度数
private static double metersToDegrees(double meters) {
    return meters / EARTH_RADIUS;
}

 

 

 

如果是docker安装的postgres数据库，lib目录在

 

两个数据的坐标系不同造成的问题，这个时候查询表xzqh_rkfr的rsid值

 

SELECT * FROM sde.sde_geometry_columns WHERE f_table_name='xzqh_rkfr'

查询出来是2

geometry点数据

 

{spatialReference: {wkid: 4490}, x: 112.99081138055888, y: 28.275827099156608}

 

　　

 

 

 geometry线数据

{
	"spatialReference": {
		"wkid": 4490
	},
	"paths": [
		[
			[112.98905540486572, 28.13629496962604],
			[113.01220823308228, 28.121919938631617],
			[112.98958916445977, 28.090485790081626],
			[112.96252835770852, 28.08555857212127]
		]
	]
}

 

geometry面数据

{
	"hasZ": true,
	"spatialReference": {
		"wkid": 4490
	},
	"rings": [
		[
			[119.56000572540604, 29.111288937097672, 0],
			[119.57821610876529, 29.09949148091114, 0],
			[119.57423315231752, 29.092214497408396, 0],
			[119.5512615002429, 29.09984882914254, 0],
			[119.56000572540604, 29.111288937097672, 0]
		]
	]
}