Tag Archives: postgres

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

			(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, 
			streets s 
as tempQuery 
	computedDistance < AREARADIUS;

Find if a point is inside a polygon with native PostgreSQL

There is the big and exhaustive PostGIS which can be installed into your PostgreSQL database and will provide you with lots of geometry functions and types you can use for your complicated geometry problems.

But what if you need, like I needed, to find out wether a point is inside a described polygon or not and you don’t want to go into all the trouble of installing PostGIS?

Well, good news. As I was happy to learn, PostgreSQL has native support for some geometry types and operators.

For instance, it supports the type polygon, which has the following syntax:

( (x1,y1) , (x2,y2), ... , (xn,yn))

It also supports the following operator which means “contains”


So, let’s put this two together and make a functional example:

-- postgresql demo for point-in-polygon with native support

create table test_polygon
("id" serial,
"data" polygon NOT NULL

insert into test_polygon (data) values ('((1,1),(12,1),(12,12),(1,12))');

-- example of query which will validate a point as being inside the polygon
select * from test_polygon where data @> '(3,4)';

-- example of query which will invalidate a point as being inside the polygon
select * from test_polygon where data @> '(24,14)';

The code is simple and powerful. If you combine it with an online tool for generating a list of coordinates for a polygon drew on a map, you can easily create systems which will compute if a GPS point is inside that map polygon.

As for PostgreSQL’s native support for geometry stuff, (which I believe is great), you can read more on the manual here: http://www.postgresql.org/docs/9.3/static/datatype-geometric.html and here: http://www.postgresql.org/docs/8.2/static/functions-geometry.html.


Useful Postgres SQL Example Codes

1. create basic demo table in postgres sql with an ENUM column and an auto-increment one

-- we create an enum type
CREATE TYPE demo_enum AS ENUM ('item1', 'item2', 'item3');

-- we create a sequence
CREATE SEQUENCE demotable_id_seq;

-- we create the actual table and we'll use the sequence for autoincrement column
-- and the type for an enum-type column
CREATE TABLE demo_table
("id" integer NOT NULL DEFAULT nextval('demotable_id_seq'),
start_date timestamp,
end_date timestamp,
demo_choice demo_enum,
demo_extern integer,
status smallint DEFAULT 1,
PRIMARY KEY ("id"));

-- we alter the sequence
ALTER SEQUENCE demotable_id_seq OWNED BY demo_table.id;

-- we add a foreign key constraint
ALTER TABLE demo_table ADD FOREIGN KEY (demo_extern) REFERENCES demo_other_table(demo_extern_column);