mysql-Graph-geoserver

发表于 更新于 分类于 Waline：

geoserver绘制形状

绘制矩形图形(POLYGON)

注意事项：第一个点和最后一个点必须相同，因此矩形至少是5个点

geoserver

1
2
3
4
5
--添加面
SET @g = 'POLYGON((114.34845 25.48141, 114.34845 25.28141, 114.51599 25.28141, 114.51599 25.48141, 114.34845 25.48141))';
INSERT INTO test(shape) VALUES (ST_PolygonFromText(@g));
--添加点
SET @g = ST_GeomFromText('POINT(114.44845 25.38141)'); INSERT INTO test(shape) VALUES (@g);

表结构：

名 类型
id int
shape geometry
name varchar

常见语句

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16

-- 插入
SET @g = ST_GeomFromText('POINT(109.49097 19.06798)',1);
 INSERT INTO infcamer(shape) VALUES (@g);
-- 更新 
UPDATE `功能分区面` set SHAPE=ST_PolygonFromText(@g,1) WHERE OGR_FID=1; 
-- 查询坐标是否正确设置
SELECT * FROM infcamer WHERE ST_Contains(SHAPE, ST_GeomFromText( 'POINT(109.49097 19.06798)',0))
-- 查询空间坐标相关设置
SELECT * FROM spatial_ref_sys LIMIT 0, 50;
-- geoserver数据库
GEOGCS["WGS 84",DATUM["WGS_1984",SPHEROID["WGS 84",6378137,298.257223563,AUTHORITY["EPSG","7030"]],AUTHORITY["EPSG","6326"]],PRIMEM["Greenwich",0,AUTHORITY["EPSG","8901"]],UNIT["degree",0.0174532925199433,AUTHORITY["EPSG","9122"]],AUTHORITY["EPSG","4326"]]
-- test数据库
GEOGCS["GCS_WGS_1984",DATUM["WGS_1984",SPHEROID["WGS_1984",6378137.0,298.257223563]],PRIMEM["Greenwich",0.0],UNIT["Degree",0.0174532925199433]]

GEOGCS["GCS_WGS_1984",DATUM["WGS_1984",SPHEROID["WGS_1984",6378137.0,298.257223563]],PRIMEM["Greenwich",0.0],UNIT["Degree",0.0174532925199433],METADATA["World",-180.0,-90.0,180.0,90.0,0.0,0.0174532925199433,0.0,1262]]

镂空面

数据格式为

POLYGON((a a, b b,a a),(c c,d d, c c))

常见问题

  1. 点坐标查询提示[Err] 3033 - Binary geometry function st_contains given two geometries of different srids: 0 and 1, which should have been identical.

    分析：由于插入时的srid不一致，用SELECT * FROM infcamer WHERE ST_Contains(SHAPE, ST_GeomFromText( 'POINT(109.49097 19.06798)'))查询时没有指定srid，所以报错提示有不同的srid

    解决1：查询时指定srid例如：SELECT * FROM infcamer WHERE ST_Contains(SHAPE, ST_GeomFromText( 'POINT(109.49097 19.06798)',0))

    解决2：插入时指定srid,指定的srid最好和原有记录的srid一致，这样就不会存在different srids: 0 and 1，例如：SET @g = ST_GeomFromText('POINT(109.49097 19.06798)',0); INSERT INTO infcamer(shape) VALUES (@g);

  2. 在Navicat客户端看不到完整数据，最好导出看

参考

Mysql官方文档

Mysql的空间扩展 较全，值得一看

mysql ogr2ogr error