PostgreSQL && PostGIS

数据库为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')
)
)
);

参考资料

ST_Envelope

获得SRID

SELECT Find_SRID('table_schema', 'tableName', 'geom');

参考资料

Find_SRID

查询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中查询