# PostGIS 空间数据学习建议

《空间复合索引加速空间搜索》

## 相关建议

``````select DropGeometryColumn ('cb','geom');
drop table if exists  cb;

create table cb(
objectid int not null, --共享单车编号
c1 int,                -- 0表示未使用，其它表示已使用
c2 int,                -- 共享单车属于哪家运营公司
constraint pk_cb_objectid primary key (objectid)
);
``````

GIS必须明确指出地图单位是什么

c3 point – 共享单车当前位置, point在文章里只能算是自定义类型(实际上是PostgreSQL内置的几何点类型),这会给参考您文章的学习GIS的同学造成困扰

where c1=0 and c2=100 and c3 <@ circle ‘((23,3175),1000)’ order by c3 <-> point(23,3175) limit 1000;

``````select AddGeometryColumn ('cb','geom',4326,'POINT',2); -- 共享单车当前位置,GPS采用4326 ,类型为点,二维坐标
``````

``````create index gidx_cb_geom on cb using gist(geom);

create index gidx_cb_geomgraphy on cb using gist((geom::geography));
``````

geometry和geography类型的区别

The geography type provides native support for spatial features represented on “geographic” coordinates (sometimes called “geodetic” coordinates, or “lat/lon”, or “lon/lat”). Geographic coordinates are spherical coordinates expressed in angular units (degrees).

The basis for the PostGIS geometry type is a plane. The shortest path between two points on the plane is a straight line. That means calculations on geometries (areas, distances, lengths, intersections, etc) can be calculated using cartesian mathematics and straight line vectors.

The basis for the PostGIS geographic type is a sphere. The shortest path between two points on the sphere is a great circle arc. That means that calculations on geographies (areas, distances, lengths, intersections, etc) must be calculated on the sphere, using more complicated mathematics. For more accurate measurements, the calculations must take the actual spheroidal shape of the world into account, and the mathematics becomes very complicated indeed.

Because the underlying mathematics is much more complicated, there are fewer functions defined for the geography type than for the geometry type. Over time, as new algorithms are added, the capabilities of the geography type will expand.

One restriction is that it only supports WGS 84 long lat (SRID:4326). It uses a new data type called geography. None of the GEOS functions support this new type. As a workaround one can convert back and forth between geometry and geography types.

The new geography type uses the PostgreSQL 8.3+ typmod definition format so that a table with a geography field can be added in a single step. All the standard OGC formats except for curves are supported.

``````经度范围：73°33′E至135°05′E

``````
``````do \$\$
declare vStart bigint;
declare vEnd bigint;
declare MAXVALE bigint;
declare INTERVAL bigint;
begin
MAXVALE := 20000000;
INTERVAL := 1000; vStart := 1 ;vEnd := INTERVAL;
loop
-- 20家公司比较符合市场现状,更能反应实际情况
insert into cb
select id,(random()*1)::integer, (random()*(20-1)+1)::integer,
ST_SetSRID(ST_Point(
round((random()*(135.085831-73.406586)+73.406586)::numeric,6),
round((random()*(53.880950-3.408477)+3.408477)::numeric,6)
),4326)
from generate_series(vStart,vEnd) as id;
raise notice  '%', vEnd;
vStart := vEnd + 1; vEnd := vEnd + INTERVAL;
if( vEnd > MAXVALE ) then
return;
end if;
end loop;
end\$\$;
``````

ix,iy为gps经度和纬度(单位为度)

idistance为搜索距离(单位为米)

``````drop function if exists spatialQuery(ix float,iy float,idistance float);
create or replace function spatialQuery(ix float,iy float,idistance float)
returns table(oobjectid integer,oc1 integer,oc2 integer,odistance float,ogeom geometry)
as \$\$
declare
vrecord record;
vcurrentpoint geometry;
vspheroid  spheroid;
begin
vspheroid := 'SPHEROID["WGS84",6378137,298.257223563]' ;  --WGS84椭球体参数定义
vcurrentpoint := ST_SetSRID(ST_Point(ix,iy),4326);  --
--查找圆心为vcurrentpoint,半径idistance米范围内未使用的共享单车,并按距离排序,只返回1千行
return query    ( with cte as(
select * from cb
where ST_DWithin(geom::geography ,vcurrentpoint::geography,idistance,true)
) select objectid,c1,c2,ST_DistanceSpheroid(geom,vcurrentpoint,vspheroid),geom
from cte where c1=0 order by ST_DistanceSpheroid(geom,vcurrentpoint,vspheroid)  limit 1000 );
end;
\$\$ language plpgsql;

select * from spatialQuery(102,24,5000);
``````

``````explain (analyze,verbose,costs,buffers,timing)
with cte as(
select * from cb
where ST_DWithin(geom::geography ,ST_SetSRID(ST_Point(102,24),4326)::geography,5000,true)
) select objectid,c1,c2,ST_DistanceSpheroid(geom,ST_SetSRID(ST_Point(102,24),4326),'SPHEROID["WGS84",6378137,298.257223563]'),geom
from cte where c1=0 order by ST_DistanceSpheroid(geom,ST_SetSRID(ST_Point(102,24),4326),'SPHEROID["WGS84",6378137,298.257223563]')  limit 1000;
``````

《PostGIS 距离计算建议 - 投影坐标与球坐标》

http://postgis.net/docs/

Tags:

Updated: