[postgis-users] distance_spheroid and index files

[Jonas Wolf]

Hi,

I'm new to PostGIS, and have a question.

I have a table places and index defined as follows:

CREATE TABLE places
(
     place_id    serial UNIQUE NOT NULL,
     name        text NOT NULL,
     latitude    DOUBLE PRECISION NOT NULL,
     longitude   DOUBLE PRECISION NOT NULL,
     population  int,
     PRIMARY KEY (place_id)
);
SELECT AddGeometryColumn('places','location_geom',4326,'POINT',2);

... create some records

CREATE INDEX places_idx ON places USING gist (location_geom);
VACCUM ANALYZE;
SELECT update_geometry_stats('places','location_geom');

Now I have around 60000 records in the table, and will extend this even 
further. I want to be able to perform a query such that I can get all 
places at a certain maximum distance from one particular place. Now the 
naive approach would be to perform something like this:

SELECT * FROM places
WHERE
     distance_spheroid(
         location_geom,
         (select location_geom from places where place = 'Berlin'),
         'SPHEROID["WGS 84",6378137,298.257223563]'
     )/1000 < 10;

which gives me all the cities in a 10 km radius around Berlin. 
Unfortunately, this query takes several minutes as the distance needs to 
be calculated for every single city, i.e. 60000 times. I read in the 
PostGIS documentation that this query can be optimised to use the 
spatial index, but I can't quite figure out how to do it. If somebody 
could help me out, I'd appreciate that.

Thanks, Jonas