PostGis 擺脫了表中的 lat long

這個問題與這個問題有關： 按距離排序

現在我的桌子發生了一些變化。表格cafes現在沒有緯度、經度列，而是有addresses包含此資訊的表格。

我遵循了已接受答案的建議：

1. 創建了地址表中呼叫的類型列geometry geography``lonlat
2. 添加索引CREATE INDEX addresses_lonlat_gist ON cafes USING gist(lonlat)
3. lonlat從地址中已經存在的緯度和經度列中的值 更新UPDATE addresses SET lonlat = ST_SetSRID(ST_MakePoint(longitude, latitude), 4326);

因此，當從 cafe 尋找附近的咖啡館時，我的查詢如下所示（Erwins 查詢）：

SELECT a.cafe_id, c.id, ST_Distance(t.x, lonlat) AS distance 
FROM cafes 
 inner join addresses a on cafes.id = a.cafe_id
   , (SELECT ST_GeographyFromText('SRID=4326;POINT(-76.000000 39.000000)')) AS t(x)
WHERE ST_DWithin(t.x, lonlat, 4828)
ORDER BY distance;

我永遠不會從地理位置搜尋附近的咖啡館。我總是會從一家咖啡館尋找附近的咖啡館。

所以我的意圖不是從 points 獲得附近的咖啡館-76.000000 39.000000，而是從x現有咖啡館的 id 的咖啡館找到附近的咖啡館。

我的問題是這裡的緯度/經度列是多餘的嗎？我應該刪除它們嗎？我可以根據我的幾何列找到附近的咖啡館lonlat嗎？

問題更新：

這是我現在的查詢，正如您在下面寫的，解釋分析：

explain analyze SELECT c.id, a.postcode, ST_Distance(t.lonlat, a.lonlat) AS dt
FROM   cafes c
JOIN   addresses     a ON a.cafe_id = c.id
   , (SELECT a0.lonlat FROM addresses a0 WHERE a0.cafe_id = 10) t
WHERE ST_DWithin(t.lonlat, a.lonlat, 4828.03)
and c.id != 10
ORDER  BY dt;

這是輸出：

"Sort  (cost=29.85..29.86 rows=1 width=124) (actual time=7.690..7.752 rows=609 loops=1)"
"  Sort Key: (_st_distance(a0.lonlat, a.lonlat, 0::double precision, true))"
"  Sort Method: quicksort  Memory: 68kB"
"  ->  Nested Loop  (cost=4.30..29.84 rows=1 width=124) (actual time=0.641..7.298 rows=609 loops=1)"
"        ->  Nested Loop  (cost=4.30..29.12 rows=1 width=124) (actual time=0.546..2.653 rows=614 loops=1)"
"              ->  Index Scan using index_addresses_on_cafe_id on addresses a0  (cost=0.00..8.28 rows=1 width=56) (actual time=0.007..0.009 rows=1 loops=1)"
"                    Index Cond: (cafe_id = 10)"
"              ->  Bitmap Heap Scan on addresses a  (cost=4.30..20.83 rows=1 width=68) (actual time=0.536..2.478 rows=614 loops=1)"
"                    Recheck Cond: (lonlat && _st_expand(a0.lonlat, 4828::double precision))"
"                    Filter: ((a0.lonlat && _st_expand(lonlat, 4828::double precision)) AND _st_dwithin(a0.lonlat, lonlat, 4828::double precision, true))"
"                    Rows Removed by Filter: 15"
"                    ->  Bitmap Index Scan on index_addresses_on_lonlat  (cost=0.00..4.30 rows=4 width=0) (actual time=0.306..0.306 rows=629 loops=1)"
"                          Index Cond: (lonlat && _st_expand(a0.lonlat, 4828::double precision))"
"        ->  Index Only Scan using cafes_pkey on cafes u  (cost=0.00..0.46 rows=1 width=4) (actual time=0.003..0.003 rows=1 loops=614)"
"              Index Cond: (id = a.cafe_id)"
"              Filter: (id <> 10)"
"              Rows Removed by Filter: 0"
"              Heap Fetches: 610"

似乎它沒有拾取地理列上的索引。這是表定義：

                                      Table "public.addresses"
    Column     |            Type             |                       Modifiers                        
----------------+-----------------------------+--------------------------------------------------------
id             | integer                     | not null default nextval('addresses_id_seq'::regclass)
address1       | character varying(255)      | 
address2       | character varying(255)      | 
town           | character varying(255)      | 
county         | character varying(255)      | 
country        | character varying(255)      | 
postcode       | character varying(255)      | 
latitude       | double precision            | 
longitude      | double precision            | 
cafe_id        | integer                     | 
type           | character varying(255)      | 
created_at     | timestamp without time zone | not null
updated_at     | timestamp without time zone | not null
state          | character varying(255)      | default 'auto_verified'::character varying
verified_by_id | integer                     | 
verified_at    | timestamp without time zone | 
lonlat         | geography                   | 
Indexes:
   "addresses_pkey" PRIMARY KEY, btree (id)
   "index_addresses_on_latitude" btree (latitude)
   "index_addresses_on_longitude" btree (longitude)
   "index_addresses_on_lonlat" gist (lonlat)
   "index_addresses_on_state" btree (state)
   "index_addresses_on_cafe_id" btree (cafe_id)

Postgis 版本資訊 ( SELECT PostGIS_full_version();):

"POSTGIS="2.1.1 r12113" GEOS="3.4.2-CAPI-1.8.2 r3921" PROJ="Rel. 4.8.0, 6 March 2012" GDAL="GDAL 1.10.1, released 2013/08/26" LIBXML="2.7.8" RASTER"

參考點來自中心的咖啡館，因此您可以使用子查詢從addresses表中檢索它，而不是手動輸入：

SELECT c.*, a.*, ST_Distance(t.lonlat, a.lonlat) AS distance -- pick columns you need
FROM   addresses a
JOIN   cafes     c ON c.id = a.cafe_id
   , (SELECT lonlat
      FROM   addresses
      WHERE  cafe_id = 10
      AND    type = 'SearchAddress') t -- center cafe
WHERE  ST_DWithin(t.lonlat, a.lonlat, 4828)
ORDER  BY distance;

• 表別名失去：cafes c。
• a.cafe_id, c.id在列表中沒有任何意義SELECT，因為根據定義，它們是相同的。選擇您實際需要的列。
• 列typeorstate不應該是類型text（或character varying(255)），我建議使用enum一個充滿類型或adr_type查找表的手，然後adr_type_id int REFERENCES adr_type (adr_type_Id在addresses.
• 無論哪種方式，我還建議使用部分覆蓋索引，以便更快地查找中心座標。建立在"There can only be one of those addresses with that type."：

CREATE addresses_search_idx ON addresses (cafe_id, lonlat)
WHERE type = 'SearchAddress';

• 聊天中討論了更多細節（不知道連結會持續多久）。

引用自：https://dba.stackexchange.com/questions/63876