PostGis部分点线相关函数测试
涉及表——pg_roads表:字段有主键gid和线几何对象geom
例如:
Gid:1698
geom: LINESTRING(120.2161129 30.2525742,120.214496430.2534469,120.2129839 30.2541385,120.2126652 30.2542843)
1:ST_DumpPoints函数测试:测试从LINESTRING中获取所有的POINT
查询语句:
SELECT ST_AsText((ST_DumpPoints(ST_GeometryFromText(‘LINESTRING(120.1361868 30.2747561,120.1362053 30.2742953,120.1362271 30.2740925)’))).geom);
返回结果:
POINT(120.1361868 30.2747561)
POINT(120.1362053 30.2742953)
POINT(120.1362271 30.2740925)
2:ST_MakePoint函数测试:测试POINT组成LINESTRING
输入参数:
POINT(120.1361868 30.2747561),POINT(120.1362053 30.2742953)
查询语句:
SELECT ST_AsText(ST_MakeLine(ST_MakePoint(120.1361868,30.2747561),ST_MakePoint(120.1362053,30.2742953)));
返回结果:
LINESTRING(120.136186830.2747561,120.1362053 30.2742953)
3:测试根据路线表pg_roads的gid生成对于的过点表pg_points
create table pg_points as SELECT gid,(ST_DumpPoints(geom)).geom,(ST_DumpPoints(geom)).path[1] FROM pg_roads
4:ST_Split函数测试:
测试ST_Split通过一个POINT将一个线分成两个线
查询语句一:
select ST_AsText(ST_Split(ST_GeometryFromText(‘LINESTRING(120.1361868 30.2747561,120.1362053 30.2742953,120.1362271 30.2740925)’),ST_MakePoint(120.1362053,30.2742953)));
返回结果:
GEOMETRYCOLLECTION(LINESTRING(120.136186830.2747561,120.1362053 30.2742953),LINESTRING(120.136205330.2742953,120.1362271 30.2740925))
查询语句二:
select ST_AsText((ST_Dump(ST_Split(ST_GeometryFromText(‘LINESTRING(120.1361868 30.2747561,120.1362053 30.2742953,120.1362271 30.2740925)’), ST_MakePoint(120.1362053,30.2742953)))).geom);
返回结果:
LINESTRING(120.1361868 30.2747561,120.1362053 30.2742953)
LINESTRING(120.1362053 30.2742953,120.1362271 30.2740925)
5:ST_Distance_Sphere函数测试:获取距离目标点最近的线段
——返回两个以经纬度表示的几何对象的最小距离。该函数使用一个半径为6370986 球体做参照。
查询语句:
SELECT gid,st_asText(geom),ST_Distance_Sphere(st_Point(120.2161, 30.2526),geom) FROM pg_roads order byST_Distance_Sphere(st_Point(120.2161, 30.25261),geom) asc limit 1
返回结果:
Gid:1698
St_asText: LINESTRING(120.2161129 30.2525742,120.2144964 30.2534469,120.2129839 30.2541385,120.2126652 30.2542843)
St_Distance_Sphere:1.77606568
6:ST_LineLocatePoint函数测试:
——根据输入的点和LINESTRING,返回这个点在LINESTRING上的位置(如果点不在这个LINESTRING上,就返回距离点最近的线上的点)
用法:float ST_LineLocatePoint(geometry a_linestring, geometry a_point);
查询语句:
SELECT ST_LineLocatePoint(geom,ST_SetSRID(st_Point(120.2161,30.2526),4326)) FROM pg_roads where gid=1698;
或:
SELECT ST_LineLocatePoint(ST_SetSRID(ST_GeometryFromText(‘LINESTRING(120.2161129 30.2525742,120.2144964 30.2534469,120.2129839 30.2541385,120.2126652 30.2542843)’),4326),ST_SetSRID(st_Point(120.2161,30.2526),4326));
返回结果:
0.00613095458965902
7:ST_LineInterpolatePoint函数测试:
——返回在一个LINESTRING对象上面在指定位置上插入(获取)的一个点的几何对象。第二个参数范围是0到1,表示被插入的点在整个LINESTRING的位置。
用法:geometry ST_LineInterpolatePoint(geometry a_linestring, float a_fraction);
查询语句:
SELECT ST_AsText(ST_LineInterpolatePoint(geom,0.00613095458965902)) FROM pg_roadswhere gid=1698
或
SELECT ST_AsText(ST_LineInterpolatePoint(ST_SetSRID(ST_GeometryFromText(‘LINESTRING(120.2161129 30.2525742,120.2144964 30.2534469,120.2129839 30.2541385,120.2126652 30.2542843)’),4326),0.00613095458965902));
返回结果:
POINT(120.216092126105 30.2525854152045)
8:ST_ClosestPoint函数测试:求点在最近线段的最近点
——返回离g2几何对象最近的g1上的点。这个点也是g1和g2之间最短的线的第一个点。
用法:geometry ST_ClosestPoint(geometry g1, geometry g2);
查询语句:
SELECT ST_AsText(ST_ClosestPoint(ST_SetSRID(ST_GeometryFromText(‘LINESTRING(120.2161129 30.2525742,120.2144964 30.2534469,120.2129839 30.2541385,120.2126652 30.2542843)’),4326),ST_SetSRID(st_Point(120.2161, 30.2526),4326)))
返回结果:
POINT(120.216092126105 30.2525854152045)
9:ST_ShortestLine函数:求点到线段的最短线段
——返回两个几何对象之间2维最短的线段,如果函数找到这两个几何对象之间不止一条线段,那么只会返回第一条。如果g1和g2只在一个点相交,该函数会返回一条返回起点和终点都在该交点的线段。如果g1和g2相交不止一个点,该函数会返回起点和终点都在同一点的线段,但该交点可以是g1和g2相交的任意一点。返回的线段起点总是属于g1,终点属于g2。
用法:geometry ST_ShortestLine(geometry g1, geometry g2);
查询语句:
SELECT ST_AsText(ST_ShortestLine(ST_SetSRID(ST_GeometryFromText(‘LINESTRING(120.2161129 30.2525742,120.2144964 30.2534469,120.2129839 30.2541385,120.2126652 30.2542843)’),4326),ST_SetSRID(st_Point(120.2161, 30.2526),4326)))
返回结果:
LINESTRING(120.216092126105 30.2525854152045,120.2161 30.2526)
参考资料:
1. postgis-2.2.0dev+ 手册中文版 伏念译.pdf
2. http://download.geofabrik.de/ OSM下载shp
转载自:https://blog.csdn.net/RedToMemory/article/details/78262292