空间数据实战（2）——pgsql

1 建表

create table city (
  fid serial primary key,
  city_name VARCHAR(255),
  geom geometry
)

2 插入数据

INSERT INTO city ( city_name, geom )
VALUES ('北京', st_geomfromtext('LINESTRING(0 0,1 1)', 4326));

insert into city(city_name,geom)
values('test','SRID=4326;polygon((112.784684 28.051125,省略省略省略省略省略,112.66978 28.205104))')

3 空间参考

3.1 查询空间参考

select st_srid(geom) from city;

3.2 更新空间参考

select UpdateGeometrySRID('city', 'geom', 4326);

4 距离、面积计算

单位：平方千米

select city_name,st_area(st_transform(geom,4527))/1000000 from city

5 空间分析

5.1 缓冲区查询

如果是WGS84（4326），缓冲距离单位是度，使用m为距离单位的话，geom后加一个::geography

SELECT st_asgeojson(ST_BUFFER(geom::geography, 100)) from monitor_point

SELECT st_asgeojson ( st_buffer ( ST_GeomFromText ( 'POINT(112.713193579136 28.2548881715701)' ) :: geography, 200 ) )

5.2 叠加分析

注意：geojson后面要带空间参考信息，不然报错
"crs":{"type":"name","properties":{"name":"EPSG:4326"}}

SELECT id, people_num from house_test where ST_Intersects(geom, st_geomfromgeojson('{"type":"Polygon","coordinates":[[[112.715231602437,28.2548626049705],[112.715186806229,28.2545110403717],[112.715065412308,28.2541739691599],[112.714872086375,28.2538643445729],[112.714614258211,28.2535940650109],[112.714301836097,28.2533735168322],[112.713946826035,28.2532111752629],[112.713562870389,28.2531132787466],[112.713164723694,28.2530835892428],[112.712767685746,28.2531232476797],[112.712387013755,28.2532307301122],[112.712037336133,28.2534019062704],[112.711732090421,28.2536301982498],[112.711483006949,28.2539068332511],[112.711299658063,28.2542211806631],[112.711189090228,28.254561160543],[112.711155553172,28.2549137078036],[112.711200336462,28.2552652742741],[112.711321719836,28.2556023493458],[112.711515039201,28.2559119791932],[112.711772865774,28.2561822646153],[112.712085291516,28.2564028183617],[112.712440309875,28.2565651643586],[112.712824277222,28.2566630634884],[112.713222437241,28.2566927533936],[112.713619488108,28.2566530930851],[112.714000170645,28.2565456067927],[112.714349854835,28.2563744253741],[112.714655102138,28.2561461275346],[112.714904181981,28.2558694869656],[112.715087522571,28.255555135126],[112.715198078704,28.2552151526326],[112.715231602437,28.2548626049705]]],"crs":{"type":"name","properties":{"name":"EPSG:4326"}}}'))

6 其他

6.1 查询为FeatureCollection

SELECT
	row_to_json ( fc ) AS result
FROM
	(
	SELECT
		'FeatureCollection' AS TYPE,
		array_to_json (
		ARRAY_AGG ( f )) AS features 
	FROM
		(
		SELECT
			'Feature' AS TYPE,
			ST_AsGeoJSON ( geom ) :: json AS geometry
			,
			(
			SELECT
				row_to_json ( T ) 
			FROM
				( SELECT b.id, b.people_num) AS T ) AS properties
		FROM
			house_test b
		) AS f 
	) AS fc;

6.2 计算与Polygon相交的线要素的长度

SELECT gid, pathname, st_length(st_transform(st_intersection(geom, st_geomfromgeojson(GeoJSON字符串)),4527))
from road_test
where ST_Intersects(geom, st_geomfromgeojson(GeoJSON字符串))

未完待续