CODEWITHSUNDEEP
sqlpostgresqlpostgis
emersonthis
emersonthis

Reputation: 33378

How to cast latitude, longitude coordinates as postgres geography type

My table has two float columns for latitude and longitude coordinates.

I want to use PostGIS's ST_DWithin to find all records which are within a certain distance from a given point.

The signature of ST_DWithin expects the first two parameters to be geometry or geography datatypes so I'm pretty sure the solution is to cast the lat/lng coordinates as geography, but I can't get it to work.

Here's what doesn't work:

SELECT * 
FROM stops 
WHERE ST_DWithin( ST_GeogFromText('SRID=4326;POINT(-77.09 38.89)'),
                  ST_GeogFromText('SRID=4326;POINT(' || stops.lng || ' ' || stops.lat || ')'), 10000.0)

I get this error:

ERROR: function st_geogfromtext(unknown) does not exist

LINE 1: SELECT * FROM stops WHERE ST_DWithin( ST_GeogFromText('SRID=...

What am I doing wrong?

Upvotes: 4

Views: 2745

Answers (2)

emersonthis
emersonthis

Reputation: 33378

Woops.

Turns out the original syntax (above) was actually correct. But I missed a key step afer installing PostGIS:

psql -d <DATABASE_NAME_HERE> -c "CREATE EXTENSION postgis";

Turns out you have to enable it on each database for it to work. This is why the function was unrecognized.

As there aren't many resources out there for this, I'm going to leave the question in case it proves useful to others.

Upvotes: 3

Ethan
Ethan

Reputation: 682

An example in Gilbert Le Blanc's link seems to contain a method for casting during a function call:

SELECT ST_Distance('LINESTRING(-122.33 47.606, 0.0 51.5)'::geography, 
    'POINT(-21.96 64.15)':: geography);

Can you adapt that to your use case?

Upvotes: 1

Related Questions