使用SQL将wkt格式转换为geojson格式

wkt格式数据

MULTIPOLYGON(((121.49691905915 31.277156147522,121.496352816047 31.2775864000466,121.4956804871 31.2779034754296,121.494958378564 31.2782205901886,121.494448233496 31.2783791571033,121.493752612235 31.2784640890938,121.492238271363 31.2784725203785,121.49216787405 31.2785296817883,121.492107937747 31.2799107067087,121.492040969341 31.2806235106285,121.491981013144 31.2812464837614,121.491818688977 31.283024242181,121.491723409249 31.2841816944166,121.49171308837 31.2841545689295,121.49166690054 31.2849566007225,121.49170574366 31.2853053312898,121.491931434217 31.2860954748162,121.492063909592 31.2863510211433,121.492374295803 31.2862322156738,121.492555407732 31.2865047887048,121.493439662569 31.2861764761976,121.492298905592 31.2840528884975,121.492929628561 31.283761632988,121.493277483893 31.2844867871829,121.494509876061 31.2839909410124,121.494103852456 31.2832473709794,121.494429975216 31.2831048837586,121.494238047747 31.2825780700065,121.49350578408 31.2828445455945,121.493281381711 31.2823301857999,121.492991920306 31.2816548939004,121.492861833546 31.2811594061846,121.494021296465 31.2809488809817,121.494050438439 31.2805773801191,121.494590323938 31.2805340422195,121.495583089145 31.2804411817154,121.495554180944 31.2803297243974,121.495923757548 31.2802368557599,121.496126380769 31.280707473287,121.496213120092 31.2811348334879,121.496669233692 31.2822686910375,121.500144529667 31.2809634744773,121.500063166229 31.2809418964971,121.49945191623 31.2797418911924,121.498558426529 31.2785205170292,121.497590140996 31.2776221027087,121.496947091929 31.2771075952951,121.49691905915 31.277156147522)),((121.492960155914 31.2874376824627,121.493561411469 31.2879178343792,121.494354354927 31.2885183638417,121.494454456683 31.2885924147454,121.493785019166 31.2880802302183,121.492960155914 31.2874376824627)),((121.494570846783 31.2886785150545,121.494786563089 31.2888380925486,121.494923460364 31.2889064327376,121.494570846783 31.2886785150545)),((121.495313116695 31.2891017025757,121.495456736839 31.289174062101,121.495533273031 31.2891950454966,121.495313116695 31.2891017025757)),((121.49615206783 31.2893311116662,121.496197900369 31.2893393260997,121.496204472584 31.2893392001659,121.49615206783 31.2893311116662)),((121.496573683825 31.2893321255099,121.496631438977 31.2893310188322,121.497506252644 31.2893448481478,121.499700627432 31.2892181855795,121.500206512305 31.2891465802521,121.500763875932 31.2889703271731,121.501298664582 31.2888106054755,121.504788119247 31.2875934786819,121.507163362429 31.2867177027353,121.507111815363 31.286602055986,121.507195599466 31.2863542646876,121.507356692136 31.2861175143664,121.50754353763 31.2858918004275,121.50786415212 31.2856923541729,121.508221638084 31.2856323554513,121.508445493249 31.2856165505493,121.50878051447 31.2856000254859,121.509067147845 31.2854348945267,121.509159802611 31.2852178852542,121.508537768071 31.2854060006109,121.507351722016 31.2831578040412,121.505721750831 31.282964596092,121.50470073499 31.2833276204825,121.503356644132 31.2806483388591,121.501970785443 31.2811985808815,121.501981236295 31.2812185096668,121.503227847436 31.2807599722571,121.504578664897 31.2833611573486,121.502966453464 31.2840075763427,121.503505033324 31.2850505931675,121.502736262618 31.2853457601785,121.501976034172 31.2839110963823,121.500390738516 31.2809204827461,121.500100676832 31.2810359589305,121.500551435321 31.2820541748348,121.501327035098 31.2835518100014,121.501548900322 31.283769300558,121.503138677405 31.286807796528,121.498611911558 31.2884134671795,121.498208980861 31.2885391360623,121.496956466791 31.2887299820005,121.49677674306 31.2887672217286,121.496640554264 31.288897569678,121.496591484363 31.2890651756891,121.496573683825 31.2893321255099),(121.507735115653 31.2839197363381,121.506097886223 31.2845529348787,121.505413564713 31.2832999533177,121.505681273111 31.2831872372574,121.505855854943 31.2831507727088,121.506259345657 31.2831739759543,121.506744347866 31.2832469043747,121.507147879161 31.2832899990587,121.507264291821 31.283313205617,121.507735115653 31.2839197363381)),((121.501548559396 31.2804626369726,121.501547210541 31.2804600807564,121.501039174637 31.2806623344523,121.501548559396 31.2804626369726)))

