《2022年空间数据库实验指导书PostGIS的空间数据库操作.docx》由会员分享，可在线阅读，更多相关《2022年空间数据库实验指导书PostGIS的空间数据库操作.docx（16页珍藏版）》请在taowenge.com淘文阁网|工程机械CAD图纸|机械工程制图|CAD装配图下载|SolidWorks_CaTia_CAD_UG_PROE_设计图分享下载上搜索。
1、精选学习资料 - - - - - - - - - PostGIS 的空间数据库操作1、SHP 导入 POSTGIS 数据库导出 sql 再导入数据库方法 1：以 SQL 文件为中间媒介实现shp 导入空间数据库Step1：shp2pgsql -s 4326 F:spatialdatacities.shp cities D:cities.sql 说明： -s 设置坐标系统；F:spatialdatacities.shp 指定数据源；cities 目标表名称；Step2：运行 postgres 数据库；输入登录密码：admin 进入数据库；选中 postgis 数据库Step3：点击工具栏上的,弹
2、出 sql 窗口名师归纳总结 - - - - - - -第 1 页,共 9 页精选学习资料 - - - - - - - - - Step4：点击,找到刚刚生成的SQL 文件,打开,运行即可；方法 2：通过命令行直接导入空间库Step1：shp2pgsql -s 4326 F:spatialdatacities.shp public.cities psql -U postgres -p admin -d postgis 说明： -s 设置坐标系统；F:spatialdatacities.shp 指定数据源；public.cities 目标表名称； U 用户名；p 密码；d 空间数据库名称；方法
3、3：通过界面导入空间库Step1：在开头菜单中,运行 postgis 2.0 下面的Step2：点击,设置数据库连接；确定,假如胜利,在前一名师归纳总结 界面上会显现“Connection succeeded. ” 的信息；第 2 页,共 9 页- - - - - - -精选学习资料 - - - - - - - - - Step3：点击,在对话框找到需要转入空间数据库的shp 文件,open Step4：点击 即可导入数据Step5：假如数据中的字段存在汉字的话,可以点击进行编码设置；2、SHP 导入 POSTGIS 数据库名师归纳总结 方法 1：通过命令行直接导出shp 第 3 页,共 9
4、页pgsql2shp -f d:shop_point.shp -h localhost -u postgres -P admin postgis public.cities 说明：-f d:shop_point.shp 导出文件的名称和路径；-h 数据库的 ip 地址； U 用户名；p 密码；postgis 空间数据库名称；public.cities 空间数据库表的名称- - - - - - -精选学习资料 - - - - - - - - - 方法 2：通过命令行直接导出 shp Step1：在开头菜单中,运行 postgis 2.0 下面的Step2：点击,设置数据库连接；确定,假如胜利,在
5、前一界面上会显现“Connection succeeded.” 的信息；Step3：挑选 export 界面Step3：点击；再点击即可3、PostGIS 函数分类字段处理函数AddGeometryColumn 为已有的数据表增加一个地理几何数据字段；DropGeometryColumn 删除一个地理数据字段的；SetSRID 设置 SRID 值几何关系函数这类函数目前共有 10 个,分别是：Distance,Equals,Disjoint ,Intersects, Touches Crosses, Within , Overlaps, Contains,Relate 名师归纳总结 - - -
6、 - - - -第 4 页,共 9 页精选学习资料 - - - - - - - - - 几何分析函数 这类函数目前共有 12 个,分别是：Centroid ,Area ,Lenth, PointOnSurface,Boundary ,Buffer ,ConvexHull ,Intersection,SymDifference , Difference , GeomUnion , MemGeomUnion 读写函数 这类函数许多,主要是用于在各种数据类型之间的转换,特别是在于 Geometry 数据类型与其他如字符型等数据类型之间的转换,函数名如4、Geo-SQL 查询AsText、GeomFr
7、omText 等；Select c1.city_name From Cities C1, Rivers R Where ST_OverlapsC1.geom,ST_BufferR.geom,3000 Select c1.city_name From Cities C1, Select geom from rivers where name=Alabama as m Where ST_OverlapsC1.geom,ST_Bufferm,3000 Select c1.city_name From Cities C1, Select ST_Buffergeom,3000 as dd from ri
8、vers where name=Platte as m Where ST_OverlapsC1.geom,m.dd=true Select c1.city_name, ST_OverlapsC1.geom,m.dd From Cities C1, Select ST_Buffergeom,3000 as dd from rivers where name=Platte as m 做一个点查询查询州数据ST_Within 点查询城市 ST_Buffer 、ST_Within 查询某一州包含的城市 ST_Contains 查询面价大于 XX 的州有哪些 ST_Area 查询州的名字根据面积从大到小
9、排序ST_Area 条件查询空间数据,查询结果的空间数据,用文本显示 ST_AsText 与某点距离小于 XX 的要素有哪些点线面分别查询一次ST_Distance、ST_MakePoint 查询名字为 XX 的河流,流经哪些州 ST_Crosses 将某个城市平移肯定的距离ST_GeomFromText 、ST_AsText 附件：常用的Geo-SQL查询FROM 1.SELECT shengjie_region.name,shijie_region.name shengjie_region ,shijie_region where shijie_region.provincena = sh
10、engjie_region.name and ST_WithinST_MakePoint121.54, 38.91, shengjie_region.geom 点查询某省份,并找到该省份的城市名师归纳总结 2.SELECT name FROM shijie_region where ST_WithinST_MakePoint121.54, 38.91, 第 5 页,共 9 页- - - - - - -精选学习资料 - - - - - - - - - geom 3.SELECT shengjie_region.name,shijie_region.name FROM shengjie_regio
11、n ,shijie_region where ST_WithinST_MakePoint121.54, 38.91, shijie_region.geom and ST_WithinST_MakePoint121.54, 38.91, shengjie_region.geom 4.SELECT shengjie_region.name,shijie_region.name,xianjie_region.name FROM shengjie_region ,shijie_region,xian_point,xianjie_region where ST_WithinST_MakePoint121
12、.54, 38.91, shengjie_region.geom and ST_WithinST_MakePoint121.54, 38.91, shijie_region.geom and ST_WithinST_MakePoint121.54, 38.91, xianjie_region.geom 5.SELECT name FROM guodao_polyline where ST_WithinST_MakePoint121.54, 38.91, geom 6.select name,st_distanceST_MAKEPOINT116.561, 40.276,geom as dista
13、nce from shengjie_region where name=天津市 or name =辽宁省 or name=北京市 7.select st_point63.573566, 44.646244 from dual; 新疆维吾尔8.SELECT ST_AsTextgeom as wkt FROM shengjie_region where name = 自治区 9. SELECT ST_AsEWKTgeom as buffer FROM shengjie_region 10. SELECT ST_AsTextst_transformst_setsridST_Bufferst_tran
14、sformst_setsridst_geomfromtext + wkt + , 4326, 2333, 10800, 2333, 4326 FROM dual 11.SELECT ST_MakePoint121.55223, 38.86758 from dual; 38.86758,geom from 12.SELECT st_distanceST_MakePoint121.55223, gongyuan_point; 13. SELECT name FROM gongyuan_point where ST_WithinST_MakePoint121.54, 38.91, geom 14.S
15、ELECT ST_SetSRIDST_MakePoint121.55223, 38.86758,4326; from 15.SELECT name,st_distanceST_MakePoint121.55223, 38.86758,geom gongyuan_point where st_distanceST_MakePoint121.55223, 38.86758,geom 0.02 ; 16.SELECT gid,name,st_setsridST_MakePoint121.55223, 38.86758,4326 from gongyuan_point where st_distanc
16、eST_MakePoint121.55223, 38.86758,geom 0.02 ; 17.SELECT ST_DistanceST_TransformST_GeomFromTextPOINT121.55223 38.86758,4326,26986,ST_TransformST_GeomFromTextPOINT121.56224 38.87757, 4326,26986; 18.select gid,POINTgeom from gongyuan_point where st_distanceST_MakePoint121.55223, 38.86758,geom 0.02 ; 19.
17、SELECT ST_DistanceST_TransformST_GeomFromTextPOINT121.55223 38.86758,4326,26986,ST_TransformST_GeomFromTextPOINT121.56171408 38.87784198, 4326,26986; 20.SELECT ST_DWithinST_TransformST_GeomFromTextPOINT121.55223 38.86758,4326,26986,ST_TransformST_GeomFromTextPOINT121.56171408 38.87784198, 4326,26986
18、,1500 from dual; 21.SELECT ST_DWithinST_TransformST_GeomFromTextPOINT121.55223 38.86758,4326,26986,ST_TransformST_GeomFromTextPOINT121.56171408 名师归纳总结 - - - - - - -第 6 页,共 9 页精选学习资料 - - - - - - - - - 38.87784198, 4326,26986,1400 from dual; 22.SELECT name,st_distanceST_MakePoint121.55223, 38.86758,ge
19、om from gongyuan_point where st_distanceST_MakePoint121.55223, 38.86758,geom 0.02 order by st_distanceST_MakePoint121.55223, 38.86758,geom; 23.SELECT ST_TransformST_GeomFromTextPOINT121.55223 38.86758,4326,26986 from dual 24.SELECT ST_Transformpointt.geom,26986,5144 from gongyuan_point t geom 25.SEL
20、ECT pointgongyuan_point.geom from gongyuan_point 26.SELECT name, gid FROM gongyuan_point ORDER BY st_setsridst_makepoint121.55223,38.86758,4326 LIMIT 10; 27.select st_extentgeom as wgs84 from gongyuan_point; from gongyuan_point 28.select gid,st_xgeom,st_ygeom,st_zgeom,POINTgeom where st_distanceST_M
21、akePoint121.55223, 38.86758,geom 0.02 ; 29.select gid,st_xgeom,st_ygeom,st_zgeom,POINTgeom,st_sridgeom from gongyuan_point where st_distanceST_MakePoint121.55223, 38.86758,geom 0.02 ; 30. select ST_Areageom,gid,st_AsTextgeom,st_xgeom,st_ygeom,st_zgeom,POINTgeom,st_sridgeom from gongyuan_point where
22、st_distanceST_MakePoint121.55223, 38.86758,geom 0.02 ; 31.select ST_distance_sphereST_MakePoint121.55223, 38.86758,geom from gongyuan_point where st_distanceST_MakePoint121.55223, 38.86758,geom 0.02 ; 32.select ST_distance_sphereST_MakePoint121.55223, 38.86758,geom from gongyuan_point where ST_dista
23、nce_sphereST_MakePoint121.55223, 38.86758,geom 1000 ; 33.select ST_distance_sphereST_MakePoint121.55223, 38.86758,geom from gongyuan_point where ST_distance_sphereST_MakePoint121.55223, 38.86758,geom 1500 ; 34. select ST_distance_sphereST_MakePoint121.55223, 38.86758,geom, ST_AsBinarygeom, ST_AsEWKT
24、geom, ST_AsEWKBgeom, ST_AsHEXEWKBgeom from gongyuan_point where ST_distance_sphereST_MakePoint121.55223, 38.86758,geom 1500 ; 35. select name,ST_distance_sphereST_MakePoint121.55223, 38.86758,geom from gongjiaozhan_point where ST_distance_sphereST_MakePoint121.55223, 38.86758,geom 500 ; 36.select na
25、me,ST_distance_sphereST_MakePoint121.55223, 38.86758,geom from dasha_point where ST_distance_sphereST_MakePoint121.55223, 38.86758,geom 1500 ; 37.select name,ST_distance_sphereST_MakePoint121.55223, 38.86758,geom from dasha_point where ST_distance_sphereST_MakePoint121.55223, 38.86758,geom 1500 orde
26、r by ST_distance_sphereST_MakePoint121.55223, 38.86758,geom limit 1 ; 38. select name,ST_distance_sphereST_MakePoint121.55223, 38.86758,geom from chaoshishangcheng_point where ST_distance_sphereST_MakePoint121.55223, 38.86758,geom 500 order by ST_distance_sphereST_MakePoint121.55223, 38.86758,geom l
27、imit 1; 名师归纳总结 - - - - - - -第 7 页,共 9 页精选学习资料 - - - - - - - - - 39.SELECT name FROM shengjie_region where ST_WithinST_MakePoint121.55223, 38.86758, geom 40.SELECT name FROM shijie_region where ST_WithinST_MakePoint121.55223, 38.86758, geom 41.SELECT name FROM xianjie_region where ST_WithinST_MakePoi
28、nt121.55223, 38.86758, geom 42. select name,ST_distance_sphereST_MakePoint121.55223, 38.86758,geom,st_xgeom,st_ygeom from dasha_point where ST_distance_sphereST_MakePoint121.55223, 38.86758,geom 3000 order by ST_distance_sphereST_MakePoint121.55223, 38.86758,geom limit 1 ; 43. select name,ST_distanc
29、e_sphereST_MakePoint121.55223, 38.86758,geom,st_xgeom,st_ygeom from binguanjiudian_point where ST_distance_sphereST_MakePoint121.55223, 38.86758,geom 3000 order by ST_distance_sphereST_MakePoint121.55223, 38.86758,geom limit 1 ; 44.select ST_AsEWKTST_Differencest_geomfromTextLINESTRING1 1,2 3,3 4,3
30、1,st_geomfromTextLINESTRING2 0,2 2,5 2,3 1 from dual; 45.select ST_AsEWKTST_Differencest_geomfromTextPOLYGON1 1,2 3,3 4,3 1,1 1,st_geomfromTextPOLYGON2 0,2 2,5 2,1 3,2 0 from dual; 46.select GeometryTypest_geomfromTextMULTILINESTRING1 1,2 3,3 4,3 1,2 1,1 1,1 2,2 3,4 5 from dual; 47. SELECT name,st_a
31、reageom as area from shengjie_region ORDER BY area DESC LIMIT 1; 48.SELECT st_Length2dst_GeomFromTextMultiLineString1 1,2 2,3 3,4 4,5 5 from dual; 49.SELECT gid,name ,st_astextgeom AS Linestring,st_lengthgeom FROM guodao_polyline where name= 东北路 ; 50. SELECT name, st_astextgeom FROM guodao_polyline
32、where gid=152415; 51. SELECT gid, st_astext geom AS MULTIPOINT FROM guodao_polyline WHERE gid = 152415; 52. SELECT st_astextgeom AS MLINESTRING FROM guodao_polyline; 53. SELECT name FROM guodao_polyline where gid=152415; 54. SELECT ST_AsEWKTST_Line_Interpolate_Pointthe_line, 0.5 55. FROM SELECT ST_G
33、eomFromEWKTLINESTRING1 2 3, 4 5 6, 6 7 8 as the_line As foo; 56.SELECT ST_AsTextST_Line_Interpolate_Pointfoo.the_line, ST_Line_Locate_Pointfoo.the_line, ST_GeomFromTextPOINT4 3 FROM SELECT ST_GeomFromTextLINESTRING1 2, 4 5, 6 7 As the_line As foo; 57.SELECT ST_AsTextST_Line_SubStringST_GeomFromTextL
34、INESTRING25 50, 100 125, 150 190, 0.333, 0.666; 58.SELECT ST_AsTexthouse_loc As as_text_house_loc,startstreet_num + CAST endstreet_num - startstreet_num*ST_Line_Locate_Pointstreet_line, house_loc As integer As street_num FROM SELECT ST_GeomFromTextLINESTRING1 2, 3 4 As street_line,ST_MakePointx*1.01
35、,y*1.03 As house_loc, 10 As startstreet_num, 20 As endstreet_num FROM generate_series1,3 x CROSS JOIN generate_series2,4 名师归纳总结 - - - - - - -第 8 页,共 9 页精选学习资料 - - - - - - - - - As y As foo WHERE ST_DWithinstreet_line, house_loc, 0.2; 59.SELECT ST_AsTextthe_geom FROM SELECT ST_LocateAlong ST_GeomFrom
36、TextMULTILINESTRINGM1 2 3, 3 4 2, 9 4 3, 1 2 3, 5 4 5,3 As the_geom As foo; 60.SELECT ST_AsTextthe_geom FROM SELECT ST_LocateBetween ST_GeomFromTextMULTILINESTRING M 1 2 3, 3 4 2, 9 4 3, 1 2 3, 5 4 5,1.5, 3 As the_geom As foo; 61.SELECT ST_AsEWKTST_Dumpthe_geom.geom FROM SELECT ST_LocateBetweenElevations ST_GeomFromEWKTLINESTRING1 2 6, 4 5 -1, 7 8 9,6,9As the_geom As foo 62. SELECT ST_InterpolatePointLINESTRING M 0 0 0, 10 0 20, POINT5 5; 63. SELECT ST_AsTextST_AddMeasure ST_GeomFromEWKTMULTILINESTRINGM1 0 4, 2 0 4, 4 0 4,1 0 4, 2 0 4, 4 0 4,10,70 As ewelev; 名师归纳总结 - - - - - - -第 9 页,共 9 页