1

I'm going to build a table of places where each contains geographical coordinates (latitude and longitude).

In my basic request to the database, I want to get all records in range X km to the location with specific lat and lng.

For example, get the restaurants in the range 50 km from point: { lat: 12.345, lng: 67.891 }.

Is it right idea to use PostGIS for this kind of query? Or can I use plain SELECT query without PostGIS features?

4

Yes, you are along the right idea, IMO in using PostGIS.

The ST_Distance

ST_Distance — For geometry type Returns the 2-dimensional cartesian minimum distance (based on spatial ref) between two geometries in projected units. For geography type defaults to return spheroidal minimum distance between two geographies in meters.

Here are some examples based in the linked documentation:

--Geometry example - units in planar degrees 4326 is WGS 84 long lat unit=degrees
SELECT ST_Distance(
ST_GeomFromText('POINT(-72.1235 42.3521)',4326),
ST_GeomFromText('LINESTRING(-72.1260 42.45, -72.123 42.1546)', 4326)
);
st_distance : 0.00150567726382282

-- Geometry example - units in meters (SRID: 26986 Massachusetts state plane meters) (most accurate for Massachusetts)
SELECT ST_Distance(
ST_Transform(ST_GeomFromText('POINT(-72.1235 42.3521)',4326),26986),
ST_Transform(ST_GeomFromText('LINESTRING(-72.1260 42.45, -72.123 42.1546)', 4326),26986)
);
st_distance : 123.797937878454

  • 1
    Better to store the Coordinates as Geometry(Point) in the database, then its possible to create and use spatial indexes which will speed up the query a lot. – pLumo Sep 7 '17 at 6:09

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.