使用sql进行转换

SELECT json_agg ( ST_AsGeoJSON ( T.* ) :: json ) FROM ( values('MULTIPOLYGON(((121.49691905915 31.277156147522,121.496352816047 31.2775864000466,121.4956804871 31.2779034754296,121.494958378564 31.2782205901886,121.494448233496 31.2783791571033,121.493752612235 31.2784640890938,121.492238271363 31.2784725203785,121.49216787405 31.2785296817883,121.492107937747 31.2799107067087,121.492040969341 31.2806235106285,121.491981013144 31.2812464837614,121.491818688977 31.283024242181,121.491723409249 31.2841816944166,121.49171308837 31.2841545689295,121.49166690054 31.2849566007225,121.49170574366 31.2853053312898,121.491931434217 31.2860954748162,121.492063909592 31.2863510211433,121.492374295803 31.2862322156738,121.492555407732 31.2865047887048,121.493439662569 31.2861764761976,121.492298905592 31.2840528884975,121.492929628561 31.283761632988,121.493277483893 31.2844867871829,121.494509876061 31.2839909410124,121.494103852456 31.2832473709794,121.494429975216 31.2831048837586,121.494238047747 31.2825780700065,121.49350578408 31.2828445455945,121.493281381711 31.2823301857999,121.492991920306 31.2816548939004,121.492861833546 31.2811594061846,121.494021296465 31.2809488809817,121.494050438439 31.2805773801191,121.494590323938 31.2805340422195,121.495583089145 31.2804411817154,121.495554180944 31.2803297243974,121.495923757548 31.2802368557599,121.496126380769 31.280707473287,121.496213120092 31.2811348334879,121.496669233692 31.2822686910375,121.500144529667 31.2809634744773,121.500063166229 31.2809418964971,121.49945191623 31.2797418911924,121.498558426529 31.2785205170292,121.497590140996 31.2776221027087,121.496947091929 31.2771075952951,121.49691905915 31.277156147522)),((121.492960155914 31.2874376824627,121.493561411469 31.2879178343792,121.494354354927 31.2885183638417,121.494454456683 31.2885924147454,121.493785019166 31.2880802302183,121.492960155914 31.2874376824627)),((121.494570846783 31.2886785150545,121.494786563089 31.2888380925486,121.494923460364 31.2889064327376,121.494570846783 31.2886785150545)),((121.495313116695 31.2891017025757,121.495456736839 31.289174062101,121.495533273031 31.2891950454966,121.495313116695 31.2891017025757)),((121.49615206783 31.2893311116662,121.496197900369 31.2893393260997,121.496204472584 31.2893392001659,121.49615206783 31.2893311116662)),((121.496573683825 31.2893321255099,121.496631438977 31.2893310188322,121.497506252644 31.2893448481478,121.499700627432 31.2892181855795,121.500206512305 31.2891465802521,121.500763875932 31.2889703271731,121.501298664582 31.2888106054755,121.504788119247 31.2875934786819,121.507163362429 31.2867177027353,121.507111815363 31.286602055986,121.507195599466 31.2863542646876,121.507356692136 31.2861175143664,121.50754353763 31.2858918004275,121.50786415212 31.2856923541729,121.508221638084 31.2856323554513,121.508445493249 31.2856165505493,121.50878051447 31.2856000254859,121.509067147845 31.2854348945267,121.509159802611 31.2852178852542,121.508537768071 31.2854060006109,121.507351722016 31.2831578040412,121.505721750831 31.282964596092,121.50470073499 31.2833276204825,121.503356644132 31.2806483388591,121.501970785443 31.2811985808815,121.501981236295 31.2812185096668,121.503227847436 31.2807599722571,121.504578664897 31.2833611573486,121.502966453464 31.2840075763427,121.503505033324 31.2850505931675,121.502736262618 31.2853457601785,121.501976034172 31.2839110963823,121.500390738516 31.2809204827461,121.500100676832 31.2810359589305,121.500551435321 31.2820541748348,121.501327035098 31.2835518100014,121.501548900322 31.283769300558,121.503138677405 31.286807796528,121.498611911558 31.2884134671795,121.498208980861 31.2885391360623,121.496956466791 31.2887299820005,121.49677674306 31.2887672217286,121.496640554264 31.288897569678,121.496591484363 31.2890651756891,121.496573683825 31.2893321255099),(121.507735115653 31.2839197363381,121.506097886223 31.2845529348787,121.505413564713 31.2832999533177,121.505681273111 31.2831872372574,121.505855854943 31.2831507727088,121.506259345657 31.2831739759543,121.506744347866 31.2832469043747,121.507147879161 31.2832899990587,121.507264291821 31.283313205617,121.507735115653 31.2839197363381)),((121.501548559396 31.2804626369726,121.501547210541 31.2804600807564,121.501039174637 31.2806623344523,121.501548559396 31.2804626369726)))' :: geometry ))  AS T

