0

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);
Randomize
  • 1,183
  • 1
  • 20
  • 33

1 Answers1

0

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.

Evan Carroll
  • 63,051
  • 46
  • 242
  • 479