Using PostgreSQL-PostGIS for coordinates

Question

I'm trying to create a database for storing coordinates

Lets say X has: Latitude 20°20’20.00’’ , Longitude 20°20’20.00’’E , with 1 mile range.

Y has: Latitude 20°21’21.00 , Longitude 20°21’21.00’’

I want to know whether Y location lies within X range.

I've been trying for days to find a way to manage that; I came across quad tree, k-d tree, but I could not find a way to represent it in a database.

I finally stumbled on PostGIS but I don't know how to create and manage simple latitude longitude interaction whit it.

Patrick · Accepted Answer · 2015-04-23 02:14:59Z

2

The first thing you want to do is learn more about PostGIS.

After you installed the postgis extension, you can use the geography type for your purposes:

CREATE TABLE locations (
  id      serial PRIMARY KEY,
  loc     geography(POINT, 4326), -- See PostGIS docs for type modifiers
  rng     double precision, -- in miles, PostGIS uses km
  ...
);

CREATE INDEX locations_geo ON locations USING GIST (loc);

Your lat,long pairs go into the loc column as a geography type; PostGIS will do the spatial analysis with that data. You can now find all points within range of the X point (say, with id=12) as follows:

SELECT src.id AS src, src.rng, near.*
FROM locations near
JOIN locations src ON ST_DWithin(src.location, near.location, src.rng * 0.6215) -- miles to km
WHERE src.id = 12;

answered Apr 23, 2015 at 2:14

Patrick

33k7 gold badges73 silver badges102 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Using PostgreSQL-PostGIS for coordinates

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related