转换后的geojson格式

[{"type": "Feature", "geometry": {"type":"MultiPolygon","coordinates":[[[[121.496919059,31.277156148],[121.496352816,31.2775864],[121.495680487,31.277903475],[121.494958379,31.27822059],[121.494448233,31.278379157],[121.493752612,31.278464089],[121.492238271,31.27847252],[121.492167874,31.278529682],[121.492107938,31.279910707],[121.492040969,31.280623511],[121.491981013,31.281246484],[121.491818689,31.283024242],[121.491723409,31.284181694],[121.491713088,31.284154569],[121.491666901,31.284956601],[121.491705744,31.285305331],[121.491931434,31.286095475],[121.49206391,31.286351021],[121.492374296,31.286232216],[121.492555408,31.286504789],[121.493439663,31.286176476],[121.492298906,31.284052888],[121.492929629,31.283761633],[121.493277484,31.284486787],[121.494509876,31.283990941],[121.494103852,31.283247371],[121.494429975,31.283104884],[121.494238048,31.28257807],[121.493505784,31.282844546],[121.493281382,31.282330186],[121.49299192,31.281654894],[121.492861834,31.281159406],[121.494021296,31.280948881],[121.494050438,31.28057738],[121.494590324,31.280534042],[121.495583089,31.280441182],[121.495554181,31.280329724],[121.495923758,31.280236856],[121.496126381,31.280707473],[121.49621312,31.281134833],[121.496669234,31.282268691],[121.50014453,31.280963474],[121.500063166,31.280941896],[121.499451916,31.279741891],[121.498558427,31.278520517],[121.497590141,31.277622103],[121.496947092,31.277107595],[121.496919059,31.277156148]]],[[[121.492960156,31.287437682],[121.493561411,31.287917834],[121.494354355,31.288518364],[121.494454457,31.288592415],[121.493785019,31.28808023],[121.492960156,31.287437682]]],[[[121.494570847,31.288678515],[121.494786563,31.288838093],[121.49492346,31.288906433],[121.494570847,31.288678515]]],[[[121.495313117,31.289101703],[121.495456737,31.289174062],[121.495533273,31.289195045],[121.495313117,31.289101703]]],[[[121.496152068,31.289331112],[121.4961979,31.289339326],[121.496204473,31.2893392],[121.496152068,31.289331112]]],[[[121.496573684,31.289332126],[121.496631439,31.289331019],[121.497506253,31.289344848],[121.499700627,31.289218186],[121.500206512,31.28914658],[121.500763876,31.288970327],[121.501298665,31.288810605],[121.504788119,31.287593479],[121.507163362,31.286717703],[121.507111815,31.286602056],[121.507195599,31.286354265],[121.507356692,31.286117514],[121.507543538,31.2858918],[121.507864152,31.285692354],[121.508221638,31.285632355],[121.508445493,31.285616551],[121.508780514,31.285600025],[121.509067148,31.285434895],[121.509159803,31.285217885],[121.508537768,31.285406001],[121.507351722,31.283157804],[121.505721751,31.282964596],[121.504700735,31.28332762],[121.503356644,31.280648339],[121.501970785,31.281198581],[121.501981236,31.28121851],[121.503227847,31.280759972],[121.504578665,31.283361157],[121.502966453,31.284007576],[121.503505033,31.285050593],[121.502736263,31.28534576],[121.501976034,31.283911096],[121.500390739,31.280920483],[121.500100677,31.281035959],[121.500551435,31.282054175],[121.501327035,31.28355181],[121.5015489,31.283769301],[121.503138677,31.286807797],[121.498611912,31.288413467],[121.498208981,31.288539136],[121.496956467,31.288729982],[121.496776743,31.288767222],[121.496640554,31.28889757],[121.496591484,31.289065176],[121.496573684,31.289332126]],[[121.507735116,31.283919736],[121.506097886,31.284552935],[121.505413565,31.283299953],[121.505681273,31.283187237],[121.505855855,31.283150773],[121.506259346,31.283173976],[121.506744348,31.283246904],[121.507147879,31.283289999],[121.507264292,31.283313206],[121.507735116,31.283919736]]],[[[121.501548559,31.280462637],[121.501547211,31.280460081],[121.501039175,31.280662334],[121.501548559,31.280462637]]]]}, "properties": {}}]

