从MySql查询以featurecollection的形式查询出GeoJson

1、创建表格test：

CREATE TABLE `test` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `route` linestring DEFAULT NULL,
  `p1` varchar(255) DEFAULT NULL,
  `p2` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;

2、插入两行数据：

-- ----------------------------
-- Records of test
-- ----------------------------
INSERT INTO `test` VALUES ('1', GeomFromText('LINESTRING(1 2, 2 1)'), 'p21', 'p22');
INSERT INTO `test` VALUES ('2', GeomFromText('LINESTRING(2 2, 3 1)'), 'p31', 'p32');

3、查询语句

SELECT
	CONCAT(
		'{
  "type": "FeatureCollection",
  "features": [
  ',
		GROUP_CONCAT(
			'{
   "type": "Feature",
      "geometry": ',
			ST_AsGeoJSON (route),
			',"properties": {',
			'"id":',id,
			',"p1":"',p1,
			'","p2":"',p2,
			'"}
   }'
		),
		']
}'
	) AS geojson
FROM
	test

4、查询结果

{
	"type": "FeatureCollection",
	"features": [{
			"type": "Feature",
			"geometry": {
				"type": "LineString",
				"coordinates": [[1, 2], [2, 1]]
			},
			"properties": {
				"id": 1,
				"p1": "p21",
				"p2": "p22"
			}
		}, {
			"type": "Feature",
			"geometry": {
				"type": "LineString",
				"coordinates": [[2, 2], [3, 1]]
			},
			"properties": {
				"id": 2,
				"p1": "p31",
				"p2": "p32"
			}
		}
	]
}

 

转载于:https://my.oschina.net/u/53041/blog/1806824