3空間數(shù)據(jù)庫試驗指導(dǎo)書-PostGIS的空間數(shù)據(jù)庫操作

3空間數(shù)據(jù)庫實驗指導(dǎo)書-PostGIS的空間數(shù)據(jù)庫操作PostGIS的空間數(shù)據(jù)庫操作1、SHP導(dǎo)入POSTGIS數(shù)據(jù)庫導(dǎo)出sql再導(dǎo)入數(shù)據(jù)庫方法1：以SQL文件為中間媒介實現(xiàn)shp導(dǎo)入空間數(shù)據(jù)庫Stepl ：shp2pgsql-s4326F:\spatial\data\cities.shpcities>D:\cities.sql說明：-s設(shè)置坐標(biāo)系統(tǒng)；F:\spatial\data\cities.shp指定數(shù)據(jù)源；cities目標(biāo)表名稱。Step2：運行postgres數(shù)據(jù)庫；輸入登錄密碼:admin進(jìn)入數(shù)據(jù)庫；選中postgis數(shù)據(jù)庫Step3：點擊工具欄上的皤，彈出sql窗口

Step4：點擊口,找到剛才生成的SQL文件，打開，運行》即可。方法2：通過命令行直接導(dǎo)入空間庫Stepl ：shplpgsql-s4326F:\spatial\data\cities.shppublic.citiespsql-Upostgres-padmin-dpostgis說明：-s設(shè)置坐標(biāo)系統(tǒng)；F:\spatial\data\cities.shp指定數(shù)據(jù)源；public.cities目標(biāo)表名稱；-U用戶名；-p密碼；-d空間數(shù)據(jù)庫名稱。方法方法3：通過界面導(dǎo)入空間庫審助和支持Step3：點擊——Ad審助和支持Step3：點擊——AdlFile一,在對話框找到需要轉(zhuǎn)入空間數(shù)據(jù)庫的shp文件，openStepl：在開始菜單中，運行postgis2.0下面的?MyEdipsePostGlS2.0forPostgreSQLx649.0APostGIS2.0ManualOn-LinePostGlS2.0ShapefileandDBFLoaderExporter-PostGlSDocumentationOn-LineIIPostGlSJDBCDriverOn-LineDo?PlPostGlSWebsitePostgreSQL9.0WinRARStep2：點擊Viewconnectiondetails...,設(shè)置數(shù)據(jù)庫連接；確定，如果成功，在前一界面上會出現(xiàn)"Connectionsucceeded.v的信息。

