Zh-hant:postgis conventions

From OpenStreetMap Wiki
Jump to: navigation, search

返回 開發 (正體中文)

找圖資

分類地標

查詢條件大致上如下,列表中僅註明 WHERE 後的條件

SELECT name,amenity,shop,ST_AsLatLonText(way,'D.DDDDD') loc
FROM planet_osm_point
WHERE ...
功能 SQL 語法
找廁所
amenity='toilets'
找單車店
shop='bicycle'
找單車停車點
amenity='bicycle_parking'
找單車租車點
amenity='bicycle_rental'
找自動販賣機
amenity='vending_machine'
找飲水地點
amenity='drinking_water'
找便利商店
shop='convenience'
找柑仔店 (不準)
shop='general'
找檳榔攤 (不準)
name LIKE '%檳榔%'
-- TODO

進階找法

功能 SQL 語法
找指定編號項目
SELECT * FROM planet_osm_polygon
WHERE osm_id IN (-3298928, -4627558)
找指定名稱的地標
SELECT name,ST_AsLatLonText(way,'D.DDDDD') pos
FROM planet_osm_point
WHERE name LIKE '%風櫃嘴%'
找指定範圍內的多邊形
(!!natural 是關鍵字,需要加引號)
SELECT osm_id,name,boundary,"natural"
FROM planet_osm_polygon
WHERE way && ST_MakeEnvelope(
   121.54432, 25.07480,
   121.54632, 25.07780
)
定點找1公里內地標
SELECT * FROM (
  SELECT name,
    ST_AsLatLonText(way,'D.DDDDD') pos,
    ST_Distance_Sphere(way, ST_Point(121.54532 25.07680)) dist
  FROM planet_osm_point
  WHERE name IS NOT NULL
    AND way &&
    ST_MakeEnvelope(
      121.54532-0.01, 25.07680-0.01,
      121.54532+0.01, 25.07680+0.01
    )
ORDER BY dist ASC
) T WHERE dist<=1000
資訊較完整的地標,分離經緯度
用於匯出 SQLite 給 App 使用
SELECT osm_id, name, ST_Y(way) lat, ST_X(way) lng,
  amenity, shop, barrier, bicycle, brand, building
FROM planet_osm_point
WHERE NAME!='' AND (
    amenity IS NOT NULL OR
    shop IS NOT NULL OR
    barrier IS NOT NULL OR
    bicycle IS NOT NULL OR
    brand IS NOT NULL OR
    building IS NOT NULL
  )
列舉座標系統資訊
用於 Mapnik XML 定義
SELECT srid,auth_name,proj4text FROM spatial_ref_sys
WHERE srid IN (3857, 4326, 900913)
-- TODO

統計

功能 SQL 語法
統計各種設施的數目
SELECT * FROM (
   SELECT amenity, COUNT(*) cnt
   FROM planet_osm_point 
   GROUP BY amenity 
   ORDER BY cnt DESC
) t WHERE cnt>=5
統計各種商店的數目
SELECT * FROM (
   SELECT shop, COUNT(*) cnt
   FROM planet_osm_point 
   GROUP BY shop
   ORDER BY cnt DESC
) t WHERE cnt>=5
統計各種分界線的數目
SELECT boundary,COUNT(boundary) cnt
FROM planet_osm_polygon
GROUP BY boundary
-- TODO

延伸閱讀

  • 圖徵資訊:用於查詢的 WHERE 條件,以及統計的 GROUP BY 條件