6

With the recent PostgreSQL/PostGIS version, you can now use the <-> operator to find the nearest rows to a point: Indexed Nearest Neighbour Search in PostGIS

According to my tests, this query:

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 than if I use the PostgreSQL Point type.

I would like to use this new operator with the Geography type to do better calculus on a spheroid. Is this possible?

6

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.

3

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

2

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

Clarification of Logical Operators in a PostGIS Database

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.