How to make ST_DWithin to use meters for distance in Postgis

postgis

I have already asked a similar question here:

Find the nearest geo points across two tables with Postgis (or without)?

but now I am stuck around the correct geo-reference to calculate the distance in ST_DWithin. What I have did with my tables is creating a column geom in this way:

ALTER TABLE my_table ADD COLUMN geom geometry(Point, 4326);
update my_table set geom = ST_SetSRID(ST_MakePoint(longitude, latitude), 4326);
CREATE INDEX my_table_geom_idx ON my_table USING gist(geom);

Using this setup will take the function ST_DWithin to use as distance parameter "degrees" instead of "meters".
How can I set the geom to work with meters? Is this the right setup?

ALTER TABLE my_table ADD COLUMN geom geometry(Point, 2163);
update my_table set geom = ST_SetSRID(ST_MakePoint(longitude, latitude), 2163);
CREATE INDEX my_table_geom_idx ON my_table USING gist(geom);

Best Answer

Cast the type to geography,

SELECT ST_AsText(geom)
FROM table
WHERE ST_DWithin( geom::geography, ... )

Consider setting the type on the table to geography too.

Related Solutions

PostGIS – Removing Latitude and Longitude from Table

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).

PostgreSQL Query – How to Make Single Row Result Return Multiple Rows

A possible Solution to the Problem I found working is a step-by-step solution which looks like this:

BEGIN;

CREATE TEMP TABLE route ON COMMIT DROP AS
  SELECT seq, source, target, km, kmh, clazz, geom_way 
  FROM pgr_dijkstra('SELECT id, source, target, cost FROM de_2po_4pgr, (SELECT ST_Expand(ST_Extent(geom_vertex),0.1) as box FROM de_2po_vertex 
    WHERE id = 1362258 OR id = 1625523 LIMIT 1) as box WHERE geom_way && box.box', 
    1362258, 1625523, FALSE, FALSE) AS route
  LEFT JOIN de_2po_4pgr AS info ON route.id2 = info.id 
  ORDER BY seq;

CREATE TEMP TABLE filling (start integer, destination integer, station_id character varying(255), distance double precision) ON COMMIT DROP;
INSERT INTO filling (start, station_id) 
  SELECT 1378549, id FROM de_tt_stations AS s WHERE ST_DWithin(s.geom::geography, (SELECT ST_LineMerge(ST_union(geom_way))::geography FROM route), 1000);
  UPDATE filling SET destination = 
    (SELECT id::integer FROM de_2po_vertex ORDER BY geom_vertex <-> 
      (SELECT geom FROM de_tt_stations WHERE id = filling.station_id) 
    LIMIT 1);
WITH f AS (SELECT start, destination FROM filling)
UPDATE filling SET distance = (SELECT SUM(km) AS distance FROM (
  SELECT km FROM pgr_dijkstra('SELECT id, source, target, cost FROM de_2po_4pgr, 
    (SELECT ST_Expand(ST_Extent(geom_vertex),0.05) as box FROM de_2po_vertex WHERE id = '|| filling.start ||' OR id = '|| filling.destination ||' LIMIT 1) as box 
     WHERE geom_way && box.box', filling.start, filling.destination, FALSE, FALSE) AS route 
     LEFT JOIN de_2po_4pgr AS info ON route.id2 = info.id) as dist);
SELECT * FROM filling ORDER BY distance;

COMMIT;

Execution Time is fair with around 500ms and less. Is there any possibility to further optimize the Querys?

Best Answer

Related Solutions

PostGIS – Removing Latitude and Longitude from Table

PostgreSQL Query – How to Make Single Row Result Return Multiple Rows

Related Question