Zh-hant:postgis conventions
Jump to navigation
Jump to 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 條件