此时的geojson可能在某些应用场景无法被识别，还需要继续转换，将以上结果去掉两边的[和]。

截取字符串sql

SELECT RIGHT ( ( LEFT ( '[{"type": "Feature", "geometry": {"type":"MultiPolygon","coordinates":[[[[121.496919059,31.277156148],[121.496352816,31.2775864],[121.495680487,31.277903475],[121.494958379,31.27822059],[121.494448233,31.278379157],[121.493752612,31.278464089],[121.492238271,31.27847252],[121.492167874,31.278529682],[121.492107938,31.279910707],[121.492040969,31.280623511],[121.491981013,31.281246484],[121.491818689,31.283024242],[121.491723409,31.284181694],[121.491713088,31.284154569],[121.491666901,31.284956601],[121.491705744,31.285305331],[121.491931434,31.286095475],[121.49206391,31.286351021],[121.492374296,31.286232216],[121.492555408,31.286504789],[121.493439663,31.286176476],[121.492298906,31.284052888],[121.492929629,31.283761633],[121.493277484,31.284486787],[121.494509876,31.283990941],[121.494103852,31.283247371],[121.494429975,31.283104884],[121.494238048,31.28257807],[121.493505784,31.282844546],[121.493281382,31.282330186],[121.49299192,31.281654894],[121.492861834,31.281159406],[121.494021296,31.280948881],[121.494050438,31.28057738],[121.494590324,31.280534042],[121.495583089,31.280441182],[121.495554181,31.280329724],[121.495923758,31.280236856],[121.496126381,31.280707473],[121.49621312,31.281134833],[121.496669234,31.282268691],[121.50014453,31.280963474],[121.500063166,31.280941896],[121.499451916,31.279741891],[121.498558427,31.278520517],[121.497590141,31.277622103],[121.496947092,31.277107595],[121.496919059,31.277156148]]],[[[121.492960156,31.287437682],[121.493561411,31.287917834],[121.494354355,31.288518364],[121.494454457,31.288592415],[121.493785019,31.28808023],[121.492960156,31.287437682]]],[[[121.494570847,31.288678515],[121.494786563,31.288838093],[121.49492346,31.288906433],[121.494570847,31.288678515]]],[[[121.495313117,31.289101703],[121.495456737,31.289174062],[121.495533273,31.289195045],[121.495313117,31.289101703]]],[[[121.496152068,31.289331112],[121.4961979,31.289339326],[121.496204473,31.2893392],[121.496152068,31.289331112]]],[[[121.496573684,31.289332126],[121.496631439,31.289331019],[121.497506253,31.289344848],[121.499700627,31.289218186],[121.500206512,31.28914658],[121.500763876,31.288970327],[121.501298665,31.288810605],[121.504788119,31.287593479],[121.507163362,31.286717703],[121.507111815,31.286602056],[121.507195599,31.286354265],[121.507356692,31.286117514],[121.507543538,31.2858918],[121.507864152,31.285692354],[121.508221638,31.285632355],[121.508445493,31.285616551],[121.508780514,31.285600025],[121.509067148,31.285434895],[121.509159803,31.285217885],[121.508537768,31.285406001],[121.507351722,31.283157804],[121.505721751,31.282964596],[121.504700735,31.28332762],[121.503356644,31.280648339],[121.501970785,31.281198581],[121.501981236,31.28121851],[121.503227847,31.280759972],[121.504578665,31.283361157],[121.502966453,31.284007576],[121.503505033,31.285050593],[121.502736263,31.28534576],[121.501976034,31.283911096],[121.500390739,31.280920483],[121.500100677,31.281035959],[121.500551435,31.282054175],[121.501327035,31.28355181],[121.5015489,31.283769301],[121.503138677,31.286807797],[121.498611912,31.288413467],[121.498208981,31.288539136],[121.496956467,31.288729982],[121.496776743,31.288767222],[121.496640554,31.28889757],[121.496591484,31.289065176],[121.496573684,31.289332126]],[[121.507735116,31.283919736],[121.506097886,31.284552935],[121.505413565,31.283299953],[121.505681273,31.283187237],[121.505855855,31.283150773],[121.506259346,31.283173976],[121.506744348,31.283246904],[121.507147879,31.283289999],[121.507264292,31.283313206],[121.507735116,31.283919736]]],[[[121.501548559,31.280462637],[121.501547211,31.280460081],[121.501039175,31.280662334],[121.501548559,31.280462637]]]]}, "properties": {}}]',- 1 ) ),- 1 )

