本文主要是博主一年内PostGIS工作经验的所有精华SQL,每个SQL都仔细研究并优化了10分钟以上,非常实用,请收藏,以后你的工作上会用到。
– 查询每一个格网的人口数,并返回该格网的空间面图层
select expanded_total_population2,gridtag,st_asgeojson(r_sz_all_grid_250.geom) as one_geojson from official_population,r_sz_all_grid_250
where official_population.gid = r_sz_all_grid_250.gridtag and expanded_total_population2> 0
– 根据起点和终点经纬度,创建空间线字段表
create table company_branches_street_geom as
select company_branches_street.*,st_geomfromtext(‘linestring(’||longitude_origin||’ ‘||latitude_origin||’,’||longitude_destination||’ ‘||latitude_destination||’)’,4326) as geom
from company_branches_street
– 根据经纬度,创建空间点字段表
CREATE TABLE patent_geom AS SELECT
patent.*,st_geomfromtext ( ‘POINT(’ || longitude || ’ ’ || latitude || ‘)’, 4326 ) AS geom
FROM patent
– 将gcj02的空间字段转化为wgs84
create table didi_boundary_lines_wgs84_geom as
select geoc_gcj02towgs84(geom) from didi_boundary_lines_geom