Take the 2-minute tour ×
Geographic Information Systems Stack Exchange is a question and answer site for cartographers, geographers and GIS professionals. It's 100% free, no registration required.

With recent postgres//postgis version, you can now use the operator <-> to find nearest rows to a point :

Indexed Nearest Neighbour Search in PostGIS

Correct me if I'm wrong, but according to my tests :

SELECT name, gid
FROM geonames
ORDER BY geom <-> st_setsrid(st_makepoint(-90,40),4326)
LIMIT 10;

is the same precision and a bit slower as if I use the Postgres Point type. Now I would like to use this new operator with Geography type to do better calculus on a spheroid.

Is it possible ?

share|improve this question
add comment

3 Answers 3

up vote 5 down vote accepted

No, the <-> operator is not defined on geography. You can use the geometry operator and a cast to geography on the resultants

ST_Distance(geom::geography, ST_GeogFromText(''))

to get the final distances, but potentially if your objects are over the poles or dateline or in the far north the initial geometry ordering of <-> won't be correct so things won't work perfectly.

share|improve this answer
add comment

You can use ST_DWithin with geography in PostGIS

--Find the nearest hospital to each school
--that is within 3000 units of the school.
-- We do an ST_DWithin search to utilize indexes to limit our search list
-- that the non-indexable ST_Distance needs to process
--If the units of the spatial reference is meters then units would be meters
SELECT DISTINCT ON (s.gid) s.gid, s.school_name, s.the_geom, h.hospital_name
    FROM schools s
        LEFT JOIN hospitals h ON ST_DWithin(s.the_geom, h.the_geom, 3000)
    ORDER BY s.gid, ST_Distance(s.the_geom, h.the_geom);

--The schools with no close hospitals
--Find all schools with no hospital within 3000 units
--away from the school.  Units is in units of spatial ref (e.g. meters, feet, degrees)
SELECT s.gid, s.school_name
    FROM schools s
        LEFT JOIN hospitals h ON ST_DWithin(s.the_geom, h.the_geom, 3000)
    WHERE h.gid IS NULL;

http://postgis.net/docs/ST_DWithin.html

share|improve this answer
add comment

You can find more information about the logical operators of postgis in another question:

Clarification of Logical Operators in a PostGIS Database

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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