《空间数据库实验指导书的空间数据库操作 .docx》由会员分享，可在线阅读，更多相关《空间数据库实验指导书的空间数据库操作 .docx（13页珍藏版）》请在taowenge.com淘文阁网|工程机械CAD图纸|机械工程制图|CAD装配图下载|SolidWorks_CaTia_CAD_UG_PROE_设计图分享下载上搜索。
1、精品名师归纳总结PostGIS 的空间数据库操作1、SHP 导入 POSTGIS数据库导出 sql 再导入数据库方法 1：以 SQL 文件为中间媒介实现shp 导入空间数据库Step1： shp2pgsql -s 4326 F:spatialdatacities.shpcities D:cities.sql说明： -s设置坐标系统。F:spatialdatacities.shp 指定数据源。cities 目标表名称。Step2： 运行 postgres 数据库。输入登录密码：admin进入数据库。选中postgis 数据库Step3： 点击工具栏上的，弹出 sql 窗口可编辑资料 - - -
2、欢迎下载精品名师归纳总结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：通过界面导入空间库Step1： 在开头菜单中，运行postgis 2.0 下面的Step2： 点击，设
3、置数据库连接。确定，假如胜利，在前一界面上会显现“ Connection succeeded.”的信息。可编辑资料 - - - 欢迎下载精品名师归纳总结Step3： 点击，在对话框找到需要转入空间数据库的shp 文件，openStep4： 点击即可导入数据Step5： 假如数据中的字段存在汉字的话，可以点击进行编码设置。2、SHP 导入 POSTGIS数据库 方法 1：通过命令行直接导出shppgsql2shp -f d:shop_point.shp -h localhost -u postgres -P admin postgispublic.cities说明： -fd:shop_point
4、.shp 导出文件的名称和路径。-h 数据库的 ip 的址。 U用户名。p 密码。postgis空间数据库名称。public.cities 空间数据库表的名称可编辑资料 - - - 欢迎下载精品名师归纳总结方法 2：通过命令行直接导出shpStep1： 在开头菜单中，运行postgis 2.0 下面的Step2 ： 点击，设置数据库连接。确定，假如胜利，在前一界面上会显现“ Connection succeeded.”的信息。Step3： 挑选 export 界面Step3： 点击。再点击即可3、PostGIS函数分类字段处理函数AddGeometryColumn为已有的数据表增加一个的理几何
5、数据字段。DropGeometryColumn删除一个的理数据字段的。SetSRID 设置 SRID 值几何关系函数这类函数目前共有10 个，分别是：Distance ，Equals，Disjoint ，Intersects， Touches Crosses， Within ， Overlaps ， Contains， Relate可编辑资料 - - - 欢迎下载精品名师归纳总结几何分析函数这类函数目前共有12 个，分别是：Centroid ，Area ， Lenth， PointOnSurface ， Boundary ， Buffer ， ConvexHull ， Intersection
6、， SymDifference ， Difference ， GeomUnion ， MemGeomUnion读写函数这类函数许多，主要是用于在各种数据类型之间的转换，特别是在于Geometry 数据类型与其他如字符型等数据类型之间的转换，函数名如AsText、GeomFromText 等。4、Geo-SQL查询Selectc1.city_nameFromCitiesC1,RiversRWhere ST_OverlapsC1.geom,ST_BufferR.geom,3000Select c1.city_name From Cities C1, Select geom from rivers
7、where name=Alabama as m Where ST_OverlapsC1.geom,ST_Bufferm,3000Select c1.city_nameFromCitiesC1, Select ST_Buffergeom,3000as dd fromrivers where name=Platte as m Where ST_OverlapsC1.geom,m.dd=trueSelectc1.city_name,ST_OverlapsC1.geom,m.ddFromCitiesC1,Select ST_Buffergeom,3000as dd from rivers where
8、name=Platte as m做一个点查询查询州数据ST_Within点查询城市ST_Buffer 、ST_Within查询某一州包含的城市ST_Contains查询面价大于 XX 的州有哪些 ST_Area查询州的名字根据面积从大到小排序ST_Area条件查询空间数据，查询结果的空间数据，用文本显示ST_AsText与某点距离小于XX 的要素有哪些点线面分别查询一次ST_Distance、ST_MakePoint查询名字为 XX 的河流，流经哪些州ST_Crosses将某个城市平移肯定的距离ST_GeomFromText 、ST_AsText附件：常用的Geo-SQL查询1. SELECT
9、shengjie_region.name,shijie_region.nameFROM shengjie_region ,shijie_regionwhere shijie_region.provincena= shengjie_region.name and ST_WithinST_MakePoint121.54, 38.91, shengjie_region.geom点查询某省份， 并找到该省份的城市2. SELECT name FROMshijie_region where ST_WithinST_MakePoint121.54,38.91,可编辑资料 - - - 欢迎下载精品名师归纳总
10、结geom3. SELECTshengjie_region.name,shijie_region.nameFROM shengjie_region ,shijie_region where ST_WithinST_MakePoint121.54, 38.91, shijie_region.geomandST_WithinST_MakePoint121.54,38.91, shengjie_region.geom4. SELECT shengjie_region.name,shijie_region.name,xianjie_region.nameFROM shengjie_region,shi
11、jie_region,xian_point,xianjie_regionwhere ST_WithinST_MakePoint121.54,38.91,shengjie_region.geomand ST_WithinST_MakePoint121.54,38.91,shijie_region.geomand ST_WithinST_MakePoint121.54, 38.91, xianjie_region.geom5. SELECTnameFROMguodao_polylinewhereST_WithinST_MakePoint121.54, 38.91, geom7.8.9.10.sel
12、ect st_point63.573566, 44.646244 from dual;SELECT ST_AsTextgeom as wkt FROM shengjie_region where name = 自治区 SELECT ST_AsEWKTgeom as buffer FROM shengjie_region SELECT新疆维吾尔11.12.ST_AsTextst_transformst_setsridST_Bufferst_transformst_setsridst_geomfromtext + wkt + , 4326, 2333, 10800, 2333, 4326 FROM
13、 dualSELECT ST_MakePoint121.55223, 38.86758 from dual;SELECTst_distanceST_MakePoint121.55223,38.86758,geomfromgongyuan_point;13.SELECT name FROM gongyuan_point where ST_WithinST_MakePoint121.54, 38.91, geom14.SELECT ST_SetSRIDST_MakePoint121.55223, 38.86758,4326;6. select name,st_distanceST_MAKEPOIN
14、T116.561,40.276,geomas distance from shengjie_region where name= 天津市 or name =辽宁省 or name=北京市 15. SELECTname,st_distanceST_MakePoint121.55223,38.86758,geomfrom gongyuan_point where st_distanceST_MakePoint121.55223, 38.86758,geom 0.02 ;16. SELECTgid,name,st_setsridST_MakePoint121.55223,38.86758,4326f
15、rom gongyuan_point where st_distanceST_MakePoint121.55223, 38.86758,geom 0.02 ;17. SELECTST_DistanceST_TransformST_GeomFromTextPOINT121.55223 38.86758,4326,26986,ST_TransformST_GeomFromTextPOINT121.56224 38.87757, 4326,26986;18. selectgid,POINTgeomfromgongyuan_pointwhere st_distanceST_MakePoint121.5
16、5223, 38.86758,geom 0.02 ;19. SELECTST_DistanceST_TransformST_GeomFromTextPOINT121.55223 38.86758,4326,26986,ST_TransformST_GeomFromTextPOINT121.56171408 38.87784198, 4326,26986;20. SELECTST_DWithinST_TransformST_GeomFromTextPOINT121.55223 38.86758,4326,26986,ST_TransformST_GeomFromTextPOINT121.5617
17、1408 38.87784198, 4326,26986,1500 from dual;21. SELECTST_DWithinST_TransformST_GeomFromTextPOINT121.55223 38.86758,4326,26986,ST_TransformST_GeomFromTextPOINT121.56171408可编辑资料 - - - 欢迎下载精品名师归纳总结38.87784198, 4326,26986,1400 from dual;22. SELECT name,st_distanceST_MakePoint121.55223, 38.86758,geom fro
18、m gongyuan_point where st_distanceST_MakePoint121.55223, 38.86758,geom 0.02 order by st_distanceST_MakePoint121.55223, 38.86758,geom;23. SELECTST_TransformST_GeomFromTextPOINT121.5522338.86758,4326,26986 from dual24. SELECT ST_Transformpointt.geom,26986,5144 from gongyuan_point t25. SELECT pointgong
19、yuan_point.geom from gongyuan_point26. SELECTname,gidFROMgongyuan_pointORDERBYgeom st_setsridst_makepoint121.55223,38.86758,4326 LIMIT 10;27. select st_extentgeom as wgs84 from gongyuan_point;28. selectgid,st_xgeom,st_ygeom,st_zgeom,POINTgeomfromgongyuan_point where st_distanceST_MakePoint121.55223,
20、 38.86758,geom 0.02 ;29. selectgid,st_xgeom,st_ygeom,st_zgeom,POINTgeom,st_sridgeomfrom gongyuan_point where st_distanceST_MakePoint121.55223, 38.86758,geom 0.02 ;30. selectST_Areageom,gid,st_AsTextgeom,st_xgeom,st_ygeom,st_zgeom,POINTgeom,st_sridgeomfromgongyuan_pointwherest_distanceST_MakePoint121
21、.55223, 38.86758,geom 0.02 ;31. selectST_distance_sphereST_MakePoint121.55223,38.86758,geomfrom gongyuan_point where st_distanceST_MakePoint121.55223, 38.86758,geom 0.02 ;32. selectST_distance_sphereST_MakePoint121.55223,38.86758,geomfrom gongyuan_point where ST_distance_sphereST_MakePoint121.55223,
22、 38.86758,geom 1000 ;33. selectST_distance_sphereST_MakePoint121.55223,38.86758,geomfrom gongyuan_point where ST_distance_sphereST_MakePoint121.55223, 38.86758,geom 1500 ;34. selectST_distance_sphereST_MakePoint121.55223,38.86758,geom, ST_AsBinarygeom,ST_AsEWKTgeom,ST_AsEWKBgeom, ST_AsHEXEWKBgeomfro
23、mgongyuan_pointwhere ST_distance_sphereST_MakePoint121.55223, 38.86758,geom 1500 ;35. select name,ST_distance_sphereST_MakePoint121.55223, 38.86758,geom from gongjiaozhan_pointwhereST_distance_sphereST_MakePoint121.55223, 38.86758,geom 500 ;36. select name,ST_distance_sphereST_MakePoint121.55223, 38
24、.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 order by ST_distance_sphereST_MakePoint121.55223,
25、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 limit 1;可编辑资料 - - - 欢迎下载精品名师归纳总结39. SELECT name
26、FROMshengjie_region whereST_WithinST_MakePoint121.55223, 38.86758, geom40. SELECTname FROMshijie_regionwhereST_WithinST_MakePoint121.55223, 38.86758, geom41. SELECTname FROMxianjie_regionwhereST_WithinST_MakePoint121.55223,38.86758, geom42. selectname,ST_distance_sphereST_MakePoint121.55223, 38.8675
27、8,geom,st_xgeom,st_ygeomfromdasha_pointwhere ST_distance_sphereST_MakePoint121.55223, 38.86758,geom 3000 order by ST_distance_sphereST_MakePoint121.55223, 38.86758,geom limit 1 ;43. selectname,ST_distance_sphereST_MakePoint121.55223, 38.86758,geom,st_xgeom,st_ygeom from binguanjiudian_point where ST
28、_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,31) ,st_geomfromTextLINESTRING2 0,2 2,5 2,3 1 from dual;45. select ST_AsEWKTST_Differencest_geomfro
29、mTextPOLYGON11,2 3,3 4,3 1,1 1,st_geomfromTextPOLYGON2 0,2 2,5 2,1 3,2 0 from dual;46. select GeometryTypest_geomfromTextMULTILINESTRING11,2 3,3 4,3 1,2 1,1 1,1 2,2 3,4 5 from dual;47. SELECTname,st_areageom as area fromshengjie_region ORDERBYarea DESC LIMIT 1;48. SELECTst_Length2dst_GeomFromTextMul
30、tiLineString11,2 2,3 3,44,5 5 from dual;49. SELECTgid,name,st_astextgeomASLinestring,st_lengthgeomFROMguodao_polyline where name= 东北路 ;50. SELECT name, st_astextgeom FROM guodao_polyline where gid=152415;51. SELECTgid,st_astextgeomASMULTIPOINTFROMguodao_polyline WHERE gid = 152415;52. SELECT st_aste
31、xtgeomAS MLINESTRINGFROM guodao_polyline;53. SELECT name FROM guodao_polyline where gid=152415;54. SELECT ST_AsEWKTST_Line_Interpolate_Pointthe_line, 0.555. FROM SELECT ST_GeomFromEWKTLINESTRING1 2 3, 4 5 6, 6 7 8 as the_line As foo;56. SELECTST_AsTextST_Line_Interpolate_Pointfoo.the_line,ST_Line_Lo
32、cate_Pointfoo.the_line,ST_GeomFromTextPOINT43FROM SELECT ST_GeomFromTextLINESTRING1 2, 4 5, 6 7 As the_line As foo;57. SELECTST_AsTextST_Line_SubStringST_GeomFromTextLINESTRING2550, 100 125, 150 190, 0.333, 0.666;58. SELECTST_AsTexthouse_locAsas_text_house_loc,startstreet_num+ CAST endstreet_num - s
33、tartstreet_num*ST_Line_Locate_Pointstreet_line, house_loc As integer As street_num FROM SELECT ST_GeomFromTextLINESTRING1 2, 34 As street_line,ST_MakePointx*1.01,y*1.03 As house_loc, 10 As startstreet_num,20 As endstreet_num FROM generate_series1,3 x CROSS JOIN generate_series2,4可编辑资料 - - - 欢迎下载精品名师
34、归纳总结As y As foo WHERE ST_DWithinstreet_line, house_loc, 0.2;59. SELECTST_AsTextthe_geomFROMSELECT ST_LocateAlong ST_GeomFromTextMULTILINESTRINGM1 2 3, 3 4 2, 9 4 3, 12 3, 5 4 5,3 As the_geom As foo;60. SELECTST_AsTextthe_geomFROMSELECT ST_LocateBetween ST_GeomFromTextMULTILINESTRING M 1 2 3, 3 4 2,
35、9 4 3,1 2 3, 5 4 5,1.5, 3 As the_geom As foo;61. SELECTST_AsEWKTST_Dumpthe_geom.geomFROMSELECT ST_LocateBetweenElevations ST_GeomFromEWKTLINESTRING1 2 6, 4 5 -1, 7 8 9,6,9As the_geom As foo62. SELECT ST_InterpolatePointLINESTRING M 0 0 0, 10 0 20, POINT5 5;63. SELECTST_AsTextST_AddMeasure ST_GeomFromEWKTMULTILINESTRINGM1 0 4, 20 4, 4 0 4,1 0 4, 2 0 4, 4 0 4,10,70 As ewelev;可编辑资料 - - - 欢迎下载