CODEWITHSUNDEEP
sqlpostgresqlgis
ikenator
ikenator

Reputation: 680

Geographic coordinates in PostgreSQL query

There are two fields in the database table, latitude and longitude. (Other fields too, but they are not important now.)

I have a function, where are three parameters: latitude, longitude and max_distance (km).

Is there any way to construct raw PostgreSQL query that returns only the rows that represent location which is within that max_distance (km) measured from given latitude and longitude?

Example parameters latitude=59.9138699, longitude=10.7522451, max_distance=10

Upvotes: 1

Views: 3251

Answers (1)

hruske
hruske

Reputation: 2253

To do this, use postgis.

Postgis has functions such as ST_Distance and ST_PointFromText:

select * from tbl
where ST_Distance(
    ST_PointFromText('POINT(' || longitude || ' ' || latitude || ')', 4326), 
    ST_PointFromText('POINT(10.7522451 59.9138699)', 4326)
) < 10000;

To do this fast, you should use a geometry field and index it.

You should also learn a bit about spatial reference systems and SRID. SRID 4326 denotes the WGS84 coordinate system, commonly in use on the web.

Upvotes: 1

Related Questions