Mybatis+postgresql_mybatis postgresql

1、postgis中常用的查询函数：

2、以及如何将postgresql中的一条记录转化成包含地理信息的GeoJson，以下为相应的sql语句：

/*查询坐标*/
select ST_AsGeoJSON(geom)::json As geometry from table_name;

/*查询属性*/
select row_to_json((select l from (select objcode,objname,ofarea,ofroad,objpos) as l)) as properties from table_name;

/*查询properties*/
select 'Features' as TYPE,
       ST_AsGeoJSON(geom)::json As geometry,
       row_to_json((select l from (select objcode,objname,ofarea,ofroad,objpos) as l)) as properties
       from table_name as lg
/*查询所有信息方法一*/
 SELECT row_to_json(fc)
 FROM ( SELECT 'FeatureCollection' As type, array_to_json(array_agg(f)) As features
 FROM (SELECT 'Feature' As type
    , ST_AsGeoJSON(lg.geog)::json As geometry
    , row_to_json(lp) As properties
   FROM table_name As lg 
         INNER JOIN (SELECT loc_id, loc_name FROM locations) As lp 
       ON lg.loc_id = lp.loc_id  ) As f )  As fc;

/*查询所有信息方法二*/
 SELECT row_to_json(fc)
 FROM ( SELECT 'FeatureCollection' As type, array_to_json(array_agg(f)) As features
 FROM (SELECT 'Feature' As type
    , ST_AsGeoJSON(lg.geom)::json As geometry
    , row_to_json(lp) As properties
   FROM ld As lg 
         INNER JOIN (SELECT gid,objcode,objname FROM table_name) As lp 
       ON lg.gid = lp.gid  ) As f )  As fc;

/*postgresql查询某一表中的所有列名名*/
select array_agg(fc)
from (select column_name from information_schema.columns where table_schema='public' and table_name='table_name' and column_name != 'geom') as fc
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34

2、Mybatis写sql语句的方式有两种，第一种是在*Map.xml中进行编写，第二中是在Map接口的抽象方法上直接进行注释，一般来说第二种方式更加高效，尤其是在编写复杂的sql语句，比如进行嵌套查询时第二种方法优势更加明显。

3、当数据库中某一个表的列数过多时，在反向生成映射文件后，在*Map.xml文件中会对该表的所有列名产生一个Base_Column_List。在与postgres进行结合时，可以将geom直接修改成ST_AsGeoJSON(geom) as geom，这样可以直接获取该feature的坐标信息。

 <sql id="Base_Column_List">
    gid, objcode, objname, ofarea, ofroad, objpos, deptcode1, deptname1, deotcode2, deptname2, 
    deptcode3, deptname3, objstate, ordate, chdate, lfunction, linecolor, width, length, 
    linearea, datasource, picture, remark, layer, ST_AsGeoJSON(geom) as geom
  </sql>
1
2
3
4
5