但是在实际应用中，不可能一个一个的转换，所以进行批量转换，如下：

Controller

    @GetMapping("/updategeom")
    @ResponseBody
    @ApiOperation(value = "测试更新layergeom")
    public ResponseData updategeom(String LayerId) {
        int[] updategeom = service.updategeom(LayerId);
        return ResponseDataUtil.buildSuccess("共" + updategeom[0] + "条数据,成功更新" + updategeom[1] + "条数据");
    }

    @GetMapping("/updategeomkuohao")
    @ResponseBody
    @ApiOperation(value = "测试更新layergeom,去掉两边的括号")
    public ResponseData updategeomkuohao(String LayerId) {
        int[] updategeom = service.updategeomkuohao(LayerId);
        return ResponseDataUtil.buildSuccess("共" + updategeom[0] + "条数据,成功更新" + updategeom[1] + "条数据");
    }

Service

	@Override
    @Transactional
    public int[] updategeom(String layerid) {
        List<String> itemids = mapper.selectItemIdsByLayerId(layerid);
        int updategeom = 0;
        for (String itemid : itemids) {
            System.out.println(updategeom);
            updategeom += mapper.updategeom(itemid);

        }
        return new int[]{itemids.size(), updategeom};
    }

    @Override
    @Transactional
    public int[] updategeomkuohao(String layerid) {
        List<String> itemids = mapper.selectItemIdsByLayerId(layerid);
        int updategeomkuohao = 0;
        for (String itemid : itemids) {
            System.out.println(updategeomkuohao);
            updategeomkuohao += mapper.updategeomkuohao(itemid);

        }
        return new int[]{itemids.size(), updategeomkuohao};
    }

Mapper

    //测试更新数据库要素
    int updategeom(String itemid);
    //测试更新数据库要素两边的括号
    int updategeomkuohao(String itemid);

Mybatis

	<!--  更新要素geom  -->
    <update id="updategeom">
        UPDATE tbblayeritem
        SET geom = ( SELECT json_agg ( ST_AsGeoJSON ( T.* ) :: json ) FROM ( values((select geom from tbblayeritem where itemid = #{itemid}::uuid) :: geometry ))  AS T )
        WHERE
	    itemid = #{itemid}::uuid
    </update>
	<!--  更新要素geom去掉括号  -->
    <update id="updategeomkuohao">
        UPDATE tbblayeritem
        SET geom = ( SELECT RIGHT ( ( LEFT ( ( SELECT geom FROM tbblayeritem WHERE itemid = #{itemid}::uuid ),- 1 ) ),- 1 ) )
        WHERE
	        itemid = #{itemid}::uuid
    </update>