数据库为PostGIS
,使用引擎为npgsql
官方资料
PostgreSQL
PostGIS
postgis的空间查询文档
npgsql
三者关系
PostGIS
是基于PostgreSQL
的空间数据管理的扩展,npgsql
是基于C#的PostgreSQL的数据引擎
查询字段信息
numeric_scale是小数位
SELECT a.attnum, a.attname AS field, t.typname AS type, a.attlen AS length, a.atttypmod AS lengthvar, a.attnotnull AS notnull, b.description AS comment, CASE atttypid WHEN 21 /*int2*/ THEN 16 WHEN 23 /*int4*/ THEN 32 WHEN 20 /*int8*/ THEN 64 WHEN 1700 /*numeric*/ THEN CASE WHEN atttypmod = -1 THEN null ELSE((atttypmod - 4) >> 16) & 65535-- calculate the precision END WHEN 700 /*float4*/ THEN 24 /*FLT_MANT_DIG*/ WHEN 701 /*float8*/ THEN 53 /*DBL_MANT_DIG*/ ELSE null END AS numeric_precision, CASE WHEN atttypid IN(21, 23, 20) THEN 0 WHEN atttypid IN(1700) THEN CASE WHEN atttypmod = -1 THEN null ELSE(atttypmod - 4) & 65535-- calculate the scale END ELSE null END AS numeric_scale FROM pg_class c, pg_attribute a LEFT OUTER JOIN pg_description b ON a.attrelid = b.objoid AND a.attnum = b.objsubid, pg_type t WHERE c.relname = 'tableName' and a.attnum > 0 and a.attrelid = c.oid and a.atttypid = t.oid ORDER BY a.attnum;
参考资料
PostgreSQL查询表名称及表结构
Where are NUMERIC precision and scale for a field found in the pg_catalog tables?
查询空间表基本信息
select f_table_catalog,f_table_schema, f_table_name,f_geometry_column,"type" from geometry_columns
f_table_name是表名,f_geometry_column是空间字段名,type是空间类型
参考资料
Selecting only Spatial tables from PostgreSQL database?
查询整表的范围Extent
SELECT ST_Extent(geom)::varchar as table_extent FROM tableName;
返回结果
BOX(35307504.5269217 3330334.18584413,35490036.5589796 3480141.43829077)
这里将box2d
对象转换为字符串后,自行处理(比如正则表达式)
如果想得到geometry
对象,则可以使用下面的语句
SELECT ST_AsText(ST_Extent(geom)) as table_extent FROM tableName; SELECT ST_AsText(ST_SetSRID(ST_Extent(geom),srid)) as table_extent FROM tableName;
参考资料
Bounding box for PostGIS table
ST_Extent
box2d
查询单条记录的范围Extent
SELECT ST_AsText( ST_Envelope( ST_GeomFromText( (select ST_AsText(geom) as "wkt" from tableName where gid = '1') ) ) );
参考资料
获得SRID
SELECT Find_SRID('table_schema', 'tableName', 'geom');
参考资料
查询geometry type
SELECT type FROM geometry_columns WHERE f_table_schema = 'tableSchema' AND f_table_name = 'tableName' and f_geometry_column = 'geom';
参考资料
ST_GeometryType
How to get the geometry type of an empty PostGIS table?
空间查询
这里展示表空间字段和目标Geometry
求空间关系
select * from tableName where ST_Intersects(geom,'SRID=4523;POINT(0 0)'::geometry)=true
结果为true
表示查询符合条件的数据,为false
为查询不符合条件的数据
注意需保证二者SRID一致,否则无法查询
空间关系列举
ST_Contains
ST_Intersects
ST_Crosses
ST_Touches
ST_DWithin
ST_Intersection
注意
以上的geom
是指空间字段,不一定是"geom"这个字符串
空间字段的实际名称可以在geometry_columns
中查询