血淚泣訴?!的把pgrouting建起來了~ 不過他只是幫你算一算兩點間路徑..還是要用程式把資訊都接起來才能用~ 囧/..
以下簡述一下導航的作業流程..
1. Input Parameter: 起終點坐標(WGS84)
2. 取得起終點的結點路段, 起點取source, 終點取target
3. 計算起終點路段的連結路徑
4. 整合路段資訊
5. Output: 回傳路線資訊
就不論防呆條件與物件化的撰寫~ 主要記錄有用到的相關sql語法..
2.a 取得起/終點最近的路段
select gid, st_distance('POINT(120.683513 24.142974)'::geography, the_geom) as dis from edges where the_geom && SetSRID(box2d(geomFromText('MULTIPOINT(120.683213 24.142674,120.683813 24.143274)')),4326) order by dis limit 1
利用box2d先縮減範圍再做計算比較快一點點..另外box2d有點蠢~ 要再給定SRID..Orz..
3.a 起點取source, 終點取target..丟進去算演算法
SELECT d.id, e.gid, e.name, e.length, asText(e.the_geom) FROM dijkstra_sp('edges', 77, 35) as d, edges as e where d.gid = e.gid order by d.id
記得要order by result的id..這樣路段的順序才會正確..
其實好像也沒什麼Orz..不過也是花了一兩天才把流程串起來的~ 就當個記錄吧..
另外在preparedStatement裡的?是沒辦法給定function的..所以就直接接成字串丟下去執行吧~~參數在前面做好一點的檢核判斷就好
2012年2月22日 星期三
pgRouting圖資轉換
pgRouting簡單的講是一個幫你算點間導航的open source..架構於PostgreSQL+PostGIS上..
不過要算導航~ pgRouting有演算法~ 但還是要有圖資..所以他本身也有提供一些轉圖資的cmd..把圖資轉成db的資料..
圖資變成db資料~ 主要就是以原圖資的屬性欄位為table內的欄位名(所以圖資欄位改成英文以免出事)..另外加一個key(default為gid), 以及一個地理欄位(default為the_geom)
但主要因為台灣有中文問題~ 所以還是先人眼check一下資料來源~ 欄位中文應改為英文..以及內容編碼(big5/UTF-8)的差異..還有坐標系
1. 先取得.shp File
PS: 欄位名為英文, 內容編碼為Big5, 並確認坐標系為WGS84(經緯度)
SRID
TWD97=3826
TWD67=3821
WGS86=4326
2. 至pg安裝路徑下之 bin\(edges為產出的table name~ 可以自己改)
shp2pgsql -s 4326 -W big5 -i -I "D:\project\圖資\AllRoad\tAllRoad.shp" edges > "D:\project\圖資\AllRoad\edges.sql"
3. Import SQL File to Postgres SQL, 至postgres bin\
(Drop edges if it exist)
psql --host [host ip] --username [user] --dbname [database name] -f D:\project\圖資\AllRoad\edges.sql
另外是有碰到可能是資料內容有亂碼或是難字之類的~ 會造成產生的sql有問題~
後來是用另一個解法解~ 就是去裝一個open source的gis工具: QGIS..朋友介紹使用的..雖然會當機~ 不過聽說情況有比之前的版本好了Orz..沒辦法~ 沒人沒錢沒知識又要上~
Quantum GIS裡面有一個工具..
先開shp檔起來..看一看有沒有什麼不對的地方..
附加元件-->Shape檔匯出至PostGIS
設定連線..設定幾合欄位名稱..接著放著給他跑到天荒地老..
似乎他對於編碼的解讀比較好的樣子Orz..反正就是這樣子可以把中文字正確的寫進去就好~囧rz
不過要算導航~ pgRouting有演算法~ 但還是要有圖資..所以他本身也有提供一些轉圖資的cmd..把圖資轉成db的資料..
圖資變成db資料~ 主要就是以原圖資的屬性欄位為table內的欄位名(所以圖資欄位改成英文以免出事)..另外加一個key(default為gid), 以及一個地理欄位(default為the_geom)
但主要因為台灣有中文問題~ 所以還是先人眼check一下資料來源~ 欄位中文應改為英文..以及內容編碼(big5/UTF-8)的差異..還有坐標系
1. 先取得.shp File
PS: 欄位名為英文, 內容編碼為Big5, 並確認坐標系為WGS84(經緯度)
SRID
TWD97=3826
TWD67=3821
WGS86=4326
2. 至pg安裝路徑下之 bin\(edges為產出的table name~ 可以自己改)
shp2pgsql -s 4326 -W big5 -i -I "D:\project\圖資\AllRoad\tAllRoad.shp" edges > "D:\project\圖資\AllRoad\edges.sql"
3. Import SQL File to Postgres SQL, 至postgres bin\
(Drop edges if it exist)
psql --host [host ip] --username [user] --dbname [database name] -f D:\project\圖資\AllRoad\edges.sql
另外是有碰到可能是資料內容有亂碼或是難字之類的~ 會造成產生的sql有問題~
後來是用另一個解法解~ 就是去裝一個open source的gis工具: QGIS..朋友介紹使用的..雖然會當機~ 不過聽說情況有比之前的版本好了Orz..沒辦法~ 沒人沒錢沒知識又要上~
Quantum GIS裡面有一個工具..
先開shp檔起來..看一看有沒有什麼不對的地方..
附加元件-->Shape檔匯出至PostGIS
設定連線..設定幾合欄位名稱..接著放著給他跑到天荒地老..
似乎他對於編碼的解讀比較好的樣子Orz..反正就是這樣子可以把中文字正確的寫進去就好~囧rz
pgRouting
自從在上個公司玩起PostgreSQL..免費的~ 又可以外掛PostGIS做地理資訊運算~
對於沒什麼數學概念的偶還真是一大救星...
在現在的公司裡~ 還是碰到導航的問題~ 還好當時有記得幾個關鍵字..有跟幾個做這方面的人套好交情可以問問題XD..就把這open source project給建出來了~~
底下分享一下偶在Windows版本的安裝方式..
1. 先安裝PostgreSQL..因之前專案使用8.4版就一直延用下來
2. 安裝PostGIS 1.5, 可以在安裝PostgreSQL時就選擇plugin一路安裝..或是另外找postgis的安裝檔..裝完後主要就是會多了一個templete_postgis database, 內有780左右的function, 2個table..跟geom相關的形態..之後有需要postgis功能只要在建資料庫時使用這個做樣版建立就有了~ 很方便..
3. 接下來要安裝pgRouting這個project..因為沒有找到win包裝的~ 所以就手動來裝了..先下載檔案..記得要找符合pg版本的~ 我是用1.03_pg_8.4.2..(最後的.2好像沒差~ 只要8.4的都可以裝起來)..
3.a 將lib下的DLL檔(有三個)~ copy到pg安裝路徑下..PostgreSQL/8.4/lib..重起pg DB Server把dll讀進去
3.b create database for pgrouting..使用templet_postgis建立新db..我這邊database name是叫pgrouting2, owner若有需要變更建議在create db前就先建好直接assign~ 以免後面要改一堆owner很麻煩..
3.c 至cmd 模式下依序執行Share/Contrib裡面的三隻sql檔..必須使用管理者權限的user執行~ 不然會有權限問題..另外需指定db
psql --host localhost --username postgres --dbname pgrouting2 -f [dir]\Share\Contrib\routing_core.sql
psql --host localhost --username postgres --dbname pgrouting2 -f [dir]\Share\Contrib\routing_core_wrappers.sql
psql --host localhost --username postgres --dbname pgrouting2 -f [dir]\Share\Contrib\routing_topology.sql
3.d 若上述出現plpgsql未啟用..執行底下這一行(可於pgAdminIII/phppgadmin連線至該database的sql語法欄執行即可)
CREATE LANGUAGE 'plpgsql'
到上面應該就把pgrouting的function裝好了~ 至該database下看應該看到function會增加到800+..不過~ 裝了很多次~ 裝出來是820左右..但是到後面用db用備份回存時又不一樣了~ 不過總之有用到的function能動就好了~ 不管他了Orz..
4. 創建Table 額外演算 資訊
ALTER TABLE edges ADD COLUMN source integer;
ALTER TABLE edges ADD COLUMN target integer;
計算source/target (要等時間)
SELECT assign_vertex_id('edges', 0.0001, 'the_geom', 'gid');
//dijsktra演算法----
5. 設定權重length
ALTER TABLE edges ADD COLUMN length double precision;
計算要等
UPDATE edges SET length = st_length(the_geom::geography);
設定Index
CREATE INDEX source_idx ON edges(source);
CREATE INDEX target_idx ON edges(target);
CREATE INDEX geom_idx ON edges USING GIST(the_geom GIST_GEOMETRY_OPS);
測試Dijsktra演算法
SELECT * FROM dijkstra_sp('edges', 52, 35);
//astar
6. 設定點資料
ALTER TABLE edges ADD COLUMN x1 double precision;
ALTER TABLE edges ADD COLUMN y1 double precision;
ALTER TABLE edges ADD COLUMN x2 double precision;
ALTER TABLE edges ADD COLUMN y2 double precision;
計算要等
UPDATE edges SET x1 = x(startpoint(the_geom));
UPDATE edges SET y1 = y(startpoint(the_geom));
UPDATE edges SET x2 = x(endpoint(the_geom));
UPDATE edges SET y2 = y(endpoint(the_geom));
NOTE:“endpoint()” function fails for some versions of PostgreSQL (ie. 8.2.5, 8.1.9). A workaround for that problem is using the “PointN()” function instead:
UPDATE edges SET x1 = x(PointN(the_geom, 1));
UPDATE edges SET y1 = y(PointN(the_geom, 1));
UPDATE edges SET x2 = x(PointN(the_geom, NumPoints(the_geom)));
UPDATE edges SET y2 = y(PointN(the_geom, NumPoints(the_geom)));
測試astar
select * from astar_sp_delta('edges', 52, 35, 0.1);
以上建置方法是從網路上爬出來的~ 另外有做一些小修改..
the_geom存的是WGS84坐標系..算長度要轉成geography~ 資料才會是正確的路段長..
TWD97/TWD67時就不需要轉換了
道路節點範圍assign_vertex_id, 裡面的第二個參數也是看情況做調整~
對於沒什麼數學概念的偶還真是一大救星...
在現在的公司裡~ 還是碰到導航的問題~ 還好當時有記得幾個關鍵字..有跟幾個做這方面的人套好交情可以問問題XD..就把這open source project給建出來了~~
底下分享一下偶在Windows版本的安裝方式..
1. 先安裝PostgreSQL..因之前專案使用8.4版就一直延用下來
2. 安裝PostGIS 1.5, 可以在安裝PostgreSQL時就選擇plugin一路安裝..或是另外找postgis的安裝檔..裝完後主要就是會多了一個templete_postgis database, 內有780左右的function, 2個table..跟geom相關的形態..之後有需要postgis功能只要在建資料庫時使用這個做樣版建立就有了~ 很方便..
3. 接下來要安裝pgRouting這個project..因為沒有找到win包裝的~ 所以就手動來裝了..先下載檔案..記得要找符合pg版本的~ 我是用1.03_pg_8.4.2..(最後的.2好像沒差~ 只要8.4的都可以裝起來)..
3.a 將lib下的DLL檔(有三個)~ copy到pg安裝路徑下..PostgreSQL/8.4/lib..重起pg DB Server把dll讀進去
3.b create database for pgrouting..使用templet_postgis建立新db..我這邊database name是叫pgrouting2, owner若有需要變更建議在create db前就先建好直接assign~ 以免後面要改一堆owner很麻煩..
3.c 至cmd 模式下依序執行Share/Contrib裡面的三隻sql檔..必須使用管理者權限的user執行~ 不然會有權限問題..另外需指定db
psql --host localhost --username postgres --dbname pgrouting2 -f [dir]\Share\Contrib\routing_core.sql
psql --host localhost --username postgres --dbname pgrouting2 -f [dir]\Share\Contrib\routing_core_wrappers.sql
psql --host localhost --username postgres --dbname pgrouting2 -f [dir]\Share\Contrib\routing_topology.sql
3.d 若上述出現plpgsql未啟用..執行底下這一行(可於pgAdminIII/phppgadmin連線至該database的sql語法欄執行即可)
CREATE LANGUAGE 'plpgsql'
到上面應該就把pgrouting的function裝好了~ 至該database下看應該看到function會增加到800+..不過~ 裝了很多次~ 裝出來是820左右..但是到後面用db用備份回存時又不一樣了~ 不過總之有用到的function能動就好了~ 不管他了Orz..
4. 創建Table 額外演算 資訊
ALTER TABLE edges ADD COLUMN source integer;
ALTER TABLE edges ADD COLUMN target integer;
計算source/target (要等時間)
SELECT assign_vertex_id('edges', 0.0001, 'the_geom', 'gid');
//dijsktra演算法----
5. 設定權重length
ALTER TABLE edges ADD COLUMN length double precision;
計算要等
UPDATE edges SET length = st_length(the_geom::geography);
設定Index
CREATE INDEX source_idx ON edges(source);
CREATE INDEX target_idx ON edges(target);
CREATE INDEX geom_idx ON edges USING GIST(the_geom GIST_GEOMETRY_OPS);
測試Dijsktra演算法
SELECT * FROM dijkstra_sp('edges', 52, 35);
//astar
6. 設定點資料
ALTER TABLE edges ADD COLUMN x1 double precision;
ALTER TABLE edges ADD COLUMN y1 double precision;
ALTER TABLE edges ADD COLUMN x2 double precision;
ALTER TABLE edges ADD COLUMN y2 double precision;
計算要等
UPDATE edges SET x1 = x(startpoint(the_geom));
UPDATE edges SET y1 = y(startpoint(the_geom));
UPDATE edges SET x2 = x(endpoint(the_geom));
UPDATE edges SET y2 = y(endpoint(the_geom));
NOTE:“endpoint()” function fails for some versions of PostgreSQL (ie. 8.2.5, 8.1.9). A workaround for that problem is using the “PointN()” function instead:
UPDATE edges SET x1 = x(PointN(the_geom, 1));
UPDATE edges SET y1 = y(PointN(the_geom, 1));
UPDATE edges SET x2 = x(PointN(the_geom, NumPoints(the_geom)));
UPDATE edges SET y2 = y(PointN(the_geom, NumPoints(the_geom)));
測試astar
select * from astar_sp_delta('edges', 52, 35, 0.1);
以上建置方法是從網路上爬出來的~ 另外有做一些小修改..
the_geom存的是WGS84坐標系..算長度要轉成geography~ 資料才會是正確的路段長..
TWD97/TWD67時就不需要轉換了
道路節點範圍assign_vertex_id, 裡面的第二個參數也是看情況做調整~
訂閱：
文章 (Atom)