Mybatis+postgresql

╰半橙微兮° 2022-05-26 07:54 75阅读 0赞

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

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>

Mybatis+postgresql

发表评论取消回复

还没有评论，来说两句吧...

相关阅读