Tag Archives: postgis

Postgres Query with GPS distance calculations without PostGIS

Let’s consider the following scenario:
You have a Postgres Database and an addresses table which stores items with GPS latitude and longitude values. You want to select all the items located inside a circular area around a GPS point you have.

You can write your SQL query without using PostGIS extension.
Let’s define the following data:

1. table: streets
id | name | latitude | longitude

2. GPS_CENTER_LATITUDE, GPS_CENTER_LONGITUDE = coordinates of the center area point around which you wanna search streets

3. AREARADIUS = the area’s radius expressed in meters

select 
	count(*) 
from 
	(
		select 
			(acos(sin(radians(s.latitude)) * sin(radians(GPS_CENTER_LATITUDE)) + cos(radians(s.latitude)) * cos(radians(GPS_CENTER_LATITUDE)) * cos(radians(s.longitude-GPS_CENTER_LONGITUDE))) * 6371 * 1000) computedDistance, 
			s.name
		from 
			streets s 
	) 
as tempQuery 
where 
	computedDistance < AREARADIUS;