點擊Step4：點擊?^^點擊Step4：點擊?^^即可導(dǎo)入數(shù)據(jù)Step5：如果數(shù)據(jù)中的字段存在漢字的話，可以O(shè)ptio-ns...進(jìn)行編碼設(shè)置。2、SHP導(dǎo)入POSTGIS數(shù)據(jù)庫方法1：通過命令行直接導(dǎo)出shppgsq12shp-fd:\shop_point.shp-hlocalhost-upostgres-Padminpostgispublicities說明：-fd:\shop_point.shp導(dǎo)出文件的名稱和路徑；-h數(shù)據(jù)庫的ip地址；-U用戶名；-p密碼；postgis空間數(shù)據(jù)庫名稱；publicities空間數(shù)據(jù)庫表的名稱方法方法2：通過命令行直接導(dǎo)出shp果成功，在前一界面上會出現(xiàn)“Connectionsucceeded.”的信息。果成功，在前一界面上會出現(xiàn)“Connectionsucceeded.”的信息。Step3：選擇export界面QtPostGISS^i5prfileImporVExpartManagerStepl：在開始菜單中，運行postgis2.0下面的Step2：點擊，設(shè)置數(shù)據(jù)庫連接；確定，如工 viewconnectiondetails，設(shè)置數(shù)據(jù)庫連接；確定，如Step3：點擊二AddT5ble；再點擊匚Export二即可3、PostGIS函數(shù)分類字段處理函數(shù)AddGeometryColumn為已有的數(shù)據(jù)表增加一個地理幾何數(shù)據(jù)字段；DropGeometryColumn刪除一個地理數(shù)據(jù)字段的；SetSRID設(shè)置SRID值幾何關(guān)系函數(shù)這類函數(shù)目前共有10個，分別是：Distance,Equals,Disjoint,Intersects,TouchesCrosses,Within,Overlaps,Contains,Relate幾何分析函數(shù)這類函數(shù)目前共有12個，分別是：Centroid,Area,Lenth,PointOnSurface,Boundary,Buffer,ConvexHull,Intersection,SymDifference,Difference,GeomUnion,MemGeomUnion讀寫函數(shù)這類函數(shù)很多，主要是用于在各種數(shù)據(jù)類型之間的轉(zhuǎn)換，尤其是在于Geometry數(shù)據(jù)類型與其他如字符型等數(shù)據(jù)類型之間的轉(zhuǎn)換，函數(shù)名如AsText、GeomFromText等。4、Geo-SQL查詢Selectc1.city_nameFromCitiesC1,RiversR WhereST_Overlaps(C1.geom,ST_Buffer(R.geom,3000))Selectc1.city_nameFromCitiesC1,(Selectgeomfromriverswherename='Alabama')asm WhereST_Overlaps(C1.geom,ST_Buffer(m,3000))Selectc1.city_nameFromCitiesC1,(SelectST_Buffer(geom,3000)asddfromriverswherename='Platte')asmWhereST_Overlaps(C1.geom,m.dd)=trueSelect c1.city_name,ST_Overlaps(C1.geom,m.dd)FromCitiesC1,(SelectST_Buffer(geom,3000)asddfromriverswherename='Platte')asm做一個點查詢(查詢州數(shù)據(jù))ST_Within點查詢城市ST_Within查詢某一州包含的城市ST_Contains查詢面價大于XX的州有哪些ST_Area查詢州的名字(按照面積從大到小排序)ST_Area條件查詢空間數(shù)據(jù)，查詢結(jié)果的空間數(shù)據(jù)，用文本顯示ST_AsText與某點距離小于XX的要素有哪些(點線面分別查詢一次)ST_Distance、ST_MakePoint查詢名字為XX的河流，流經(jīng)哪些州ST_Crosses將某個城市平移一定的距離ST_GeomFromText、ST_AsText附件：常用的Geo-SQL查詢SELECTshengjie_,shijie_FROMshengjie_region,shijie_regionwhereshijie_vincena=shengjie_ andST_Within(ST_MakePoint(121.54,38.91),shengjie_region.geom)點查詢某省份，并找到該省份的城市SELECTnameFROMshijie_regionwhereST_Within(ST_MakePoint(121.54,38.91),geom)SELECTshengjie_,shijie_FROMshengjie_region,shijie_regionwhereST_Within(ST_MakePoint(121.54,38.91),shijie_region.geom)andST_Within(ST_MakePoint(121.54,38.91),shengjie_region.geom)SELECTshengjie_,shijie_,xianjie_ FROMshengjie_region,shijie_region,xian_point,xianjie_region whereST_Within(ST_MakePoint(121.54,38.91),shengjie_region.geom) andST_Within(ST_MakePoint(121.54,38.91),shijie_region.geom) andST_Within(ST_MakePoint(121.54,38.91),xianjie_region.geom)SELECTnameFROMguodao_polylinewhereST_Within(ST_MakePoint(121.54,38.91),geom)selectname,st_distance(ST_MAKEPOINT(116.5640.276),geom)asdistancefromshengjie_regionwherename='天津市'orname='遼寧省'orname='北京市'selectst_point(63.573566,44.646244)fromdual;SELECTST_AsText(geom)aswktFROMshengjie_regionwherename='新疆維吾爾自治區(qū)’SELECTST_AsEWKT(geom)asbufferFROMshengjie_regionSELECTST_AsText(st_transform(st_setsrid(ST_Buffer(st_transform(st_setsrid(st_geomfromtext('"+wkt+"'),4326),2333),10800),2333),4326))FROMdualSELECTST_MakePoint(121.55223,38.86758)fromdual;SELECTst_distance(ST_MakePoint(121.55223,38.86758),geom)fromgongyuan_point;SELECTnameFROMgongyuan_pointwhereST_Within(ST_MakePoint(121.54,38.91),geom)SELECTST_SetSRID(ST_MakePoint(121.55223,38.86758),4326);SELECTname,st_distance(ST_MakePoint(12L55223,38.86758),geom)fromgongyuan_pointwherest_distance(ST_MakePoint(121.55223,38.86758),geom)<0.02;SELECTgid,name,st_setsrid(ST_MakePoint(12L55223,38.86758),4326)fromgongyuan_pointwherest_distance(ST_MakePoint(121.55223,38.86758),geom)<0.02;SELECTST_Distance(ST_Transform(ST_GeomFromText('POINT(121.5522338.86758)',4326),26986),ST_Transform(ST_GeomFromText('POINT(121.5622438.87757)',4326),26986));selectgid,POINT(geom)fromgongyuan_point wherest_distance(ST_MakePoint(121.55223,38.86758),geom)<0.02;SELECTST_Distance(ST_Transform(ST_GeomFromText('POINT(121.5522338.86758)',4326),26986),ST_Transform(ST_GeomFromText('POINT(121.5617140838.87784198)',4326),26986));SELECTST_DWithin(ST_Transform(ST_GeomFromText('POINT(121.5522338.86758)',4326),26986),ST_Transform(ST_GeomFromText('POINT(121.5617140838.87784198)',4326),26986),1500)fromdual;SELECTST_DWithin(ST_Transform(ST_GeomFromText('POINT(121.5522338.86758)',4326),26986),ST_Transform(ST_GeomFromText('POINT(121.5617140838.87784198)',4326),26986),1400)fromdual;SELECTname,st_distance(ST_MakePoint(12L55223,38.86758),geom)fromgongyuan_pointwherest_distance(ST_MakePoint(121.55223,38.86758),geom)< 0.02orderbyst_distance(ST_MakePoint(121.55223,38.86758),geom);SELECTST_Transform(ST_GeomFromText('POINT(121.5522338.86758)',4326),26986)fromdualSELECTST_Transform(point(t.geom,26986),5144)fromgongyuan_pointtSELECTpoint(gongyuan_point.geom)fromgongyuan_pointSELECTname,gidFROMgongyuan_pointORDERBYgeom<->st_setsrid(st_makepoint(121.55223,38.86758),4326)LIMIT10;selectst_extent(geom)aswgs84fromgongyuan_point;selectgid,st_x(geom),st_y(geom),st_z(geom),POINT(geom)fromgongyuan_pointwherest_distance(ST_MakePoint(121.55223,38.86758),geom)<0.02;selectgid,st_x(geom),st_y(geom),st_z(geom),POINT(geom),st_srid(geom) fromgongyuan_point wherest_distance(ST_MakePoint(121.55223,38.86758),geom)<0.02;30.selectST_Area(geom),gid,st_AsText(geom),st_x(geom),st_y(geom),st_z(geom),POINT(geom),st_srid(geom)fromgongyuan_pointwherest_distance(ST_MakePoint(121.55223,38.86758),geom)<0.02;selectST_distance_sphere(ST_MakePoint(12L55223,38.86758),geom)fromgongyuan_pointwherest_distance(ST_MakePoint(12L55223,38.86758),geom)<0.02;selectST_distance_sphere(ST_MakePoint(12L55223,38.86758),geom)fromgongyuan_pointwhereST_distance_sphere(ST_MakePoint(12L55223,38.86758),geom)<1000;selectST_distance_sphere(ST_MakePoint(12L55223,38.86758),geom)fromgongyuan_pointwhereST_distance_sphere(ST_MakePoint(121.55223,38.86758),geom)<1500;selectST_distance_sphere(ST_MakePoint(121.55223,38.86758),geom),ST_AsBinary(geom),ST_AsEWKT(geom),ST_AsEWKB(geom),ST_AsHEXEWKB(geom) fromgongyuan_point whereST_distance_sphere(ST_MakePoint(12L55223,38.86758),geom)<1500;selectname,ST_distance_sphere(ST_MakePoint(121.55223, 38.86758),geom)fromgongjiaozhan_point whereST_distance_sphere(ST_MakePoint(12L55223,38.86758),geom)<500;selectname,ST_distance_sphere(ST_MakePoint(121.55223, 38.86758),geom)fromdasha_point whereST_distance_sphere(ST_MakePoint(12L55223,38.86758),geom)<1500;selectname,ST_distance_sphere(ST_MakePoint(121.55223, 38.86758),geom)fromdasha_point whereST_distance_sphere(ST_MakePoint(121.55223,38.86758),geom)<1500orderbyST_distance_sphere(ST_MakePoint(12L55223,38.86758),geom)limit1;selectname,ST_distance_sphere(ST_MakePoint(121.55223, 38.86758),geom)fromchaoshishangcheng_point whereST_distance_sphere(ST_MakePoint(12L55223,38.86758),geom)<500orderbyST_distance_sphere(ST_MakePoint(12L55223,38.86758),geom)limit1;SELECTnameFROMshengjie_regionwhereST_Within(ST_MakePoint(121.55223,38.86758),geom)SELECTnameFROMshijie_regionwhereST_Within(ST_MakePoint(121.55223,38.86758),geom)SELECTnameFROMxianjie_regionwhereST_Within(ST_MakePoint(121.55223,38.86758),geom)selectname,ST_distance_sphere(ST_MakePoint(121.55223,38.86758),geom),st_x(geom),st_y(geom)from dasha_point whereST_distance_sphere(ST_MakePoint(12L55223,38.86758),geom)<3000orderbyST_distance_sphere(ST_MakePoint(12L55223,38.86758),geom)limit1;selectname,ST_distance_sphere(ST_MakePoint(121.55223,38.86758),geom),st_x(geom),st_y(geom)frombinguanjiudian_pointwhereST_distance_sphere(ST_MakePoint(12L55223,38.86758),geom)<3000orderbyST_distance_sphere(ST_MakePoint(121.55223,38.86758),geom)limit1;selectST_AsEWKT(ST_Difference(st_geomfromText('LINESTRING(11,2 3,3 4,31)'),st_geomfromText('LINESTRING(20,22,52,31)')))fromdual;selectST_AsEWKT(ST_Difference(st_geomfromText('POLYGON((11,23,34,3 1,11))'),st_geomfromText('POLYGON((20,22,52,13,20))')))fromdual;selectGeometryType(st_geomfromText('MULTILINESTRING((11,23,34,31,21,11),(12,23,45))'))fromdual;SELECTname,st_area(geom)asareafromshengjie_regionORDERBYareaDESCLIMIT1;SELECTst_Length2d(st_GeomFromText('MultiLineString((11,22,33),(44,55))'))fromdual;SELECTgid,name,st_astext(geom)ASLinestring,st_length(geom) FROMguodao_polylinewherename='東北路'；SELECTname,st_astext(geom)FROMguodao_polylinewheregid='152415';SELECTgid,st_astext(geom)ASMULTIPOINTFROMguodao_polylineWHEREgid=152415;SELECTst_astext(geom)ASMLINESTRING FROMguodao_polyline;SELECTnameFROMguodao_polylinewheregid='152415';SELECTST_AsEWKT(ST_Line_Interpolate_Point(the_line,0.5))FROM (SELECTST_GeomFromEWKT('LINESTRING(123,456,678)')asthe_line)Asfoo;SELECTST_AsText(ST_Line_Interpolate_Point(foo.the_line,ST_Line_Locate_Point(foo.the_line,ST_GeomFromText('POINT(43)'))))FROM (SELECTST_GeomFromText('LINESTRING(12,45,67)')Asthe_line)Asfoo;SELECTST_AsText(ST_Line_SubString(ST_GeomFromText('LINESTRING(2550,100125,150190)'),0.333