2

This question is related to this question : Order by distance

Now my tables have changed a bit. Table cafes now doesn't have latitude, longitude columns instead there are table addresses which contains this information.

I followed the advice from the accepted accepted answer :

  1. Created the geometry geography type column called lonlat in the addresses table

  2. Added index CREATE INDEX addresses_lonlat_gist ON cafes USING gist(lonlat)

  3. Updated lonlat from the values in lattitude and longitude columns already present in addresses UPDATE addresses SET lonlat = ST_SetSRID(ST_MakePoint(longitude, latitude), 4326);

So when looking for nearby cafes from cafe my query looks like this (Erwins query):

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;

I will never search for nearby cafe(s) from geo points. I will always search for nearby cafes from one cafe.

So my intention is not to get nearby cafes from points -76.000000 39.000000, but rather to find nearby cafe(s) from cafe with id x which is existing cafe.

My question is are the lat/long columns redundant here? Should I remove them? Can I get nearby cafes based on my geometry column lonlat?

Question update:

This is my query now, as you wrote below, with explain analyze :

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;

This is the output :

"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"

It seems like it's not picking up the index on the geography column. Here is table definition :

                                       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 version info (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"
  • Ideally, you would include (relevant parts of) table definitions for cafes and addresses - what you get with \d cafes in psql. And still Postgres 9.2, I assume? – Erwin Brandstetter Apr 25 '14 at 0:22
  • I see _st_expand in your EXPLAIN output. Makes me wonder what version of PostGis you are running? consider this quote from the current manual: Prior to 1.3, ST_Expand was commonly used in conjunction with && and ST_Distance to achieve the same effect and in pre-1.3.4 this function was basically short-hand for that construct. From 1.3.4, ST_DWithin uses a more short-circuit distance function which should make it more efficient than prior versions for larger buffer regions. – Erwin Brandstetter Apr 25 '14 at 21:35
  • @ErwinBrandstetter I updated question with additional info on the postgis version. Should I install other version? Is this causing my index not to be picked up? Am I even using the good column type. Currently using geography column type. – Gandalf StormCrow Apr 25 '14 at 21:40
  • No, just making sure. You might be interested in the latest release 2.1.2 but the version is not the problem here. Also, geography and geometry, both should work. I think the problem is that the current implementation of nearest neighbor search can only use the index for queries on a single constant value. I might have an idea ... – Erwin Brandstetter Apr 25 '14 at 21:48
  • Why are cafes and addresses in two separate tables? Can there be multiple addresses for a single cafe? – Erwin Brandstetter Apr 25 '14 at 21:57
3

The point of reference comes from the cafe in the center, so you can use a subquery to retrieve it from the addresses table instead of the manual input:

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;
  • Table alias was missing: cafes c.
  • Makes no sense to have a.cafe_id, c.id in the SELECT list, since those are the same by definition. Pick the columns you actually need.

  • The columns type or state shouldn't be type text (or character varying(255)), I suggest an enum for a hand full of types or a adr_type look-up table and adr_type_id int REFERENCES adr_type (adr_type_Id in addresses.

  • Either way, I also suggest a partial, covering index for a faster look-up of the center coordinate. Building on "There can only be one of those addresses with that type.":

    CREATE addresses_search_idx ON addresses (cafe_id, lonlat)
    WHERE type = 'SearchAddress';
    
  • A lot more details discussed in chat (don't know for how long the link will be good).

  • thank you for your answer. This version seems slower than the previous one with coordinates, is it possible to optimize this one? I mean if the previous one is faster I'll stick with previous one (using lat and long). What is your opinion? thanks – Gandalf StormCrow Apr 25 '14 at 20:37
  • @GandalfStormCrow: I assume you have an index on cafe_id? The geography type can only make it faster. There may be an issue with the query planner that it does not understand it can use the GiST index. Maybe a CTE or LIMIT 1 might do the trick ... I would have to play with this, starting with exact table definitions and the output of EXPLAIN ANALYZE. Add that to the question if you can't solve it. – Erwin Brandstetter Apr 25 '14 at 20:43
  • Thank you for your response, I've updated question with hopefully relevant facts, if you need some other info please do tell. By the looks of it, it doesn't seem to pick the index on the geography column. And please ignore lat/longs for now, I'll get rid of those once I'm done – Gandalf StormCrow Apr 25 '14 at 21:03

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.